サンプルクエリ集
クエリを書く際の参考として、KARTEのイベントデータに対するクエリを例を中心に、サンプルクエリをご紹介します。
特定イベントのレコードを取得
karte_event
のデータは、KARTEにタグ等で連携されたイベント毎にレコードが分かれています- イベント名を指定して抽出する場合は、
event_name
カラムに対してwhere句で条件指定してください
例: コンバージョンタグを取得日時順に取得
SELECT
*
FROM
{% karte_event period[0], period[1] %} -- Periodパラメータをパラメータ定義で設定
WHERE
event_name = 'buy'
ORDER BY
sync_date
SELECT
*
FROM
{{ karte_event(period[0], period[1]) }} -- Periodパラメータをパラメータ定義で設定
WHERE
event_name = 'buy'
ORDER BY
sync_date
セグメントに該当するユーザーを抽出
karte_events
テーブルのshorten_segmentsカラムには、各イベントが記録された段階で、そのユーザーが所属していた全セグメントの短縮IDが含まれています- shorten_segmentsに対して任意セグメントの短縮IDが含まれるという条件で抽出することで、セグメントに該当するユーザーを絞り込めます
- セグメントの短縮IDと名称についてはkarte_segmentテーブルを参照してください
- クエリパラメータとしてセグメントを選択させることも可能です
セグメント抽出の注意事項
shorten_segmentsの内容は各イベントが記録された際にセグメントの所属状況が更新されるため、shorten_segmentsに特定の短縮IDを含むという条件で抽出した場合には「過去そのセグメントに所属していたが現在所属していない」というユーザーが含まれます。
現在(≒KARTE側で計測された最新データ)そのセグメントに所属しているユーザーを抽出したい場合は、各ユーザーの最新データに対してshorten_segmentsのID抽出を行う必要があります。各ユーザーの最新データの抽出については、クエリコレクション上の「ユーザーの最新のセグメント」等を参照してください。
例: セグメントIDが含まれるユーザーの抽出
SELECT
*
FROM
{% karte_event period[0], period[1] %} -- Periodパラメータをパラメータ定義で設定
WHERE
shorten_segments LIKE CONCAT('%', {{segment_id}}, '%') -- Textパラメータをパラメータ定義で設定
SELECT
*
FROM
{{ karte_event(period[0], period[1]) }} -- Periodパラメータをパラメータ定義で設定
WHERE
shorten_segments LIKE '%{{segment_id}}%' -- Textパラメータをパラメータ定義で設定
JSON形式データのプロパティを取得
- JSON形式のデータを参照するには、JSON関数を使います
- 参考:
例: ユーザー情報を重複無しで取得
SELECT
user_id
, JSON_VALUE(values, '$.identify.name') name
, JSON_VALUE(values, '$.identify.email') email
FROM
{% karte_event period[0], period[1] %} -- Periodパラメータをパラメータ定義で設定
WHERE
event_name = 'identify'
GROUP BY user_id, name, email
SELECT
user_id
, JSON_VALUE(values, '$.identify.name') name
, JSON_VALUE(values, '$.identify.email') email
FROM
{{ karte_event(period[0], period[1]) }} -- Periodパラメータをパラメータ定義で設定
WHERE
event_name = 'identify'
GROUP BY user_id, name, email
文字列操作
- 文字列の結合を行うには、
CONCAT()
を使います
例: user_idを使い、KARTE管理画面上のユーザー詳細画面URLを生成
SELECT
CONCAT('https://admin.karte.io/p/{{project_id}}/user/', user_id ) URL
FROM
{% karte_event period[0], period[1] %} -- Periodパラメータをパラメータ定義で設定
WHERE
event_name = "identify"
GROUP BY user_id
SELECT
CONCAT('https://admin.karte.io/p/{{project_id}}/user/', user_id ) URL
FROM
{{ karte_event(period[0], period[1]) }} -- Periodパラメータをパラメータ定義で設定
WHERE
event_name = "identify"
GROUP BY user_id
sync_dateが最大のレコードを抽出
- 特定条件に合致するレコードの中で、sync_dateが最大のレコードを抽出します
ユーザー毎の最新の_karte_rankを抽出
MAX(sync_date)
を使った書き方
WITH events AS (
SELECT
*
, JSON_VALUE(scores, '$._karte_rank') AS _karte_rank
FROM
{% karte_event period[0], period[1] %} -- Periodパラメータをパラメータ定義で設定
)
, max_sync_date AS (
SELECT
user_id
, MAX(sync_date) AS sync_date
FROM events
WHERE _karte_rank IS NOT NULL
GROUP BY user_id
)
SELECT DISTINCT
e.user_id
, e._karte_rank
, m.sync_date
FROM events AS e
INNER JOIN max_sync_date AS m
ON
e.user_id = m.user_id
AND e.sync_date = m.sync_date
WITH events AS (
SELECT
*
, JSON_VALUE(scores, '$._karte_rank') AS _karte_rank
FROM
{{ karte_event(period[0], period[1]) }} -- Periodパラメータをパラメータ定義で設定
)
, max_sync_date AS (
SELECT
user_id
, MAX(sync_date) AS sync_date
FROM events
WHERE _karte_rank IS NOT NULL
GROUP BY user_id
)
SELECT DISTINCT
e.user_id
, e._karte_rank
, m.sync_date
FROM events AS e
INNER JOIN max_sync_date AS m
ON
e.user_id = m.user_id
AND e.sync_date = m.sync_date
ROW_NUMBER()
を使った書き方
WITH events AS (
SELECT
*
, JSON_VALUE(scores, '$._karte_rank') AS _karte_rank
, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY sync_date DESC) AS row_num
FROM
{% karte_event period[0], period[1] %} -- Periodパラメータをパラメータ定義で設定
WHERE JSON_VALUE(scores, '$._karte_rank') IS NOT NULL
)
SELECT DISTINCT
user_id
, _karte_rank
, sync_date
FROM events
WHERE row_num = 1
WITH events AS (
SELECT
*
, JSON_VALUE(scores, '$._karte_rank') AS _karte_rank
, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY sync_date DESC) AS row_num
FROM
{{ karte_event(period[0], period[1]) }} -- Periodパラメータをパラメータ定義で設定
WHERE JSON_VALUE(scores, '$._karte_rank') IS NOT NULL
)
SELECT DISTINCT
user_id
, _karte_rank
, sync_date
FROM events
WHERE row_num = 1
特定の接客サービスが表示されたユーザーのuser_idを抽出
1234567890ab1234567890ab
の部分は、実際のcampaign_idで置き換えてください
SELECT
user_id
, sync_date
, JSON_VALUE(values, "$.message_open.message.campaign_id") AS campaign_id
FROM
{% karte_event period[0], period[1] %} -- Periodパラメータをパラメータ定義で設定
WHERE
event_name = "message_open"
AND JSON_VALUE(values, "$.message_open.message.campaign_id") = "1234567890ab1234567890ab"
SELECT
user_id
, sync_date
, JSON_VALUE(values, "$.message_open.message.campaign_id") AS campaign_id
FROM
{{ karte_event(period[0], period[1]) }} -- Periodパラメータをパラメータ定義で設定
WHERE
event_name = "message_open"
AND JSON_VALUE(values, "$.message_open.message.campaign_id") = "1234567890ab1234567890ab"
さらにアクションIDで絞り込む
1234567890ab1234567890ab
の部分は、実際の接客サービスID(campaign_id)で置き換えてくださいZaZ
の部分は、実際のアクションID(shorten_id)で置き換えてください
SELECT
user_id
, sync_date
, JSON_VALUE(values, "$.message_open.message.campaign_id") AS campaign_id
, JSON_VALUE(values, "$.message_open.message.shorten_id") AS shorten_id
FROM
{% karte_event period[0], period[1] %} -- Periodパラメータをパラメータ定義で設定
WHERE
event_name = "message_open"
AND JSON_VALUE(values, "$.message_open.message.campaign_id") = "1234567890ab1234567890ab"
AND JSON_VALUE(values, "$.message_open.message.shorten_id") = "ZaZ"
SELECT
user_id
, sync_date
, JSON_VALUE(values, "$.message_open.message.campaign_id") AS campaign_id
, JSON_VALUE(values, "$.message_open.message.shorten_id") AS shorten_id
FROM
{{ karte_event(period[0], period[1]) }} -- Periodパラメータをパラメータ定義で設定
WHERE
event_name = "message_open"
AND JSON_VALUE(values, "$.message_open.message.campaign_id") = "1234567890ab1234567890ab"
AND JSON_VALUE(values, "$.message_open.message.shorten_id") = "ZaZ"
別のテーブルとJOINする
WITH view_urls AS ( -- 閲覧URL履歴を取得
SELECT
user_id
, JSON_VALUE(values, '$.view.access.uri.url') AS url
, sync_date
FROM
{% karte_event period[0], period[1] %} -- Periodパラメータをパラメータ定義で設定
WHERE event_name = "view"
)
, external_ids AS ( -- external_idsテーブルを取得
SELECT
karte_user_id
, foo_id
FROM `prd-karte-per-client.karte_client_ab123456789012345678901234567890.external_ids`
)
SELECT -- 両テーブルをJOINする
user_id
, external_ids.foo_id
, url
, sync_date
FROM view_urls
INNER JOIN external_ids
ON view_urls.user_id = external_ids.karte_user_id
WITH view_urls AS ( -- 閲覧URL履歴を取得
SELECT
user_id
, JSON_VALUE(values, '$.view.access.uri.url') AS url
, sync_date
FROM
{{ karte_event(period[0], period[1]) }} -- Periodパラメータをパラメータ定義で設定
WHERE event_name = "view"
)
, external_ids AS ( -- external_idsテーブルを取得
SELECT
karte_user_id
, foo_id
FROM `prd-karte-per-client.karte_client_ab123456789012345678901234567890.external_ids`
)
SELECT -- 両テーブルをJOINする
user_id
, external_ids.foo_id
, url
, sync_date
FROM view_urls
INNER JOIN external_ids
ON view_urls.user_id = external_ids.karte_user_id
JSON内のオブジェクトの配列を複数レコードに展開する
- 以下のように展開するクエリを作成します
展開前のテーブル
user_id | favorite_items |
---|---|
a | [{"item_id": "001","item_name":"foo"}] |
b | [{"item_id": "001","item_name":"foo"},{"item_id": "002","item_name":"bar"},{"item_id": "003","item_name":"baz"}] |
c | [{"item_id": "001","item_name":"foo"},{"item_id": "004","item_name":"qux"}] |
展開後のテーブル
user_id | favorite_item_id | favorite_item_name |
---|---|---|
a | 001 | foo |
b | 001 | foo |
b | 002 | bar |
b | 003 | baz |
c | 001 | foo |
c | 004 | qux |
オブジェクトの配列を展開するクエリ
- BigQueryには文字列を配列に変換する関数がまだ無いため、SPLIT関数を使っています
- そのため、複数階層あるJSONの場合はクエリの書き換えが必要です
WITH convert_to_array AS (
SELECT
user_id
, SPLIT(SUBSTR(favorite_items, 2 , LENGTH(favorite_items) - 2), '},{') AS favorite_items
FROM `prd-karte-per-client.karte_client_ab123456789012345678901234567890.favorite_items`
)
, unnested AS (
SELECT
user_id
, favorite_item
FROM convert_to_array, UNNEST(favorite_items) AS favorite_item
)
, restored_to_json AS (
SELECT
user_id
, CONCAT('{', REPLACE(REPLACE(favorite_item, '{', ''), '}', ''), '}') AS favorite_item
FROM unnested
)
SELECT
user_id
, JSON_VALUE(favorite_item, '$.favorite_item_id') AS favorite_item_id
, JSON_VALUE(favorite_item, '$.favorite_item_name') AS favorite_item_name
FROM restored_to_json
複数レコードに展開されたデータを1レコードに集約する
- 以下のように複数レコードを集約するクエリを作成します
集約前のテーブル
user_id | favorite_item_id | favorite_item_name |
---|---|---|
a | 001 | foo |
b | 001 | foo |
b | 002 | bar |
b | 003 | baz |
c | 001 | foo |
c | 004 | qux |
集約後のテーブル
user_id | favorite_item_ids | favorite_item_names |
---|---|---|
a | 001 | foo |
b | 001,002,003 | foo,bar,baz |
c | 001,004 | foo,qux |
複数レコードに展開されたデータを1レコードに集約するクエリ
- 区切り文字を指定して、文字列として集約します
SELECT
user_id
, STRING_AGG(favorite_item_id, ',') AS favorite_item_ids
, STRING_AGG(favorite_item_name, ',') AS favorite_item_names
FROM `prd-karte-per-client.karte_client_ab123456789012345678901234567890.favorite_items`
GROUP BY user_id
テーブル名に日付が含まれるテーブルIDに対して問い合わせる
- BigQueryの
_TABLE_SUFFIX
を利用することで、テーブル名のsuffixを指定して問い合わせることが可能です。 - 参考:ワイルドカード テーブル
SELECT
*
FROM
`prd-karte-per-client.karte_client_ab123456789012345678901234567890.summary_*`
WHERE
_TABLE_SUFFIX = '20190220'
- また、
CURRENT_DATE()
関数を利用することで当日日付を動的に生成して条件に指定することも可能です。
SELECT
*
FROM
`prd-karte-per-client.karte_client_ab123456789012345678901234567890.summary_*`
WHERE
_TABLE_SUFFIX = REPLACE(CAST(CURRENT_DATE() AS STRING) , '-', '')
user_idを最新化して抽出する
karte_event
テーブルの抽出の中で、user_id連携前のイベントについても最新のuser_idにmapして抽出したいことがありますkarte_event
テーブルからvisitor_id毎の最新のuser_idを抽出してマッピングテーブルを生成することで実現できます
WITH raw_event AS (
SELECT * FROM {% karte_event period[0], period[1] %} -- Periodパラメータをパラメータ定義で設定
)
, user_id_map AS (
SELECT
user_id
, ANY_VALUE(merged_user_id) AS merged_user_id
FROM
(
SELECT
user_id
, FIRST_VALUE(user_id) OVER (PARTITION BY visitor_id ORDER BY sync_date DESC) AS merged_user_id
FROM
raw_event
WHERE
visitor_id IS NOT NULL
)
GROUP BY
user_id
)
, user_mapped_event AS (
SELECT
IFNULL(b.merged_user_id, a.user_id) AS user_id
, sync_date
, event_name
, values
FROM
raw_event AS a
LEFT OUTER JOIN
user_id_map AS b
ON a.user_id = b.user_id
)
SELECT * FROM user_mapped_event
WITH raw_event AS (
SELECT * FROM {{ karte_event(period[0], period[1]) }} -- Periodパラメータをパラメータ定義で設定
)
, user_id_map AS (
SELECT
user_id
, ANY_VALUE(merged_user_id) AS merged_user_id
FROM
(
SELECT
user_id
, FIRST_VALUE(user_id) OVER (PARTITION BY visitor_id ORDER BY sync_date DESC) AS merged_user_id
FROM
raw_event
WHERE
visitor_id IS NOT NULL
)
GROUP BY
user_id
)
, user_mapped_event AS (
SELECT
IFNULL(b.merged_user_id, a.user_id) AS user_id
, sync_date
, event_name
, values
FROM
raw_event AS a
LEFT OUTER JOIN
user_id_map AS b
ON a.user_id = b.user_id
)
SELECT * FROM user_mapped_event
Updated 11 months ago