12. PostgreSQLのstring_aggを模倣
PostgreSQLのstring_agg関数を模倣します。次のサンプルデータを見てください。
create table StringAggT(ID int,Val char(1));
insert into StringAggT values(111,'a'),
(111,'b'),
(111,'c'),
(222,'d'),
(222,'e'),
(222,'f');
PostgreSQLでは以下のように、文字列を集約するstring_agg関数がWindow関数として使用できます。
模倣対象のPostgreSQLのSQL
select ID,Val, string_agg(Val,',') over(partition by ID) as strAgg1, string_agg(Val,',') over(order by Val) as strAgg2 from StringAggT order by ID,Val; ID | Val | strAgg1 | strAgg2 -----+-----+---------+------------- 111 | a | a,b,c | a 111 | b | a,b,c | a,b 111 | c | a,b,c | a,b,c 222 | d | d,e,f | a,b,c,d 222 | e | d,e,f | a,b,c,d,e 222 | f | d,e,f | a,b,c,d,e,f
MySQL 8.0.15では、gruop_concat関数がWindow関数として使用できませんが、JSON_ArrayAgg関数で似た結果を取得可能です。Window関数の集約の内訳を見る用途であれば、JSON_ArrayAgg関数でも問題ないと思います。
Sample18
select ID,Val, JSON_ArrayAgg(Val) over(partition by ID) as strAgg1, JSON_ArrayAgg(Val) over(order by Val) as strAgg2 from StringAggT order by ID,Val; +------+------+-----------------+--------------------------------+ | ID | Val | strAgg1 | strAgg2 | +------+------+-----------------+--------------------------------+ | 111 | a | ["a", "b", "c"] | ["a"] | | 111 | b | ["a", "b", "c"] | ["a", "b"] | | 111 | c | ["a", "b", "c"] | ["a", "b", "c"] | | 222 | d | ["d", "e", "f"] | ["a", "b", "c", "d"] | | 222 | e | ["d", "e", "f"] | ["a", "b", "c", "d", "e"] | | 222 | f | ["d", "e", "f"] | ["a", "b", "c", "d", "e", "f"] | +------+------+-----------------+--------------------------------+
13. PostgreSQLのgroups指定を模倣
PostgreSQLのWindow関数では、rows指定やrange指定の他に、groups指定が使えます。（詳しくは、そーだいなるらくがき帳を参照）
create table groupsT(ID int,Val int);
insert into groupsT values(1, 1),
(2, 1),
(3, 3),
(4, 5),
(5, 5),
(6, 5),
(7, 6);
模倣対象のPostgreSQLのSQL
select id,Val,
array_agg(id) over(order by Val
groups between 1 preceding and 1 following) as groups_id,
array_agg(Val) over(order by Val
groups between 1 preceding and 1 following) as groups_Val
from t;
id | Val | groups_id | groups_Val
----+-----+---------------+---------------
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}
MySQL 8.0で模倣しようとすると、dense_rankで、競技プログラミングの座標圧縮みたいなことをしてから、range指定を使う必要があります。
Sample19
with tmp as(
select id,Val,dense_rank() over(order by Val) as rn
from groupsT)
select id,Val,
JSON_ArrayAgg(id) over(order by rn
range between 1 preceding and 1 following) as groups_id,
JSON_ArrayAgg(Val) over(order by rn
range between 1 preceding and 1 following) as groups_Val
from tmp
order by id;
+------+------+--------------------+--------------------+
| id | Val | groups_id | groups_Val |
+------+------+--------------------+--------------------+
| 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] |
+------+------+--------------------+--------------------+
14. OracleのCountでのdistinct指定を模倣
Oracleでは、分析関数のcount関数でdistinctオプションが使えます。以下のサンプルデータを見てください。
create table OracleDistinctT(ID int,Val int not null); insert into OracleDistinctT values(1,111); insert into OracleDistinctT values(1,111); insert into OracleDistinctT values(1,222); insert into OracleDistinctT values(1,222); insert into OracleDistinctT values(1,333); insert into OracleDistinctT values(2,111); insert into OracleDistinctT values(2,111); insert into OracleDistinctT values(3,111); insert into OracleDistinctT values(3,222); insert into OracleDistinctT values(4,333);
MySQL 8.0で、下記のOracleのSQLと同じ結果を取得してみます。
模倣対象のOracleのSQL
select ID,Val,count(distinct Val) over(partition by ID) as disCnt from OracleDistinctT; ID Val disCnt -- --- ------ 1 111 3 1 111 3 1 222 3 1 222 3 1 333 3 2 111 1 2 111 1 3 111 2 3 222 2 4 333 1
Sample20
select ID,Val,max(rn) over(partition by ID) as disCnt
from (select ID,Val,dense_rank() over(partition by ID order by Val) as rn
from OracleDistinctT) tmp
order by ID,Val;
Sample20の相関サブクエリを使った代替方法
select ID,Val, (select count(distinct b.Val) from OracleDistinctT b where b.ID = a.ID) as disCnt from OracleDistinctT a order by ID,Val;
dense_rank関数の結果の最大値を取得して模倣しています。脳内のイメージは、このようになります。partition by ID に対応する赤線を引いています。
ちなみに、count(distinct Val) は、Valがnullだとカウントしないので、Valがnullの場合も考慮するなら、下記のように存在肯定命題を使う必要があります。
Sample21
select ID,Val,max(rn) over(partition by ID) - HasNull as disCnt
from (select ID,Val,dense_rank() over(partition by ID order by Val) as rn,
max(Val is null) over(partition by ID) as HasNull
from OracleDistinctT) tmp
order by ID,Val;
15. Oracleのnth_Valueでのfrom Lastを模倣
Oracleでは、分析関数のnth_Value関数でfrom Lastが使えます。以下のサンプルデータを見てください。
create table OracleFromLastT(ID char(1),SortKey1 int,SortKey2 int,Val int);
insert into OracleFromLastT values('A',1,1,111);
insert into OracleFromLastT values('A',1,2,222);
insert into OracleFromLastT values('A',3,1,333);
insert into OracleFromLastT values('A',3,2,444);
insert into OracleFromLastT values('B',5,1,555);
insert into OracleFromLastT values('B',5,2,666);
insert into OracleFromLastT values('B',5,3,777);
insert into OracleFromLastT values('B',7,1,888);
MySQL 8.0で、下記のOracleのSQLと同じ結果を取得してみます。
模倣対象のOracleのSQL
select ID,SortKey1,SortKey2,Val,
nth_Value(Val,2) from Last
over(partition by ID order by SortKey1,SortKey2
Rows between Unbounded Preceding
and Unbounded Following) as SecondVal
from OracleFromLastT
order by SortKey1,SortKey2;
ID SortKey1 SortKey2 Val SecondVal
-- -------- -------- --- ---------
A 1 1 111 333
A 1 2 222 333
A 3 1 333 333
A 3 2 444 333
B 5 1 555 777
B 5 2 666 777
B 5 3 777 777
B 7 1 888 777
Sample22
select ID,SortKey1,SortKey2,Val,
nth_Value(Val,2)
over(partition by ID order by SortKey1 desc,SortKey2 desc
Rows between Unbounded Preceding
and Unbounded Following) as SecondVal
from OracleFromLastT
order by SortKey1,SortKey2;
Sample22の相関サブクエリを使った代替方法
select ID,SortKey1,SortKey2,Val, (select b.Val from OracleFromLastT b where b.ID = a.ID order by b.SortKey1 desc,b.SortKey2 desc Limit 1 OffSet 1) as SecondVal from OracleFromLastT a order by ID,Val;
order by句のascとdescを入れ替えると、逆ソートができることをふまえています。
例1 order by X,Y,Zの逆ソートは、
order by X desc,Y desc,Z desc
例2 order by X desc,Y,Z ascの逆ソートは、
order by X asc,Y desc,Z desc
16. OracleのLast_Valueでのignore nullsを模倣（entire）
以下のサンプルを見てください。
create table ignLastValT(ID int,SortKey int,Val int); insert into ignLastValT values(1,1, 555); insert into ignLastValT values(1,2,null); insert into ignLastValT values(1,3, 111); insert into ignLastValT values(1,4,null); insert into ignLastValT values(2,1, 888); insert into ignLastValT values(2,2, 222); insert into ignLastValT values(2,3,null); insert into ignLastValT values(2,4, 444); insert into ignLastValT values(3,1, 777); insert into ignLastValT values(3,2,null); insert into ignLastValT values(3,3,null); insert into ignLastValT values(3,4,null); insert into ignLastValT values(3,5, 333); insert into ignLastValT values(4,1,null); insert into ignLastValT values(4,2,null);
Oracleの分析関数では、First_Value関数やLast_Value関数やnth_Value関数で、ignore nullsを指定できます。Last_Value(値 ignore nulls) over句 が基本的な使い方ですが、Last_Value(case when 条件 then 値 end ignore nulls) over句 といった風に、case式を組み合わせて使う場合が多いです。
MySQL 8.0で、下記のOracleのSQLと同じ結果を取得してみます。
模倣対象のOracleのSQL
select ID,SortKey,Val,
Last_Value(Val ignore nulls)
over(partition by ID
order by SortKey Rows between Unbounded Preceding
and Unbounded Following) as LastVal1
from ignLastValT
order by ID,SortKey;
ID SortKey Val LastVal1
-- ------- ---- --------
1 1 555 111
1 2 null 111
1 3 111 111
1 4 null 111
2 1 888 444
2 2 222 444
2 3 null 444
2 4 444 444
3 1 777 333
3 2 null 333
3 3 null 333
3 4 null 333
3 5 333 333
4 1 null null
4 2 null null
Sample23
select ID,SortKey,Val,
Last_Value(Val) over(partition by ID
order by if(Val is null,0,1),SortKey
Rows between Unbounded Preceding
and Unbounded Following) as LastVal1
from ignLastValT
order by ID,SortKey;
if関数で、Valがnullな行が最後にならないようにしています。
Sample23の相関サブクエリを使った代替方法
select ID,SortKey,Val,
(select b.Val
from ignLastValT b
where b.ID = a.ID
and b.Val is not null
order by b.SortKey desc Limit 1) as LastVal1
from ignLastValT a
order by ID,SortKey;
脳内のイメージは、このようになります。
17. OracleのLast_Valueでのignore nullsを模倣（until）
今度は、その行までを対象としたLast_Value関数（ignore nulls）を模倣します。
模倣対象のOracleのSQL
select ID,SortKey,Val, Last_Value(Val ignore nulls) over(partition by ID order by SortKey) as LastVal2 from ignLastValT order by ID,SortKey; ID SortKey Val LastVal2 -- ------- ---- -------- 1 1 555 555 1 2 null 555 1 3 111 111 1 4 null 111 2 1 888 888 2 2 222 222 2 3 null 222 2 4 444 444 3 1 777 777 3 2 null 777 3 3 null 777 3 4 null 777 3 5 333 333 4 1 null null 4 2 null null
Sample24
select ID,SortKey,Val,
max(case SortKey when maxSortKey then Val end)
over(partition by ID,maxSortKey) as LastVal2
from (select ID,SortKey,Val,
max(case when Val is not null then SortKey end)
over(partition by ID order by SortKey) as maxSortKey
from ignLastValT) a
order by ID,SortKey;
最初にインラインビューでWindow関数のmax関数を使って、SortKeyの昇順で、その行以前で最後にValがnullでなかったSortKeyを求めています。次に、そのSortKeyの行のValの値を、Pivotクエリでよく使われるmax関数とcase式を組み合わせる手法で求めています。
Sample24の相関サブクエリを使った代替方法
select ID,SortKey,Val,
(select b.Val
from ignLastValT b
where b.ID = a.ID
and b.SortKey <= a.SortKey
and b.Val is not null
order by b.SortKey desc Limit 1) as LastVal2
from ignLastValT a
order by ID,SortKey;
脳内のイメージ（第1段階）は、こうなります。partition by ID に対応する赤線を引いています。
脳内のイメージ（最終段階）は、こうなります。partition by ID,maxSortKey に対応する紫線を引いています。
18. OracleのLag,Leadでのignore nullsを模倣
Oracleでは、Lag関数とLead関数でもignore nullsを指定することができます。MySQL 8.0で、下記のOracleのSQLと同じ結果を取得してみます。
create table ignLagLeadT(SortKey int,Val int); insert into ignLagLeadT values( 1, 2); insert into ignLagLeadT values( 2,null); insert into ignLagLeadT values( 5, 4); insert into ignLagLeadT values( 9,null); insert into ignLagLeadT values(11, 6); insert into ignLagLeadT values(12,null); insert into ignLagLeadT values(14,null); insert into ignLagLeadT values(16, 5); insert into ignLagLeadT values(17,null); insert into ignLagLeadT values(20, 3); insert into ignLagLeadT values(21,null); insert into ignLagLeadT values(22, 4);
模倣対象のOracleのSQL
select SortKey,Val,
Lag (Val,2,999) ignore nulls over(order by SortKey) as Lag2,
Lead(Val,2,999) ignore nulls over(order by SortKey) as Lead2
from ignLagLeadT;
SortKey Val Lag2 Lead2
------- ---- ---- -----
1 2 999 6
2 null 999 6
5 4 999 5
9 null 2 5
11 6 2 3
12 null 4 3
14 null 4 3
16 5 4 4
17 null 6 4
20 3 6 999
21 null 5 999
22 4 5 999
Sample25
select SortKey,Val,
IfNull(max(Val) over(order by NonNullCnt1
range between 2 Preceding
and 2 Preceding),999) as Lag2,
IfNull(max(Val) over(order by NonNullCnt2
range between 2 Preceding
and 2 Preceding),999) as Lead2
from (select SortKey,Val,
count(Val) over(order by SortKey Rows
between Unbounded Preceding
and 1 Preceding) as NonNullCnt1,
count(Val) over(order by SortKey Rows
between 1 Following
and Unbounded Following) as NonNullCnt2
from ignLagLeadT) a
order by SortKey;
Window関数のcount関数で、その行の1つ前までのNullでない行数と、その行の1つ後からのNullでない行数を求めておき、その行数でorder byしてrange指定しています。
Sample25の相関サブクエリを使った代替方法
select SortKey,Val,
IfNull((select b.Val
from ignLagLeadT b
where b.Val is not null
and b.SortKey < a.SortKey
order by b.SortKey desc Limit 1 offset 1),999) as Lag2,
IfNull((select b.Val
from ignLagLeadT b
where b.Val is not null
and b.SortKey > a.SortKey
order by b.SortKey Limit 1 offset 1),999) as Lead2
from ignLagLeadT a
order by SortKey;
最後に
今回は、Window関数を扱いました。次回は、再帰With句を扱います。
