Funciones ventana en SQL
Introducción a Spark SQL en Python
Mark Plutowski
Data Scientist
¿Qué es una función ventana en SQL?
- Expresa operaciones más simple que la notación de puntos o consultas
- Cada fila usa valores de otras filas para calcular su valor
Un horario de tren
| train_id | estación | hora |
|---|---|---|
| 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 |
Columna con tiempo hasta la siguiente parada
| train_id | estación | hora | tiempo_hasta_siguiente_parada |
|---|---|---|---|
| 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 |
Columna con la hora de la siguiente parada
| train_id | estación | hora | hora (fila siguiente) |
|---|---|---|---|
| 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 |
Cláusulas OVER y 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 |
+--------+-------------+-----+---------+
Cláusula PARTITION BY
SELECT
train_id,
station,
time,
LEAD(time,1) OVER (PARTITION BY train_id ORDER BY time) AS time_next
FROM sched
Resultado al añadir 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 | estación | hora | tiempo_hasta_siguiente_parada |
|---|---|---|---|
| 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 |
¡Vamos a practicar!
Introducción a Spark SQL en Python
