サンプルクエリ集

Suggest Edits

クエリを書く際の参考として、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が含まれるという条件で抽出することで、セグメントに該当するユーザーを絞り込めます

🚧

セグメント抽出の注意事項

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する

  • karte_eventテーブルと、外部データから作成したexternal_idsというデータテーブルを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_idfavorite_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_idfavorite_item_idfavorite_item_name
a001foo
b001foo
b002bar
b003baz
c001foo
c004qux

オブジェクトの配列を展開するクエリ

  • 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_idfavorite_item_idfavorite_item_name
a001foo
b001foo
b002bar
b003baz
c001foo
c004qux

集約後のテーブル

user_idfavorite_item_idsfavorite_item_names
a001foo
b001,002,003foo,bar,baz
c001,004foo,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 5 months ago