Funkcje okna SQL

Wprowadzenie do Spark SQL w Pythonie

Mark Plutowski

Data Scientist

Czym jest funkcja okna SQL?

Prostsze wyrażanie operacji niż notacja kropkowa lub zapytania
Każdy wiersz używa wartości innych wierszy do obliczenia własnej wartości

Rozkład jazdy pociągów

train_id	station	time
324	San Francisco	7:59
324	22nd Street	8:03
324	Millbrae	8:16
324	Hillsdale	8:24
324	Redwood City	8:31
324	Palo Alto	8:37
324	San Jose	9:05

Dodana kolumna z czasem do następnego przystanku

train_id	station	time	time_to_next_stop
324	San Francisco	7:59	4 min
324	22nd Street	8:03	13 min
324	Millbrae	8:16	8 min
324	Hillsdale	8:24	7 min
324	Redwood City	8:31	6 min
324	Palo Alto	8:37	28 min
324	San Jose	9:05	null

Kolumna z godziną następnego przystanku

train_id	station	time	time (następny wiersz)
324	San Francisco	7:59	8:03
324	22nd Street	8:03	8:16
324	Millbrae	8:16	8:24
324	Hillsdale	8:24	8:31
324	Redwood City	8:31	8:37
324	Palo Alto	8:37	9:05
324	San Jose	9:05	null

Klauzule OVER i ORDER BY

query = """
SELECT train_id, station, time, 
LEAD(time, 1) OVER (ORDER BY time) AS time_next 
FROM sched 
WHERE train_id=324 """

spark.sql(query).show()

+--------+-------------+-----+---------+
|train_id|      station| time|time_next|
+--------+-------------+-----+---------+
|     324|San Francisco|7:59 |    8:03 |
|     324|  22nd Street|8:03 |    8:16 |
|     324|     Millbrae|8:16 |    8:24 |
|     324|    Hillsdale|8:24 |    8:31 |
|     324| Redwood City|8:31 |    8:37 |
|     324|    Palo Alto|8:37 |    9:05 |
|     324|     San Jose|9:05 |    null |
+--------+-------------+-----+---------+

Klauzula PARTITION BY

SELECT 
train_id, 
station, 
time, 
LEAD(time,1) OVER (PARTITION BY train_id ORDER BY time) AS time_next 
FROM sched

Wynik dodania klauzuli PARTITION BY

+--------+-------------+-----+---------+
|train_id|      station| time|time_next|
+--------+-------------+-----+---------+
|     217|       Gilroy|6:06 |    6:15 |
|     217|   San Martin|6:15 |    6:21 |
|     217|  Morgan Hill|6:21 |    6:36 |
|     217| Blossom Hill|6:36 |    6:42 |
|     217|      Capitol|6:42 |    6:50 |
|     217|       Tamien|6:50 |    6:59 |
|     217|     San Jose|6:59 |    null |
|     324|San Francisco|7:59 |    8:03 |
|     324|  22nd Street|8:03 |    8:16 |
|     324|     Millbrae|8:16 |    8:24 |
|     324|    Hillsdale|8:24 |    8:31 |
|     324| Redwood City|8:31 |    8:37 |
|     324|    Palo Alto|8:37 |    9:05 |
|     324|     San Jose|9:05 |    null |
+--------+-------------+-----+---------+

train_id	station	time	time_to_next_stop
324	San Francisco	7:59	4 min
324	22nd Street	8:03	13 min
324	Millbrae	8:16	8 min
324	Hillsdale	8:24	7 min
324	Redwood City	8:31	6 min
324	Palo Alto	8:37	28 min
324	San Jose	9:05	null

Czas na ćwiczenia!

Wprowadzenie do Spark SQL w Pythonie