過激なタイトルですが、とても優しい内容ですw BigQuery（以下、BQ）をよく使います。とても便利なのですが、PRIMARY KEY制約（またはUNIQUE制約）がないのが不満です。

例えば、こんなテーブルがあったとき（あえてDDLで書いてます）

CREATE TABLE clients (
     client_id NUMERIC
  ,  client_name VARCHAR
  ,  create_at TIMESTAMP
);

client_idにPRIMARY KEY制約が張られていないと、キーが重複するデータが存在しない可能性を否定できません。

作成者に言わせると「いや、clientsってテーブルだから、client_idで一意になるのは当然っしょ♪」ってなるかもだけどうるせー。こっちは、お前の書いたプログラムも、テーブル設計も、ETLのスクリプトも全くもって信用してねー（お前＝1年前の俺、とかだったりするのが悲しいところ、、、）

そして、キーが重複すると結合したとき地獄をみます。たとえば、logってテーブル（スタースキーマでいう、Factテーブル）と前述のclients（ディメンションテーブル）を結合するとき

SELECT
      client_id
   ,  count(1) as cnt
FROM
  log JOIN clients USING(client_id)
;

重複データがあるとログの件数が２倍、３倍とかになってしまいます（この集計結果を元に課金とかしてると、誤課金ですね♪）

ということで、自分の身を守るために、結合する前に重複を排除する必要があります。愚直にやるなら下記のように、GROUP BYを使うやり方があります。

SELECT
     client_id
  ,  MIN(client_name) AS client_name
  ,  MIN(create_at) AS create_at
FROM
  clients
GROUP BY
  1
;

ただ、これだとカラムが増えるたびにメンテしなきゃだし、なにより全部のカラムを集合関数で書き直すのがめんどうです。

そこで、おそらくBQだけですが、STRUCT型を使って下記のようにすると、もっと楽にかけます。

WITH
  -- いったんSTRUCTに
     s AS ( SELECT AS STRUCT c FROM clients)
  -- idでGROUP BY、のこりはANY_VALUE
  ,  g AS ( SELECT c.client_id, ANY_VALUE(c) AS c FROM s GROUP BY 1
  -- cを展開
  SELECT c.* FROM g
;

ところで、BQは実はSELECT句でテーブル名を指定すると、STRUCTとして展開してくれます。例えば、下記のようなクエリを実行すると

WITH clients AS (
  SELECT 1 AS client_id, 'クライアント名' AS client_name, timestamp('2022-12-01') AS create_at
)
SELECT clients FROM clients
;

結果はこうなります

※jsonだとこう

[{
  "clients": {
    "client_id": "1",
    "client_name": "クライアント名",
    "create_at": "2022-12-01 00:00:00.000000 UTC"
  }
}]

この仕組みを使うと、実は１行で書けます

SELECT client_id, ARRAY_AGG(clients ORDER BY create_at LIMIT 1)[OFFSET(0)].* EXCEPT (client_id) FROM clients GROUP BY 1;

ARRAY_AGG(clients)とすることで、clientsテーブルのSTRUCTのARRAYにしています。[OFFSET(0)]で１個目の要素をとりだし、.*で構造体の中身を展開しています。EXCEPT (client_id)は、グループ化の基準列もclient_idでダブっているので、１つ除外しています。また、ARRAY_AGG内でORDER BY hoge とすることで、実行のたびに結果が変わるのを防いでいます（この例だと、create_atが重複したら意味ないですがw）

ということで、上記でviewをつくって自分の管理するデータセットに置いとけば、無用な心配をする必要なく、結合できますね。

CREATE VIEW my_clients
AS
SELECT client_id, array_agg(clients ORDER BY create_at LIMIT 1)[OFFSET(0)].* EXCEPT (client_id) FROM clients GROUP BY 1;

番外編

重複があっても正しい集計結果を出す方法が他に、log側でUUIDを作ってユニークをとる、というやり方もあります。

WITH l AS (SELECT GENERATE_UUID() AS uuid, log.* FROM log)
SELECT
      client_id
   ,  COUNT(DISTINCT uuid) as cnt
FROM
  l JOIN clients USING(client_id)
;

が、FACTテーブルは大概でかいので、スロットの消費時間がえげつないことになるので、やめたほうがいいでしょう。

doilux’s tech blog

ITに関する備忘録。 DDP : http://doiluxng.hatenablog.com/entry/2018/01/01/195409

他人がつくったテーブルなど信用しない

番外編