BigQueryでWIH句を使用する

BigQueryでWIH句を使用します．

架空の行動履歴データの作成
行動履歴データの加工
まとめ
参考サイト

# 架空の行動履歴データの作成

import random
import datetime
import pandas as pd

# 重複なしランダム発生
def rand_ints_nodup(a, b, k):
    ns = []
    while len(ns) < k:
        n = random.randint(a, b)
        if not n in ns:
            ns.append(n)
    return ns

# 架空の行動履歴作成
def make_probe(_list, s, e):
    for _id in range(s, e):
        # 立ち寄りスポット数
        spot_num = random.randint(1, 5)
        # 立ち寄りスポットリスト
        spot_list = rand_ints_nodup(1, 5, spot_num)

        # 日付
        dt = datetime.datetime(2018, 2, 1, 9, 15, 30)
        # 日にち加算
        dp = random.randint(1, 20)
        dt = dt + datetime.timedelta(days=dp)
        date = str(dt).split('-')[1] + str(dt).split('-')[2].split(' ')[0]
        # 時刻加算
        hp = random.randint(1, 10)
        dt = dt + datetime.timedelta(hours=hp)

        for spot in spot_list:
            dt = dt + datetime.timedelta(minutes=1)
            _list.append([date, _id+1, dt, f'spot_{spot}'])
    return _list

# 行動履歴作成
probe_list = []
probe_list = make_probe(probe_list, 0, 400)
probe_list = make_probe(probe_list, 30, 140)

# データフレーム化
df = pd.DataFrame(probe_list, columns=['date', 'id', 'o_time', 'o_spot'])
df.head()

# index date	id	o_time	o_spot
# 0	0211	1	2018-02-11 18:16:30	spot_1
# 1	0211	1	2018-02-11 18:17:30	spot_2

# 行動履歴データの加工

WIH句を使って、下記の処理を実施します。

条件1〜3で各々の出力を整理する

query = '''
WITH 
    t1 AS
    (SELECT 
        COUNT(id) AS CNT_id1, COUNT(DISTINCT id) AS CNTD_id1
    FROM
        probe
    WHERE
        条件1),
    t2 AS
    (SELECT 
        COUNT(id) AS CNT_id2, COUNT(DISTINCT id) AS CNTD_id2
    FROM
        probe
    WHERE
        条件2),
    t3 AS
    (SELECT 
        COUNT(id) AS CNT_id3, COUNT(DISTINCT id) AS CNTD_id3
    FROM
        probe
    WHERE
        条件3)
SELECT 
    t1.CNT_id1, t1.CNTD_id1, t2.CNTD_id2, t3.CNTD_id3
FROM
    t1,t2,t3
'''