データサイエンティストの阿部です。東京五輪開会式の選手入場を何度か見返してしまいました。冒頭とカザフスタン入場のシーンは胸が高鳴りました。
さて、明日から使いたくなるデータハンドリング術を紹介する "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 でした！次回またお会いしましょう。
