PostgreSQL 16 (Beta 1) で実装された SQL:2023 標準の関数 ANY_VALUE() って何者？

第 42 回 PostgreSQL アンカンファレンス@オンライン 2023/7/3

Transcript

1. PostgreSQL 16 (Beta 1) で実装された SQL:2023 標準の関数 ANY_VALUE() って何者？ 第 42 回 PostgreSQL アンカンファレンス@オンライン　 2023/7/3 まつひさ（hmatsu47）
2. 自己紹介 松久裕保（@hmatsu47） • https://qiita.com/hmatsu47 • 現在のステータス： ◦ 名古屋で Web インフラのお守り係をしています ◦ 現在は自社サービスのセキュリティ強化中 ▪ 自社が事故ったわけではないけれど ▪ DB は関係があったりなかったり ◦ #pgunconf ゆるふわ勢 2
3. 本日のネタ • 「PostgreSQL 16のリリースノートと虎の巻でわいわい 言う会（続）」にて 3
4. 本日のネタ • そして 4
5. あらかじめことわっておきますが • この話を聞いてもあまり役に立つ場面はありません 5
6. ANY_VALUE() とは？ • 「PostgreSQL 16 新機能検証結果 (Beta 1)」 （篠田の虎の巻）より https://community.hpe.com/t5/hpe-blog-japan/%E7%AF%A0%E7%94%B0%E3%81%AE%E8%99% 8E%E3%81%AE%E5%B7%BB-postgresql-16-beta-1-%E6%96%B0%E6%A9%9F%E8%83%BD%E 6%A4%9C%E8%A8%BC%E7%B5%90%E6%9E%9C-%E5%85%AC%E9%96%8B/ba-p/7188972 • なるほど（何に使えるのか）わからん 6 □ any_value 集計関数 any_value が追加されました。この関数は集約の入力値から任意の値を非決定的に返しま す。この関数は SQL:2023 標準に含まれます。
7. そこで • ANY_VALUE() の使い方を「完全に理解した」まではいか ないけれど ◦ それっぽい使い方を1 つぐらいは示しておきます 7
8. 例えばこんな（微妙な）テーブルがあったとして postgres=# SELECT * FROM category; category_large | category_middle | category_small | large_furigana | middle_furigana | small_furigana ----------------+-----------------+----------------+----------------+-----------------+---------------- 芝犬 | 赤色 | 赤柴 | しばいぬ | あかいろ | あかしば 芝犬 | 黒褐色 | 黒柴 | しばいぬ | こっかっしょく | くろしば 芝犬 | 白色 | 白柴 | しばいぬ | しろいろ | しろしば 芝犬 | 胡麻色 | 胡麻柴 | しばいぬ | ごまいろ | ごましば 芝犬 | 胡麻色 | 黒胡麻 | しばいぬ | ごまいろ | くろごま 芝犬 | 胡麻色 | 赤胡麻 | しばいぬ | ごまいろ | あかごま 猫 | 単色 | 白猫 | ねこ | たんしょく | しろねこ 猫 | 単色 | 黒猫 | ねこ | たんしょく | くろねこ 猫 | 単色 | 灰猫 | ねこ | たんしょく | はいねこ 猫 | 多色 | 白黒猫 | ねこ | たしょく | しろくろねこ 猫 | 多色 | 三毛猫 | ねこ | たしょく | みけねこ 猫 | 多色 | サビ猫 | ねこ | たしょく | さびねこ 猫 | 多色 | キジ白 | ねこ | たしょく | きじしろ 猫 | 多色 | サバ白 | ねこ | たしょく | さばしろ 猫 | 多色 | 茶白 | ねこ | たしょく | ちゃしろ 猫 | シマ | キジトラ | ねこ | しま | きじとら 猫 | シマ | サバトラ | ねこ | しま | さばとら 猫 | シマ | 茶トラ | ねこ | しま | ちゃとら 猫 | その他 | ポインテッド | ねこ | そのた | ぽいんてっど 猫 | その他 | ハチワレ | ねこ | そのた | はちわれ 猫 | その他 | 靴下 | ねこ | そのた | くつした (21 rows) 8
9. GROUP BY で集計したいとき • これなら大丈夫、でも… postgres=# SELECT category_large, category_middle, COUNT(category_small) FROM category GROUP BY category_large, category_middle ORDER BY category_large, category_middle; category_large | category_middle | count ----------------+-----------------+------- 猫 | シマ | 3 猫 | その他 | 3 猫 | 単色 | 3 猫 | 多色 | 6 芝犬 | 白色 | 1 芝犬 | 胡麻色 | 3 芝犬 | 赤色 | 1 芝犬 | 黒褐色 | 1 (8 rows) 9
10. こうすると（当然）怒られます • GROUP BY の対象列に「large_furigana」と 「middle_furigana」が無いので postgres=# SELECT category_large, large_furigana, category_middle, middle_furigana, COUNT(category_small) FROM category GROUP BY category_large, category_middle ORDER BY category_large, category_middle; ERROR: column "category.large_furigana" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT category_large, large_furigana, category_middle, midd... ^ 10
11. GROUP BY に列を加えて対応しても良いのですが • グループ化したいわけでは無いので意図が分かりづらい ◦ 複数種の値が紛れ込むと更に大きなバグを生むかも postgres=# SELECT category_large, large_furigana, category_middle, middle_furigana, COUNT(category_small) FROM category GROUP BY category_large, large_furigana, category_middle, middle_furigana ORDER BY category_large, category_middle; category_large | large_furigana | category_middle | middle_furigana | count ----------------+----------------+-----------------+-----------------+------- 猫 | ねこ | シマ | しま | 3 猫 | ねこ | その他 | そのた | 3 猫 | ねこ | 単色 | たんしょく | 3 猫 | ねこ | 多色 | たしょく | 6 芝犬 | しばいぬ | 白色 | しろいろ | 1 芝犬 | しばいぬ | 胡麻色 | ごまいろ | 3 芝犬 | しばいぬ | 赤色 | あかいろ | 1 芝犬 | しばいぬ | 黒褐色 | こっかっしょく | 1 (8 rows) 11
12. さらに別解として • MIN()・MAX() などを使う手も https://docs.snowflake.com/ja/sql-reference/functions/any_value ◦ 意図がわかりづらいのは変わらず postgres=# SELECT category_large, MIN(large_furigana) AS large_furigana, category_middle, MIN(middle_furigana) AS middle_furigana, COUNT(category_small) FROM category GROUP BY category_large, category_middle ORDER BY category_large, category_middle; category_large | large_furigana | category_middle | middle_furigana | count ----------------+----------------+-----------------+-----------------+------- 猫 | ねこ | シマ | しま | 3 猫 | ねこ | その他 | そのた | 3 猫 | ねこ | 単色 | たんしょく | 3 猫 | ねこ | 多色 | たしょく | 6 芝犬 | しばいぬ | 白色 | しろいろ | 1 芝犬 | しばいぬ | 胡麻色 | ごまいろ | 3 芝犬 | しばいぬ | 赤色 | あかいろ | 1 芝犬 | しばいぬ | 黒褐色 | こっかっしょく | 1 (8 rows) 12
13. そんなときに ANY_VALUE() を使っても • ちゃんと取れます ◦ 列名のエイリアスをつけると結局長くなるので微妙ですが postgres=# SELECT category_large, ANY_VALUE(large_furigana) AS large_furigana, category_middle, ANY_VALUE(middle_furigana) AS middle_furigana, COUNT(category_small) FROM category GROUP BY category_large, category_middle ORDER BY category_large, category_middle; category_large | large_furigana | category_middle | middle_furigana | count ----------------+----------------+-----------------+-----------------+------- 猫 | ねこ | シマ | しま | 3 猫 | ねこ | その他 | そのた | 3 猫 | ねこ | 単色 | たんしょく | 3 猫 | ねこ | 多色 | たしょく | 6 芝犬 | しばいぬ | 白色 | しろいろ | 1 芝犬 | しばいぬ | 胡麻色 | ごまいろ | 3 芝犬 | しばいぬ | 赤色 | あかいろ | 1 芝犬 | しばいぬ | 黒褐色 | こっかっしょく | 1 (8 rows) 13
14. EXPLAIN は？→あまり差がなかった postgres=# EXPLAIN SELECT category_large, large_furigana, category_middle, middle_furigana, COUNT(category_small) FROM category GROUP BY category_large, large_furigana, category_middle, middle_furigana ORDER BY category_large, category_middle; QUERY PLAN ------------------------------------------------------------------------------------ GroupAggregate (cost=12.37..13.87 rows=60 width=880) Group Key: category_large, category_middle, large_furigana, middle_furigana -> Sort (cost=12.37..12.52 rows=60 width=1090) Sort Key: category_large, category_middle, large_furigana, middle_furigana -> Seq Scan on category (cost=0.00..10.60 rows=60 width=1090) (5 rows) postgres=# EXPLAIN SELECT category_large, ANY_VALUE(large_furigana) AS large_furigana, category_middle, ANY_VALUE(middle_furigana) AS middle_furigana, COUNT(category_small) FROM category GROUP BY category_large, category_middle ORDER BY category_large, category_middle; QUERY PLAN ------------------------------------------------------------------------- GroupAggregate (cost=12.37..13.87 rows=60 width=508) Group Key: category_large, category_middle -> Sort (cost=12.37..12.52 rows=60 width=1090) Sort Key: category_large, category_middle -> Seq Scan on category (cost=0.00..10.60 rows=60 width=1090) (5 rows) 14 MIN() の EXPLAIN 結果もこちらと同じ
15. なお🐬㌠（MySQL）では • 「ONLY_FULL_GROUP_BY」を無効化すると… mysql> SELECT @@sql_mode; +-----------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +-----------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@sql_mode; +----------------------------------------------------------------------------------------------------+ | @@sql_mode | +----------------------------------------------------------------------------------------------------+ | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +----------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 15
16. ANY_VALUE() がなくてもそのまま取れます mysql> SELECT category_large, large_furigana, category_middle, middle_furigana, COUNT(category_small) AS count FROM category GROUP BY category_large, category_middle ORDER BY category_large, category_middle; +----------------+----------------+-----------------+-----------------------+-------+ | category_large | large_furigana | category_middle | middle_furigana | count | +----------------+----------------+-----------------+-----------------------+-------+ | 猫 | ねこ | シマ | しま | 3 | | 猫 | ねこ | その他 | そのた | 3 | | 猫 | ねこ | 単色 | たんしょく | 3 | | 猫 | ねこ | 多色 | たしょく | 6 | | 芝犬 | しばいぬ | 白色 | しろいろ | 1 | | 芝犬 | しばいぬ | 胡麻色 | ごまいろ | 3 | | 芝犬 | しばいぬ | 赤色 | あかいろ | 1 | | 芝犬 | しばいぬ | 黒褐色 | こっかっしょく | 1 | +----------------+----------------+-----------------+-----------------------+-------+ 8 rows in set (0.00 sec) 16 • ちなみに ◦ ANY_VALUE() は 5.7 で実装されました
17. ちょっとやりすぎな気もしますが • これも行けます ◦ これは ANY_VALUE() では代用不可 17 mysql> SELECT category_large, large_furigana, category_middle, middle_furigana, COUNT(category_small) AS count FROM category GROUP BY category_middle ORDER BY category_large, category_middle; +----------------+----------------+-----------------+-----------------------+-------+ | category_large | large_furigana | category_middle | middle_furigana | count | +----------------+----------------+-----------------+-----------------------+-------+ | 猫 | ねこ | シマ | しま | 3 | | 猫 | ねこ | その他 | そのた | 3 | | 猫 | ねこ | 単色 | たんしょく | 3 | | 猫 | ねこ | 多色 | たしょく | 6 | | 芝犬 | しばいぬ | 白色 | しろいろ | 1 | | 芝犬 | しばいぬ | 胡麻色 | ごまいろ | 3 | | 芝犬 | しばいぬ | 赤色 | あかいろ | 1 | | 芝犬 | しばいぬ | 黒褐色 | こっかっしょく | 1 | +----------------+----------------+-----------------+-----------------------+-------+ 8 rows in set (0.00 sec)
18. おまけ：SQL:1999 標準に関する話 • MySQL 8.0 リファレンスマニュアルより https://dev.mysql.com/doc/refman/8.0/ja/group-by-handling.html 18 SQL:1999 以降では、GROUP BY カラムに機能的に依存している場合、オプション機能 T301 ごとに このような非集計が許可されます : このような関係が name と custid の間に存在する場合、クエ リーは有効です。 たとえば、これは custid が customers の主キーであった場合です。 SELECT o.custid, c.name, MAX(o.payment) FROM orders AS o, customers AS c WHERE o.custid = c.custid GROUP BY o.custid;
19. というわけで • 🐬㌠で、こんなテーブルを追加して… ◦ 「category_large」が主キー 19 mysql> SELECT * FROM category_alias; +----------------+-----------------+ | category_large | category_alias | +----------------+-----------------+ | 猫 | にゃんこ | | 芝犬 | しばわんこ | +----------------+-----------------+ 2 rows in set (0.00 sec)
20. GROUP BY 指定列を使って結合すると • ANY_VALUE() がなくても行けますが、 mysql> SELECT category.category_large, category_alias, category_middle, COUNT(category_small) FROM category, category_alias WHERE category.category_large = category_alias.category_large GROUP BY category.category_large, category_middle ORDER BY category.category_large, category_middle; +----------------+-----------------+-----------------+-----------------------+ | category_large | category_alias | category_middle | COUNT(category_small) | +----------------+-----------------+-----------------+-----------------------+ | 猫 | にゃんこ | シマ | 3 | | 猫 | にゃんこ | その他 | 3 | | 猫 | にゃんこ | 単色 | 3 | | 猫 | にゃんこ | 多色 | 6 | | 芝犬 | しばわんこ | 白色 | 1 | | 芝犬 | しばわんこ | 胡麻色 | 3 | | 芝犬 | しばわんこ | 赤色 | 1 | | 芝犬 | しばわんこ | 黒褐色 | 1 | +----------------+-----------------+-----------------+-----------------------+ 8 rows in set (0.00 sec) 20
21. 🐘㌠（PostgreSQL）ではダメです postgres=# SELECT category.category_large, category_alias, category_middle, COUNT(category_small) FROM category, category_alias WHERE category.category_large = category_alias.category_large GROUP BY category.category_large, category_middle ORDER BY category.category_large, category_middle; ERROR: column "category_alias.category_alias" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT category.category_large, category_alias, category_mid... ^ 21
22. ここでも ANY_VALUE() を使う必要があります postgres=# SELECT category.category_large, ANY_VALUE(category_alias) AS category_alias, category_middle, COUNT(category_small) FROM category, category_alias WHERE category.category_large = category_alias.category_large GROUP BY category.category_large, category_middle ORDER BY category.category_large, category_middle; category_large | category_alias | category_middle | count ----------------+----------------+-----------------+------- 猫 | にゃんこ | シマ | 3 猫 | にゃんこ | その他 | 3 猫 | にゃんこ | 単色 | 3 猫 | にゃんこ | 多色 | 6 芝犬 | しばわんこ | 白色 | 1 芝犬 | しばわんこ | 胡麻色 | 3 芝犬 | しばわんこ | 赤色 | 1 芝犬 | しばわんこ | 黒褐色 | 1 (8 rows) 22 • または GROUP BY に列を加えるなど
23. まとめ（1/2） • 関数従属しているけどテーブルを分けるほどでもない列 が存在するテーブルで集計するときに使える（かも） ◦ とはいえ別の対応方法もあるので効果は微妙 ◦ グループ内の特定の行の値を取りたくても取れる保証はないし ▪ それをやりたいならウィンドウ関数を使ったほうが… • 🐬㌠の「ONLY_FULL_GROUP_BY」無効状態は無敵 ◦ ゆえに危険ともいえる（いずれ廃止されそう） 23
24. まとめ（2/2） • 🐘㌠では一部 SQL:1999 標準に対応していない部分も ◦ そこで ANY_VALUE() が使える ▪ ただし別の対応方法もあるのでやっぱり微妙 24