Oktatóanyag: Adattavak felderítése és elemzése kiszolgáló nélküli SQL-készlettel

Ebből az oktatóanyagból megtudhatja, hogyan végezhet feltáró jellegű adatelemzést. Különböző Azure Open-adatkészleteket kombinálhat kiszolgáló nélküli SQL-készlet használatával. Ezután az eredményeket az Azure Synapse Analytics Synapse Studio-ben jelenítheti meg.

A OPENROWSET(BULK...) függvény lehetővé teszi a fájlok elérését az Azure Storage-ban. [OPENROWSET](develop-openrowset.md) Beolvassa egy távoli adatforrás tartalmát, például egy fájlt, és sorhalmazként adja vissza a tartalmat.

Automatikus sémakövetkeztetés

Mivel az adatok Parquet-fájlformátumban tárolódnak, az automatikus sémakövetkeztetés is elérhető. Az adatokat anélkül kérdezheti le, hogy felsorolja a fájlok összes oszlopának adattípusát. A virtuális oszlop mechanizmusát és a filepath függvényt is használhatja a fájlok egy bizonyos részhalmazának szűréséhez.

Megjegyzés

Az alapértelmezett rendezés a következő SQL_Latin1_General_CP1_CI_ASIf: . Nem alapértelmezett rendezés esetén vegye figyelembe a kis- és nagybetűk érzékenységét.

Ha az oszlopok megadásakor kis- és nagybetűk megkülönböztetésével rendelkező adatbázist hoz létre, ügyeljen arra, hogy az oszlop helyes nevét használja.

Az oszlopnév tpepPickupDateTime helyes lenne, ha tpeppickupdatetime nem alapértelmezett rendezésben működne.

Ez az oktatóanyag a New York City (NYC) Taxi adatkészletét használja:

• Átvétel és legördülő lista dátumai és időpontjai
• Pick-up és drop-off helyek
• Utazási távolságok
• Tételes viteldíjak
• Sebességtípusok
• Fizetési típusok
• Sofőr által jelentett utasok száma

Az NYC Taxi adatainak megismeréséhez futtassa a következő lekérdezést:

SELECT TOP 100 * FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [nyc]

Hasonlóképpen az ünnepnapok adathalmazát az alábbi lekérdezéssel kérdezheti le:

SELECT TOP 100 * FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/holidaydatacontainer/Processed/*.parquet',
        FORMAT='PARQUET'
    ) AS [holidays]

A Weather Data adatkészletet az alábbi lekérdezéssel is lekérdezheti:

SELECT
    TOP 100 *
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/isdweatherdatacontainer/ISDWeather/year=*/month=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [weather]

Az egyes oszlopok jelentéséről az adathalmazok leírásában olvashat bővebben:

• NYC Taxi
• Munkaszüneti
• Időjárási adatok

Idősor, szezonalitás és kiugró elemzés

A taxis utazások éves számát az alábbi lekérdezéssel összegezheti:

SELECT
    YEAR(tpepPickupDateTime) AS current_year,
    COUNT(*) AS rides_per_year
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [nyc]
WHERE nyc.filepath(1) >= '2009' AND nyc.filepath(1) <= '2019'
GROUP BY YEAR(tpepPickupDateTime)
ORDER BY 1 ASC

Az alábbi kódrészlet a taxis utazások éves számának eredményét mutatja be:

Az adatok Synapse Studio vizualizálhatók a Táblázat nézetről a Diagram nézetre való váltással. Különböző diagramtípusok közül választhat, például Terület, Sáv, Oszlop, Vonal, Kör és Pont. Ebben az esetben ábrázolja az Oszlop diagramot a Kategória oszlop current_year:

Ebből a vizualizációból látható az évek során csökkenő menetszámok trendje. Ez a csökkenés feltehetően a ride-sharing cégek közelmúltbeli megnövekedett népszerűségének köszönhető.

Megjegyzés

Az oktatóanyag írásakor a 2019-hez tartozó adatok hiányosak. Ennek eredményeképpen hatalmas csökkenés tapasztalható az adott évben a túrák számában.

Az elemzést egyetlen évre, például 2016-ra összpontosíthatja. Az alábbi lekérdezés az adott év során a túrák napi számát adja vissza:

SELECT
    CAST([tpepPickupDateTime] AS DATE) AS [current_day],
    COUNT(*) as rides_per_day
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [nyc]
WHERE nyc.filepath(1) = '2016'
GROUP BY CAST([tpepPickupDateTime] AS DATE)
ORDER BY 1 ASC

Az alábbi kódrészlet a lekérdezés eredményét jeleníti meg:

Az adatokat úgy is megjelenítheti, hogy az Oszlop diagramot a Kategória oszlopot current_day , a Jelmagyarázat (sorozat) oszlopot pedig rides_per_day értékre állítja.

A diagramon látható egy heti minta, amelynek csúcsnapja a szombat. A nyári hónapokban a nyaralások miatt kevesebb taxival járnak. Azt is figyelje meg, hogy a taxis utazások száma jelentős mértékben csökken, anélkül, hogy egyértelmű mintázatot mutatnának, hogy mikor és miért fordulnak elő.

Ezután ellenőrizze, hogy a túrák csökkenése korrelál-e a munkaszüneti napokkal. Ellenőrizze, hogy van-e korreláció az NYC Taxi rides adathalmazhoz való csatlakozással az ünnepnapok adathalmazával:

WITH taxi_rides AS (
SELECT
    CAST([tpepPickupDateTime] AS DATE) AS [current_day],
    COUNT(*) as rides_per_day
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [nyc]
WHERE nyc.filepath(1) = '2016'
GROUP BY CAST([tpepPickupDateTime] AS DATE)
),
public_holidays AS (
SELECT
    holidayname as holiday,
    date
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/holidaydatacontainer/Processed/*.parquet',
        FORMAT='PARQUET'
    ) AS [holidays]
WHERE countryorregion = 'United States' AND YEAR(date) = 2016
),
joined_data AS (
SELECT
    *
FROM taxi_rides t
LEFT OUTER JOIN public_holidays p on t.current_day = p.date
)

SELECT 
    *,
    holiday_rides = 
    CASE   
      WHEN holiday is null THEN 0   
      WHEN holiday is not null THEN rides_per_day
    END   
FROM joined_data
ORDER BY current_day ASC

Emelje ki a tömegközlekedési utak számát a munkaszüneti napokon. Ehhez válassza a Kategória oszlop current_day, rides_per_day és holiday_ridesjelmagyarázat (sorozat) oszlopként.

A diagramon látható, hogy munkaszüneti napokon a taxis utazások száma alacsonyabb. Január 23-án még mindig van egy megmagyarázhatatlan nagy visszaesés. A Weather Data adathalmaz lekérdezésével ellenőrizzük az adott napon az NYC időjárását:

SELECT
    AVG(windspeed) AS avg_windspeed,
    MIN(windspeed) AS min_windspeed,
    MAX(windspeed) AS max_windspeed,
    AVG(temperature) AS avg_temperature,
    MIN(temperature) AS min_temperature,
    MAX(temperature) AS max_temperature,
    AVG(sealvlpressure) AS avg_sealvlpressure,
    MIN(sealvlpressure) AS min_sealvlpressure,
    MAX(sealvlpressure) AS max_sealvlpressure,
    AVG(precipdepth) AS avg_precipdepth,
    MIN(precipdepth) AS min_precipdepth,
    MAX(precipdepth) AS max_precipdepth,
    AVG(snowdepth) AS avg_snowdepth,
    MIN(snowdepth) AS min_snowdepth,
    MAX(snowdepth) AS max_snowdepth
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/isdweatherdatacontainer/ISDWeather/year=*/month=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [weather]
WHERE countryorregion = 'US' AND CAST([datetime] AS DATE) = '2016-01-23' AND stationname = 'JOHN F KENNEDY INTERNATIONAL AIRPORT'

A lekérdezés eredményei azt jelzik, hogy a taxis utazások számának csökkenése a következő miatt következett be:

• Volt egy hóvihar azon a napon NYC nehéz hó (~ 30 cm).
• Hideg volt (a hőmérséklet nulla Celsius fok alatt volt).
• Szeles volt (~10 m/s).

Ez az oktatóanyag azt mutatja be, hogy az adatelemzők hogyan végezhetnek gyorsan feltáró adatelemzést. A különböző adathalmazokat kiszolgáló nélküli SQL-készlet használatával kombinálhatja, és az eredményeket Azure Synapse Studióval jelenítheti meg.

Következő lépések

A kiszolgáló nélküli SQL-készlet Power BI Desktop és jelentések létrehozásához való csatlakoztatásáról a Kiszolgáló nélküli SQL-készlet csatlakoztatása Power BI Desktop és jelentések létrehozása című témakörben olvashat.

A külső táblák kiszolgáló nélküli SQL-készletben való használatának megismeréséhez lásd: Külső táblák használata a Synapse SQL-sel