長年 Window Function のない RDBMS を使っていたので触れる機会がなかったんですが、10年戦えるデータ分析入門という書籍で色々と説明してあったのでとりあえず練習。
この本では PostgreSQL を題材にしていましたが、BigQuery でも Window Function が使えることがわかったので、それでやってみました。
題材
BigQuery の Public Datasets の中には Hacker News の投稿データを持った bigquery-public-data:hacker_news.stories というデータから、2015 年 1 月の日別投稿数とその累積和を出してみます。
クエリ
とりあえずこんな感じに書いてみた。
SELECT
posted_date,
COUNT(posted_date) AS posts,
SUM(posts) OVER (ORDER BY posted_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_posts
FROM (
SELECT
STRFTIME_UTC_USEC(time_ts, '%Y-%m-%d') AS posted_date
FROM
[bigquery-public-data:hacker_news.stories]
WHERE
time_ts BETWEEN TIMESTAMP('2015-01-01 00:00:00')
AND TIMESTAMP('2015-02-01 00:00:00'))
GROUP BY
posted_date
- GROUP BY に直接
STRFTIME_UTC_USEC()関数を使うことができなかったのでサブクエリを使った。 -
SUM(posts)のpostsは直前のCOUNT(posted_date)のエイリアスだが、Window Function の実行はSELECTよりも後なので問題なし (ということなのかな?)
結果
| posted_date | posts | cumulative_posts |
|---|---|---|
| 1/1/2015 | 455 | 455 |
| 1/2/2015 | 694 | 1149 |
| 1/3/2015 | 566 | 1715 |
| 1/4/2015 | 545 | 2260 |
| 1/5/2015 | 1060 | 3320 |
| 1/6/2015 | 1094 | 4414 |
| 1/7/2015 | 1030 | 5444 |
| 1/8/2015 | 1009 | 6453 |
| 1/9/2015 | 922 | 7375 |
| 1/10/2015 | 542 | 7917 |
| 1/11/2015 | 496 | 8413 |
| 1/12/2015 | 968 | 9381 |
| 1/13/2015 | 1139 | 10520 |
| 1/14/2015 | 1177 | 11697 |
| 1/15/2015 | 1096 | 12793 |
| 1/16/2015 | 963 | 13756 |
| 1/17/2015 | 567 | 14323 |
| 1/18/2015 | 543 | 14866 |
| 1/19/2015 | 894 | 15760 |
| 1/20/2015 | 1130 | 16890 |
| 1/21/2015 | 1112 | 18002 |
| 1/22/2015 | 1041 | 19043 |
| 1/23/2015 | 980 | 20023 |
| 1/24/2015 | 508 | 20531 |
| 1/25/2015 | 539 | 21070 |
| 1/26/2015 | 1084 | 22154 |
| 1/27/2015 | 1146 | 23300 |
| 1/28/2015 | 1145 | 24445 |
| 1/29/2015 | 1170 | 25615 |
| 1/30/2015 | 950 | 26565 |
| 1/31/2015 | 555 | 27120 |
Re:dash でグラフ化
