PostgerSQL ARRAY_AGG の と Oracle の LISTAGG

PostgreSQL には 行を 配列に変える ARRAY_AGG という関数があります。 これと配列を文字列に変える ARRAY_TO_STRING を組み合わせることで 行をカンマ区切りなどの文字列にすることができます。

例えば こんなテーブルに対して...

db=# SELECT id, name FROM fruit;

 id |  name
----+--------
  1 | りんご
  2 | みかん
  3 | ぶどう

次のような結果を得ることができます。

db=# SELECT ARRAY_TO_STRING(ARRAY_AGG(name order by id), ',') AS names FROM fruit;

        names
----------------------
 りんご,みかん,ぶどう

これを Oracle でなんとかできなかと思っていたんですが Oracleでも11g R2 から LISTAGG という関数が追加されていました。

SQL> SELECT LISTAGG(name, ',') WITHIN GROUP (order by id) AS names FROM fruit;

        names
----------------------
 りんご,みかん,ぶどう

LISTAGG 関数は ARRAY_AGG + ARRAY_TO_STRING のような動きをします。

ORACLE で実行した SQLから実行計画を表示する

ORACLE で実行計画を表示したい状況があったのですが 自由にテーブルを作れる環境ではなく どうしようかと思っていたところ、 実行したSQLに対して実行計画を表示する方法がありました。

次のように「DBMS_XPLAN.DISPLAY_CURSOR」を使用します。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

最後に実行したSQLの実行計画が次のように表示されます。

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  dyk4dprp70d74, child number 0
-------------------------------------
SELECT DECODE('A','A','1','2') FROM DUAL

Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------------

ただ、DISPLAY_CURSOR の実行には「V$SQL_PLAN、V$SESSIONおよびV$SQL_PLAN_STATISTICS_ALL.の各固定ビューに対するSELECT権限が必要です。」とのことです。

[参考]
DBMS_XPLAN - OracleR Database PL/SQLパッケージおよびタイプ・リファレンス

実行したSQLを指定するには、 V$SQL で SQL_ID を調べて 以下のように実行します。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('SQL_ID'));

実行したSQLにしか使えませんが テーブル等を作らなくて良いのは助かります。

ORACLE の LIMIT OFFSET

業務でデータベースは PostgreSQL を使うことが多いですが ORACLE も使います。 その ORACLE が 12c になりました。 そう、12c になるということは LIMIT や OFFSET が使えるということです。 そんな嬉しさから書きます。

まずはデータの準備。

テーブルを作りたくないので 次の SQL でゴマかします。

SELECT *
FROM     (SELECT 1 AS ID, 85 AS SCORE, 3 AS RANK FROM DUAL
UNION ALL SELECT 2 AS ID, 70 AS SCORE, 7 AS RANK FROM DUAL
UNION ALL SELECT 3 AS ID, 98 AS SCORE, 1 AS RANK FROM DUAL
UNION ALL SELECT 4 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 5 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 6 AS ID, 95 AS SCORE, 2 AS RANK FROM DUAL
UNION ALL SELECT 7 AS ID, 75 AS SCORE, 6 AS RANK FROM DUAL)
ORDER BY RANK, ID
SQL> /
        ID      SCORE       RANK
---------- ---------- ----------
         3         98          1
         6         95          2
         1         85          3
         4         80          4
         5         80          4
         7         75          6
         2         70          7

このようにテストの点数と ランキングが入っています。

まずは LIMIT ですが ORACLE (というかSQL:2008)では LIMIT ではなく 次のような書き方をします。

FETCH FIRST 100 ROWS ONLY

ちなみに この書き方は PostgreSQL でもサポートされています。 (完全に同じではありません)

まずは 4 行だけ取得。

SELECT *
FROM     (SELECT 1 AS ID, 85 AS SCORE, 3 AS RANK FROM DUAL
UNION ALL SELECT 2 AS ID, 70 AS SCORE, 7 AS RANK FROM DUAL
UNION ALL SELECT 3 AS ID, 98 AS SCORE, 1 AS RANK FROM DUAL
UNION ALL SELECT 4 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 5 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 6 AS ID, 95 AS SCORE, 2 AS RANK FROM DUAL
UNION ALL SELECT 7 AS ID, 75 AS SCORE, 6 AS RANK FROM DUAL)
ORDER BY RANK, ID
FETCH FIRST 4 ROWS ONLY
SQL> /
        ID      SCORE       RANK
---------- ---------- ----------
         3         98          1
         6         95          2
         1         85          3
         4         80          4

4 行取得できました。

取得元のデータには RANK が 4 のデータが 2 レコードあります。 4位タイということですね。 4位までをを取得しようとして 4位が 1人しか取得できないのは困りますよね。

そんなときは 次のように書きます。

SELECT *
FROM     (SELECT 1 AS ID, 85 AS SCORE, 3 AS RANK FROM DUAL
UNION ALL SELECT 2 AS ID, 70 AS SCORE, 7 AS RANK FROM DUAL
UNION ALL SELECT 3 AS ID, 98 AS SCORE, 1 AS RANK FROM DUAL
UNION ALL SELECT 4 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 5 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 6 AS ID, 95 AS SCORE, 2 AS RANK FROM DUAL
UNION ALL SELECT 7 AS ID, 75 AS SCORE, 6 AS RANK FROM DUAL)
ORDER BY RANK
FETCH FIRST 4 ROWS WITH TIES
SQL> /
        ID      SCORE       RANK
---------- ---------- ----------
         3         98          1
         6         95          2
         1         85          3
         4         80          4
         5         80          4

これで 4 位までを取得することができました。

ここで注意しなくてはならないのは ORDER BY から ID 列を外していることです。

WITH TIES の仕様は “ソート順の最後の項目が同じであれば取得する”ということなので ID が最後ではダメなのです。 もし更に“同順位は ID でソートしたい”ということであれば もう 1 段階ラッピングする必要があります。

行の他に百分率で指定することもできます。

SELECT *
FROM     (SELECT 1 AS ID, 85 AS SCORE, 3 AS RANK FROM DUAL
UNION ALL SELECT 2 AS ID, 70 AS SCORE, 7 AS RANK FROM DUAL
UNION ALL SELECT 3 AS ID, 98 AS SCORE, 1 AS RANK FROM DUAL
UNION ALL SELECT 4 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 5 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 6 AS ID, 95 AS SCORE, 2 AS RANK FROM DUAL
UNION ALL SELECT 7 AS ID, 75 AS SCORE, 6 AS RANK FROM DUAL)
ORDER BY RANK, ID
FETCH FIRST 50 PERCENT ROWS ONLY
SQL> /
        ID      SCORE       RANK
---------- ---------- ----------
         3         98          1
         6         95          2
         1         85          3
         4         80          4

50% を指定したので 7行中の 4行が取得できました。

次は OFFSET です。
次のような書き方をします。

OFFSET 10 ROWS

OFFSET 4 で 5行目から取得してみます。

SELECT *
FROM     (SELECT 1 AS ID, 85 AS SCORE, 3 AS RANK FROM DUAL
UNION ALL SELECT 2 AS ID, 70 AS SCORE, 7 AS RANK FROM DUAL
UNION ALL SELECT 3 AS ID, 98 AS SCORE, 1 AS RANK FROM DUAL
UNION ALL SELECT 4 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 5 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 6 AS ID, 95 AS SCORE, 2 AS RANK FROM DUAL
UNION ALL SELECT 7 AS ID, 75 AS SCORE, 6 AS RANK FROM DUAL)
ORDER BY RANK, ID
OFFSET 4 ROWS
FETCH FIRST 4 ROWS ONLY
SQL> /
        ID      SCORE       RANK
---------- ---------- ----------
         5         80          4
         7         75          6
         2         70          7

5 行目から 3行取得できました。

これでページ送りなんかの機能も簡単に作れますね。

Oracle のマルチテーブル・インサート

最近は PostgreSQL の機能も充実してきましたが Oracle にはまだまだ独自の機能が残っています。

今回は、前回やったマルチテーブル・インサートについて。

次のように INSERT文をまとめることができます。

SQL> INSERT ALL
  2    INTO table1 (field1, field2) VALUES ('xxxxx1', 'xxxxxxxxx')
  3    INTO table1 (field1, field2) VALUES ('xxxxx2', 'xxxxxxxxx')
  4    INTO table1 (field1, field2) VALUES ('xxxxx3', 'xxxxxxxxx')
  5  SELECT * FROM DUAL;

まとめるだけでなく複数のテーブルに INSERT できます。

SQL> INSERT ALL
  2    INTO table1 (field1, field2) VALUES ('xxxxx1', 'xxxxxxxxx')
  3    INTO table2 (field1, field2) VALUES ('xxxxx2', 'xxxxxxxxx')
  4    INTO table3 (field1, field2) VALUES ('xxxxx3', 'xxxxxxxxx')
  5  SELECT * FROM DUAL;

文末の DUAL に気づいたでしょうか?

  5  SELECT * FROM DUAL;

ここにはテーブルや福問い合わせを指定することができます。

つまり 次のようなことができてしまうわけです。

SQL> INSERT ALL
  2    INTO table1 (field1, field2) VALUES (fieldA, fieldB)
  3    INTO table2 (field1, field2) VALUES (fieldA, fieldC)
  4    INTO table3 (field1, field2) VALUES (fieldA, fieldD)
  5  SELECT * FROM table4;

さらに条件も指定することができます。

SQL> INSERT ALL
  2    WHEN fieldB < 10 THEN
  3      INTO table1 (field1, field2) VALUES (fieldA, fieldB)
  4    WHEN fieldC < 10 THEN
  5      INTO table2 (field1, field2) VALUES (fieldA, fieldC)
  6    WHEN fieldD < 10 THEN
  7      INTO table3 (field1, field2) VALUES (fieldA, fieldD)
  8  SELECT * FROM table4;

やっぱり Oracle は恐ろしいですね。

Oracle でも 1回の SQL で複数行 INSERT する

PostgreSQL や MySQL では次のように INSERT 文で 複数行を指定することができます。

psql=# INSERT INTO table1 (field1, field2)
psql-#   VALUES ('xxxxx1', 'xxxxxxxxx')
psql-#        , ('xxxxx2', 'xxxxxxxxx')
psql-#        , ('xxxxx3', 'xxxxxxxxx');

Oracle ではこの書き方ができません。

以下のように書きます。

SQL> INSERT ALL
  2    INTO table1 (field1, field2) VALUES ('xxxxx1', 'xxxxxxxxx')
  3    INTO table1 (field1, field2) VALUES ('xxxxx2', 'xxxxxxxxx')
  4    INTO table1 (field1, field2) VALUES ('xxxxx3', 'xxxxxxxxx')
  5  SELECT * FROM DUAL;

単に INSERT文をまとめただけのようですが この書き方は、異なるテーブルでもまとめることができます。 マルチテーブル・インサート と言うのですが 単に INSERT をまとめるだけではありません。

詳しくは次回。

Oracle で簡単棒グラフ表示

Oracle の SQL で簡単に棒グラフを作る小ネタです。

表示制御のために COLUMN とかを使っていますが ポイントは LPAD 関数の使い方です。

SQL> COLUMN TNAME FORMAT A30
SQL> COLUMN BAR   FORMAT A30

SQL> SELECT tname, LPAD('*', LENGTH(tname), '*') AS bar FROM TAB;

TNAME                          BAR
------------------------------ ------------------------------
W_AB_T_OPTAR_POLOLOPL_CLOSED   ****************************
W_START                        *******
E_AB_T_PLANT                   ************
E_AB_T_EERCC_BODY              *****************
E_AB_T_EERCC_HEAD              *****************
E_AB_PLANT_DATA                ***************
E_AB_KUJIDE_BODY               ****************
E_AB_KUJIDE_HEAD               ****************
R_AB_T_OPTAR_CLOSED            *******************
R_AB_T_OPTAR_KAMOKU            *******************
R_AB_KK_DATA                   ************

このサンプルはテーブルの名前の長さという かなりどうでも良いものを棒グラフにしてますが (棒グラフ化しなくてもそのままですし)、 棒グラフ化することで 見えやすくなるものは結構あると思います。