declare temp_dataset string;
execute immediate format("create or replace temporary table `%s` as (select 1 as a limit 0)", generate_uuid());

set temp_dataset = (
  select as value
    ifnull(destination_table.dataset_id, error('not found job')) 
  from `region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
  where job_id = @@last_job_id
    and creation_time >= current_timestamp() - interval 30 minute
  limit 1
)
;
set @@dataset_id = temp_dataset;


-- table の作成と利用
create table `temp_table_test1`
as select 1 as a;
select * from `temp_table_test1`;

-- TVF の作成と利用
execute immediate format("""
  create or replace table function `%s.hoge`()
  as select 1 as a;
"""
  , temp_dataset
);
execute immediate format("select * from `%s.hoge`()", temp_dataset)

select  *
from unnest([1, 3]) as lhs
full join unnest([1,2]) as rhs

select distinct 
  lhs, rhs 
from unnest([1,2] || [1, 3]) as both
left join unnest([1,2]) as lhs on both = lhs
left join unnest([1,3]) as rhs on both = rhs

select 
  labels
from unnest(['[STRUCT("hoge", "fuga"), STRUCT("hoge", "fugb")]']) v
left join unnest([struct(
    array(
      select as struct
        label[0] as key, label[1] as value 
      from unnest(json_extract_array(parse_json(replace(replace(replace(v, "STRUCT", ""), '(', '['), ')', ']')))) as label
    ) as labels
)]) as v2

-- Show unique users and revenue daily from order table
SELECT DATE(order_date) AS order_date, COUNT(DISTINCT user_id) AS unique_users, SUM(total_price) AS revenue
FROM order_table
GROUP BY DATE(order_date);

-- Show order amount by newbie user from order table
SELECT
    o.user_id,
    SUM(o.amount) AS amount
FROM
    orders o
WHERE
    o.user_id IN (
        SELECT
            u.id
        FROM
            users u
        WHERE
            u.role = 'newbie'
    )
GROUP BY
    o.user_id;

select 
  CURRENT_DATE('Asia/Tokyo')
  , CURRENT_DATETIME('Asia/Tokyo')
  , timestamp('2022-01-01', 'Asia/Tokyo')
  , timestamp_trunc(current_timestamp(), HOUR, 'Asia/Tokyo')

create or replace function `fn.ts_conv`(ts timestamp)
as (
  struct(
    ts as raw
    , struct(
      string(ts, 'UTC') as pretty
      , struct(
          timestamp_trunc(ts, hour, 'UTC') as hour
          , timestamp_trunc(ts, day, 'UTC') as day
          , timestamp_trunc(ts, week, 'UTC') as week
          , timestamp_trunc(ts, month, 'UTC') as month
        ) as trunc
      , struct(
          extract(hour from ts at time zone 'UTC') as hour
          , extract(day from ts at time zone 'UTC') as day
          , extract(week from ts at time zone 'UTC') as week
          , extract(month from ts at time zone 'UTC') as month
        ) as part
    ) as utc
    , struct(
      string(ts, 'Asia/Tokyo') as pretty
      struct(
        timestamp_trunc(ts, hour, 'Asia/Tokyo') as hour
        , timestamp_trunc(ts, day, 'Asia/Tokyo') as day
        , timestamp_trunc(ts, week, 'Asia/Tokyo') as week
        , timestamp_trunc(ts, month, 'Asia/Tokyo') as month
      ) as trunc
      , struct(
          extract(hour from ts at time zone 'Asia/Tokyo') as hour
          , extract(day from ts at time zone 'Asia/Tokyo') as day
          , extract(week from ts at time zone 'Asia/Tokyo' ) as week
          , extract(month from ts  at time zone 'Asia/Tokyo') as month
        ) as part
    ) as jst
  )
);

-- 構造体のアクセス宣言のみで時間の変換が可能になる
select ts_conv(current_timestamp()).jst.trunc.hour
;

set @@time_zone = 'Asia/Tokyo'

select current_date()
--> 'Asia/Tokyo'での日時となる

set @@time_zone = 'Asia/Tokyo';
create or replace table `sandbox.test_partition`
-- DATE(d) の日付はUTCを基準に計算される
partition by DATE(d) 
as
  select d from unnest(
    generate_timestamp_array(
      timestamp('2022-08-24', 'UTC')
      , timestamp('2022-08-25', 'UTC')
      , interval 3 hour
    )
  ) as d

-- 引用: https://cloud.google.com/bigquery/docs/default-configuration#default_configurations

-- Orgnization-wide configuration
ALTER ORGANIZATION
SET OPTIONS (
  `region-us.default_time_zone `= 'Asia/Tokyo'
);

-- Project-wide configuration
ALTER PROJECT project_id
SET OPTIONS (
  `region-us.default_time_zone` = 'Asia/Tokyo'
);

-- 現在のプロジェクトで有効な設定の状態
SELECT * FROM region-us.INFORMATION_SCHEMA.EFFECTIVE_PROJECT_OPTIONS;

-- Organization-wide Configuration 
SELECT * FROM region-us.INFORMATION_SCHEMA.ORGANIZATION_OPTIONS;

create or replace function `v0.zgensql__clineage_queries`(
  target_dataset struct<project_id string, dataset_id string>
  , table_names array<string>
)
as (
  replace(replace(
    """
    -- Query single column for column lineage
    with lineage_sql as (
      select as struct
        table_catalog, table_schema, table_name, field_path, vhash
        , trim(format(r"select %s from `%s.%s.%s` limit 1"
          , field_path, table_catalog, table_schema, table_name
        )) as query
      from `!METADATA_COLUMN_FILED_PATH!`
        , (select generate_uuid() as vhash)
      where
        array_length(!TABLE_NAMES!) = 0
        OR table_name in unnest(!TABLE_NAMES!)
    )
    select array_agg(c) from lineage_sql as c
    """
    , "!TABLE_NAMES!", format('%T', table_names))
    , "!METADATA_COLUMN_FILED_PATH!", format('%s.%s.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS', target_dataset.project_id, target_dataset.dataset_id)
  )
);

begin
  declare clineage_query array<struct<
    table_catalog STRING, table_schema STRING, table_name STRING, field_path STRING, vhash STRING, query STRING
  >>;

  execute immediate `v0.zgensql__clineage_queries`(struct("bqmake", "zgolden"), []) into clineage_query
  ;

  for r in (select * from unnest(clineage_query)) do
    set @@query_label = array_to_string(
      [
        format("clineage__catalog:%s", r.table_catalog),
        format("clineage__schema:%s", r.table_schema),
        format("clineage__table:%s", r.table_name),
        format("clineage__field_path:%s", replace(r.field_path, ".", "_-_")),
        format("clineage__vhash:c%s", r.vhash)
      ]
      , "," 
    );
    execute immediate r.query;
    set @@query_label = null;
  end for
  ;

  with datasource as (
    select
      metadata.`@type` as type
      , c.timestamp
      , resource
      , protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent
      , JSON_VALUE_ARRAY(metadata.tableDataRead.fields) as fields
      , string(metadata.tableDataRead.jobName) as jobName
      , c
    from `bqmake._auditlog.cloudaudit_googleapis_com_data_access` as c
    left join unnest([struct(
      safe.parse_json(protopayload_auditlog.metadataJson) as metadata
    )])
    where
      date(timestamp) >= date(current_date() - interval 1 day)
  )
  , tableReadData as (
    select 
      regexp_extract(jobName, '[^/]+$') as jobId
      , resource.labels.dataset_id
      , resource.labels.project_id
      , json_value_array(nullif(to_json_string(fields), 'null')) as fields
      , to_json(datasource) as _raw
    from datasource
  )
  , jobCompleteEvent as (
    select
      timestamp
      , jobCompletedEvent.job.jobName.jobId as jobId
      , struct(
        jobCompletedEvent.job.jobConfiguration as config
        , jobCompletedEvent.job.jobStatistics as statistics
      ) as job
      , to_json(jobCompletedEvent) as _raw
    from datasource
    where 
      jobCompletedEvent.eventName = 'query_job_completed'
      and contains_substr(jobCompletedEvent.job.jobConfiguration.labels, 'clineage')
  )
  , fmt as (
    select 
      any_value(struct(
        clienage__resource, vhash
      )).*
      , array_agg(
        struct(
          field_path as column
          , struct(
            tableReadData.project_id
            , tableReadData.dataset_id
            , tableReadData.fields
          ) as lineage
          , struct(
            job.statistics.totalSlotMs
            , job.statistics.totalProcessedBytes
            , job.statistics.endTime - job.statistics.startTime as leadTime
          ) as stats
        )
      ) as column_lineage
      , struct(
        min(timestamp) as min
        , max(timestamp) as max
      ) as analyze_span
  --    , job.statistics
    from jobCompleteEvent
    left join tableReadData using(jobId)
    left join unnest([struct(
      struct(
        `bqutil.fn.get_value`('clineage__schema',  job.config.labels) as schema
        , `bqutil.fn.get_value`('clineage__catalog',  job.config.labels) as catalog
        , `bqutil.fn.get_value`('clineage__table',  job.config.labels) as table
      ) as clienage__resource
      , replace(`bqutil.fn.get_value`('clineage__field_path',  job.config.labels), '_-_', '.') as field_path
      , `bqutil.fn.get_value`('clineage__vhash',  job.config.labels) as vhash
    )])
    group by format('%t', (clienage__resource, vhash))
    order by analyze_span.min desc
  )

  select * from fmt
;
end
;

with A as (
  select ix, ix * ix as ix2  from unnest(generated_array(1, 100, 1)) as ix
)
, B as (
 select avg(ix2) from A
)
, C as (
 select count(1) from A
)

select * from A
-- 以下のfinal節に置き換えて利用したい
-- select * from B
-- select * from C

with 
super_large_A as (
  select ix, ix * ix as ix2  from unnest(generated_array(1, 100, 1)) as ix
)
, A as (
  -- デバッグ利用のみで利用
  select ix, ix * ix as ix2  from unnest(generated_array(1, 100000000, 1)) as ix
)
, __constrains_A_table as (
  -- Aとsuper_large_Aは同一スキーマである必要がある.
  select * from A
  union all
  select * from super_large_A
)
, B as (
 select avg(ix2) from A
 -- 本番時は以下に書きかえて集計したい
 -- select avg(ix2) from super_large_A
)
, C as (
 select count(1) from A
 -- 本番時は以下に書きかえて集計したい
 -- select avg(ix2) from A
)

select * from C

-- bqmake.bqtest.demo_sample_view
with datasource as (
  select * from `bigquery-public-data.google_trends.top_terms`
)
, datasource_sampled as (
  select * from `bigquery-public-data.google_trends.top_terms`
  TABLESAMPLE SYSTEM (5 percent)
)
, __test_count as (
  select count(1) from datasource
)

select * from datasource

execute immediate `bqtest.zbqt_gensql__remake_view`(
   'demo_sample_view', '__test_count', [('datasource', 'datasource_sampled')]
);

-- bqmake.bqtest.demo_sample_view
with datasource as (
  select * from `bigquery-public-data.google_trends.top_terms`
)
, datasource_sampled as (
  select * from `bigquery-public-data.google_trends.top_terms`
  TABLESAMPLE SYSTEM (5 percent)
)
, __test_count as (
  select count(1) from datasource_sampled 
)

select * from __test_count

create or replace function `sandbox.sample_udf`(X int64, _options struct<param1 int64, prefix string >)
as
(
  format('%s %d', ifnull(_options.prefix, 'PREFIX: ' ), X + ifnull(_options.param1, 0)) 
)

create or replace function `sandbox.sample_udf`(X int64, arr_options array<struct<key string, value string>>)
as
((
  with _options as (
    with options as (
      select * from unnest(arr_options) option
      where if(
        key in ('param1', 'prefix')
        , true
        , error(format("Invalid Option: %t", option))
        )
    )
    select as struct
      -- 任意の型にParseする
      cast(param1 as int64) as param1
      , cast(prefix as string) as prefix
    from options
      pivot (any_value(value) for key in ('param1', 'prefix'))
    limit 1
  )
  
  select format('%s %d', ifnull(_options.prefix, 'PREFIX: ' ), X + ifnull(_options.param1, 0))
  from unnest([0]), _options
))

create or replace function `sandbox.sample_udf`(X int64, json_options JSON)
as 
((
  with _options as (
  select as struct
    safe.int64(json_options.params1) as param1
    , safe.string(json_options.prefix) as prefix
    -- Assert options
    , (
        select logical_and(if(
          key in ('correct')
          , true
          , error(format("Invalid Option: name=%t in %t'", key, json_options))
        ))
        from unnest(`bqutil.fn.json_extract_keys`(to_json_string(json_options))) key
    ) as _assert
  )
  
  select 
    format('%s %d', ifnull(_options.prefix, 'PREFIX: ' ), X + ifnull(_options.param1, 0))
  from unnest([0]), _options
  where _options._assert
))

  select 
    -- normal distribution: box-muller
    sqrt(-2 * ln(rand())) * cos(rand() * 4 * atan(1.0)) as r
  from
    unnest(generate_array(1, 10000)) as v 

SELECT * 
FROM `jp_holidays_preprocessed_by_bq_fun.holidays` 
where extract(year from date) between 2016 and 2022
order by date 

# 特許の分布と、特許数の多い企業名について調べる
with base as (
  SELECT 
    corporate_number, count(1) as n_patent
  FROM `project-id-7288898082930342315.gbizinfo_preprocessed_by_bq_fun.patent` 
  group by corporate_number
)
, stats as (
  select 
    approx_quantiles(n_patent, 20) as q20tiles
    , approx_top_sum(corporate_number, n_patent, 10) as top10
  from base
)
, report as (
  SELECT 
    *
    , array(
      select as struct
        kv.value, name, kv.sum
      from unnest(stats.top10) kv
      left join `project-id-7288898082930342315.gbizinfo_preprocessed_by_bq_fun.basic`
        on value = corporate_number
      order by kv.sum desc
    )
  from stats
)

select * from report

create temp function `assert`(expected ANY TYPE, actual ANY TYPE)
as (
  if(expected = actual, null, error(format("Expected %T: %T", expected, actual)))
);

select assert('hoge', 'hoge')

WITH 
BASE AS (
  SELECT 
    _PARTITIONTIME as __PARTITIONTIME
    , #... 
  FROM
    mytable
)
, AGG AS (
  SELECT 
    __PARTITIONTIME
    , # ... 
  FROM
    BASE
  GROUP BY
    __PARTITIONTIME, # ...
)

SELECT 
  *
FROM 
  AGG
WHERE
  __PARTITIONTIME = TIMESTAMP("2017-10-01") -- 擬似列を利用しスキャン量が抑えられる

-- データセット用意
create schema if not exists `check_table_schema`;

-- 参照テーブル: 整合性を確かめる
create or replace table `check_table_schema.reference_table`
as
select * from `bigquery-public-data.austin_bikeshare.bikeshare_stations`
;

select * replace (cast(station_id as STRING) as station_id)
from `check_table_schema.reference_table`

create or replace table `check_table_schema.target_table`
as
select * replace (cast(station_id as STRING) as station_id)
from `check_table_schema.reference_table`
;

with table1 as (
  select *
  from `check_table_schema.INFORMATION_SCHEMA.COLUMNS`
  where table_name = 'reference_table'
)
, table2 as (
  select
    *
  from `check_table_schema.INFORMATION_SCHEMA.COLUMNS`
  where table_name = 'target_table'
)
select
  if(
    count(1) > 0
    , error(
      string_agg(
        format('%t', (
          column_name
          , lhs.data_type
          , rhs.data_type
          , compare_result
        ))
      )
    )
    , "PASSED"
  ) as msg
from table1 as lhs
full join table2 as rhs using(column_name)
left join unnest([STRUCT(
  case 
    when lhs.data_type = rhs.data_type  then "STRICT_MATCH"
    when lhs.data_type != rhs.data_type then "STRICT_UNMATCH"
    when lhs.data_type is not null then "EXISTS_ONLY_LEFT"
    when rhs.data_type is not null then "EXISTS_ONLY_RIGHT"
  end as compare_result
)])
where compare_result not in ("STRICT_MATCH")

with core as (
  select * replace (cast(station_id as STRING) as station_id)
  from `check_table_schema.reference_table`
)
, _validation as (
	-- ※ カラム名一致や厳密な型一致は取れない
	select * from `check_table_schema.reference_table`
	union all
	select * from core
	limit 0
)
select * from core
--> Column 1 in UNION ALL has incompatible types: INT64, STRING at [9:9]

with core as (
  select * replace (cast(station_id as BIGNUMERIC) as station_id)
  from `check_table_schema.reference_table`
)
, _validation as (
  -- ※ カラム名一致や厳密な型一致は取れない
  select * from `check_table_schema.reference_table`
  union all
  select * from core
  limit 0
)
select * from core

with core as (
  select * replace (cast(station_id as BIGNUMERIC) as station_id)
  from `check_table_schema.reference_table`
)
, _validation as (
  select
    array(select as struct * from `check_table_schema.reference_table` limit 0)
    || array(select as struct * from core limit 0) --> Error
)
select * from core

-- dee テーブルを構築
create temp table _skelton_dee
as
select 
  (
    select as struct
     (select as struct * from `check_table_schema.reference_table` limit 0).*
  ) as lhs
  , (
    select as struct
    (
	  select as struct * replace (cast(station_id as STRING) as station_id)
	  from `check_table_schema.reference_table` limit 0
	).*
  ) as rhs
;

select
  if(
    array_length(`bqutil.fn.json_extract_keys`(to_json_string(lhs))) = (
      select
        countif(lhs = rhs)
      from 
        unnest(`bqutil.fn.json_extract_keys`(to_json_string(lhs))) as lhs with offset ix
        , unnest(`bqutil.fn.json_extract_keys`(to_json_string(rhs))) as rhs with offset iy
    )
    and [lhs] || [rhs]
    , "PASSED"
    , error('table schema is not matched')
  )  
from _skelton as r

with 
  recursive newton as (
	-- Initial step
    select 0 as n, 1 + 0.5 * (N - 1) as x, N as tgt from unnest(generate_array(2, 1000)) as N
    union all
	-- Update step
    select 
      n + 1
      , x - (x * x - tgt) / (2 * x)
      , tgt
    from newton
    where 
      -- Maximum recursive depth 
      n < 98
      -- tolerance error
      and abs(x * x - tgt) > 1e-06
  )

  select *, tgt - x*x as e from newton
  qualify n = max(n) over (partition by tgt) 
  order by tgt

-- Dashboard ultimate datasource
/*
create or replace table `sandbox.test2`
partition by date(time_id)
cluster by unit
as 
*/
with
 datasource as (
  select
    struct(
      timestamp_trunc(timestamp, hour) as time_id
      , user_pseudo_id as user_id
      , item.item_id
    ) as units
    , struct(
      device
      , geo
    ) as user
    , item as item
    , event_name as signal_type
    , struct(
      -- ID類
      timestamp
      , ecommerce.transaction_id
      , ecommerce.purchase_revenue
      , page_id
      , user_pseudo_id as device_id
      , user_id
      , session_id
      , item_id
      , 1 as first_visit_per_day
      , 1 as first_purchase_per_user
    ) as signal
  from
   `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
  left join unnest([struct(
    timestamp_micros(event_timestamp) as timestamp
    , `bqutil.fn.get_value`('ga_session_id', event_params).string_value as session_id
    , `bqutil.fn.get_value`('page_location', event_params).string_value as page_id
  )])
  left join unnest(items) as item
)
, grain as (
  select
    any_value(units) as units
    , any_value(user) as user
    , any_value(item) as item
    , struct(
      count(1) as records -- 型ごとに主要統計量の取り方が定まる
      -- timestamp signal

      , struct(
        countif(signal.timestamp is not null) as nonnull
        , min(signal.timestamp) as min
        , max(signal.timestamp) as max
      ) as timestamp 
      -- identifier signal
      , struct(
        countif(signal.transaction_id is not null) as nonnull
        , hll_count.init(signal.transaction_id) as hll
      ) as `order` 
      -- numeric
      , struct(
        countif(signal.purchase_revenue is not null) as nonnull
        , sum(signal.purchase_revenue) as sum
      ) as revenue
    ) as metrics
  from
    datasource
  group by
    format('%t', units)
)

, core_stats as (
  select
    -- Unit Metrics
    any_value(_group_key).*
    -- Unit Metrics
    , struct(
      nullif(approx_count_distinct(string(grain.units.time_id)), 0) as time
      , nullif(approx_count_distinct(grain.units.user_id), 0) as users
      , nullif(approx_count_distinct(grain.units.item_id), 0) as items
      , approx_count_distinct(ifnull(grain.units.user_id, 'none') || '-' || ifnull(grain.units.item_id, 'none')) as user_item
    ) as unit_metrics
    -- Additive Metrics
    , struct(
        sum(metrics.records) as records
      , struct(
          sum(metrics.timestamp.nonnull) as nonnull
        , min(metrics.timestamp.min) as min
        , max(metrics.timestamp.min) as max
      ) as timestamp
      , struct(
          sum(metrics.order.nonnull) as nonnull
        , hll_count.merge_partial(metrics.order.hll) as hll
      ) as `order`
      , struct(
          sum(metrics.revenue.nonnull) as nonnull
        , sum(metrics.revenue.sum) as sum
        ) as revenue
      ) as metrics
  from grain
--    left join `user` using(user_id)
--    left join `item` using(item_id) -- segments
    left join unnest(
      [struct(
        struct(
          user.device.category as category
          , user.geo.country as country
        ) as user
      , struct(item.item_category) as item
      , struct(
          extract(
            dayofweek
            from
              units.time_id
          ) as dayofweek
        ) as time
      )]
    ) as context
    left join unnest([
      -- Overall
      struct(
          if(false, null, context.user) as user
        , if(false, null, context.item) as item
        , if(false, null, context.time) as time
      )
      -- Time Specific
      , struct(
          if(false, null, context.user) as user
        , if(false, null, context.item) as item
        , if(true, null, context.time) as time
        )
      -- Item Specific
      , struct(
          if(false, null, context.user) as user
        , if(true, null, context.item) as item
        , if(false, null, context.time) as time
        )
      -- User Specific
      , struct(
          if(true, null, context.user) as user
        , if(false, null, context.item) as item
        , if(false, null, context.time) as time
    )]) as segments -- marginalization for units
    left join unnest([struct('item' as unit, units.item_id as id), null]) as item
    left join unnest([struct('user' as unit, units.user_id as id), null]) as user
    left join unnest([
      struct('week' as unit, datetime(timestamp_trunc(units.time_id, week)) as id)
      , struct('day'  as unit, datetime(date(units.time_id)) as id)
      , struct('hour' as unit, datetime(units.time_id) as id)
      , null
    ]) as time
    left join unnest([
      struct(struct(time, user, item) as units, segments)
    ]) as _group_key

  group by
    -- unit
    format('%t', _group_key)
)

select
  ifnull(nullif(array_to_string([units.time.unit, units.user.unit, units.item.unit], ' x '), ''), '#overall') as unit
  , units.time.id as time_id
  , units.user.id as user_id
  , units.item.id as item_id
  , fn.json_pretty_kv(fn.json_trim_empty(to_json_string(segments)), ', ', null) as segment_label
  , * except(units) 
from core_stats

-- JSによる書き換え
CREATE TEMPORARY FUNCTION escape_regex_pattern1(pattern STRING)
RETURNS STRING
LANGUAGE js AS r"""
  return pattern.replace(/[.*+?^${}()|[\]\\]/g,  '\\$&');
""";

--  SQLのみでのエスケープ (REPALCE版)
CREATE TEMPORARY FUNCTION escape_regex_pattern2(pattern STRING)
RETURNS STRING
AS (
  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    pattern,
    '\\', '\\\\\\'),
    '|', '\\|'),
    '(', '\\('),
    ')', '\\)'),
    '{', '\\{'),
    '}', '\\}'),
    '$', '\\$'),
    '^', '\\^'),
    '*', '\\*'),
    '+', '\\+'),
    '?', '\\?'),
    '.', '\\.')
)
;

--  SQLのみでのエスケープ (REGEXP_REPLACE)
CREATE TEMPORARY FUNCTION escape_regex_pattern3(pattern STRING)
RETURNS STRING
AS (
  REGEXP_REPLACE(pattern, r'([.*+?^${}()|[\]\\])', r'\\\1')
)
;

-- テストコード
WITH test_cases AS (
  SELECT '.*+' AS pattern, r'\.\*\+' AS expected_result UNION ALL
  SELECT '$.[]()' AS pattern, r'\$\.\[\]\(\)' AS expected_result UNION ALL
  SELECT 'hello, world!' AS pattern, r'hello, world!' AS expected_result UNION ALL
  SELECT '\\[\\]' AS pattern, r'\\\[\\\]' AS expected_result UNION ALL
  SELECT '' AS pattern, '' AS expected_result　UNION ALL
  SELECT REPEAT('abcdefghijklmnopqrstuvwxyz', 200) AS pattern, REPEAT('abcdefghijklmnopqrstuvwxyz', 200) AS expected_result
)
, benchmark__dataset as (
  select * from test_cases, unnest(generate_array(1, 100000))
)
, benchmark1 as (
 select escape_regex_pattern1(pattern) = expected_result as assert from benchmark__dataset 
)
, benchmark2 as (
 select escape_regex_pattern2(pattern) = expected_result as assert from benchmark__dataset 
)
, benchmark3 as (
 select escape_regex_pattern3(pattern) = expected_result as assert from benchmark__dataset 
)

select countif(assert) from benchmark1
-- select countif(assert) from benchmark2
-- select countif(assert) from benchmark3
-- select pattern, escape_regex_pattern2(pattern), expected_result from test_cases

declare scale int64 default 3; 
declare buckets int64 default cast(pow(10, scale) as int64);
declare plots array<int64> default 
  array(select cast(pow(10, s) as int64) from unnest(generate_array(0, scale -1)) as s)
  || [cast(buckets/2 as int64)]
  || array(select cast(pow(10, scale) - pow(10, s) as int64) from unnest(generate_array(0, scale - 1)) as s)
  ;
declare n_data int64 default 1000000;
declare n_sample int64 default 10;


with 
  uniform as (
    select 
      v2 as sample_ix
      , rand() as r
    from
      unnest(generate_array(1, n_data)) as v 
      , unnest(generate_array(1, n_sample)) as v2
  )
  , normal_dist as (
    select
      v2 as sample_ix 
      -- normal distribution: box-muller
      , sqrt(-2 * ln(rand())) * cos(rand() * 4 * atan(1.0)) as r
    from
      unnest(generate_array(1, n_data)) as v
      , unnest(generate_array(1, n_sample)) as v2

  )
  , heavy_tailed as (
    select 
      v2 as sample_ix
      -- normal distribution: box-muller
      , POWER((-1. / 1.5) * LOG(1. - rand()), 1./0.1)
      as r
    from
      unnest(generate_array(1, n_data)) as v 
      , unnest(generate_array(1, n_sample)) as v2
  )

  , experimented as (
    with datasource as (
      select 'heavytail' as label, * from heavy_tailed
      union all 
      select 'normal', * from normal_dist
      union all
      select 'uniform', * from uniform
    )
    , groundtruth as (
      with calc as (
        select 
          label, sample_ix
          , percent_rank() over (partition by label, sample_ix order by r) * buckets as prank
          , r
        from datasource
      )
      select 
        label, sample_ix
        , cast(round(prank, 0) as int64) as qtile
        , min(r) as min
        , max(r) as max
        , (max(r) + min(r)) / 2 as mid
      from calc
      group by label, sample_ix, qtile 
    )
    , approximate as (
      select
        label, sample_ix 
        , approx_quantiles(r, buckets) as value
      from datasource
      group by label, sample_ix
    )
    
    select 
      groundtruth, round(approx, 2) as approx, round((approx - mid), 3) as err_abs, round((approx - mid) / mid, 3) as err_rel
    from approximate as A
    left join unnest(A.value) as approx with offset qtile
    left join groundtruth using(label, sample_ix, qtile)
    where qtile in unnest(plots)
    order by qtile
  )

  select  
    groundtruth.label
    , to_json(struct(scale, n_data, n_sample)) as config
    , groundtruth.qtile / buckets as qtile
    , round(any_value(groundtruth.mid), 2) as groundtruth
    , round(avg(approx), 2) as approx
    , struct(
      round(avg(err_abs), 3) as avg
      , round(stddev(err_abs), 3) as stddev
     ) as err_abs
    , struct(
      round(avg(err_rel), 3) as avg
      , round(stddev(err_rel), 3) as stddev
    ) as err_rel
  from experimented
  group by label, qtile
  order by label

select 
  metadata.`@type` as type
  , metadata.tableDataRead.reason
  , JSON_VALUE_ARRAY(metadata.tableDataRead.fields) as fields
  , *
from `bqmake._auditlog.cloudaudit_googleapis_com_data_access` as c
left join unnest([struct(
   safe.parse_json(protopayload_auditlog.metadataJson) as metadata
)])
where 
  date(timestamp) >= date(current_date() - interval 1 day)
  and resource.type = 'bigquery_dataset'
order by timestamp desc

-- Set up
create schema if not exists `demo`;
create or replace table `demo.bikeshare_stations_blue`
copy `bigquery-public-data.austin_bikeshare.bikeshare_stations`
;

create or replace table `demo.bikeshare_stations_green`
as
  select * from `bigquery-public-data.austin_bikeshare.bikeshare_stations`
;

-- Feature toggle by user
create or replace view `demo.bikeshare_stations`
as
with switched_by_user as (
  with unioned as (
    select *, 'blue' as _metadata_version from `takegue.canary.bikeshare_stations_blue`
    union all
    select *, 'green' as _metadata_version from `takegue.canary.bikeshare_stations_green`
  )
  select *
  from unioned
  where 
    case
      -- ユーザによってテーブルを出しわける 
      when mod(farm_fingerprint(session_user()), 10) = 1 then _metadata_version = 'blue'
      else _metadata_version = 'green'
    end
)

select * from switched_by_user

-- Feature toggle by user
create or replace view `demo.bikeshare_stations`
as
with switched_by_user as (
  with unioned as (
    select *, 'blue' as _metadata_version from `takegue.canary.bikeshare_stations_blue`
    union all
    select *, 'green' as _metadata_version from `takegue.canary.bikeshare_stations_green`
  )
  select *
  from unioned
  where 
    case
      -- ユーザによってテーブルを出しわける 
      when mod(farm_fingerprint(format('%t', station_id)), 10) = 1 then _metadata_version = 'blue'
	  -- レコードのIDによって出しわける
      else _metadata_version = 'green'
    end
)

select * from switched_by_user

with unioned as (
  select *, 'blue' as _source from `demo.bikeshare_stations_blue`
  union all
  select *, 'green' as _source from `demo.bikeshare_stations_green`
)
, metadata__feature as (
  select as value
    label
  from `demo.INFORMATION_SCHEMA.TABLE_OPTIONS`
  left join unnest([struct(
    -- BigQuery の option_valueのパース
    -- https://github.com/takegue/bqmake/blob/a43db847795a5e6be6aa4a3751ba0c43a6b04ff1/bigquery/%40default/v0/%40routines/zget_bqlabel_from_option/ddl.sql
    array(
      select as struct
        string(label[0]) as name, string(label[1]) as value
      from unnest(json_extract_array(safe.parse_json(replace(replace(replace(option_value, "STRUCT", ""), '(', '['), ')', ']')))) as label
    ) as labels
  )])
  left join unnest(labels) as label
  where 
    table_schema = 'canary' and table_name = 'bikeshare_stations'
    and option_name = 'labels'
    and label.name = 'feature'
)
, core as (
  select *
  from 
    unioned
  left join metadata__feature as _feat on true
  where
    case 
      when _feat.value = 'blue' then _source = 'blue'
      else _source = 'green'
    end
)

select * from core

with unioned as (
  select *, 'blue' as _source from `demo.bikeshare_stations_blue`
  union all
  select *, 'green' as _source from `demo.bikeshare_stations_green`
)
, metadata__stats as (
  select as value count(*) from `demo.bikeshare_stations_green`
)
, core as (
  select *
  from 
    unioned
  left join metadata__stats as _stats on true
  where
    case 
      when _stats >= 50 then _source = 'blue'
      else _source = 'green'
    end
)

select * from core

truncate from `demo.bikeshare_stations_blue`

create materialized view `demo.bikeshare_stations`
options(
  allow_non_incremental_definition = true
  , max_staleness = interval 30 minute
)
as
-- 省略

-- DWH layer
with data as (
	select * from unnest([
		struct(1 as id, "one" as label)
        ,  (2, "two")
        ,  (3, "three")
        ,  (4, "four")
        # ...
	])
)

select * from data

-- Datamart Layer
with 
_pre as (
  select * from `awesome_dataset.dwh`
  where mod(id, 2) = 1
)
, _agg as (
  select 
    countif(label is not null) as nonnulls
    ,  countif(distinct id) as uniques
  from _pre
)

select * from _agg

select "Odd Number Uniqueness" as name, uniques as value  from `awesome_datamart.core`
-- union all ...

-- DWH layer
with data as (
	select * from unnest([
		struct(1 as id, "one" as label)
        ,  (2, "two")
        ,  (3, "three")
        ,  (4, "four")
        # ...
	])
)
, metric_columns as (
  select 
     *
     , if(mod(id, 2) = 1,  id, null) as metric__oddlabel__uniquenss
     , if(mod(id, 2) = 1,  label is not null, null) as metric__oddlabel_nonnull
   from data
)

select * from metric_columns 

select 
  count(distinct metric__oddlabel_uniquenss)
  , countif(metric__oddlabel_nonnull)
from `awesome_dataset.dwh`

with data as (
  select 'key1' as key, "_name_a"  as name, "X" as label
  union all   
  select 'key2' as key, "_name_b"  as name, NULL as label
  union all   
  select 'key3' as key, "_name_c"  as name, "Y" as label
)

select 
   count(1) 
from 
  data
where 
   label != "X"

-- >  1(actual)   != 2 (expected)

-- ifnull によるハンドリング
-- 省略
select 
   count(1) 
from 
  data
where 
   ifnull(label, "Unknown")  != "X"

-- is distinct from によるハンドリング
-- 省略
select 
   count(1) 
from 
  data
where 
  label is not distinct from  "X"

select struct(1, null) = struct(2, null)
--> false

select struct(2, null) = struct(2, null)
--> null

select struct(null, null) = struct(2, null)
--> null

select format('%t', struct(2, null)) = format('%t', struct(2, null))
--> true

select true AND NULL 
--> null

select true AND NULL 
--> null

select true OR NULL 
--> true

select false OR NULL 
--> null

create or replace function `fn.trim_multiline_string`(s string)
returns string
as
((
  select as value
    string_agg(nullif(trim(line), ''), '\n')
  from unnest(split(s, '\n')) line
))
;

select `fn.trim_multiline_string`("
hoge
    ,  fuga
") 
-- > "hoge\n, fuga"

create or replace table `sandbox.stations_scd_type2`
partition by DATE(valid_to)
cluster by unique_key
as
select  
  entity.station_id as unique_key
  , version_hash
  , timestamp(null) as valid_from
  , timestamp(null) as valid_to
  , entity
from `bigquery-public-data.austin_bikeshare.bikeshare_stations` as entity 
 , (select as value generate_uuid() ) as version_hash
limit 0

SELECT 
  entity.*
FROM `project-id-7288898082930342315.sandbox.stations_scd_type2` 
where valid_to is null

SELECT 
  entity.*
FROM `project-id-7288898082930342315.sandbox.stations_scd_type2` 
where 
  -- when snapshot_point is null, use latest version
  (valid_from <= snapshot_point and ifnull(snapshot_point < valid_to, true))
  or
   (snapshot_point is null and valid_to is null)

merge `sandbox.stations_scd_type2` T
using 
  (
    SELECT 
      entity.station_id as unique_key
      , uuid as version_hash
      , timestamp '2010-01-01' as valid_from
      , timestamp(null) as valid_to
      , entity
    FROM 
      -- 例として、 特定のIDのみのデータを置き換えている
      (select * replace(if(station_id in (2499), "closed", status) as status) from `bigquery-public-data.austin_bikeshare.bikeshare_stations`) as entity
    , (select as value generate_uuid()) uuid
  ) as S
on
  T.valid_to is null
  and S.unique_key = T.unique_key
  and format('%t', S.entity) = format('%t', T.entity)
-- Insert new records changed  
when not matched then
  insert row
-- Deprecate old records changed
when 
  not matched by source 
  and T.valid_to is null then  
  update set
    valid_to = current_timestamp()

-->  affect 2 records

with datasource as (
  select v.* from unnest([
    struct(1 as id, 'one' as name)
    , struct(2 as id, 'two' as name)
    , struct(3 as id, 'three' as name)
    , struct(3 as id, 'three' as name)
  ]) as v
)
, _validation as (
  with uniqueness as (
    select
      "#1 Uniquness" as title
      , format("%s should be unique. count=%d", unique_key, count(1)) as message
      , 1 = count(1) as assertion
    from datasource
    left join unnest([struct(
       format('%t', (id)) as unique_key
    )])
    group by unique_key
  )

  , test as (
    select as struct
      title
      , string_agg(distinct message, '\n') as error
    from (
      select * from uniqueness
    )
    where not assertion
    group by title
  )

  select as value
    if(
      count(error) > 0
      , error(format('Failed: %s', string_agg(error, ',' limit 10)))
      , true
    ) as errors
  from test
  where error is not null
)

select
  datasource.*
from datasource, _validation
where
  _validation

with source as (
  select * from `table`  --> このテーブルは 日々replaceされるかもしれない. 古いレコードは削除されるかもしれない
)
, agg as (
  select 
     dim1 --> dim1のラベルは減ったり増えたり、欠損するようになるかもしれない
     , dim2
    -- 何かしら集計したい指標
     , sum(value) as sum_value --> 特定のユーザ (user_id)の寄与が99%かもしれない. 値域が変わるかもしれない
  from source
  where 
    dim4 = "some_condition"  --> ドリフトにより 条件外の時のデータが当時と違って増えるかもしれない. dim4のratio情報が必要
  group by dim1, dim2 
)

select * from agg

with source as (
  select  
    _PARTITIONTIME  as __PARTITIONTIME
    , * 
  from `table` 
  where _PARTITIONTIME between "2022-06-10" and "2022-06-12"
)
, _metadata_table as (
   select as struct
    "`table`" as name
     , min(__PARTITIONTIME) as __PARTITIONTIME_from
      , max(__PARTITIONTIME) as __PARTITIONTIME_to
     ,  count(1) as n_records
     -- フィルタ操作に対するメタデータ
     ,  countif(dim4 = "some_condition")  as n_matched
   from source
)
, agg as (
  select 
     dim1
     , dim2
     , sum(value) as sum_value
     -- metadata
     , struct(
       struct(
          -- valueの統計値
          struct(
            min(value) as min
            , min(value) as max
            , avg(value) as avg
            , approx_qunatiles(value, 10) q10tiles
          ) as value
          -- valueに関わるエンティティの統計情報
          , struct(
　	　  approx_top_sum(user_id, sum_value, 10) as top5_sum
	      , approx_top_count(user_id, 10) as top10_count
          ) as user
       ) as column
       ,  _metadata_table as table
       , struct(
         current_timestamp() as created_at
       ) as job
     ) as _metadata
  from source, _metadata_table
  group by dim1, dim2
)

select * from agg

merge `sandbox.test_scd_type4` T
using 
  (
      select 
         src.unique_key 
         , version_hash
         , current_timestamp() as valid_from
         , timestamp(null) as  valid_to
      from 
         src as src
         ,  (select as value generate_uuid()) as version_hash
  ) as S
on
  T.valid_to is null
  and S.unique_key = T.unique_key
  and S.column_name = T.column_name
  -- Evaluate `struct(1, null) = struct(2, null)` is false, `struct(1, null) = struct(1, null)` is false
  and format('%t', S.column_value) = format('%t', T.column_value)

-- Insert New Record
when not matched then
  insert row
-- Deprecated Old Rercord
when 
  not matched by source 
  and T.valid_to is null then  
  update set
    valid_to = current_timestamp()

declare arr array<bool> default [true, false];
select
  (select logical_and(v) from unnest(arr) as v ) as ret

create temp function `any`(arr array<bool>)
as 
 ((
   select as value 
    if(
      array_length(arr) > 0
      , if(logical_or(v), true, if(countif(v is null) > 0, null, false))
      , false
    )
   from unnest(arr) as v
 ))
;
assert `any`([true, false]) = true;
assert `any`([true, null]) = true;
assert `any`([false, null]) is null;
assert `any`([]) = false;

create temp function `all`(arr array<bool>)
as 
 ((
   select as value 
    if(
      array_length(arr) > 0
      , if(logical_and(v), if(countif(v is null) > 0, null, true), false)
      , true
    ) 
   from unnest(arr) as v
 ))
;

assert `all`([true, false]) = false;
assert `all`([false, null]) = false;
assert `all`([true, null]) is null;
assert `all`([]) = true;

-- 最適化されたSQLの例: 次から引用
-- https://cloud.google.com/bigquery/docs/best-practices-performance-filter-order#use_limit_with_an_order_by_clause
SELECT  id,  reputation,  creation_date,  DENSE_RANK() OVER (ORDER BY creation_date) AS user_rankFROM bigquery-public-data.stackoverflow.users  
ORDER BY user_rank ASCLIMIT 10;

with complex_1 as (
  -- ...
)
, complex_2 as (
  -- ...
  -- from complext_1
)
, complex_3 as (
  -- ...
  -- from complex_2
)

select * from complex_3

create temp table `check_schema`
as
with complex_1 as (
  -- ...
)
, complex_2 as (
  -- ...
  -- from complext_1
)
, complex_3 as (
  -- ...
  -- from complex_2
)

select * from complex_3
limit 0

create or replace temp table `takegue.sandbox.sample_partition_table`
partition by date_jst
options(
  require_partition_filter  = true
)
as select * from `takegue.sandbox.sample_partition_table` limit 0

;

with _source as (
  SELECT * FROM `takegue.sandbox.sample_partition_table`
)
, _query as (
  SELECT date(null) as date_jst
)

select * from _source
union all
select * from _query
limit 0

declare limit_num int64;
set limit_num = 3;

select 
  [
    select
      u
    from
	  unnest(generate_array(1, 10)) as u
	-- Error!: limit limit_num
	-- Passed: limit @limit_num
	limit 3
  ]

declare limit_num int64;
set limit_num = 3;

select 
  [
    select
      u
    from
	  unnest(generate_array(1, 10)) as u 
	    with offset ix
	where
	   ix < 3
	-- Passed: ix < limit_num
	-- Passed: ix < @limit_num
  ]

-- Prepare Sample TVF
create schema if not exists `sandbox`;
create or replace table function `sandbox.sample_tvf`(target_date DATE)
as
  select * from `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events*`
  where parse_date(_TABLE_SUFFIX, '%Y%m%d') = target_date

-- Analyze referenced tables
execute immediate 'select * from `sandbox.sample_tvf`("2022-06-20")';
set last_job_id = @@last_job_id;
set ret = (
  select as value
    referenced_tables
  from `region-us.INFORMATION_SCHEMA.JOBS_BY_USER`
  where job_id = last_job_id
  and date(creation_time) = current_date()
  order by start_time desc
  limit 1
);

-- 課金されず、スロット消費が最低となる
select 1 from `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` limit 0

-- エラーにより課金されず、スロット消費も最小限となる
select 1 from `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` where error("try error") limit 0

-- エラーにより課金されず、スロット消費も最小限となる
with A as (
  select * from `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` 
)
select error('intentional error')

  select
   partition_key
   , null as group_keys   , count(1) as count
     -- station_id (f1)
          , 'station_id' as f1__name
          , count(station_id is not null) as f1__nonnull
          , approx_count_distinct(station_id) as f1__unique
          , hll_count.init(station_id) as f1__hll
          , sum(station_id) as f1__sum
          , avg(station_id) as f1__avg
          , min(station_id) as f1__min
          , max(station_id) as f1__max
        
          , approx_top_count(station_id, 5) as f1__top_count
          , approx_quantiles(station_id, 20) as f1__20quantile
    -- name (f2)
          , 'name' as f2__name
          , count(name is not null) as f2__nonnull
          , approx_count_distinct(name) as f2__unique
          , hll_count.init(name) as f2__hll
          , avg(CHARACTER_LENGTH(name)) as f2__avg_len
          , min(CHARACTER_LENGTH(name)) as f2__min_len
          , max(CHARACTER_LENGTH(name)) as f2__max_len
        
            , approx_top_count(name, 5) as f2__top_count
            , approx_quantiles(name, 20) as f2__20quantile
   # ...