BigQueryで文字列解析

前置き

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

出力結果

-- 続き
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

あとがき

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