データサイエンティストの阿部です。東京五輪開会式の選手入場を何度か見返してしまいました。冒頭とカザフスタン入場のシーンは胸が高鳴りました。
さて、明日から使いたくなるデータハンドリング術を紹介する "Sexy Tech for You" の第3話を配信しました。動画中に記載したクエリや補足事項を本ブログに掲載しますので、少しでも皆様のお役に立てたら幸いです。
Youtube
BigQuery クエリ
user_idとdateの例
#StandardSQL --user_idとdateでユニークなtableがあるとする。またdateは集計したい期間に関してtable内に1レコード以上あるとする WITH user_master AS (SELECT DISTINCT user_id FROM table), date_master AS (SELECT DISTINCT date FROM table) SELECT * ,AVG(views)OVER(PARTITION BY user_id ORDER BY date ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS avg_views_3b_1b FROM( SELECT A.user_id ,B.date ,IFNULL(C.views, 0) AS views FROM user_master A CROSS JOIN date_master B LEFT JOIN dat C ON A.user_id = C.user_id AND B.date = C.date )
BigQueryの一般公開データセットbigquery-public-data.wikipedia.pageviews_2021
での適用例
#StandardSQL WITH -- 一般公開データセットを利用 dat AS ( SELECT DATE(datehour) AS date ,title -- ユーザーに該当するカラムとしてtitleを使用 ,SUM(views) AS views FROM `bigquery-public-data.wikipedia.pageviews_2021` WHERE DATE(datehour) BETWEEN DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 7 DAY) AND DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY) AND wiki = 'ja' GROUP BY 1,2 ), title_master AS ( SELECT DISTINCT title FROM dat ), date_master AS ( SELECT DISTINCT date FROM dat ) SELECT * ,AVG(views)OVER(PARTITION BY title ORDER BY date ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS avg_views_3b_1b FROM( SELECT A.title ,B.date ,IFNULL(C.views, 0) AS views FROM title_master A CROSS JOIN date_master B LEFT JOIN dat C ON A.title = C.title AND B.date = C.date ) ORDER BY title, date
補足
BigQueryのwindow関数は、対象レコードが1レコードでも計算対象が存在すれば計算するため、例えば直前3日間の移動平均を計算する場合に2日目以降から値が算出されます。期間が満了しているレコードにのみ値を付与したい場合は、例えばCOUNT()OVER(PARTITION BY)
で行数をカウントして条件指定するなど、別途制御することが必要です。
以上、Moblie Tech for All のヤプリがお送りする Sexy Tech for You でした!次回またお会いしましょう。