SQL に関する偉大なる下記書籍を復習する機会があったため、その内容をメモしておきます。実行環境は BigQuery です。
目次。
- 水平展開
- 重複行の抽出
- ウィンドウ関数による行間比較
- 重複期間の抽出
- 部分的不一致な行の抽出
- 欠落の判定
- 条件を満たす集合を探す（HAVING 句と特性関数）
- 存在量化子、全称量化子
- 順列、組み合わせ
- 集合演算
- 関係演算
水平展開
行から列（CASE 式）
テスト用のテーブルを用意。
CREATE OR REPLACE TABLE test.test_score AS SELECT LPAD(CAST(num AS STRING), 6, "0") AS student_id, CASE MOD(CAST((RAND()*10) AS INT64), 5) WHEN 0 THEN "A組" WHEN 1 THEN "B組" WHEN 2 THEN "C組" WHEN 3 THEN "D組" WHEN 4 THEN "E組" ELSE NULL END AS class, CASE MOD(CAST((RAND()*10) AS INT64), 2) WHEN 0 THEN "man" WHEN 1 THEN "woman" ELSE NULL END AS sex, CAST((RAND()*100) AS INT64) AS score FROM UNNEST(GENERATE_ARRAY(1, 100)) AS num ;
test.test_score
| student_id | class | sex | score |
|---|---|---|---|
| 000001 | C 組 | woman | 94 |
| 000002 | B 組 | man | 12 |
| 000003 | A 組 | woman | 79 |
| 000004 | E 組 | woman | 48 |
| 000005 | B 組 | man | 77 |
| 000006 | C 組 | woman | 71 |
| 000007 | B 組 | man | 65 |
| 000008 | D 組 | man | 19 |
| 000009 | A 組 | man | 48 |
| 000010 | E 組 | woman | 91 |
| 000011 | A 組 | woman | 8 |
| 000012 | D 組 | man | 75 |
| 000013 | C 組 | woman | 44 |
| 000014 | C 組 | man | 96 |
| ... | ... | ... | ... |
sex 列を man と woman に分けて、それぞれ class 毎の平均点を表示させます。
以下は CASE 式を用いた例です。AVG() 関数内で CASE 式を利用しています。CASE 式は SELECT 句以外にも、WHERE 句や GROUP BY 句、HAVING 句にも書くことができ、本投稿内ではそのような使い方が頻出します。
SELECT class, ROUND(AVG(CASE WHEN sex = "man" THEN score ELSE NULL END), 2) AS men, ROUND(AVG(CASE WHEN sex = "woman" THEN score ELSE NULL END), 2) AS women FROM test.test_score GROUP BY class ORDER BY class;
| class | men | women |
|---|---|---|
| A 組 | 49.0 | 63.62 |
| B 組 | 50.36 | 39.3 |
| C 組 | 49.15 | 69.08 |
| D 組 | 47.13 | 55.14 |
| E 組 | 51.33 | 56.0 |
以下は、男子生徒と女子生徒のクラス毎平均点を求めるテーブルをサブクエリでそれぞれ作成し、それを内部結合させて表示させたものです。
SELECT men_avg.class AS class, men, women FROM ( (SELECT class, ROUND(AVG(score), 2) AS men FROM test.test_score WHERE sex = "man" GROUP BY class) AS men_avg INNER JOIN (SELECT class, ROUND(AVG(score), 2) AS women FROM test.test_score WHERE sex = "woman" GROUP BY class) AS women_avg ON men_avg.class = women_avg.class ) ORDER BY class;
| class | men | women |
|---|---|---|
| A 組 | 49.0 | 63.62 |
| B 組 | 50.36 | 39.3 |
| C 組 | 49.15 | 69.08 |
| D 組 | 47.13 | 55.14 |
| E 組 | 51.33 | 56.0 |
以下は、AVG 関数をウィンドウ関数を利用して表示したものです。
SELECT DISTINCT class, ROUND(AVG(IF(sex = "man", score, NULL)) OVER (PARTITION BY class), 2) AS men, ROUND(AVG(IF(sex = "woman", score, NULL)) OVER (PARTITION BY class), 2) AS women FROM test.test_score ORDER BY class;
| class | men | women |
|---|---|---|
| A 組 | 49.0 | 63.62 |
| B 組 | 50.36 | 39.3 |
| C 組 | 49.15 | 69.08 |
| D 組 | 47.13 | 55.14 |
| E 組 | 51.33 | 56.0 |
- 参考
- 達人に学ぶ SQL 徹底指南書 - 1.CASE 式のすすめ
行から列（外部結合・スカラサブクエリ）
テスト用のテーブルを用意。
CREATE OR REPLACE TABLE test.testdata AS ( SELECT "夜空" AS name, "A" AS class UNION ALL SELECT "夜空" AS name, "B" AS class UNION ALL SELECT "赤井" AS name, "B" AS class UNION ALL SELECT "白上" AS name, "A" AS class UNION ALL SELECT "白上" AS name, "C" AS class UNION ALL SELECT "夏色" AS name, "B" AS class UNION ALL SELECT "夏色" AS name, "C" AS class );
test.testdata
| name | class |
|---|---|
| 夏色 | B |
| 夏色 | C |
| 夜空 | B |
| 夜空 | A |
| 白上 | A |
| 白上 | C |
| 赤井 | B |
class 列を、それぞれ A 列と B 列と C 列に分けて、値の有無のチェック印を登録します。
以下は、各列毎にスカラサブクエリを実行したものです。
SELECT DISTINCT name, (SELECT "○" FROM test.testdata AS T1 WHERE class = "A" AND T1.name = T0.name) AS A, (SELECT "○" FROM test.testdata AS T2 WHERE class = "B" AND T2.name = T0.name) AS B, (SELECT "○" FROM test.testdata AS T3 WHERE class = "C" AND T3.name = T0.name) AS C FROM test.testdata AS T0;
| name | A | B | C |
|---|---|---|---|
| 夏色 | (NULL) | ○ | ○ |
| 夜空 | ○ | ○ | (NULL) |
| 白上 | ○ | (NULL) | ○ |
| 赤井 | (NULL) | ○ | (NULL) |
値 A をもつテーブル、値 B をもつテーブル、値 C をもつテーブルをそれぞれ用意し、外部結合して表示したものです。
SELECT T0.name AS name, CASE WHEN T1.name IS NOT NULL THEN "○" ELSE NULL END AS A, CASE WHEN T2.name IS NOT NULL THEN "○" ELSE NULL END AS B, CASE WHEN T3.name IS NOT NULL THEN "○" ELSE NULL END AS C FROM (SELECT DISTINCT name FROM test.testdata) AS T0 LEFT OUTER JOIN (SELECT name FROM test.testdata WHERE class = "A") AS T1 ON T1.name = T0.name LEFT OUTER JOIN (SELECT name FROM test.testdata WHERE class = "B") AS T2 ON T2.name = T0.name LEFT OUTER JOIN (SELECT name FROM test.testdata WHERE class = "C") AS T3 ON T3.name = T0.name ;
| name | A | B | C |
|---|---|---|---|
| 夏色 | (NULL) | ○ | ○ |
| 夜空 | ○ | ○ | (NULL) |
| 白上 | ○ | (NULL) | ○ |
| 赤井 | (NULL) | ○ | (NULL) |
- 参考
- 達人に学ぶ SQL 徹底指南書 - 8.外部結合の使い方
- SQL 外部結合と等価なスカラサブクエリについて
列から行
テスト用のテーブルを用意。
CREATE TABLE IF NOT EXISTS test.favorites ( name STRING, item_1 STRING, item_2 STRING, item_3 STRING ); INSERT INTO test.favorites VALUES ("夜空", "りんご", NULL, NULL), ("赤井", "みかん", "きうい", NULL), ("白上", "ばなな", "みかん", "りんご"), ("夏色", NULL, NULL, NULL) ;
test.favorites
| name | item_1 | item_2 | item_3 |
|---|---|---|---|
| 夏色 | (NULL) | (NULL) | (NULL) |
| 夜空 | りんご | (NULL) | (NULL) |
| 白上 | ばなな | みかん | りんご |
| 赤井 | みかん | きうい | (NULL) |
item_1 列と item_2 列と item_3 列を、1 つの列にして表示します。
下記 SQL は、各 item 列毎のテーブルを用意して、それらテーブルを和集合して表示したものです。ただしこの SQL では、すべての item 列が NULL である行（テストデータは「夏色」にあたるもの）が表示されないことです。
WITH testdata AS ( SELECT name AS name, item_1 AS item FROM test.favorites where item_1 IS NOT NULL UNION ALL SELECT name AS name, item_2 AS item FROM test.favorites where item_2 IS NOT NULL UNION ALL SELECT name AS name, item_3 AS item FROM test.favorites where item_3 IS NOT NULL ) SELECT name, item FROM testdata ORDER BY name;
| name | item |
|---|---|
| 夜空 | りんご |
| 白上 | ばなな |
| 白上 | りんご |
| 白上 | みかん |
| 赤井 | みかん |
| 赤井 | きうい |
「夏色」を NULL として表示する SQL は下記となります。各 item 列を和集合することは同じですが、その和集合したテーブルにオリジナルのテーブルを外部結合したものとなります。オリジナルのテーブル列を、マスター列のように利用したイメージです。
WITH testdata AS ( SELECT name AS name, item_1 AS item FROM test.favorites WHERE item_1 IS NOT NULL UNION ALL SELECT name AS name, item_2 AS item FROM test.favorites WHERE item_2 IS NOT NULL UNION ALL SELECT name AS name, item_3 AS item FROM test.favorites WHERE item_3 IS NOT NULL ) SELECT T0.name AS name, T1.item AS item FROM test.favorites AS T0 LEFT OUTER JOIN testdata AS T1 ON T0.name = T1.name ORDER BY name;
| name | item |
|---|---|
| 夏色 | (NULL) |
| 夜空 | りんご |
| 白上 | りんご |
| 白上 | みかん |
| 白上 | ばなな |
| 赤井 | きうい |
| 赤井 | みかん |
- 参考
- 達人に学ぶ SQL 徹底指南書 - 8.外部結合の使い方
入れ子の表
テスト用のテーブルを用意。
CREATE OR REPLACE TABLE test.m_gen AS ( SELECT "1" AS gen_id, "一期生" AS gen UNION ALL SELECT "2" AS gen_id, "二期生" AS gen UNION ALL SELECT "3" AS gen_id, "三期生" AS gen ); CREATE OR REPLACE TABLE test.m_blood AS ( SELECT "1" AS blood_id, "A型" AS blood UNION ALL SELECT "2" AS blood_id, "B型" AS blood UNION ALL SELECT "3" AS blood_id, "O型" AS blood UNION ALL SELECT "4" AS blood_id, "AB型" AS blood ); CREATE OR REPLACE TABLE test.members AS ( SELECT "夜空メル" AS name, "1" AS gen_id, "4" AS blood_id, 4 AS dresses UNION ALL SELECT "赤井はあと" AS name, "1" AS gen_id, "1" AS blood_id, 5 AS dresses UNION ALL SELECT "白上フブキ" AS name, "1" AS gen_id, "1" AS blood_id, 5 AS dresses UNION ALL SELECT "夏色まつり" AS name, "1" AS gen_id, "3" AS blood_id, 6 AS dresses UNION ALL SELECT "兎田ぺこら" AS name, "3" AS gen_id, "2" AS blood_id, 4 AS dresses UNION ALL SELECT "不知火フレア" AS name, "3" AS gen_id, "2" AS blood_id, 4 AS dresses UNION ALL SELECT "白銀ノエル" AS name, "3" AS gen_id, "4" AS blood_id, 5 AS dresses UNION ALL SELECT "宝鐘マリン" AS name, "3" AS gen_id, "4" AS blood_id, 4 AS dresses );
test.m_gen
| gen_id | gen |
|---|---|
| 1 | 一期生 |
| 2 | 二期生 |
| 3 | 三期生 |
test.m_blood
| blood_id | blood |
|---|---|
| 1 | A 型 |
| 2 | B 型 |
| 3 | O 型 |
| 4 | AB 型 |
test.members
| name | gen_id | blood_id | dresses |
|---|---|---|---|
| 不知火フレア | 3 | 2 | 4 |
| 兎田ぺこら | 3 | 2 | 4 |
| 夏色まつり | 1 | 3 | 6 |
| 夜空メル | 1 | 4 | 4 |
| 宝鐘マリン | 3 | 4 | 4 |
| 白上フブキ | 1 | 1 | 5 |
| 白銀ノエル | 3 | 4 | 5 |
| 赤井はあと | 1 | 1 | 5 |
m_gen(3 rows) * m_blood(4 rows) のテーブルを、NULLとなる行も含めて作成します。
WITH master AS ( SELECT gen_id, gen, blood_id, blood FROM test.m_gen CROSS JOIN test.m_blood ), data AS ( SELECT gen_id, blood_id, sum(dresses) AS sum_dresses FROM test.members GROUP BY gen_id, blood_id ) SELECT master.gen, master.blood, data.sum_dresses FROM master LEFT OUTER JOIN data ON master.gen_id = data.gen_id AND master.blood_id = data.blood_id ORDER BY master.gen_id, master.blood_id;
| gen | blood | sum_dresses |
|---|---|---|
| 一期生 | A 型 | 10 |
| 一期生 | B 型 | (NULL) |
| 一期生 | O 型 | 6 |
| 一期生 | AB 型 | 4 |
| 二期生 | A 型 | (NULL) |
| 二期生 | B 型 | (NULL) |
| 二期生 | O 型 | (NULL) |
| 二期生 | AB 型 | (NULL) |
| 三期生 | A 型 | (NULL) |
| 三期生 | B 型 | 8 |
| 三期生 | O 型 | (NULL) |
| 三期生 | AB 型 | 9 |
- 参考
- 達人に学ぶ SQL 徹底指南書 - 8.外部結合の使い方
重複行の抽出
ROW_NUMBER() 関数を利用します。
WITH testdata AS ( SELECT "001" AS id, "ときのそら" AS name, MD5(CAST(RAND()*10 AS STRING)) AS random UNION ALL SELECT "002" AS id, "ロボ子さん" AS name, MD5(CAST(RAND()*10 AS STRING)) AS random UNION ALL SELECT "003" AS id, "さくらみこ" AS name, MD5(CAST(RAND()*10 AS STRING)) AS random UNION ALL SELECT "003" AS id, "さくらみこ" AS name, MD5(CAST(RAND()*10 AS STRING)) AS random UNION ALL SELECT "004" AS id, "星街すいせい" AS name, MD5(CAST(RAND()*10 AS STRING)) AS random UNION ALL SELECT "005" AS id, "AZKi" AS name, MD5(CAST(RAND()*10 AS STRING)) AS random ) SELECT * EXCEPT(row_num) FROM ( SELECT id, name, random, -- 重複とする列の条件で PARTITION BY する ROW_NUMBER() OVER (PARTITION BY id, name) AS row_num FROM testdata ) -- 重複している場合は値が 2 以上となる WHERE row_num >= 2 ;
| id | name | random |
|---|---|---|
| 003 | さくらみこ | jGoIHqshLKNiMArEpqf2kw== |
ウィンドウ関数による行間比較
テスト用のテーブルを用意。
CREATE OR REPLACE TABLE test.sales AS SELECT store, sale_date, sale FROM ( SELECT "A" AS store, sale_date, CAST(RAND() * 100 AS INTEGER) AS sale FROM UNNEST(GENERATE_DATE_ARRAY("2022-11-01", "2022-11-03", INTERVAL 1 DAY)) AS sale_date UNION ALL SELECT "B" AS store, sale_date, CAST(RAND() * 100 AS INTEGER) AS sale FROM UNNEST(GENERATE_DATE_ARRAY("2022-11-01", "2022-11-03", INTERVAL 1 DAY)) AS sale_date UNION ALL SELECT "C" AS store, sale_date, CAST(RAND() * 100 AS INTEGER) AS sale FROM UNNEST(GENERATE_DATE_ARRAY("2022-11-01", "2022-11-03", INTERVAL 1 DAY)) AS sale_date ) ;
test.sales
| store | sale_date | sale |
|---|---|---|
| A | 2022-11-03 | 86 |
| A | 2022-11-02 | 24 |
| A | 2022-11-01 | 24 |
| B | 2022-11-02 | 91 |
| B | 2022-11-03 | 51 |
| B | 2022-11-01 | 45 |
| C | 2022-11-01 | 4 |
| C | 2022-11-02 | 35 |
| C | 2022-11-03 | 1 |
ウィンドウ関数のフレーム句で、1 つ前のレコード（ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING）を指定し列を用意してあげて比較します。
SELECT store, sale_date, sale, pre_day_sale - sale AS pre_day_diff FROM ( SELECT store, sale_date, sale, MAX(sale) OVER ( PARTITION BY store ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) AS pre_day_sale FROM test.sales ) ORDER BY store, sale_date;
| store | sale_date | sale | pre_day_diff |
|---|---|---|---|
| A | 2022-11-01 | 24 | (NULL) |
| A | 2022-11-02 | 24 | 0 |
| A | 2022-11-03 | 86 | -62 |
| B | 2022-11-01 | 45 | (NULL) |
| B | 2022-11-02 | 91 | -46 |
| B | 2022-11-03 | 51 | 40 |
| C | 2022-11-01 | 4 | (NULL) |
| C | 2022-11-02 | 35 | -31 |
| C | 2022-11-03 | 1 | 34 |
- 参考
- 達人に学ぶ SQL 徹底指南書 - 7.ウィンドウ関数で行間比較を行う
- BigQuery - ウィンドウ関数の構文
重複期間の抽出
テスト用のテーブルを用意。
CREATE OR REPLACE TABLE test.reservations AS SELECT "さくらみこ" AS user, CAST("2022-10-01" AS DATE) AS start_date, CAST("2022-10-04" AS DATE) AS end_date UNION ALL SELECT "兎田ぺこら" AS user, CAST("2022-10-02" AS DATE) AS start_date, CAST("2022-10-05" AS DATE) AS end_date UNION ALL SELECT "潤羽るしあ" AS user, CAST("2022-10-06" AS DATE) AS start_date, CAST("2022-10-07" AS DATE) AS end_date UNION ALL SELECT "不知火フレア" AS user, CAST("2022-10-09" AS DATE) AS start_date, CAST("2022-10-18" AS DATE) AS end_date UNION ALL SELECT "白銀ノエル" AS user, CAST("2022-10-11" AS DATE) AS start_date, CAST("2022-10-13" AS DATE) AS end_date UNION ALL SELECT "宝鐘マリン" AS user, CAST("2022-10-17" AS DATE) AS start_date, CAST("2022-10-20" AS DATE) AS end_date UNION ALL SELECT "星街すいせい" AS user, CAST("2022-10-19" AS DATE) AS start_date, CAST("2022-10-22" AS DATE) AS end_date ;
test.reservations
| user | start_date | end_date |
|---|---|---|
| さくらみこ | 2022-10-01 | 2022-10-04 |
| 兎田ぺこら | 2022-10-02 | 2022-10-05 |
| 潤羽るしあ | 2022-10-06 | 2022-10-07 |
| 不知火フレア | 2022-10-09 | 2022-10-18 |
| 白銀ノエル | 2022-10-11 | 2022-10-13 |
| 宝鐘マリン | 2022-10-17 | 2022-10-20 |
| 星街すいせい | 2022-10-19 | 2022-10-22 |
副問合せ内の SQL で、ウィンドウ関数を利用して 1 つ手前のレコードを取得、その 1 つ手前の開始日と比較することで重複を検知します。ただしこの方法では、 1 つ手前のレコードとの比較しかできないため、多重度が 3 つ以上ある場合の検知はできません。
SELECT * FROM ( SELECT user, start_date, end_date, -- ウィンドウ関数で 1 つ手前のレコードを取得する MAX(user) OVER ( ORDER BY start_date ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING ) AS next_user, -- ウィンドウ関数で 1 つ手前のレコードを取得する MAX(start_date) OVER ( ORDER BY start_date ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING ) AS next_start_date FROM test.reservations ) -- ウィンドウ関数で取得した値と比較する WHERE next_start_date BETWEEN start_date AND end_date ORDER BY start_date;
| user | start_date | end_date | next_user | next_start_date |
|---|---|---|---|---|
| さくらみこ | 2022-10-01 | 2022-10-04 | 兎田ぺこら | 2022-10-02 |
| 不知火フレア | 2022-10-09 | 2022-10-18 | 白銀ノエル | 2022-10-11 |
| 宝鐘マリン | 2022-10-17 | 2022-10-20 | 星街すいせい | 2022-10-19 |
矩形の当たり判定のアルゴリズム を利用します。アルゴリズムの詳細については、下記の参考リンクより。この方法であれば多重度が 3 つ以上の期間も抽出できますが、重複のペアがそれぞれ 1 レコードずつ出力されるのですこし冗長です。
SELECT T1.user AS user_a, T1.start_date AS user_a_start, T1.end_date AS user_a_end, T2.user AS user_b, T2.start_date AS user_b_start, T2.end_date AS user_b_end FROM test.reservations AS T1 -- 比較元と比較先の情報を用意するために直積をつくる CROSS JOIN test.reservations AS T2 -- 自分自身とは比較しないので除外する WHERE T1.user <> T2.user -- 矩形の当たり判定のアルゴリズム AND NOT (T2.end_date <= T1.start_date OR T2.start_date >= T1.end_date) ORDER BY T1.start_date, T2.start_date;
| user_a | user_a_start | user_a_end | user_b | user_b_start | user_b_end |
|---|---|---|---|---|---|
| さくらみこ | 2022-10-01 | 2022-10-04 | 兎田ぺこら | 2022-10-02 | 2022-10-05 |
| 兎田ぺこら | 2022-10-02 | 2022-10-05 | さくらみこ | 2022-10-01 | 2022-10-04 |
| 不知火フレア | 2022-10-09 | 2022-10-18 | 白銀ノエル | 2022-10-11 | 2022-10-13 |
| 不知火フレア | 2022-10-09 | 2022-10-18 | 宝鐘マリン | 2022-10-17 | 2022-10-20 |
| 白銀ノエル | 2022-10-11 | 2022-10-13 | 不知火フレア | 2022-10-09 | 2022-10-18 |
| 宝鐘マリン | 2022-10-17 | 2022-10-20 | 不知火フレア | 2022-10-09 | 2022-10-18 |
| 宝鐘マリン | 2022-10-17 | 2022-10-20 | 星街すいせい | 2022-10-19 | 2022-10-22 |
| 星街すいせい | 2022-10-19 | 2022-10-22 | 宝鐘マリン | 2022-10-17 | 2022-10-20 |
- 参考
- 達人に学ぶ SQL 徹底指南書 - 7.ウィンドウ関数で行間比較を行う
- SQL パズル - 3.忙しい麻酔医
- 2 つの期間が重なり合うかどうかを判定する。
部分的不一致な行の抽出
testdata のデータは gen と live_date の列がペアとなるテーブルである、とします。その場合、夏色まつりの live_date の値は間違って登録されているため、どのように検知するか。
内部結合の自己結合をする。ペアとなる一方の列を結合する列として利用し、ペアのもう一方の列の値を比較することで、登録間違いを検知できます。
WITH testdata AS ( SELECT "白上フブキ" AS name, 1 AS gen, "2021-05-28" AS live_date UNION ALL SELECT "赤井はあと" AS name, 1 AS gen, "2021-05-28" AS live_date UNION ALL SELECT "夏色まつり" AS name, 1 AS gen, "2021-06-29" AS live_date UNION ALL SELECT "兎田ぺこら" AS name, 3 AS gen, "2021-11-25" AS live_date UNION ALL SELECT "宝鐘マリン" AS name, 3 AS gen, "2021-11-25" AS live_date ) SELECT DISTINCT A.gen, A.live_date FROM testdata AS A INNER JOIN testdata AS B ON A.gen = B.gen WHERE A.live_date <> B.live_date ORDER BY A.gen;
| gen | live_date |
|---|---|
| 1 | 2021-05-28 |
| 1 | 2021-06-29 |
内部結合した状態のテーブルは以下となっています（まつりちゃんの分だけ抜粋）。
| name | gen | live_date | name_1 | gen_1 | live_date_1 |
|---|---|---|---|---|---|
| 夏色まつり | 1 | 2021-06-29 | 白上フブキ | 1 | 2021-05-28 |
| 夏色まつり | 1 | 2021-06-29 | 赤井はあと | 1 | 2021-05-28 |
| 夏色まつり | 1 | 2021-06-29 | 夏色まつり | 1 | 2021-06-29 |
- 参考
- 達人に学ぶ SQL 徹底指南書 - 3.自己結合の使い方
欠落の判定
テスト用のテーブルを用意。2022 年 10 月 3 日から 2022 年 11 月 2 日までのカレンダーテーブルを作成して、2022 年 10 月 10 日と 2022 年 10 月 25 日のレコードを削除しておきます。
CREATE OR REPLACE TABLE test.tmp_calendar AS SELECT tmp_date FROM UNNEST(GENERATE_DATE_ARRAY("2022-10-03", "2022-11-02", INTERVAL 1 DAY)) AS tmp_date; DELETE FROM test.tmp_calendar WHERE tmp_date IN ("2022-10-10", "2022-10-25");
| tmp_date |
|---|
| 2022-10-03 |
| 2022-10-04 |
| 2022-10-05 |
| 2022-10-06 |
| 2022-10-07 |
| 2022-10-08 |
| 2022-10-09 |
| 2022-10-11 |
| 2022-10-12 |
| 2022-10-13 |
| 2022-10-14 |
| 2022-10-15 |
| 2022-10-16 |
| 2022-10-17 |
| 2022-10-18 |
| 2022-10-19 |
| 2022-10-20 |
| 2022-10-21 |
| 2022-10-22 |
| 2022-10-23 |
| 2022-10-24 |
| 2022-10-26 |
| 2022-10-27 |
| 2022-10-28 |
| 2022-10-29 |
| 2022-10-30 |
| 2022-10-31 |
| 2022-11-01 |
| 2022-11-02 |
CASE 式内で比較期間の日数を取得して、テーブル内のレコード数と比較します。
SELECT CASE COUNT(tmp_date) WHEN (SELECT COUNT(t) FROM UNNEST(GENERATE_DATE_ARRAY("2022-10-03", "2022-11-02", INTERVAL 1 DAY)) AS t) THEN "問題なし" ELSE "欠落あり" END AS gap, FROM test.tmp_calendar ;
| gap |
|---|
| 欠落あり |
BigQuery での HAVING 句の制限
書籍で紹介されている SQL は、HAVING 句で特性関数を利用した比較をするものでした。しかしながら、BigQuery ではそのまま実行することはできませんでした。書籍で紹介されていた SQL は GROUP BY 句をもたないものとなり、 そういった SQL に対して HAVING 句を実行すると、以下のエラーが表示されます。
The HAVING clause only allows aggregation if GROUP BY or SELECT list aggregation is present at [x:x]
HAVING 句を利用したいのであれば集合をつくれ、という指摘ですね。
HAVING句は、GROUP BYまたは集計で生成された結果をフィルタリングします。GROUP BYまたは集計がクエリに存在する必要があります。
そこで、以下のように全レコードに一意の値を持たせ、その値を GROUP BY することで、特性関数を利用した SQL を実行できるようにしています。
WITH _tmp_calendar AS ( SELECT *, -- 全レコードで一意の値を付与する "1" AS tmp FROM test.tmp_calendar ) SELECT "欠落あり" AS gap FROM _tmp_calendar GROUP BY tmp HAVING count(tmp_date) <> DATE_DIFF(DATE '2022-10-03', DATE '2022-11-02', DAY) ;
| gap |
|---|
| 欠落あり |
- 参考
- 達人に学ぶ SQL 徹底指南書 - 6.HAVING 句の力
条件を満たす集合を探す（HAVING 句と特性関数）
テスト用のテーブルを用意。
CREATE OR REPLACE TABLE test.test_score AS SELECT LPAD(CAST(num AS STRING), 6, "0") AS student_id, CASE MOD(CAST((RAND()*10) AS INT64), 5) WHEN 0 THEN "A組" WHEN 1 THEN "B組" WHEN 2 THEN "C組" WHEN 3 THEN "D組" WHEN 4 THEN "E組" ELSE NULL END AS class, CASE MOD(CAST((RAND()*10) AS INT64), 2) WHEN 0 THEN "man" WHEN 1 THEN "woman" ELSE NULL END AS sex, CAST((RAND()*100) AS INT64) AS score FROM UNNEST(GENERATE_ARRAY(1, 100)) AS num ;
test.test_score
| student_id | class | sex | score |
|---|---|---|---|
| 000001 | E 組 | man | 95 |
| 000002 | E 組 | man | 43 |
| 000003 | B 組 | man | 75 |
| 000004 | B 組 | woman | 77 |
| 000005 | D 組 | man | 19 |
| 000006 | C 組 | man | 37 |
| 000007 | B 組 | man | 47 |
| 000008 | C 組 | man | 5 |
| 000009 | E 組 | woman | 64 |
| 000010 | E 組 | man | 74 |
| 000011 | C 組 | man | 61 |
| 000012 | A 組 | man | 85 |
| ... | ... | ... | ... |
特性関数とは下記。集合の条件を判断する関数、ということでしょうか。
(1) 確率論で確率変数の分布の特性を示す関数で，確率密度のフーリエ変換をいう。 (2) 一般に集合 X の部分集合 A に対し，A を指定するために x∈A なら 1，x∉A なら 0 として，x が A に入るかどうかを判断すればよいが，これを X 上の関数と考えて A の特性関数という。これについては特徴関数，定義関数などの用語も使われる。
この「集合の条件を判断する関数」を、HAVING 句で指定することで、GROUP BY で揃えたものより該当する集合を探す方法です。
以下の SQL は、score 50 点以上を取得した生徒が、半数を超える class を検索。
SELECT class FROM test.test_score GROUP BY class HAVING COUNT(score) * 0.50 <= SUM(CASE WHEN score >= 50 THEN 1 ELSE 0 END) ORDER BY class;
| class |
|---|
| A 組 |
| E 組 |
男子生徒の平均点が、女子生徒の平均点を上回る class を検索。
SELECT class FROM test.test_score GROUP BY class HAVING AVG(CASE WHEN sex = "man" THEN score ELSE NULL END) > AVG(CASE WHEN sex = "woman" THEN score ELSE NULL END) ORDER BY class;
| class |
|---|
| A 組 |
| E 組 |
存在量化子、全称量化子
存在量化子 と 全称量化子 の定義は下記です。
存在量化子
・ 条件を満たすものが存在することを表す
・ 条件を満たすような～は存在する、の形式的な表現
全称量化子
・ 条件を満たすもの全てを表す
・ 全ての～は条件～を満たす、の形式的な表現
テスト用のテーブルを用意。
CREATE OR REPLACE TABLE test.subject_score ( student_id STRING, subject STRING, score INTEGER ); INSERT INTO test.subject_score VALUES ("001", "国語", 71), ("001", "英語", 43), ("001", "数学", 68), ("002", "国語", 35), ("002", "英語", 89), ("003", "国語", 55), ("003", "数学", 68), ("004", "国語", 62), ("004", "英語", 93) ;
| student_id | subject | score |
|---|---|---|
| 001 | 国語 | 71 |
| 001 | 数学 | 68 |
| 001 | 英語 | 43 |
| 002 | 国語 | 35 |
| 002 | 英語 | 89 |
| 003 | 国語 | 55 |
| 003 | 数学 | 68 |
| 004 | 国語 | 62 |
| 004 | 英語 | 93 |
存在量化子
EXISTS 句は、存在量化子を実装したものとなります。
以下は、subject に拘らず score 80 点以上を取得した生徒を求める、素直な EXISTS 句です。
SELECT DISTINCT student_id, FROM test.subject_score AS T1 WHERE EXISTS ( SELECT 1 FROM test.subject_score AS T2 WHERE T1.student_id = T2.student_id AND score >= 80 ) ORDER BY student_id;
| student_id |
|---|
| 002 |
| 004 |
全称量化子
全称量化子は、NOT EXISTS 句を利用した、二重否定の副問合せで実現できます。
「受験した教科すべてが 50 点以上である生徒」を探したい場合、「『50 点未満の教科がない生徒』以外の生徒」を探す NOT EXISTS 句を利用します。
SELECT DISTINCT student_id FROM test.subject_score AS T1 WHERE NOT EXISTS ( SELECT 1 FROM test.subject_score AS T2 WHERE T1.student_id = T2.student_id AND score < 50 );
| student_id |
|---|
| 003 |
| 004 |
全称量化子は、HAVING 句と特性関数を利用しても実現できます。特性関数で、50 点未満の行数を数えて 0 件である場合は、（当たり前ですが）全教科 50 点以上であると判断できます。
SELECT student_id FROM test.subject_score GROUP BY student_id HAVING 0 = SUM(CASE WHEN score < 50 THEN 1 ELSE 0 END) ORDER BY student_id;
| student_id |
|---|
| 003 |
| 004 |
下記は、下記の条件を満たす生徒を検索する SQL です。
- 国語は 40 点以上
- 英語は 40 点以上
SELECT student_id FROM test.subject_score AS T1 -- 対象とするレコードを、国語と英語のレコードに限定する WHERE subject IN ("国語", "英語") -- 国語と英語それぞれが 40 点未満でない生徒を検索する AND NOT EXISTS ( SELECT 1 FROM test.subject_score AS T2 WHERE T1.student_id = T2.student_id AND 1 = CASE WHEN subject = "国語" AND score < 40 THEN 1 WHEN subject = "英語" AND score < 40 THEN 1 ELSE 0 END ) GROUP BY student_id -- 2 教科試験していることを確認するため、集合内のレコード数を数えてあげる HAVING count(student_id) >= 2 ORDER BY student_id;
| student_id |
|---|
| 001 |
| 004 |
これも HAVING 句と特性関数で表現できます。
SELECT student_id FROM test.subject_score WHERE subject IN ("国語", "英語") GROUP BY student_id HAVING 2 = SUM(CASE WHEN subject = "国語" AND score >= 40 THEN 1 WHEN subject = "英語" AND score >= 40 THEN 1 ELSE 0 END ) ORDER BY student_id;
| student_id |
|---|
| 001 |
| 004 |
- 参考
- 達人に学ぶ SQL 徹底指南書 - 5.EXISTS 述語の使い方
順列、組み合わせ
テスト用のテーブルを用意。
CREATE OR REPLACE TABLE test.holo_0 (id STRING, name STRING); INSERT INTO test.holo_0 VALUES ("001", "ときのそら"), ("002", "ロボ子さん"), ("003", "さくらみこ"), ("004", "星街すいせい"), ("005", "AZKi") ;
test.holo_0
| id | name |
|---|---|
| 001 | ときのそら |
| 002 | ロボ子さん |
| 003 | さくらみこ |
| 004 | 星街すいせい |
| 005 | AZKi |
順列
取り出す要素の数だけ、交差結合の自己結合をします。そのままでは {"ときのそら", "ときのそら", "ときのそら"} といった重複順列を許すことになるので、WHERE 句で重複を除外してあげます。
SELECT A.name AS p_1, B.name AS p_2, C.name AS p_3 FROM test.holo_0 AS A CROSS JOIN test.holo_0 AS B CROSS JOIN test.holo_0 AS C WHERE B.name NOT IN (A.name) AND C.name NOT IN (A.name, B.name) ORDER BY p_1;
| p_1 | p_2 | p_3 |
|---|---|---|
| AZKi | ときのそら | 星街すいせい |
| AZKi | ときのそら | さくらみこ |
| AZKi | ときのそら | ロボ子さん |
| AZKi | 星街すいせい | ときのそら |
| AZKi | 星街すいせい | さくらみこ |
| AZKi | 星街すいせい | ロボ子さん |
| AZKi | ロボ子さん | 星街すいせい |
| AZKi | ロボ子さん | ときのそら |
| AZKi | ロボ子さん | さくらみこ |
| AZKi | さくらみこ | 星街すいせい |
| AZKi | さくらみこ | ときのそら |
| AZKi | さくらみこ | ロボ子さん |
| さくらみこ | AZKi | 星街すいせい |
| さくらみこ | AZKi | ときのそら |
| さくらみこ | AZKi | ロボ子さん |
| ... | ... | ... |
- 参考
- SQL パズル - 40.順列
組み合わせ
取り出す要素の数だけ、内部結合の自己結合をします。そのままでは重複を許してしまうため、結合条件で非等値結合を利用します。非等値結合を利用することで、辞書順に比較してくれるため、既に登場している値を除外できるようになります。
SELECT A.name AS c_1, B.name AS c_2, C.name AS c_3 FROM test.holo_0 AS A INNER JOIN test.holo_0 AS B ON A.name > B.name INNER JOIN test.holo_0 AS C ON B.name > C.name ORDER BY c_1;
| c_1 | c_2 | c_3 |
|---|---|---|
| ときのそら | さくらみこ | AZKi |
| ロボ子さん | ときのそら | AZKi |
| ロボ子さん | ときのそら | さくらみこ |
| ロボ子さん | さくらみこ | AZKi |
| 星街すいせい | さくらみこ | AZKi |
| 星街すいせい | ロボ子さん | ときのそら |
| 星街すいせい | ロボ子さん | AZKi |
| 星街すいせい | ロボ子さん | さくらみこ |
| 星街すいせい | ときのそら | AZKi |
| 星街すいせい | ときのそら | さくらみこ |
- 参考
- 達人に学ぶ SQL 徹底指南書 - 3.自己結合の使い方
順列から組み合わせを作成
順列テーブルから組み合わせテーブルを作成します。
-- 順列テーブルをつくる処理 WITH testdata AS ( SELECT A.name AS p_1, B.name AS p_2, C.name AS p_3 FROM test.holo_0 AS A CROSS JOIN test.holo_0 AS B CROSS JOIN test.holo_0 AS C WHERE B.name NOT IN (A.name) AND C.name NOT IN (A.name, B.name) ) SELECT T1.p_1 AS c_1, T2.p_2 AS c_2, T3.p_3 AS c_3 FROM testdata AS T1 INNER JOIN testdata AS T2 ON T1.p_1 > T2.p_2 INNER JOIN testdata AS T3 ON T2.p_2 > T3.p_3 GROUP BY T1.p_1, T2.p_2, T3.p_3 ORDER BY c_1;
| c_1 | c_2 | c_3 |
|---|---|---|
| ときのそら | さくらみこ | AZKi |
| ロボ子さん | ときのそら | AZKi |
| ロボ子さん | ときのそら | さくらみこ |
| ロボ子さん | さくらみこ | AZKi |
| 星街すいせい | ロボ子さん | AZKi |
| 星街すいせい | ロボ子さん | さくらみこ |
| 星街すいせい | ロボ子さん | ときのそら |
| 星街すいせい | さくらみこ | AZKi |
| 星街すいせい | ときのそら | AZKi |
| 星街すいせい | ときのそら | さくらみこ |
- 参考
- SQL パズル - 44.商品のペア
集合演算
テスト用のテーブルを用意。
CREATE OR REPLACE TABLE test.holo_1 (id STRING, name STRING); CREATE OR REPLACE TABLE test.holo_gamers (id STRING, name STRING); INSERT INTO test.holo_1 VALUES ("001", "夜空メル"), ("002", "アキ・ローゼンタール"), ("003", "赤井はあと"), ("004", "白上フブキ"), ("005", "夏色まつり") ; INSERT INTO test.holo_gamers VALUES ("004", "白上フブキ"), ("006", "大神ミオ"), ("007", "猫又おかゆ"), ("008", "戌神ころね") ;
test.holo_1
| id | name |
|---|---|
| 001 | 夜空メル |
| 002 | アキ・ローゼンタール |
| 003 | 赤井はあと |
| 004 | 白上フブキ |
| 005 | 夏色まつり |
test.holo_gamers
| id | name |
|---|---|
| 004 | 白上フブキ |
| 006 | 大神ミオ |
| 007 | 猫又おかゆ |
| 008 | 戌神ころね |
和集合
UNION DISTINCT を利用。
SELECT id, name FROM test.holo_1 UNION DISTINCT SELECT id, name FROM test.holo_gamers ORDER BY id;
| id | name |
|---|---|
| 001 | 夜空メル |
| 002 | アキ・ローゼンタール |
| 003 | 赤井はあと |
| 004 | 白上フブキ |
| 005 | 夏色まつり |
| 006 | 大神ミオ |
| 007 | 猫又おかゆ |
| 008 | 戌神ころね |
差集合
EXCEPT DISTINCT を利用。「A EXCEPT DISTINCT B」で、A から B を引いた差が求められます。
SELECT id, name FROM test.holo_1 EXCEPT DISTINCT SELECT id, name FROM test.holo_gamers ORDER BY id;
| id | name |
|---|---|
| 001 | 夜空メル |
| 002 | アキ・ローゼンタール |
| 003 | 赤井はあと |
| 005 | 夏色まつり |
共通集合
INTERSECT DISTINCT を利用。
SELECT id, name FROM test.holo_1 INTERSECT DISTINCT SELECT id, name FROM test.holo_gamers ORDER BY id;
| id | name |
|---|---|
| 004 | 白上フブキ |
直積集合
CROSS JOIN を利用。
SELECT holo_1.id AS id_1, holo_1.name AS name_1, holo_gamers.id AS id_gamers, holo_gamers.name AS name_gamers FROM test.holo_1 AS holo_1 CROSS JOIN test.holo_gamers AS holo_gamers ORDER BY id_1, id_gamers;
| id_1 | name_1 | id_gamers | name_gamers |
|---|---|---|---|
| 001 | 夜空メル | 004 | 白上フブキ |
| 001 | 夜空メル | 006 | 大神ミオ |
| 001 | 夜空メル | 007 | 猫又おかゆ |
| 001 | 夜空メル | 008 | 戌神ころね |
| 002 | アキ・ローゼンタール | 004 | 白上フブキ |
| 002 | アキ・ローゼンタール | 006 | 大神ミオ |
| 002 | アキ・ローゼンタール | 007 | 猫又おかゆ |
| 002 | アキ・ローゼンタール | 008 | 戌神ころね |
| 003 | 赤井はあと | 004 | 白上フブキ |
| 003 | 赤井はあと | 006 | 大神ミオ |
| 003 | 赤井はあと | 007 | 猫又おかゆ |
| 003 | 赤井はあと | 008 | 戌神ころね |
| 004 | 白上フブキ | 004 | 白上フブキ |
| 004 | 白上フブキ | 006 | 大神ミオ |
| 004 | 白上フブキ | 007 | 猫又おかゆ |
| 004 | 白上フブキ | 008 | 戌神ころね |
| 005 | 夏色まつり | 004 | 白上フブキ |
| 005 | 夏色まつり | 006 | 大神ミオ |
| 005 | 夏色まつり | 007 | 猫又おかゆ |
| 005 | 夏色まつり | 008 | 戌神ころね |
排他的和集合
比較する両テーブルの完全外部結合をつくり、両テーブルどちらかの列が NULL である行のみ取得します。
SELECT COALESCE(A.id, B.id) AS id, COALESCE(A.name, B.name) AS name FROM test.holo_1 AS A FULL OUTER JOIN test.holo_gamers AS B ON A.id = B.id WHERE A.id IS NULL OR B.id IS NULL ORDER BY id;
| id | name |
|---|---|
| 001 | 夜空メル |
| 002 | アキ・ローゼンタール |
| 003 | 赤井はあと |
| 005 | 夏色まつり |
| 006 | 大神ミオ |
| 007 | 猫又おかゆ |
| 008 | 戌神ころね |
完全外部結合のみした状態は下記となります。ここから NULL を持たない行を除いてあげています。
| A.id | A.name | B.id | B.name |
|---|---|---|---|
| 001 | 夜空メル | (NULL) | (NULL) |
| 002 | アキ・ローゼンタール | (NULL) | (NULL) |
| 003 | 赤井はあと | (NULL) | (NULL) |
| 004 | 白上フブキ | 004 | 白上フブキ |
| 005 | 夏色まつり | (NULL) | (NULL) |
| (NULL) | (NULL) | 006 | 大神ミオ |
| (NULL) | (NULL) | 007 | 猫又おかゆ |
| (NULL) | (NULL) | 008 | 戌神ころね |
- 参考
- 達人に学ぶ SQL 徹底指南書 - 8.外部結合の使い方
関係演算
商演算（関係除算）
テスト用のテーブルを用意。
CREATE OR REPLACE TABLE test.arsenal ( name STRING, position STRING ); INSERT INTO test.arsenal VALUES ("Benjamin White", "RCB"), ("Benjamin White", "RFB"), ("William Saliba", "RCB"), ("William Saliba", "LCB"), ("Gabriel Magalhaes", "LCB"), ("TOMIYASU Takehiro", "RFB"), ("TOMIYASU Takehiro", "RCB"), ("TOMIYASU Takehiro", "LCB"), ("TOMIYASU Takehiro", "LFB"), ("Kieran Tierney", "LFB"), ("Oleksandr Zinchenko", "LFB") ; CREATE OR REPLACE TABLE test.requires ( position STRING ); INSERT INTO test.requires VALUES ("RCB"), ("RFB") ;
test.arsenal
| name | position |
|---|---|
| Benjamin White | RCB |
| Benjamin White | RFB |
| Gabriel Magalhaes | LCB |
| Kieran Tierney | LFB |
| Oleksandr Zinchenko | LFB |
| TOMIYASU Takehiro | LCB |
| TOMIYASU Takehiro | LFB |
| TOMIYASU Takehiro | RCB |
| TOMIYASU Takehiro | RFB |
| William Saliba | LCB |
| William Saliba | RCB |
test.requires
| position |
|---|
| RCB |
| RFB |
test.arsenal に登録されている選手から、 test.requires に登録された position をできる選手を探す方法を考えます。
まず両テーブを内部結合することで、requires の値をもつ選手のみ抽出します。その後、HAVIGN 句の特性関数で requires と同レコード数の選手に絞り込みます。ただし、この方法では requires に登録された値以外をもったレコード（テストデータを踏まえると冨安のこと）も検知されることになります。こういった関係除算を 剰余を持った関係除算（division with a remainder） と呼ぶとのこと。
SELECT name FROM test.arsenal AS arsenal -- この内部結合の処理で、requires を満たす選手のみに絞られる INNER JOIN test.requires AS requires ON arsenal.position = requires.position GROUP BY arsenal.name -- 絞られたテーブルと requires テーブルのレコード数を比較する特性関数 HAVING COUNT(arsenal.position) = (SELECT COUNT(position) FROM test.requires) ORDER BY name;
| name |
|---|
| Benjamin White |
| TOMIYASU Takehiro |
内部結合のみした状態は下記となります。サリバも取得されているため、HAVING 句の特性関数でさらに絞ってあげます。
| name | position | position_1 |
|---|---|---|
| Benjamin White | RCB | RCB |
| Benjamin White | RFB | RFB |
| TOMIYASU Takehiro | RCB | RCB |
| TOMIYASU Takehiro | RFB | RFB |
| William Saliba | RCB | RCB |
余りを作らない除算の方法です。arsenal テーブル側を左側にして両テーブを左外部結合すると、結合できなかった require テーブル側の値が NULL に変換されるため、その情報を利用して特性関数を実行します。これを 厳密な関係除算（exact relational division） と呼ぶとのことです。
SELECT name FROM test.arsenal AS arsenal -- 外部結合する LEFT OUTER JOIN test.requires AS requires ON arsenal.position = requires.position GROUP BY arsenal.name -- test.requires とレコード数が一致するものに絞り込む HAVING COUNT(arsenal.position) = (SELECT COUNT(position) FROM test.requires) -- NULL は COUNT されないため、結合前後で requires レコードの数が異なるものは除外できる AND COUNT(requires.position) = (SELECT COUNT(position) FROM test.requires) ORDER BY name;
| name |
|---|
| Benjamin White |
外部結合のみした状態は下記となります。requires テーブルにない値は NULL として結合されるため、絞り込むための特性関数を実行してあげます。
| name | position | position_1 |
|---|---|---|
| Benjamin White | RCB | RCB |
| Benjamin White | RFB | RFB |
| Gabriel Magalhaes | LCB | (NULL) |
| Kieran Tierney | LFB | (NULL) |
| Oleksandr Zinchenko | LFB | (NULL) |
| TOMIYASU Takehiro | LCB | (NULL) |
| TOMIYASU Takehiro | LFB | (NULL) |
| TOMIYASU Takehiro | RCB | RCB |
| TOMIYASU Takehiro | RFB | RFB |
| William Saliba | LCB | (NULL) |
| William Saliba | RCB | RCB |
関係除算の SQL は、以下の 3 つの方法で表現できるそうです。
- NOT EXISTS を入れ子にする
- HAVING 句を使った一対一対応を利用する
- 割り算を引き算で表現する
それぞれ書籍にて「1」および「3」の参考 SQL が記載されていましたが、BigQuery では実行できませんでした。その際に出力されるエラー内容です。
Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.
以下が BigQuery の相関サブクエリに関する記述です。マニュアルの文章を掴みきれず、これは動作を踏まえた上での憶測ですが、相関サブクエリを利用したサブクエリ内で、さらに結合処置のような事をしようとすると怒られるのではないかと考えています。
相関サブクエリは、そのサブクエリの外部から列を参照するサブクエリです。相関関係では、サブクエリの結果を再利用できません。
