Zenn
Open5

SQLメモ

SQL
BigQuery
YuichiYuichi

bigqueryで誕生日から年齢を算出

      ,DATE_DIFF(CURRENT_DATE('Asia/Tokyo'),birthday,YEAR)
      - CASE
        WHEN DATE_ADD(
          birthday
          ,INTERVAL DATE_DIFF(CURRENT_DATE('Asia/Tokyo'),birthday,YEAR) YEAR
        ) > CURRENT_DATE('Asia/Tokyo'
        ) THEN 1
        ELSE 0
      END AS age -- 誕生日をまだ迎えていない場合は年を引く
YuichiYuichi

bigqueryで２つのテーブルの一致率

WITH t1 as (
  --
)

,t2 as (
 --
)

,matched_records AS (
  SELECT COUNT(*) AS matched_count
  FROM t1
  JOIN t2
  ON t1.id = t2.id
)

, total_records AS ( -- 全体のレコード数を取得
  SELECT
    (SELECT COUNT(*) FROM t1) AS total_count1,
    (SELECT COUNT(*) FROM t2) AS total_count2
)

SELECT -- 一致率を計算
  matched_count,
  total_count1,
  total_count2,
  (matched_count / GREATEST(total_count1, total_count2)) * 100 AS match_rate
FROM
  matched_records,total_records
YuichiYuichi

https://docs.snowflake.com/ja/user-guide/querying-approximate-similarity
2つ以上のセットの類似性の推定
同じことBQでできるか確認


with mhtab1 as (
  SELECT 1 AS c1, 1.1 AS c2, 'item 1' AS c3, DATE('2016-11-30') AS c4 UNION ALL
  SELECT 2, 2.31, 'item 2', DATE('2016-11-30') UNION ALL
  SELECT 3, 1.1, 'item 3', DATE('2016-11-29') UNION ALL
  SELECT 4, 44.4, 'item 4', DATE('2016-11-30')
)
,mhtab2 as (
  SELECT 1 AS c1, 1.1 AS c2, 'item 1' AS c3, DATE('2016-11-30') AS c4 UNION ALL
  SELECT 2, 2.31, 'item 2', DATE('2016-11-30') UNION ALL
  SELECT 3, 1.1, 'item 3', DATE('2016-11-29') UNION ALL
  SELECT 4, 44.4, 'item 4', DATE('2016-11-30') UNION ALL
  SELECT 6, 34.23, 'item 6', DATE('2016-11-29')
)

,minhash_data AS (
  SELECT FARM_FINGERPRINT(ARRAY_AGG(STRUCT(c1, c2, c3, c4))) AS mh
  FROM (
    SELECT c1, c2, c3, c4 FROM mhtab1
    UNION ALL
    SELECT c1, c2, c3, c4 FROM mhtab2
  )
)
SELECT APPROXIMATE_SIMILARITY(mh) AS similarity
FROM minhash_data;

https://cloud.google.com/bigquery/docs/reference/standard-sql/hash_functions
ハッシュ関数は色々ありそう

名前 まとめ
FARM_FINGERPRINT FarmHash Fingerprint64 アルゴリズムを使用して、 STRINGまたは 値 のフィンガープリントを計算します。BYTES
MD5 MD5 アルゴリズムを使用して、 STRINGまたは 値 のハッシュを計算します。BYTES
SHA1 SHA-1 アルゴリズムを使用して、 STRINGまたは 値 のハッシュを計算します。BYTES
SHA256 SHA-256 アルゴリズムを使用して、 STRINGまたは 値 のハッシュを計算します。BYTES
SHA512 SHA-512 アルゴリズムを使用して、 STRINGまたは 値 のハッシュを計算します。BYTES

https://cloud.google.com/bigquery/docs/migration/snowflake-sql?hl=ja

Snowflake BigQuery
MINHASH(k, [DISTINCT] expressions) カスタム UDF を使用して、k の個別のハッシュ関数で MINHASH を実装できます。MINHASH のバリアンスを削減するもう一つの方法は、1 つのハッシュ関数の最小値を k にすることです。
APPROXIMATE_JACCARD_INDEX([DISTINCT] expression) カスタム UDF を使用して、k の個別のハッシュ関数で MINHASH を実装できます。MINHASH のバリアンスを削減するもう 1 つの方法は 1 つのハッシュ関数の最小値を k にすることです。この場合、Jaccard インデックスは次のように近似できます。 
with mhtab1 as (
  SELECT 1 AS c1, 1.1 AS c2, 'item 1' AS c3, DATE('2016-11-30') AS c4 UNION ALL
  SELECT 2, 2.31, 'item 2', DATE('2016-11-30') UNION ALL
  SELECT 3, 1.1, 'item 3', DATE('2016-11-29') UNION ALL
  SELECT 4, 44.4, 'item 4', DATE('2016-11-30')
)
,mhtab2 as (
  SELECT 1 AS c1, 1.1 AS c2, 'item 1' AS c3, DATE('2016-11-30') AS c4 UNION ALL
  SELECT 2, 2.31, 'item 2', DATE('2016-11-30') UNION ALL
  SELECT 3, 1.1, 'item 3', DATE('2016-11-29') UNION ALL
  SELECT 4, 44.4, 'item 4', DATE('2016-11-30') UNION ALL
  SELECT 6, 34.23, 'item 6', DATE('2016-11-29')
)

,minhash_A AS (
  SELECT DISTINCT FARM_FINGERPRINT(TO_JSON_STRING(t)) AS h
  FROM mhtab1 AS t
  ORDER BY h
  LIMIT 100
)

,minhash_B AS (
  SELECT DISTINCT FARM_FINGERPRINT(TO_JSON_STRING(t)) AS h
  FROM mhtab2 AS t
  ORDER BY h
  LIMIT 100
)

SELECT
  COUNT(*) / 100 AS APPROXIMATE_JACCARD_INDEX
FROM minhash_A
INNER JOIN minhash_B
  ON minhash_A.h = minhash_B.h

同じ結果にならなかった終わり

YuichiYuichi

BQのクエリ履歴

SELECT
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', creation_time, 'Asia/Tokyo') as jst,
  concat(
    referenced_table.project_id,"."
    ,referenced_table.dataset_id,"."
    ,referenced_table.table_id) as table,
  query
FROM 
  `project_id`.`region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
  UNNEST(referenced_tables) AS referenced_table
WHERE 
 state = 'DONE'
  AND DATE(creation_time) > DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  -- and referenced_table.table_id like ""
ORDER BY 
  creation_time DESC
YuichiYuichi

標準偏差

BigQueryで、ウェブの閲覧回数とそれに基づくユーザー数から標準偏差を計算するには、SQLの標準偏差関数 STDDEV_POP（母集団の標準偏差）や STDDEV_SAMP（サンプルの標準偏差）を使うことができます。どちらを使うかは、全体母集団を対象にするか、サンプルを対象にするかによります。

以下は、BigQueryのテーブル web_activity があり、その中に各ユーザーの user_id と各ユーザーの page_views（閲覧回数）のデータがある場合の例です。このデータから各ユーザーの閲覧回数の標準偏差を計算します。

例: web_activity テーブル

  • user_id: ユーザーの識別子
  • page_views: ユーザーごとの閲覧回数

クエリ例:

WITH web_activity AS (
  -- サンプルデータを定義
  SELECT "user_1" AS user_id, 5 AS page_views UNION ALL
  SELECT "user_2" AS user_id, 8 AS page_views UNION ALL
  SELECT "user_3" AS user_id, 2 AS page_views UNION ALL
  SELECT "user_4" AS user_id, 10 AS page_views UNION ALL
  SELECT "user_5" AS user_id, 6 AS page_views
)

-- 標準偏差の計算
SELECT
  STDDEV_POP(page_views) AS page_view_stddev -- 母集団の標準偏差
FROM
  web_activity;

説明:

  • WITH web_activity AS (...): サンプルデータを作成するためのWITH句。5人のユーザーがそれぞれの閲覧回数 (page_views) を持っています。
  • STDDEV_POP(page_views): 母集団全体を基に閲覧回数の標準偏差を計算します。

実行すると、このクエリはサンプルデータに基づくページ閲覧回数の標準偏差を返します。

結果の例:

例えば、このサンプルデータに基づいて標準偏差を計算すると、結果は約 2.83 となります（実際の計算結果は若干異なる場合があります）。

WITH句で定義したサンプルデータを使い、ユーザーごとの95%信頼区間を計算する場合、ページ閲覧回数のデータが1つしかないため、標準偏差を計算することができません。標準偏差はデータが複数必要なため、信頼区間も同様に計算できません。

ただし、ページ閲覧回数のデータが1つだけの場合、信頼区間を計算するのは理論的に難しいので、異なるページ閲覧回数を持つ複数のデータポイントが必要です。

ここでは、単純に全体のページ閲覧回数に対して信頼区間を計算する例を示します。ユーザーごとのページ閲覧回数データが少ない場合は、すべてのユーザーのページ閲覧回数をまとめて全体の信頼区間を計算します。

クエリ例:


WITH web_activity AS (
  -- サンプルデータを定義
  SELECT "user_1" AS user_id, 5 AS page_views UNION ALL
  SELECT "user_2" AS user_id, 8 AS page_views UNION ALL
  SELECT "user_3" AS user_id, 2 AS page_views UNION ALL
  SELECT "user_4" AS user_id, 10 AS page_views UNION ALL
  SELECT "user_5" AS user_id, 6 AS page_views
)

-- 全体の平均、標準偏差、信頼区間を計算
SELECT
  AVG(page_views) AS avg_page_views,  -- 平均
  STDDEV_SAMP(page_views) AS stddev_page_views,  -- 標準偏差
  COUNT(page_views) AS sample_size,  -- サンプルサイズ
  -- 95%信頼区間の下限
  AVG(page_views) - 1.96 * (STDDEV_SAMP(page_views) / SQRT(COUNT(page_views))) AS ci_lower,
  -- 95%信頼区間の上限
  AVG(page_views) + 1.96 * (STDDEV_SAMP(page_views) / SQRT(COUNT(page_views))) AS ci_upper
FROM
  web_activity;

説明:

  • AVG(page_views): ページ閲覧回数の全体平均。
  • STDDEV_SAMP(page_views): 標本の標準偏差を計算します。
  • COUNT(page_views): サンプルサイズを計算します。
  • AVG(page_views) - 1.96 * (STDDEV_SAMP(page_views) / SQRT(COUNT(page_views))): 95%信頼区間の下限を計算します。
  • AVG(page_views) + 1.96 * (STDDEV_SAMP(page_views) / SQRT(COUNT(page_views))): 95%信頼区間の上限を計算します。

実行結果の例:

このクエリを実行すると、全体のページ閲覧回数に基づいた95%信頼区間が得られます。

  • 平均閲覧回数、標準偏差、信頼区間の上限と下限が表示されます。

データポイントが少ないため、推定される信頼区間が広がる可能性があります。

母集団を用いたクエリ:

WITH web_activity AS (
  -- サンプルデータを定義
  SELECT "user_1" AS user_id, 5 AS page_views UNION ALL
  SELECT "user_2" AS user_id, 8 AS page_views UNION ALL
  SELECT "user_3" AS user_id, 2 AS page_views UNION ALL
  SELECT "user_4" AS user_id, 10 AS page_views UNION ALL
  SELECT "user_5" AS user_id, 6 AS page_views
)

-- 母集団の平均、標準偏差、信頼区間を計算
SELECT
  AVG(page_views) AS avg_page_views,  -- 平均
  STDDEV_POP(page_views) AS stddev_page_views,  -- 母集団の標準偏差
  COUNT(page_views) AS population_size,  -- 母集団サイズ
  -- 95%信頼区間の下限
  AVG(page_views) - 1.96 * (STDDEV_POP(page_views) / SQRT(COUNT(page_views))) AS ci_lower,
  -- 95%信頼区間の上限
  AVG(page_views) + 1.96 * (STDDEV_POP(page_views) / SQRT(COUNT(page_views))) AS ci_upper
FROM
  web_activity;

説明:

  • STDDEV_POP(page_views): 母集団全体の標準偏差を計算します。これを使うことで、母集団に基づいた信頼区間を計算できます。
  • AVG(page_views) - 1.96 * (STDDEV_POP(page_views) / SQRT(COUNT(page_views))): 母集団の95%信頼区間の下限。
  • AVG(page_views) + 1.96 * (STDDEV_POP(page_views) / SQRT(COUNT(page_views))): 母集団の95%信頼区間の上限。

実行結果:

このクエリを実行すると、母集団データに基づいた95%信頼区間が得られます。