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に対して実行計画を表示する方法がありました。
次のように「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にしか使えませんが テーブル等を作らなくて良いのは助かります。
業務でデータベースは 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行取得できました。
これでページ送りなんかの機能も簡単に作れますね。
最近は 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 は恐ろしいですね。
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 の 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 ************
このサンプルはテーブルの名前の長さという かなりどうでも良いものを棒グラフにしてますが （棒グラフ化しなくてもそのままですし）、 棒グラフ化することで 見えやすくなるものは結構あると思います。