標準はあるにはあるが癖の多いSQL 全部俺 #20 結果セットを単一列に連結するにも癖がある Tweet

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の20日目です。

また、本記事はJPOUG Advent Calendar 2020の20日目の窓へクロスポストしています。
JPOUG Advent Calendar 2020の19日目はNaotaka ShinogiさんのNutanix Eraで描くDatabase管理とOracleSEのデータ同期でした。

さて、今日は、最終日のネタ作りの途中でどうしても、非互換なところと格闘しないといけないので、その部分の対応をかねてw

今回は、結果セット（複数行）を単一列に連結する集約関数 Oracleでは LISTAGG()という関数の非互換です。
同様の関数はあるものの関数名は違うし多少引数も違うのでぱっと見、どう書き換えるかってなると迷うわけです。知ってれば別ですけども。

では、早速みてみましょう。

Oracle

LISTAGG

LISTAGG( [ ALL ] [ DISTINCT ] measure_expr [, 'delimiter'] [listagg_overflow_clause] ) [ WITHIN GROUP ] (order_by_clause) [OVER query_partition_clause]
https://docs.oracle.com/cd/F19136_01/sqlrf/LISTAGG.html#GUID-B6E50D8E-F467-425B-9436-F7F8BF38D466

使ったことがある方なら、ああ、アレかと思い出せると思います。あまり使う機会はないので私もマニュアル見て思出すことが多いですね。この関数w

ORACLE>
 *1 SELECT * FROM list;

        ID STR
---------- -------------
         1 foo 
         1 bar 
         1 tiger 
         1 scott
         2 bill
         2 steve

ORACLE> r
  1  SELECT
  2    id
  3    , LISTAGG(str, ',')
  4	 WITHIN GROUP
  5	 (
  6	   ORDER BY str
  7	 )
  8    AS  lists
  9  FROM
 10    list
 11  GROUP BY id
 12* ORDER BY id

	      ID LISTS
---------- ----------------------------------------
	 1 bar,foo,scott,tiger
	 2 bill,steve

次は、MySQL(8.0)

MySQLでは　GROUP_CONCAT()関数が該当します。使い方は似ていますが、セパレータの指定方法に特徴がありますね。

GROUP_CONCAT(expr)
https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat

GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])

mysql> SELECT
    ->   id
    ->   , GROUP_CONCAT(
    ->       str
    ->       ORDER BY str
    ->       SEPARATOR ','
    ->   )
    ->   AS  lists
    -> FROM
    ->   list
    -> GROUP BY id
    -> ORDER BY id;
+------+---------------------+
| id   | lists               |
+------+---------------------+
|    1 | bar,foo,scott,tiger |
|    2 | bill,steve          |
+------+---------------------+
2 rows in set (0.03 sec)

PostgreSQL

PostgreSQLには類似する機能を持つ関数が複数ありますが、LISTAGG()と同じ結果を得る場合には、array_agg＋array_to_string関数の組み合わせか、LISTAGGに近いSTRING_AGG()関数を単独で利用します。

array_agg(expression)
string_agg(expression, delimiter)
https://www.postgresql.jp/document/12/html/functions-aggregate.html

array_to_string(anyarray, text [, text])
https://www.postgresql.jp/document/12/html/functions-array.html

postgres=> SELECT
postgres->   id
postgres->   , STRING_AGG(
postgres(>       str, ','
postgres(>       ORDER BY str
postgres(>   )
postgres->   AS  lists
postgres-> FROM
postgres->   list
postgres-> GROUP BY id
postgres-> ORDER BY id;
 id |        lists
----+---------------------
  1 | bar,foo,scott,tiger
  2 | bill,steve

postgres=> SELECT
postgres->   id
postgres->   , ARRAY_TO_STRING(
postgres(>       ARRAY_AGG(str ORDER BY str)
postgres(>       , ','
postgres(>   )
postgres->   AS  lists
postgres-> FROM
postgres->   list
postgres-> GROUP BY id
postgres-> ORDER BY id;
 id |        lists
----+---------------------
  1 | bar,foo,scott,tiger
  2 | bill,steve

---

最終日のエントリーはこの集約関数やSUBSTR()など、標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020で紹介した複数のSQL構文や関数を利用したネタを予定しています。（ネタが厳しくなったら早めに公開するかもしれませんが）

明日の12月21日のJPOUG Advent Calendar 2020は Yohei Azekatsu さんです。何か、やらかして。。。いや、何か、やってくれることでしょうw　よろしくおねがいします！

---

---

・標準はあるにはあるが癖の多いSQL 全部俺　#1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺　#2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺　#3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺　#4 リテラル値での除算の内部精度も違うのよ!
・標準はあるにはあるが癖の多いSQL 全部俺　#5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺　#6 時間厳守！
・標準はあるにはあるが癖の多いSQL 全部俺　#7 期間リテラル！
・標準はあるにはあるが癖の多いSQL 全部俺　#8 翌月末日って何日？
・標準はあるにはあるが癖の多いSQL 全部俺　#9 部分文字列の扱いでも癖が出る＞＜
・標準はあるにはあるが癖の多いSQL 全部俺　#10 文字列連結の罠（有名なやつ）
・標準はあるにはあるが癖の多いSQL 全部俺　#11 デュエル、じゃなくて、デュアル
・標準はあるにはあるが癖の多いSQL 全部俺　#12 文字[列]探すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#13 あると便利ですが意外となかったり
・標準はあるにはあるが癖の多いSQL 全部俺　#14 連番の集合を返すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#15 SQL command line client
・標準はあるにはあるが癖の多いSQL 全部俺　#16 SQLのレントゲンを撮る方法
・標準はあるにはあるが癖の多いSQL 全部俺　#17 その空白は許されないのか？
・標準はあるにはあるが癖の多いSQL 全部俺　#18 (+)の外部結合は方言
・標準はあるにはあるが癖の多いSQL 全部俺　#19 帰ってきた、部分文字列の扱いでも癖w