Функції вікна в SQL

Вступ до Spark SQL у Python

Mark Plutowski

Data Scientist

Що таке функція вікна в SQL?

  • Виражайте операції простіше, ніж через крапкову нотацію або окремі запити
  • Кожен рядок використовує значення інших рядків для обчислення свого
Вступ до Spark SQL у Python

Розклад поїзда

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
Вступ до Spark SQL у Python

Додано стовпець із часом до наступної зупинки

train_id station time time_to_next_stop
324 San Francisco 7:59 4 хв
324 22nd Street 8:03 13 хв
324 Millbrae 8:16 8 хв
324 Hillsdale 8:24 7 хв
324 Redwood City 8:31 6 хв
324 Palo Alto 8:37 28 хв
324 San Jose 9:05 null
Вступ до Spark SQL у Python

Стовпець із часом наступної зупинки

train_id station time time (following row)
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
Вступ до Spark SQL у Python

Клаузи OVER та 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 |
+--------+-------------+-----+---------+
Вступ до Spark SQL у Python

Клауза PARTITION BY

SELECT 
train_id, 
station, 
time, 
LEAD(time,1) OVER (PARTITION BY train_id ORDER BY time) AS time_next 
FROM sched
Вступ до Spark SQL у Python

Результат додавання 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 |
+--------+-------------+-----+---------+
Вступ до Spark SQL у Python
train_id station time time_to_next_stop
324 San Francisco 7:59 4 хв
324 22nd Street 8:03 13 хв
324 Millbrae 8:16 8 хв
324 Hillsdale 8:24 7 хв
324 Redwood City 8:31 6 хв
324 Palo Alto 8:37 28 хв
324 San Jose 9:05 null
Вступ до Spark SQL у Python

Давайте потренуємось

Вступ до Spark SQL у Python

Preparing Video For Download...