ユーザーIDを指定して◯日前のセッション数が見たい
実現したいこと
叶えたかったことは以下の通り。
- 特定のユーザーIDに絞り、セッション数が見たい
- 日付は常に「◯日前」と指定したい
- 日次でクエリを更新してスプレッドシートに反映させる、という流れを作りたかったため
- 出力する日付は指定したフォーマットにしたい
- 諸事情により。
作成したクエリ
SELECT
user_id,
FORMAT_DATE("%Y/%m/%d", CAST(CONCAT(SUBSTR(event_date, 1, 4), '-', SUBSTR(event_date, 5, 2), '-', SUBSTR(event_date, 7, 2) ) as DATE)) as event_date,
count(distinct (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number')) AS ga_session_count
FROM
`テーブル名`
WHERE
_TABLE_SUFFIX = FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 2 DAY))
and user_id in ("10001, 10002, 10003......")
GROUP BY user_id, event_date
ORDER BY user_id;
自分用 - 何がおこってるかメモ
SELECT
-- ▼ まずはユーザーIDがほしい
user_id,
-- ▼ 日付の型を変換しないとFORMAT_DATEが使えないっぽい。「参照」に記載しているリンクから拝借。
FORMAT_DATE("%Y/%m/%d", CAST(CONCAT(SUBSTR(event_date, 1, 4), '-', SUBSTR(event_date, 5, 2), '-', SUBSTR(event_date, 7, 2) ) as DATE)) as event_date,
-- ▼ 「その日のセッションが何回あったか」をカウントするには ga_session_number を数えればよさそう
count(distinct (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number')) AS ga_session_count
FROM
`テーブル名`
WHERE
-- ▼ 欲しい日付は「◯日前」指定なので、CURRENT_DATE に対して「◯日前」という指定をしてみた。以下は「2日前」を指定。
_TABLE_SUFFIX = FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 2 DAY))
-- ▼ 欲しいユーザーの対象をここで絞る
and user_id in ("10001, 10002, 10003......")
-- ▼ 同じユーザーIDと日付が出るので、重複する行はまとめる
GROUP BY user_id, event_date
ORDER BY user_id;
参照
SELECT
user_id,
FORMAT_DATE("%Y/%m/%d", CAST(CONCAT(SUBSTR(event_date, 1, 4), '-', SUBSTR(event_date, 5, 2), '-', SUBSTR(event_date, 7, 2) ) as DATE)) as event_date,
count(distinct (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number')) AS ga_session_count
FROM
`テーブル名`
WHERE
_TABLE_SUFFIX = FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 2 DAY))
and user_id in ("10001, 10002, 10003......")
GROUP BY user_id, event_date
ORDER BY user_id;
SELECT
-- ▼ まずはユーザーIDがほしい
user_id,
-- ▼ 日付の型を変換しないとFORMAT_DATEが使えないっぽい。「参照」に記載しているリンクから拝借。
FORMAT_DATE("%Y/%m/%d", CAST(CONCAT(SUBSTR(event_date, 1, 4), '-', SUBSTR(event_date, 5, 2), '-', SUBSTR(event_date, 7, 2) ) as DATE)) as event_date,
-- ▼ 「その日のセッションが何回あったか」をカウントするには ga_session_number を数えればよさそう
count(distinct (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number')) AS ga_session_count
FROM
`テーブル名`
WHERE
-- ▼ 欲しい日付は「◯日前」指定なので、CURRENT_DATE に対して「◯日前」という指定をしてみた。以下は「2日前」を指定。
_TABLE_SUFFIX = FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 2 DAY))
-- ▼ 欲しいユーザーの対象をここで絞る
and user_id in ("10001, 10002, 10003......")
-- ▼ 同じユーザーIDと日付が出るので、重複する行はまとめる
GROUP BY user_id, event_date
ORDER BY user_id;
参照
BigQueryでstring型の文字列からdate型の日付に変換する方法
BigQueryビギナーなので、間違ってたりもっと良いやり方があったりしたら教えてください🙌
Author And Source
この問題について(ユーザーIDを指定して◯日前のセッション数が見たい), 我々は、より多くの情報をここで見つけました https://qiita.com/nacky_design/items/3eaa6378792e213f7d6f著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .