BigQueryのCROSS JOINとwindow関数で移動統計量算出【Sexy Tech for You #3】

データサイエンティストの阿部です。東京五輪開会式の選手入場を何度か見返してしまいました。冒頭とカザフスタン入場のシーンは胸が高鳴りました。

さて、明日から使いたくなるデータハンドリング術を紹介する "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 でした！次回またお会いしましょう。