SQL via ChatGPT - AOKI's copy&paste archive

大前提の経緯
経緯
基礎
As Is: デバック
To Be: 今後のAI活用
- 自然言語のインタラクティブアプローチ
- GitHub Copilotのアプローチ
ChatGPT実用例
別途参考？

大前提の経緯

会社で課内向けにPythonとSQLの研修的なもののPIC的なものを任されてしまった～
— AOKI Takashige (@aochan_0119) May 22, 2023

まあタイミングとしてはGPT系の活用が広まるので絶好だし、自分自身の学び直しのいい機会にもなるのはいいな
これでGW前にしようと思ってた課金にもふんきりがつく
— AOKI Takashige (@aochan_0119) May 22, 2023

その下書きという位置づけで、社内用にまた肉付け必要かな。

Copilotも話したいが時間がないので、別のPythonの枠で活用できればいいかな。
多分そこまで深掘りすると、営業寄りのBU向けの要素はもはやないので。

経緯

生成系AI: ChatGPTの台頭による環境激変
別PJTの発足とその効果測定のニーズの高まり

基礎

一応、課内は全員このあたりは抑えているはずだが、業務上使わない人とかは忘れていそうなので、軽くおさらい的なのが必要そう。

Where
Count Distinct
Group By
Join
Union

SQL記述者全員が理解すべきSELECT文の論理的な処理順序のお話 - Qiita

As Is: デバック

類似事例の検索・コピペ
- チートシートなど
個別事例へのアレンジ

ある程度の能力があれば、ここまではほぼ独力でもできる
また簡単な事例であれば、以下のような作業をせずとも完了できる

テスト実行およびバグ・エラー発生
エラーコードの検索
類似事例の検索：詳細把握
- GitHub
- StackOverFlow
- Qiita
個別事例の起票
- teratail
コードのエラー修正

To Be: 今後のAI活用

自然言語のインタラクティブアプローチ

要件定義丸投げ

やりたいことを自然言語でAIに投げかける

提案されたコードを実行する
実現性を確認する
エラーコードまたは実現できていない部分を指摘し、改善された提案を促す

この作業を完成まで繰り返す

GitHub Copilotのアプローチ

(要件定義丸投げ)
自身で骨組みを作る
AIによる補完的な提案を随時受け入れる

youtu.be
youtu.be

ChatGPT実用例

普遍的：日付指定

以下はChatGPTでの回答。
ビジネス文書と同様にローコンテクストで明確であればあるほど、回答の精度は高まる。
また曖昧な指示などには免責事項をつけて逃げ道を作りがち、逆にそこを塞ぐように意識すれば効率的に答えにたどり着ける。

質問の内容は自動化させる時の指定するクエリに入れられる。
これまでは実行毎に日付を手入力で修正していたが、こうすることで使いまわせ機械にも任せられる。
ここではSELECT文中になっているが、これを自身が書いたWhere文とかに入れられるわけで。

これらのクエリは、一般的なSQLの構文と関数を使用していますが、データベースシステムによっては若干の違いがあるかもしれません。データベースシステムのドキュメントを参照して、正確な構文や関数を確認してください。

sqlでcurrent_dateから当月1日を示す方法と、さらにそのちょうど1年前を示す方法。日付型で

SQLで現在の日付から当月の1日を示す方法は、一般的には以下のようなクエリを使用します（データベースシステムによって若干の違いがある場合があります）。

SELECT DATE_TRUNC('month', CURRENT_DATE) AS first_day_of_month;
このクエリでは、DATE_TRUNC関数を使用してCURRENT_DATEを月の先頭に丸め込みます。first_day_of_monthという別名を付けて、結果を取得しています。

次に、ちょうど1年前の日付を示すために、INTERVALを使用して日付から1年を引く方法があります。以下のようにクエリを書くことができます。

SELECT DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 year' AS one_year_ago;
このクエリでは、CURRENT_DATEからINTERVAL '1 year'を引くことで、ちょうど1年前の日付を計算しています。結果はone_year_agoという別名で表示されます。

これらのクエリは、一般的なSQLの構文と関数を使用していますが、データベースシステムによっては若干の違いがあるかもしれません。データベースシステムのドキュメントを参照して、正確な構文や関数を確認してください。

Bigqueryのsqlでcurrent_dateから当月1日を示す方法と、さらにそのちょうど1年前を示す方法。日付型で

BigQueryのSQLで、CURRENT_DATEを使用して当月1日を示す方法は次のとおりです。

SELECT DATE_TRUNC(CURRENT_DATE(), MONTH) AS first_day_of_month
上記のクエリは、DATE_TRUNC関数を使用して、CURRENT_DATE()を月の最初の日に丸め込んでいます。結果は日付型となります。

また、当月1日からちょうど1年前を示す方法は次のとおりです。

SELECT DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 YEAR) AS one_year_ago

上記のクエリは、DATE_TRUNC関数を使用してCURRENT_DATE()を月の最初の日に丸め込み、DATE_SUB関数を使用してその結果から1年を引いています。結果は日付型となります。

これらのクエリを使用すると、現在の日付から当月1日と1年前の日付を取得できます。

ドメイン：社内課内分析

社内ルールも追いつききれていない感もあるが、当然社外秘的な情報があるので、現状は普遍的に聞いている。
ただドメイン的な内容も結局、世間的にはあるあるな事例でChatGPTにとっても取るに足らない内容が多いらしい。

環境に適応した回答が一発で出せない場合は応用的な場合ではしばしばあるものの、そこはインタラクティブ性を活用してインタラクティブな経緯で修正、個別撃破していけば問題ない。
そこはやや面倒なので、全社契約でGPT4を導入してドメイン情報の前提も入れられると幾分楽なのだが。

また例によってGPT側で免責事項がされているので改めて言うまでもないが、汎用的な回答に対しこちら側で修正すればいい場合がしばしばだ。
特に日付項目のカラム名はorder_timeやらexchange_datetimeやらtimestampやら表記ゆれの嵐になるが、日付項目としての役割は統一されているので、GPT側の回答を置換処理すればいい。
まあさすがに型変換は面倒なので、そこは入力してもいいと思う。

ちなみにドメインとは
youtu.be

実例1：購買データ分析

ECサイトにて同じ日に同じユーザが複数購買している場合のみを抽出するbigquery

以下は、Google BigQueryで同じ日に同じユーザが複数購買している場合のみを抽出するクエリ例です。

SELECT
  user_id,
  DATE_TRUNC(order_date, DAY) AS purchase_date,
  COUNT(DISTINCT order_id) AS num_orders
FROM
  `your_project.your_dataset.your_table`
GROUP BY
  user_id,
  purchase_date
HAVING
  num_orders > 1

このクエリは、your_project.your_dataset.your_tableというテーブルから、ユーザごとに購買日をまとめ、1日に複数回購入したユーザだけを抽出しています。user_idはユーザID、order_dateは注文日、order_idは注文IDを表しています。

DATE_TRUNC関数は、日付を指定した単位で切り捨てる関数です。ここでは、注文日を日単位で切り捨てています。これにより、同じ日に複数回購入したユーザが抽出されます。

COUNT(DISTINCT order_id)は、ユーザごとに注文IDを数える関数です。HAVING num_orders > 1は、注文数が2以上のユーザのみを抽出する条件です。

このクエリを実行すると、同じ日に同じユーザが複数回購入したデータが抽出されます。

そのローデータのレコードを得るには

前提条件として、抽出したいローデータがどのテーブルに格納されているかを確認しておく必要があります。仮に、上記のクエリで抽出したいローデータが your_project.your_dataset.your_table に格納されていると仮定します。

以下のクエリを使用して、同じ日に同じユーザが複数回購入したレコードを抽出することができます。

SELECT 
  *
FROM 
  `your_project.your_dataset.your_table`
WHERE 
  (user_id, DATE_TRUNC(order_date, DAY)) IN (
    SELECT 
      user_id,
      DATE_TRUNC(order_date, DAY) AS purchase_date
    FROM 
      `your_project.your_dataset.your_table`
    GROUP BY 
      user_id,
      purchase_date
    HAVING 
      COUNT(DISTINCT order_id) > 1
  )

このクエリは、以下の手順でデータを抽出しています。

元のテーブル（your_project.your_dataset.your_table）から、同じ日に同じユーザが複数回購入したユーザIDと購入日を抽出します。これは、先に紹介したクエリと同じです。

上記で抽出したユーザIDと購入日をサブクエリに使用し、元のテーブルから該当するレコードを抽出します。具体的には、元のテーブルの user_id と DATE_TRUNC(order_date, DAY) がサブクエリで抽出したユーザIDと購入日のペアに含まれているレコードを取得します。

取得したレコードを返します。

このクエリを実行すると、元のテーブルから同じ日に同じユーザが複数回購入したレコードが抽出されます。

Subquery of type IN must have only one output column at [7:5]

申し訳ありません、前回の回答で誤りがありました。修正したクエリを以下に示します。

SELECT 
  t.*
FROM 
  `your_project.your_dataset.your_table` AS t
JOIN (
  SELECT 
    user_id,
    DATE_TRUNC(order_date, DAY) AS purchase_date
  FROM 
    `your_project.your_dataset.your_table`
  GROUP BY 
    user_id,
    purchase_date
  HAVING 
    COUNT(DISTINCT order_id) > 1
) AS s
ON t.user_id = s.user_id AND DATE_TRUNC(t.order_date, DAY) = s.purchase_date

このクエリは、以下の手順でデータを抽出しています。

上記で抽出したユーザIDと購入日をサブクエリに使用して、該当するレコードを抽出します。ただし、前回の回答と異なり、サブクエリの結果を JOIN 句で元のテーブルに結合する方法を使用しています。

取得したレコードを返します。

このクエリを実行すると、元のテーブルから同じ日に同じユーザが複数回購入したレコードが抽出されます。

joinするので重いです。。

ご指摘の通り、JOIN句を使用するとクエリの実行に時間がかかる可能性があります。代替案として、以下のようにEXISTS 句を使用する方法があります。

SELECT 
  *
FROM 
  `your_project.your_dataset.your_table` AS t1
WHERE 
  EXISTS (
    SELECT 
      1 
    FROM 
      `your_project.your_dataset.your_table` AS t2
    WHERE 
      t1.user_id = t2.user_id 
      AND DATE_TRUNC(t1.order_date, DAY) = DATE_TRUNC(t2.order_date, DAY) 
      AND t1.order_id <> t2.order_id
  )

このクエリは、以下の手順でデータを抽出しています。

元のテーブル（your_project.your_dataset.your_table）から、注文ユーザーが同じで、注文日が同じであり、注文IDが異なるレコードを抽出します。これは、先に紹介したクエリと同様です。

抽出したレコードが元のテーブルに存在する場合、そのレコードを返します。

このクエリを実行すると、元のテーブルから同じ日に同じユーザが複数回購入したレコードが抽出されます。EXISTS 句は、JOIN句よりもクエリの実行速度が速い場合があるため、クエリの実行速度を向上させることができます。

同日の同ユーザの購買を1レコードにまとめて、そのオーダIDを1つのセルにまとめる方法

同じ日に同じユーザーが複数回購入した場合、それらの注文IDを1つのセルにまとめるには、STRING_AGG関数を使用します。以下は、先に紹介したクエリにSTRING_AGG関数を組み合わせた例です。

SELECT 
  user_id,
  DATE_TRUNC(order_date, DAY) AS purchase_date,
  STRING_AGG(order_id, ',') AS order_ids
FROM 
  `your_project.your_dataset.your_table`
GROUP BY 
  user_id,
  purchase_date
HAVING 
  COUNT(DISTINCT order_id) > 1

このクエリでは、同じ日に同じユーザーが複数回購入した場合に、注文IDをカンマ区切りの文字列にまとめ、1つのセルに格納しています。GROUP BY句を使用して、ユーザーIDと購入日でグループ化し、COUNT関数を使用して、グループ内の注文数が2以上であるグループのみを取得します。

このクエリを実行すると、同じ日に同じユーザが複数回購入したレコードが、ユーザーID、購入日、注文IDがカンマ区切りの文字列にまとめられた形式で返されます。

このまとめたセルから特定の組み合わせであるかを判定してフラグ付するには？

前提条件として、特定の組み合わせとはどのようなものでしょうか？例えば、同じ日に同じユーザーが複数回購入し、かつ特定の商品を全て購入している場合を特定の組み合わせとすることを考えてみます。

その場合、以下のようなクエリを使用して、特定の商品を全て購入した注文をフラグ付けすることができます。

WITH orders AS (
  SELECT 
    user_id,
    DATE_TRUNC(order_date, DAY) AS purchase_date,
    STRING_AGG(order_id, ',') AS order_ids
  FROM 
    `your_project.your_dataset.your_table`
  GROUP BY 
    user_id,
    purchase_date
  HAVING 
    COUNT(DISTINCT order_id) > 1
),
order_items AS (
  SELECT 
    user_id,
    order_id,
    item_name
  FROM 
    `your_project.your_dataset.order_items`
  WHERE 
    item_name IN ('item_1', 'item_2', 'item_3') -- 対象の商品を指定
)
SELECT 
  o.*,
  IF(COUNT(DISTINCT oi.item_name) = 3, true, false) AS target_items_flag -- 3つ全て購入している場合にフラグを立てる
FROM 
  orders o
  JOIN order_items oi ON oi.user_id = o.user_id AND oi.order_id IN UNNEST(SPLIT(o.order_ids, ','))
GROUP BY 
  o.user_id,
  o.purchase_date,
  o.order_ids

このクエリでは、先に紹介したクエリに、商品テーブル（your_project.your_dataset.order_items）を結合しています。特定の商品を対象にするため、WHERE句で商品名を指定します。

そして、COUNT関数を使用して、対象商品を全て購入した注文のみを抽出します。IF関数を使用して、3つの商品を全て購入した場合にtrue、それ以外の場合にfalseを返すように設定しています。

このクエリを実行すると、同じ日に同じユーザが複数回購入したレコードから、特定の商品を全て購入した注文がフラグ付けされた形式で返されます。

全てではなく含む場合で構いません

了解です。商品名の一部を含む注文を特定する場合は、LIKE演算子を使用して、商品名の一部を含む注文を抽出することができます。以下は、先に紹介したクエリにLIKE演算子を組み合わせた例です。

WITH orders AS (
  SELECT 
    user_id,
    DATE_TRUNC(order_date, DAY) AS purchase_date,
    STRING_AGG(order_id, ',') AS order_ids
  FROM 
    `your_project.your_dataset.your_table`
  GROUP BY 
    user_id,
    purchase_date
  HAVING 
    COUNT(DISTINCT order_id) > 1
),
order_items AS (
  SELECT 
    user_id,
    order_id,
    item_name
  FROM 
    `your_project.your_dataset.order_items`
  WHERE 
    item_name LIKE '%target_item%' -- 商品名の一部を指定
)
SELECT 
  o.*,
  IF(COUNT(DISTINCT oi.item_name) >= 2, true, false) AS target_items_flag -- 指定した商品名を2つ以上購入している場合にフラグを立てる
FROM 
  orders o
  JOIN order_items oi ON oi.user_id = o.user_id AND oi.order_id IN UNNEST(SPLIT(o.order_ids, ','))
GROUP BY 
  o.user_id,
  o.purchase_date,
  o.order_ids

このクエリでは、商品名の一部を指定するために、LIKE演算子を使用しています。例えば、target_itemという文字列を含む商品名を指定する場合は、'%target_item%'と指定します。また、指定した商品名を2つ以上購入している注文を特定するために、COUNT関数を使用して、指定した商品名の中から2つ以上の商品を購入している注文のみを抽出しています。

このクエリを実行すると、同じ日に同じユーザが複数回購入したレコードから、指定した商品名の一部を含む注文がフラグ付けされた形式で返されます。

target_itemにand文で条件を付加すれば、特定の商品購買の組み合わせで抽出できるわけですね

はい、その通りです。複数の条件を指定したい場合は、AND句を使用して条件を連結することができます。以下は、2つの商品名を指定して、その2つの商品を含む注文を抽出するクエリの例です。

WITH orders AS (
  SELECT 
    user_id,
    DATE_TRUNC(order_date, DAY) AS purchase_date,
    STRING_AGG(order_id, ',') AS order_ids
  FROM 
    `your_project.your_dataset.your_table`
  GROUP BY 
    user_id,
    purchase_date
  HAVING 
    COUNT(DISTINCT order_id) > 1
),
order_items AS (
  SELECT 
    user_id,
    order_id,
    item_name
  FROM 
    `your_project.your_dataset.order_items`
  WHERE 
    item_name LIKE '%target_item_1%' AND item_name LIKE '%target_item_2%' -- 複数の商品名を指定
)
SELECT 
  o.*,
  IF(COUNT(DISTINCT oi.item_name) >= 2, true, false) AS target_items_flag -- 指定した商品名を2つ以上購入している場合にフラグを立てる
FROM 
  orders o
  JOIN order_items oi ON oi.user_id = o.user_id AND oi.order_id IN UNNEST(SPLIT(o.order_ids, ','))
GROUP BY 
  o.user_id,
  o.purchase_date,
  o.order_ids

このクエリでは、2つの商品名を指定するために、AND句を使用しています。例えば、target_item_1とtarget_item_2という2つの商品名を指定する場合は、item_name LIKE '%target_item_1%' AND item_name LIKE '%target_item_2%'と指定します。また、指定した2つの商品を購入している注文を特定するために、COUNT関数を使用して、指定した2つの商品を購入している注文のみを抽出しています。

このクエリを実行すると、同じ日に同じユーザが複数回購入したレコードから、指定した2つの商品を含む注文がフラグ付けされた形式で返されます。

売上のランキングを実売上の数量とともに表示

売上のランキングを実売上の数量とともに表示するためには、SUM関数を使用して商品ごとの実売上数量を計算し、ORDER BY句を使用して降順にソートする必要があります。以下は、商品ごとの実売上数量を計算し、ランキングとともに表示するクエリの例です。

SELECT
  item_name,
  SUM(quantity) AS sales_quantity,
  RANK() OVER (ORDER BY SUM(quantity) DESC) AS sales_rank
FROM
  `your_project.your_dataset.your_table`
GROUP BY
  item_name
ORDER BY
  sales_rank ASC

このクエリでは、SUM関数を使用して商品ごとの実売上数量を計算し、RANK関数を使用して売上ランキングを付与しています。ORDER BY句を使用して、ランキングの昇順にソートしています。また、ランキングとともに商品名と実売上数量も表示するために、SELECT句でそれぞれの列を指定しています。

このクエリを実行すると、商品名、実売上数量、ランキングが表示される結果が返されます。ランキングが1位の商品が最も実売上数量が多く、ランキングが上がるにつれて実売上数量が少なくなっていきます。

このsales_rankにtarget_itemの条件を紐づけて、上位100位以内で絞り込むには？

売上ランキングの上位100位以内で特定の商品購買の組み合わせを抽出するには、以下のようなクエリを使用できます。

WITH ranked_sales AS (
  SELECT
    item_name,
    SUM(quantity) AS sales_quantity,
    RANK() OVER (ORDER BY SUM(quantity) DESC) AS sales_rank
  FROM
    `your_project.your_dataset.your_table`
  GROUP BY
    item_name
)
SELECT
  o.order_id,
  o.user_id,
  STRING_AGG(DISTINCT oi.item_name, ',') WITHIN GROUP (ORDER BY oi.item_name) AS order_items,
  rs.sales_rank
FROM
  `your_project.your_dataset.your_table` AS oi
JOIN
  `your_project.your_dataset.your_table` AS o
ON
  oi.order_id = o.order_id
JOIN
  ranked_sales AS rs
ON
  oi.item_name = rs.item_name
WHERE
  rs.sales_rank <= 100 AND
  oi.item_name IN ('target_item1', 'target_item2', ...)
GROUP BY
  o.order_id,
  o.user_id,
  rs.sales_rank

このクエリでは、まずranked_salesというサブクエリを使用して、商品ごとの実売上数量とランキングを計算しています。次に、JOINを使用して、オーダーごとの商品購買情報とranked_salesの情報を結合しています。そして、WHERE句を使用して、ランキングが100位以内かつ指定した商品購買の組み合わせを含むオーダーのみを抽出しています。

最後に、GROUP BY句を使用して、オーダーID、ユーザーID、ランキングごとにグループ化し、SELECT句でそれぞれの列を指定しています。また、商品名はSTRING_AGG関数を使用してカンマ区切りの文字列にまとめています。

このクエリを実行すると、ランキングの上位100位以内で指定した商品購買の組み合わせを含むオーダーのみが表示されます。オーダーID、ユーザーID、商品名、ランキングの順で表示されます。

bigqueryで単にjoinと記載した際の結合方式はinner, left, right, full, crossのどれですか

「単にjoin」という記載だけであれば、デフォルトで使用されるのはINNER JOINです。INNER JOINは、結合条件を満たす行のみを返し、両方のテーブルに共通する値のみが含まれます。他の結合方式を使用する場合は、明示的にLEFT JOIN、RIGHT JOIN、FULL OUTER JOINなどを指定する必要があります。CROSS JOINは、結合条件なしに全ての行を結合するため、別途指定する必要があります。

「target_itemにand文で条件を付加すれば、特定の商品購買の組み合わせで抽出できるわけですね」上記の回答のクエリを参考にしたところ、対象外の購買のデータが抜けてしまいました。0カウントでもいいので残したいので、改善版を提示して

ご指摘ありがとうございます。対象外の購買データが抜けてしまうのは、INNER JOINを使用しているためです。改善版のクエリを以下に示します。

WITH 
  user_order_items AS (
    SELECT 
      user_id, 
      order_id,
      item_id
    FROM 
      `dataset.order_items` 
    WHERE 
      date(order_time) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND CURRENT_DATE()
  ),
  user_order_combinations AS (
    SELECT 
      a.user_id, 
      a.order_id, 
      b.order_id AS target_order_id, 
      a.item_id AS item_id1, 
      b.item_id AS item_id2
    FROM 
      user_order_items a
      INNER JOIN user_order_items b ON a.user_id = b.user_id AND a.order_id != b.order_id
    WHERE 
      a.item_id < b.item_id AND date(a.order_time) = date(b.order_time)
  )
SELECT 
  a.target_order_id, 
  STRING_AGG(DISTINCT CONCAT(a.item_id1, '_', a.item_id2), '|') AS item_pairs,
  SUM(b.quantity) AS total_quantity,
  RANK() OVER (ORDER BY SUM(b.quantity) DESC) AS sales_rank
FROM 
  user_order_combinations a
  LEFT JOIN `dataset.order_items` b ON a.target_order_id = b.order_id AND (b.item_id = SPLIT(a.item_pairs, '|')[OFFSET(0)] OR b.item_id = SPLIT(a.item_pairs, '|')[OFFSET(1)])
WHERE 
  SPLIT(a.item_pairs, '|')[OFFSET(0)] = 'target_item' OR SPLIT(a.item_pairs, '|')[OFFSET(1)] = 'target_item'
GROUP BY 
  1
HAVING 
  sales_rank <= 100

主な変更点は、INNER JOINをLEFT JOINに変更し、WHERE句でSPLIT(a.item_pairs, '|')[OFFSET(0)] = 'target_item' OR SPLIT(a.item_pairs, '|')[OFFSET(1)] = 'target_item'として、購入された商品のいずれかが対象商品であるレコードを抽出するようにしました。また、LEFT JOINにしたため、SUM(b.quantity)をCOALESCE(SUM(b.quantity), 0)で置き換えることで、0カウントのデータも抽出できるようにしています。

これを最小限の労力で0も含まれるよう改善するには？

WITH orders AS (
  SELECT 
    user_id,
    DATE_TRUNC(order_date, DAY) AS purchase_date,
    STRING_AGG(order_id, ',') AS order_ids
  FROM 
    `your_project.your_dataset.your_table`
  GROUP BY 
    user_id,
    purchase_date
  HAVING 
    COUNT(DISTINCT order_id) > 1
),
order_items AS (
  SELECT 
    user_id,
    order_id,
    item_name
  FROM 
    `your_project.your_dataset.order_items`
  WHERE 
    item_name LIKE '%target_item_1%' AND item_name LIKE '%target_item_2%' -- 複数の商品名を指定
)
SELECT 
  o.*,
  IF(COUNT(DISTINCT oi.item_name) >= 2, true, false) AS target_items_flag -- 指定した商品名を2つ以上購入している場合にフラグを立てる
FROM 
  orders o
  JOIN order_items oi ON oi.user_id = o.user_id AND oi.order_id IN UNNEST(SPLIT(o.order_ids, ','))
GROUP BY 
  o.user_id,
  o.purchase_date,
  o.order_ids

(ここから回答)
商品の組み合わせに含まれていない注文については、LEFT JOINを使ってordersとorder_itemsを結合し、その結果を元にフラグを立てることで、0も含めて抽出することができます。以下はその改善版クエリです。

Copy code
WITH orders AS (
  SELECT 
    user_id,
    DATE_TRUNC(order_date, DAY) AS purchase_date,
    STRING_AGG(order_id, ',') AS order_ids
  FROM 
    `your_project.your_dataset.your_table`
  GROUP BY 
    user_id,
    purchase_date
  HAVING 
    COUNT(DISTINCT order_id) > 1
),
order_items AS (
  SELECT 
    user_id,
    order_id,
    item_name
  FROM 
    `your_project.your_dataset.order_items`
  WHERE 
    item_name LIKE '%target_item_1%' AND item_name LIKE '%target_item_2%' -- 複数の商品名を指定
)
SELECT 
  o.*,
  IF(COUNT(DISTINCT oi.item_name) >= 2, true, false) AS target_items_flag -- 指定した商品名を2つ以上購入している場合にフラグを立てる
FROM 
  orders o
  LEFT JOIN order_items oi ON oi.user_id = o.user_id AND oi.order_id IN UNNEST(SPLIT(o.order_ids, ','))
GROUP BY 
  o.user_id,
  o.purchase_date,
  o.order_ids

LEFT JOINを使うことで、order_itemsに存在しないorder_idの場合でも、ordersの情報は残ります。そして、COUNT(DISTINCT oi.item_name) >= 2の部分は、COUNT関数の戻り値がNULLになる可能性があるため、IF関数でNULLに対してfalseを返すようにしています。

実例2：ユーザ分析

年度、ユーザID、ユーザカテゴリごとの情報のテーブルがあります。ここから年度ごとのユーザカテゴリの変遷のIDの集計値をBigqueryで算出するクエリは

以下のクエリを使用して、BigQueryで年度ごとのユーザカテゴリの変遷のIDの集計値を算出できます。

SELECT
  年度,
  ユーザカテゴリ,
  COUNT(DISTINCT ユーザID) AS ID数
FROM
  テーブル名
GROUP BY
  年度,
  ユーザカテゴリ
ORDER BY
  年度

上記のクエリでは、テーブル名をデータが格納されている実際のテーブル名に置き換えてください。また、年度、ユーザID、ユーザカテゴリは、実際のテーブルのカラム名に置き換えてください。

このクエリは、指定されたテーブルから年度ごとにユーザカテゴリとユーザIDの組み合わせの集計値を算出し、結果を年度の昇順で表示します。各年度のユーザカテゴリとその年度におけるユーザIDの数を確認することができます。

例として2019年Aカテゴリで2020年Bカテゴリになった人の人数といったようなものを存在するすべての組み合わせで集計したいです

指定された条件での組み合わせごとの集計を行うためには、サブクエリを使用する方法があります。以下のクエリは、2019年Aカテゴリから2020年Bカテゴリに変わったユーザの人数を含むすべての組み合わせの集計を行います。

SELECT
  t1.年度 AS 前年度,
  t1.ユーザカテゴリ AS 前ユーザカテゴリ,
  t2.年度 AS 後年度,
  t2.ユーザカテゴリ AS 後ユーザカテゴリ,
  COUNT(DISTINCT t1.ユーザID) AS ID数
FROM
  テーブル名 AS t1
JOIN
  テーブル名 AS t2
ON
  t1.ユーザID = t2.ユーザID
WHERE
  t1.年度 = 2019
  AND t1.ユーザカテゴリ = 'A'
  AND t2.年度 = 2020
  AND t2.ユーザカテゴリ = 'B'
GROUP BY
  t1.年度,
  t1.ユーザカテゴリ,
  t2.年度,
  t2.ユーザカテゴリ

このクエリでは、テーブルを自己結合して、条件に基づいて前年度と後年度の組み合わせを選択します。その後、組み合わせごとにユーザIDの重複を排除し、結果を集計して表示します。上記の例では、2019年のAカテゴリから2020年のBカテゴリに変わったユーザの人数を表示します。必要に応じて、年度とユーザカテゴリの条件を変更して、他の組み合わせに適用することもできます。

データは4年分あります。そこでの汎用的で効率的な方法は？

データが4年分ある場合、汎用的で効率的な方法としては、窓関数を使用して前後の年度の組み合わせを比較する方法があります。以下のクエリは、任意の年度の前年度との組み合わせを比較し、集計する例です。

WITH cte AS (
  SELECT
    年度,
    ユーザID,
    ユーザカテゴリ,
    LAG(ユーザカテゴリ) OVER (PARTITION BY ユーザID ORDER BY 年度) AS 前ユーザカテゴリ
  FROM
    テーブル名
)
SELECT
  年度 AS 後年度,
  前ユーザカテゴリ AS 前ユーザカテゴリ,
  ユーザカテゴリ AS 後ユーザカテゴリ,
  COUNT(DISTINCT ユーザID) AS ID数
FROM
  cte
WHERE
  年度 > 2019
GROUP BY
  年度,
  前ユーザカテゴリ,
  ユーザカテゴリ

上記のクエリでは、CTE（Common Table Expression）を使用して、元のテーブルから各年度ごとのユーザIDとユーザカテゴリ、前年度のユーザカテゴリを取得します。窓関数のLAGを使用して前年度のユーザカテゴリを取得し、その結果をCTE内に保存します。

その後、CTE内のデータを使って、後年度と前年度の組み合わせごとにユーザIDの重複を排除し、結果を集計して表示します。上記の例では、2019年以降の各年度において、前年度と後年度のユーザカテゴリの組み合わせとユーザIDの数を表示します。

この方法は、データの年数に関係なく汎用的に適用できますし、窓関数を使用することで効率的に処理できます。必要に応じて、クエリ内の条件を変更して他の組み合わせを集計することもできます。

これは連続する1年の比較ですが、離れた年度で比較してすべての組み合わせで集計する場合はどうなりますか

離れた年度での比較を行い、すべての組み合わせで集計する場合、CROSS JOINを使用して組み合わせを生成する方法があります。以下のクエリは、すべての年度間の組み合わせを生成し、それぞれの組み合わせでのユーザIDの数を集計します。

WITH cte AS (
  SELECT
    t1.年度 AS 前年度,
    t1.ユーザID,
    t1.ユーザカテゴリ AS 前ユーザカテゴリ,
    t2.年度 AS 後年度,
    t2.ユーザカテゴリ AS 後ユーザカテゴリ
  FROM
    テーブル名 AS t1
  CROSS JOIN
    テーブル名 AS t2
  WHERE
    t1.年度 < t2.年度
)
SELECT
  前年度,
  前ユーザカテゴリ,
  後年度,
  後ユーザカテゴリ,
  COUNT(DISTINCT ユーザID) AS ID数
FROM
  cte
GROUP BY
  前年度,
  前ユーザカテゴリ,
  後年度,
  後ユーザカテゴリ
ORDER BY
  前年度,
  後年度

上記のクエリでは、CTE（Common Table Expression）を使用して、CROSS JOINによってすべての年度間の組み合わせを生成します。その後、組み合わせごとにユーザIDの重複を排除し、結果を集計して表示します。

このクエリは、すべての年度間の組み合わせを生成するため、データセットが大きくなる場合には処理時間が増える可能性があります。そのため、データセットのサイズや性能に合わせて、実行可能な範囲で処理を行うことが重要です。必要に応じて、クエリ内の条件や集計の粒度を調整してください。

Cross joinはいいですが、異なるユーザの組み合わせが集計されてしまいませんか？

申し訳ありません、先の回答においてユーザIDを考慮していなかったため、異なるユーザの組み合わせが集計されてしまいます。ユーザIDを考慮して正確な結果を得るために、以下のように修正したクエリをご提供します。

WITH cte AS (
  SELECT
    t1.年度 AS 前年度,
    t1.ユーザID,
    t1.ユーザカテゴリ AS 前ユーザカテゴリ,
    t2.年度 AS 後年度,
    t2.ユーザカテゴリ AS 後ユーザカテゴリ
  FROM
    テーブル名 AS t1
  JOIN
    テーブル名 AS t2
  ON
    t1.ユーザID = t2.ユーザID
  WHERE
    t1.年度 < t2.年度
)
SELECT
  前年度,
  前ユーザカテゴリ,
  後年度,
  後ユーザカテゴリ,
  COUNT(DISTINCT ユーザID) AS ID数
FROM
  cte
GROUP BY
  前年度,
  前ユーザカテゴリ,
  後年度,
  後ユーザカテゴリ
ORDER BY
  前年度,
  後年度