##はじめに
分析をするにあたって、BigQueryのテーブル構造や指標の出し方に則って、
『1レコード1セッション』にデータを持つと便利。
うまくデータ取得したはずでも『1レコード1セッション』にならないとき、
日付を跨いでいるセッションにポイントがあったのでメモ。

##前提・仮説
◯前提：
　GAのセッションが切れるタイミング：
　・30分に1度
　・日にちを跨ぐとき

◯仮説
　BigQueryでの30分に一度、日にち跨ぐとセッションIDが変わっている

#問題
rawテーブルからデータを抜いてきても、1レコード1セッションにならない！
※1意性の判断には、「ユーザーID+セッションID」の複合キーの重複なしカウントを利用します。

##発見
日にち跨ぎ前後でも同じセッションIDが使われている！
(例)

--①rawデータ抜く
SELECT
fullVisitorId,
visitId,
date,
HOUR(DATE_ADD(SEC_TO_TIMESTAMP(visitStartTime), 9, 'HOUR')) AS hour, --時刻(日本時間)
CONCAT(fullVisitorId,STRING(visitId)) AS uniqSessionId --ユニークのセッションIDと定義
FROM (TABLE_DATE_RANGE( [dataset.table_],TIMESTAMP('2018-01-01'), TIMESTAMP('2018-01-02')))

--②日にち跨ぎセッションのユニークIDを指定して、データを確認
SELECT *
FROM ①で作成したテーブル WHERE uniqSessionId = 'aaaaaaaaaaaaaaaaaaabbbbbbbbbb'

fullVisitorId	visitId	date	hour	uniqSessionId
aaaaaaaaaaaaaaaa…	bbbbbbbbbb	20180101	23	aaaaaaaaaaaaaaaaaaabbbbbbbbbb
aaaaaaaaaaaaaaaa…	bbbbbbbbbb	20180102	0	aaaaaaaaaaaaaaaaaaabbbbbbbbbb

##解決策
Dateの箇所だけ初日側に寄せ、『1レコード1セッション』にする。

(例)ユニークのセッションID毎にDATEを元にSEQをふる。⇒次のテーブルでSEQが1のものだけ対象にする。

--SEQを振る
SELECT
uniqSessionId,
COUNT(*) OVER (PARTITION BY uniqSessionId ORDER BY date) AS SEQ,
CONCAT(fullVisitorId,STRING(visitId)) AS uniqSessionId --ユニークのセッションIDと定義
FROM dataset.table

※来訪時セッションを取るのはこれでOK
集約が必要なデータ(ヒット数、CV数など)については、集約関数を利用するべき。
来訪時セッション用と、集約セッション用でテーブルを作成し、新たにテーブルにまとめる。

##注意点
どうしても毀損させたくない・クレンジングしたくない要件がある場合には、
違うやり方をやってみる必要があるかも。