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

PostgreSQL 16 (Beta 1) で実装された SQL:2023 標準の関数 ANY_VALUE() って何者？ 第 42 回 PostgreSQL アンカンファレンス@オンライン 2023/7/3 まつひさ（hmatsu47）

自己紹介 松久裕保（@hmatsu47） ● https://qiita.com/hmatsu47 ● 現在のステータス： ○ 名古屋で Web インフラのお守り係をしています ○ 現在は自社サービスのセキュリティ強化中 ■ 自社が事故ったわけではないけれど ■ DB は関係があったりなかったり ○ #pgunconf ゆるふわ勢 2

• https://qiita.com/hmatsu47

本日のネタ ● 「PostgreSQL 16のリリースノートと虎の巻でわいわい 言う会（続）」にて 3

本日のネタ ● そして 4

あらかじめことわっておきますが ● この話を聞いてもあまり役に立つ場面はありません 5

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 □ any_value 集計関数 any_value が追加されました。この関数は集約の入力値から任意の値を非決定的に返しま す。この関数は SQL:2023 標準に含まれます。 ● なるほど（何に使えるのか）わからん 6

• https://community.hpe.com/t5/hpe-blog-japan/篠田の虎の巻-postgresql-16-beta-1-新機能検証結果-公開/ba-p/7188972

そこで ● ANY_VALUE() の使い方を「完全に理解した」まではいか ないけれど ○ それっぽい使い方を1 つぐらいは示しておきます 7

例えばこんな（微妙な）テーブルがあったとして postgres=# SELECT * FROM category; category_large | category_middle | category_small | large_furigana | middle_furigana | small_furigana ----------------+-----------------+----------------+----------------+-----------------+---------------芝犬 | 赤色 | 赤柴 | しばいぬ | あかいろ | あかしば 芝犬 | 黒褐色 | 黒柴 | しばいぬ | こっかっしょく | くろしば 芝犬 | 白色 | 白柴 | しばいぬ | しろいろ | しろしば 芝犬 | 胡麻色 | 胡麻柴 | しばいぬ | ごまいろ | ごましば 芝犬 | 胡麻色 | 黒胡麻 | しばいぬ | ごまいろ | くろごま 芝犬 | 胡麻色 | 赤胡麻 | しばいぬ | ごまいろ | あかごま 猫 | 単色 | 白猫 | ねこ | たんしょく | しろねこ 猫 | 単色 | 黒猫 | ねこ | たんしょく | くろねこ 猫 | 単色 | 灰猫 | ねこ | たんしょく | はいねこ 猫 | 多色 | 白黒猫 | ねこ | たしょく | しろくろねこ 猫 | 多色 | 三毛猫 | ねこ | たしょく | みけねこ 猫 | 多色 | サビ猫 | ねこ | たしょく | さびねこ 猫 | 多色 | キジ白 | ねこ | たしょく | きじしろ 猫 | 多色 | サバ白 | ねこ | たしょく | さばしろ 猫 | 多色 | 茶白 | ねこ | たしょく | ちゃしろ 猫 | シマ | キジトラ | ねこ | しま | きじとら 猫 | シマ | サバトラ | ねこ | しま | さばとら 猫 | シマ | 茶トラ | ねこ | しま | ちゃとら 猫 | その他 | ポインテッド | ねこ | そのた | ぽいんてっど 猫 | その他 | ハチワレ | ねこ | そのた | はちわれ 猫 | その他 | 靴下 | ねこ | そのた | くつした (21 rows) 8

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

こうすると（当然）怒られます 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... ^ ● GROUP BY の対象列に「large_furigana」と 「middle_furigana」が無いので 10

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

さらに別解として 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) ● MIN()・MAX() などを使う手も https://docs.snowflake.com/ja/sql-reference/functions/any_value ○ 意図がわかりづらいのは変わらず 12

• https://docs.snowflake.com/ja/sql-reference/functions/any_value

そんなときに 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

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) MIN() の EXPLAIN 結果もこちらと同じ 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

なお🐬㌠（MySQL）では 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) ● 「ONLY_FULL_GROUP_BY」を無効化すると… 15

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) ● ちなみに ○ ANY_VALUE() は 5.7 で実装されました 16

ちょっとやりすぎな気もしますが 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) ● これも行けます ○ これは ANY_VALUE() では代用不可 17

おまけ：SQL:1999 標準に関する話 ● MySQL 8.0 リファレンスマニュアルより https://dev.mysql.com/doc/refman/8.0/ja/group-by-handling.html SELECT o.custid, c.name, MAX(o.payment) FROM orders AS o, customers AS c WHERE o.custid = c.custid GROUP BY o.custid; SQL:1999 以降では、 GROUP BY カラムに機能的に依存している場合、オプション機能 T301 ごとに このような非集計が許可されます : このような関係が name と custid の間に存在する場合、クエ リーは有効です。 たとえば、これは custid が customers の主キーであった場合です。 18

• https://dev.mysql.com/doc/refman/8.0/ja/group-by-handling.html

というわけで mysql> SELECT * FROM category_alias; +----------------+-----------------+ | category_large | category_alias | +----------------+-----------------+ | 猫 | にゃんこ | | 芝犬 | しばわんこ | +----------------+-----------------+ 2 rows in set (0.00 sec) ● 🐬㌠で、こんなテーブルを追加して… ○ 「category_large」が主キー 19

GROUP BY 指定列を使って結合すると 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) ● ANY_VALUE() がなくても行けますが、 20

🐘㌠（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

ここでも 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) ● または GROUP BY に列を加えるなど 22

まとめ（1/2） ● 関数従属しているけどテーブルを分けるほどでもない列 が存在するテーブルで集計するときに使える（かも） ○ とはいえ別の対応方法もあるので効果は微妙 ○ グループ内の特定の行の値を取りたくても取れる保証はないし ■ それをやりたいならウィンドウ関数を使ったほうが… ● 🐬㌠の「ONLY_FULL_GROUP_BY」無効状態は無敵 ○ ゆえに危険ともいえる（いずれ廃止されそう） 23

まとめ（2/2） ● 🐘㌠では一部 SQL:1999 標準に対応していない部分も ○ そこで ANY_VALUE() が使える ■ ただし別の対応方法もあるのでやっぱり微妙 24