OracleのSQLに欲しい13の他DBの機能

OracleのSQLに欲しいと思う他DBの機能をまとめてみました。

#01 分析関数を使用したUpdatebleViewとDeletableView
SQLServerの機能です。
SQLServerでは、分析関数を使用したビューが更新(および削除)可能です。

create table TestTable(Val int);

insert into TestTable values
(1),(3),(5),(10),(20),(30),(40);

with UpdView as(
select Val,Row_Number() over(order by Val) as NewVal
 from TestTable)
update UpdView set Val = NewVal;

with DelView as(
select Row_Number() over(order by Val) as rn
 from TestTable)
delete from DelView where rn < 3;

IBM Db2は、さらに便利でして、With句を使わずに、UpdateやDeleteできます。

update TestTable set Val = Row_Number() over(order by Val);

delete from (select Row_Number() over(order by Val) as rn
              from TestTable)
 where rn < 3;

#02 count関数のdistinct指定で複数式指定
MySQLの機能です。
MySQLでは、count関数のdistinct指定で複数式指定できます。

select count(distinct ColA,ColB) as Cnt
  from (select 1 as ColA,2 as ColB union all
        select 1,2 union all
        select 8,8 union all
        select 8,9 union all
        select 9,8) as tmp;

+-----+
| Cnt |
+-----+
|   4 |
+-----+

Oracleだと下記のように、
分析関数のDense_rankで、競技プログラミングの座標圧縮みたいなことをして、模倣できます。

with t(ColA,ColB) as(
select 1,2 from dual union all
select 1,2 from dual union all
select 8,8 from dual union all
select 8,9 from dual union all
select 9,8 from dual)
select count(distinct rn) as Cnt
from (select dense_rank() over(order by ColA,ColB) as rn
        from t)

| Cnt |
|-----|
|   4 |

集約関数のDense_rankでも模倣できますが、分かりづらいです。

with t(ColA,ColB) as(
select 1,2 from dual union all
select 1,2 from dual union all
select 8,8 from dual union all
select 8,9 from dual union all
select 9,8 from dual)
select -1 + dense_rank(null,null) WithIn group(order by ColA,ColB) as Cnt
  from t;

| Cnt |
|-----|
|   4 |

#03 bool_or関数とbool_and関数
PostgreSQLの機能です。
SQLパズル(2版)の「17. 人材紹介会社」のようなSQLを書くときに使えます。

PostgreSQLでSQLパズルの問題を解く 17. 人材紹介会社
https://oraclesqlpuzzle.ninja-web.net/pgcon2017-sqlpuzzle.html#1-17

#04 正規表現での肯定先読み、否定先読み、肯定戻り読み、否定戻り読み
PostgreSQLの機能です。

PostgreSQL 9.6.5文書 9.7. パターンマッチ
https://www.postgresql.jp/document/9.6/html/functions-matching.html#posix-atoms-table

#05 generate_series関数
PostgreSQLの機能です。

PostgreSQL 9.6.5文書 9.24. 集合を返す関数
https://www.postgresql.jp/document/9.6/html/functions-srf.html

Oracleで同じことをやろうとすると表関数を定義する必要があるので、
定義済ファンクションとして用意しておいて欲しいです。

#06 boolean型
PostgreSQLやMySQLの機能です。

OracleはPL/SQLでは、boolean型をサポートしますが、
SQLでは、サポートしてないです。

1行1列、または、0行1列の結果を返すselect文を
スカラーサブクエリといいますが、

boolean型をサポートするDBでは、
where句やhaving句やcase式のWhen句で
boolean型を列としたスカラーサブクエリを直接書けます。

#07 date型とdatetime型
MySQLの機能です。
テーブル設計の際に、時間情報の必要有無で使い分けできます。

#08 UnSigned Int型
MySQLの機能です。
テーブル設計の際に、負数の必要有無で使い分けできます。

#09 配列型とArray_Agg関数
PostgreSQLの機能です。
配列型は、SQL99の機能でもあります。

配列型とArray_Agg関数は、
SQLパズル(2版)の「27. 等しい集合を見つける」のようなSQLを書くときに使えます。

PostgreSQLでSQLパズルの問題を解く 27. 等しい集合を見つける
https://oraclesqlpuzzle.ninja-web.net/pgcon2017-sqlpuzzle.html#1-27

#10 Values構文
PostgreSQLの機能です。
Insert文のValues指定とか、With句でテストデータを定義する際に使えます。

with t(ColA,ColB) as(
values(1,2),
      (3,4),
      (5,6))
select * from t;

| ColA | ColB |
|------|------|
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |

#11 列なしSelect文
PostgreSQLの機能です。
Exists述語のSelect文のSelect句には、何も書かないという選択肢が登場しました

リレーショナル・データベースの世界 EXISTS述語のサブクエリ内
http://mickindex.sakura.ne.jp/database/db_optimize.html#LocalLink-aster

with t(Val) as(values('A'))
select * from t
 where exists(select from t);

| Val |
|-----|
|   A |

#12 Window関数でのGroups指定
PostgreSQLの機能です。

そーだいなるらくがき帳
https://soudai.hatenablog.com/entry/2018/11/05/005103 より引用

with t(id, value) as(
values (1, 1),
       (2, 1),
       (3, 3),
       (4, 5),
       (5, 5),
       (6, 5),
       (7, 6))
select id,value,
array_agg(id) over(order by value
                   groups between 1 preceding and 1 following) as groups_id,
array_agg(value) over(order by value
                      groups between 1 preceding and 1 following) as groups_value
  from t;

 id | value |   groups_id   | groups_value
----+-------+---------------+---------------
  1 |     1 | {1,2,3}       | {1,1,3}
  2 |     1 | {1,2,3}       | {1,1,3}
  3 |     3 | {1,2,3,4,5,6} | {1,1,3,5,5,5}
  4 |     5 | {3,4,5,6,7}   | {3,5,5,5,6}
  5 |     5 | {3,4,5,6,7}   | {3,5,5,5,6}
  6 |     5 | {3,4,5,6,7}   | {3,5,5,5,6}
  7 |     6 | {4,5,6,7}     | {5,5,5,6}

Oracle11g2XEで模倣しようとすると、
Dense_rankで、競技プログラミングの座標圧縮みたいなことをしてから
Range指定を組み合わせる必要があるので、面倒です。

分析関数としても使えて、order指定できたwm_sys.wm_conatは、
11g2XEで消されたようなので、sumを使ってます。

with t(id, value) as(
select 1, 1 from dual union
select 2, 1 from dual union
select 3, 3 from dual union
select 4, 5 from dual union
select 5, 5 from dual union
select 6, 5 from dual union
select 7, 6 from dual),
tmp as(
select id, value,dense_rank() over(order by value) as rn
  from t)
select id, value,
sum(id) over(order by rn
             range between 1 preceding and 1 following) as groups_id,
sum(value) over(order by rn
                range between 1 preceding and 1 following) as groups_value
  from tmp
order by id;

| id | value | groups_id | groups_value |
|----|-------|-----------|--------------|
|  1 |     1 |         6 |            5 |
|  2 |     1 |         6 |            5 |
|  3 |     3 |        21 |           20 |
|  4 |     5 |        25 |           24 |
|  5 |     5 |        25 |           24 |
|  6 |     5 |        25 |           24 |
|  7 |     6 |        22 |           21 |

#13 集合演算のInterSectとMinusでのall指定
PostgreSQLの機能です。

特にMinusでのall指定が使えれば、
データ比較が強力になります。

第3回 分析関数 count(*) over() と minus
https://www.oracle.com/technetwork/jp/articles/otnj-sql-image3-2-323603-ja.html#p01d