luckyriverr

SELECT
  -- eventParamValueByKeyの中でunnestしてくれるので、ここではunnestしなくてもSELECTできる
  eventParamValueByKey('firebase_screen_class', event_params).string_value
FROM
  `firebase-public-project.analytics_153293282.events_*`
WHERE
  _table_suffix = '20181003'
  AND event_name = 'level_complete_quickplay'
  

luckyriverr

# UDF for event_params
CREATE TEMP FUNCTION eventParamValueByKey(k STRING, params ARRAY<STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64, float_value FLOAT64, double_value FLOAT64 >>>) AS (
  (SELECT x.value FROM UNNEST(params) x WHERE x.key = k)
);

# UDF for user_properties
CREATE TEMP FUNCTION userPropertyValueByKey(k STRING, properties ARRAY<STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64, float_value FLOAT64, double_value FLOAT64, set_timestamp_micros INT64>>>) AS (
  (SELECT x.value FROM UNNEST(properties) x WHERE x.key = k)
);

luckyriverr

-- firebaseの公開データセット firebase-public-projectより
-- 2018-09-01〜2018-09-30の期間の時間ごとの
-- アクティブなUU数を集計する
WITH firebase_events AS  (
  SELECT
    CAST
    (
      FORMAT_TIMESTAMP(
        '%Y-%m-%d %H:%M:%S',
        TIMESTAMP_SECONDS(CAST(CAST(event_timestamp AS  INT64)/1000000 AS  INT64))

luckyriverr

SELECT
  ep.value.string_value	
FROM
  `firebase-public-project.analytics_153293282.events_*`,
  unnest(event_params) ep
WHERE
  _table_suffix = '20181003'
  AND event_name = 'level_complete_quickplay'
  AND ep.key = 'firebase_screen_class'
  

luckyriverr

# UDF for event_params
CREATE TEMP FUNCTION eventParamValueByKey(k STRING, params ARRAY<STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64, float_value FLOAT64, double_value FLOAT64 >>>) AS (
  (SELECT x.value FROM UNNEST(params) x WHERE x.key = k)
);

# UDF for user_properties
CREATE TEMP FUNCTION userPropertyValueByKey(k STRING, properties ARRAY<STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64, float_value FLOAT64, double_value FLOAT64, set_timestamp_micros INT64>>>) AS (
  (SELECT x.value FROM UNNEST(properties) x WHERE x.key = k)
);

luckyriverr

with data as (
  select
    'https://www.example.com/video/detail?id=100' url
)
select
  url,
  -- //からパラメータの間をマッチさせ/で分割
  split(regexp_extract(url, '//[^/]+([^?#]+)'), '/')[SAFE_ORDINAL(2)] path1,
  split(regexp_extract(url, '//[^/]+([^?#]+)'), '/')[SAFE_ORDINAL(3)] path2
from data

luckyriverr

package main

import (
    "fmt"
    "math/rand"
    "os"
    "reflect"
    "time"
)