BigQueryで文字列解析 #SQL

前置き

BigQueryでデータ解析（分析）をしたいが、 データが文字列 になっていて断念したということはないだろうか？
今回は文字列を分解しつつ解析する方法を解説したい。

お題

ブログの記事別で、 空の状態について特定の状態から変わるのに掛かった時間 を知りたい。

例の文字列

本文更新:
今日は太郎くんと野球をした。
途中で花子さんが合流した。
+野球に飽きたあとは家でゲームをした。
夏休みの宿題をやる気が起きない。
+宿題なんて無くなればいいのに。

更新日時:2018/08/02 15:08

空の状態:晴れ
更新日時:2018/08/02 15:26
メモ:更新無し

空の状態:曇り
更新日時:2018/08/02 17:12

空の状態:曇り
更新日時:2018/08/03 10:49
メモ: 宿題のことをお母さんに見つかると怒られてしまう！

本文更新:
今日は太郎くんと野球をした。
途中で花子さんが合流した。
野球に飽きたあとは家でゲームをした。
-夏休みの宿題をやる気が起きない。
-宿題なんて無くなればいいのに。

更新日時:2018/08/03 17:24

空の状態:雨
更新日時:2018/08/05 10:55

ポイント

更新日時の出現パターン

更新日時が2つのパターンで出現している

解決策

空の状態と対になる形で更新日時を抽出する

WITH statuses_and_times AS (
    SELECT
        id
        , REGEXP_EXTRACT_ALL(history, r'空の状態:([^\s]+)') AS statuses
        , REGEXP_EXTRACT_ALL(history, r'空の状態:[^\s]+\n更新日時:([^\n]+)') AS times
    FROM
        blog_contents
)
, expand_status AS (
    SELECT
        id
        , status
        , times[SAFE_OFFSET(offset)] AS time
    FROM
        statuses_and_times
        , UNNEST(statuses) AS status WITH OFFSET AS offset
)

空の状態が変わらないパターン

空の状態は変わらないが、ログに連続で出現している

解決策

旅人算で集約し、空の状態の更新日時を抽出する

-- 続き
, grouped_status AS (
    SELECT
        id
        , status
        , MIN(time) AS _time
        , distance
    FROM (
        SELECT
            id
            , status
            , time
            , ROW_NUMBER() OVER (ORDER BY id, time) -
                ROW_NUMBER() OVER (PARTITION BY id, status ORDER BY time) AS distance
        FROM
            expand_status
    )
    GROUP BY
         id, status, distance
    ORDER BY
        id, _time
)

空の状態が変わった後の時間を取得しなければならない

経過時間を知りたいため、SQL出力結果で他の行の値を参照する

解決策

ウィンドウ関数で参照する

-- 続き
, with_next AS (
    SELECT
        id
        , status
        , _time AS time
        , LEAD(status) OVER (PARTITION BY id ORDER BY _time) AS next_status
        , LEAD(_time) OVER (PARTITION BY id ORDER BY _time) AS next_time
    from
        grouped_status
)

まとめ

手順

解析したいペアを作る
連続する行を集約する
次の行のデータを参照し、1行に集約する（ここのステップはなくてもOK）
最終的に出したかった結果を演算する

出力結果

-- 続き
SELECT
    id
    , status
    , time
    , next_status
    , next_time
    , TIMESTAMP_DIFF(
        PARSE_TIMESTAMP('%Y/%m/%d %H:%M', next_time, 'Asia/Tokyo')
        , PARSE_TIMESTAMP('%Y/%m/%d %H:%M', time, 'Asia/Tokyo')
        , MINUTE) AS minutes
FROM
    with_next
WHERE
    status = '曇り'
AND
    next_status IS NOT NULL

ROW	id	status	time	next_status	next_time	minutes
1	1	曇り	2018/08/02 17:12	雨	2018/08/05 10:55	3943

あとがき

さて、あなたの身近に同じような手順で解決できる課題はあったでしょうか？
もっとスマートな方法がある！などありましたらコメントください。
みんなでデータ解析（分析）をエンジョイしましょう！