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つのパターンで出現している
    - 解決策
        - 空の状態と **対** になる形で更新日時を抽出する

            ```sql
            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
            )
            ```

#### 空の状態が変わらないパターン
- 空の状態は変わらないが、ログに連続で出現している
    - 解決策
        - 旅人算で集約し、空の状態の更新日時を抽出する

            ```sql
            -- 続き
            , 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出力結果で他の行の値を参照する
    - 解決策
        - ウィンドウ関数で参照する

            ```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. 最終的に出したかった結果を演算する


#### 出力結果
```sql
-- 続き
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|


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