Window Function SQL
Python Spark SQL 入門
Mark Plutowski
Data Scientist
什麼是 Window Function SQL？
- 比點記法或查詢更簡潔地表達運算
- 以其他列的值來計算每一列的結果
火車時刻表
| 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 |
新增下一站所需時間欄位
| 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 |
下一站時間欄位
| 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 |
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 |
+--------+-------------+-----+---------+
PARTITION BY 子句
SELECT
train_id,
station,
time,
LEAD(time,1) OVER (PARTITION BY train_id ORDER BY time) AS time_next
FROM sched
加入 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 |
一起來練習吧！
Python Spark SQL 入門
