5. 前後の行の値
指定したソートキーでの、前の行の値が欲しい時に使われるのが、Lag関数で、後の行の値が欲しい時に使われるのが、Lead関数です。以下のようになります。
create table LagLeadT(ID char(2),SortKey int,Val int);
insert into LagLeadT values('AA',1,10),
('AA',3,20),
('AA',5,60),
('AA',7,30),
('BB',2,40),
('BB',4,80),
('BB',6,50);
Sample08
select ID,SortKey, Lag(Val) over(partition by ID order by SortKey) as Prev,Val, Lead(Val) over(partition by ID order by SortKey) as Next from LagLeadT order by ID,SortKey; +------+---------+------+------+------+ | ID | SortKey | Prev | Val | Next | +------+---------+------+------+------+ | AA | 1 | NULL | 10 | 20 | | AA | 3 | 10 | 20 | 60 | | AA | 5 | 20 | 60 | 30 | | AA | 7 | 60 | 30 | NULL | | BB | 2 | NULL | 40 | 80 | | BB | 4 | 40 | 80 | 50 | | BB | 6 | 80 | 50 | NULL | +------+---------+------+------+------+
Sample08の相関サブクエリを使った代替方法
select ID,SortKey, (select b.Val from LagLeadT b where b.ID = a.ID and b.SortKey < a.SortKey order by b.SortKey desc Limit 1) as Prev, Val, (select b.Val from LagLeadT b where b.ID = a.ID and b.SortKey > a.SortKey order by b.SortKey Limit 1) as Next from LagLeadT a order by ID,SortKey;
脳内のイメージは、このようになります。partition byで、脳内で赤線を引くと分かりやすいです。
Lag関数とLead関数は、キーブレイクの事前検知にも使えます。ID列,SortKey列をソートキー、ID列をブレークキーとして、 キーブレイクを検知してみます。case式を使うのが定番ですが、これは、case式を使わなくて良いパターンです。
case式を使う定番の方法
select ID,SortKey,Val, case when ID = Lag(ID) over(order by ID,SortKey) then 0 else 1 end as IsKeyBreaked, case when ID = Lead(ID) over(order by ID,SortKey) then 0 else 1 end as WillKeyBreak from LagLeadT order by ID,SortKey;
Sample09
select ID,SortKey,Val, Lag (0,1,1) over(partition by ID order by SortKey) as IsKeyBreaked, Lead(0,1,1) over(partition by ID order by SortKey) as WillKeyBreak from LagLeadT order by ID,SortKey;
6. 累計
帳票で累計を求めたい時に使うのが、order byを指定した、Window関数のsum関数です。以下のようになります。
create table runSumT(ID char(2),SortKey int,Val int);
insert into runSumT values('AA',1,10),
('AA',2,20),
('AA',3,40),
('AA',4,80),
('BB',1,10),
('BB',2,30),
('BB',3,90),
('CC',1,50),
('CC',2,60),
('CC',2,60);
Sample10
select ID,SortKey,Val, sum(Val) over(partition by ID order by SortKey) as runSum from runSumT order by ID,SortKey; +------+---------+------+--------+ | ID | SortKey | Val | runSum | +------+---------+------+--------+ | AA | 1 | 10 | 10 | | AA | 2 | 20 | 30 | | AA | 3 | 40 | 70 | | AA | 4 | 80 | 150 | | BB | 1 | 10 | 10 | | BB | 2 | 30 | 40 | | BB | 3 | 90 | 130 | | CC | 1 | 50 | 50 | | CC | 2 | 60 | 170 | | CC | 2 | 60 | 170 | +------+---------+------+--------+
Sample10の相関サブクエリを使った代替方法
select ID,SortKey,Val, (select sum(b.Val) from runSumT b where b.ID = a.ID and b.SortKey <= a.SortKey) as runSum from runSumT a order by ID,SortKey;
order byを指定して、frame句を省略すると、デフォルトの、Range Between Unbounded Preceding and Current rowになります。frame句は、下記のように解釈すると分かりやすいです。
frame句の解釈
order by SortKey -- SortKeyの昇順で、 Range Between -- 値の範囲は、 Unbounded Preceding -- 前方は、際限なく、 and Current row -- 後方は、カレント行まで
脳内のイメージは、このようになります。partition byで、脳内で赤線を引くと分かりやすいです。
7. 移動累計
続いて、3日移動累計を求めてみます。サンプルは以下の通りです。
create table idouT(DayCol date,Val int);
insert into idouT values(date '2019-05-11', 10),
(date '2019-05-12', 20),
(date '2019-05-15', 60),
(date '2019-05-16', 100),
(date '2019-05-17', 200),
(date '2019-05-18', 600),
(date '2019-05-19',1000),
(date '2019-05-25',2000);
Sample11
select DayCol,Val, sum(Val) over(order by DayCol rows 2 preceding) as moveSum1, sum(Val) over(order by DayCol range InterVal 2 day preceding) as moveSum2 from idouT order by DayCol; +------------+------+----------+----------+ | DayCol | Val | moveSum1 | moveSum2 | +------------+------+----------+----------+ | 2019-05-11 | 10 | 10 | 10 | | 2019-05-12 | 20 | 30 | 30 | | 2019-05-15 | 60 | 90 | 60 | | 2019-05-16 | 100 | 180 | 160 | | 2019-05-17 | 200 | 360 | 360 | | 2019-05-18 | 600 | 900 | 900 | | 2019-05-19 | 1000 | 1800 | 1800 | | 2019-05-25 | 2000 | 3600 | 2000 | +------------+------+----------+----------+
Sample11の相関サブクエリを使った代替方法
select DayCol,Val,
(select sum(b.Val)
from idouT b
where (select count(*)
from idouT c
where c.DayCol between b.DayCol and a.DayCol)
between 1 and 2 + 1) as moveSum1,
(select sum(b.Val)
from idouT b
where b.DayCol between a.DayCol - 2 and a.DayCol) as moveSum2
from idouT a
order by DayCol;
Window関数のorder by以降の、省略時の仕様として、order by DayCol rows 2 precedingは、order by DayCol rows between 2 preceding and current rowと同じ扱いとなります。
同様に、order by DayCol range InterVal 2 day precedingは、order by DayCol range between InterVal 2 day preceding and current rowと同じ扱いとなります。
rows指定とrange指定の違いは、rowsは、ソートキーで並べた時の、前もしくは後ろの行数の指定である一方、rangeは、ソートキーが、どれだけ前もしくは後ろかの指定であることです。それぞれ、下記のように解釈すると分かりやすいです。
frame句（rows）の解釈
order by DayCol -- DayColの昇順で、 rows between -- 行の範囲は、 2 preceding -- 2行前から and current row -- カレント行まで
frame句（Range）の解釈
order by DayCol -- DayColの昇順で、 range between -- 値の範囲は、 InterVal 2 day preceding -- 2日前から and current row -- カレント行まで
脳内のイメージは、このようになります。
8. First_ValueとLast_Valueとnth_Value
指定したソートキーでの、最初の行の値を求めるのが、First_Valueで、指定したソートキーでの、最後の行の値を求めるのが、Last_Valueです。指定したソートキーでの、（Row_Numberな順位が）n番目の行の値を求めるのが、nth_Valueとなります。サンプルは以下の通りです。
create table nthT(ID int,SortKey int,Val int);
insert into nthT values(1,10,666),
(1,30,333),
(1,40,222),
(1,50,444),
(2,20,777),
(2,25,111),
(2,27,555),
(3,60,999),
(3,61,888);
Sample12
select ID,SortKey,Val,
First_Value(Val) over(partition by ID order by SortKey) as FirVal,
Last_Value(Val) over(partition by ID order by SortKey
Rows between Unbounded Preceding
and Unbounded Following) as LastVal,
nth_Value(Val,2) over(partition by ID order by SortKey
Rows between Unbounded Preceding
and Unbounded Following) as SecondVal,
nth_Value(Val,3) over(partition by ID order by SortKey
Rows between Unbounded Preceding
and Unbounded Following) as ThirdVal
from nthT
order by ID,SortKey;
+------+---------+------+--------+---------+-----------+----------+
| ID | SortKey | Val | FirVal | LastVal | SecondVal | ThirdVal |
+------+---------+------+--------+---------+-----------+----------+
| 1 | 10 | 666 | 666 | 444 | 333 | 222 |
| 1 | 30 | 333 | 666 | 444 | 333 | 222 |
| 1 | 40 | 222 | 666 | 444 | 333 | 222 |
| 1 | 50 | 444 | 666 | 444 | 333 | 222 |
| 2 | 20 | 777 | 777 | 555 | 111 | 555 |
| 2 | 25 | 111 | 777 | 555 | 111 | 555 |
| 2 | 27 | 555 | 777 | 555 | 111 | 555 |
| 3 | 60 | 999 | 999 | 888 | 888 | NULL |
| 3 | 61 | 888 | 999 | 888 | 888 | NULL |
+------+---------+------+--------+---------+-----------+----------+
Sample12の相関サブクエリを使った代替方法
select ID,SortKey,Val, (select b.Val from nthT b where b.ID = a.ID order by b.SortKey Limit 1) as FirVal, (select b.Val from nthT b where b.ID = a.ID order by b.SortKey desc Limit 1) as LastVal, (select b.Val from nthT b where b.ID = a.ID order by b.SortKey Limit 1 OffSet 1) as SecondVal, (select b.Val from nthT b where b.ID = a.ID order by b.SortKey Limit 1 OffSet 2) as ThirdVal from nthT a order by ID,SortKey;
脳内のイメージは、このようになります。partition byで、脳内で赤線を引くと分かりやすいです。
9. 全称肯定、全称否定、存在肯定、存在否定
全称肯定、全称否定、存在肯定、存在否定は、
- 全ての行が条件を満たすか？
- 全ての行が条件を満たさないか？
- 少なくとも1行が条件を満たすか？
- 少なくとも1行が条件を満たさないか？
といった複数行にまたがったチェックをしたい時に使います。以下のサンプルを見てください。
create table boolCheckT(ID char(2),Val int);
insert into boolCheckT values('AA',10),
('AA',20),
('BB',10),
('BB',30),
('BB',50),
('CC',80),
('CC',90),
('DD',20),
('DD',70);
そして、下記をチェックしてみましょう。
- check1 IDごとで、全ての行が Val < 40 を満たすか？
- check2 IDごとで、全ての行が Val < 40 を満たさないか？
- check3 IDごとで、少なくとも1つの行が Val < 40 を満たすか？
- check4 IDごとで、少なくとも1つの行が Val < 40 を満たさないか？
- check5 IDごとで、少なくとも1つの行が Val = 10 を満たし、かつ、少なくとも1つの行が Val = 50 を満たすか？
Sample13
select ID,Val, min(Val < 40) over(partition by ID) as chk1, min((Val < 40) = false) over(partition by ID) as chk2, max(Val < 40) over(partition by ID) as chk3, max((Val < 40) = false) over(partition by ID) as chk4, max(Val = 10) over(partition by ID) and max(Val = 50) over(partition by ID) as chk5 from boolCheckT order by ID,Val; +------+------+------+------+------+------+------+ | ID | Val | chk1 | chk2 | chk3 | chk4 | chk5 | +------+------+------+------+------+------+------+ | AA | 10 | 1 | 0 | 1 | 0 | 0 | | AA | 20 | 1 | 0 | 1 | 0 | 0 | | BB | 10 | 0 | 0 | 1 | 1 | 1 | | BB | 30 | 0 | 0 | 1 | 1 | 1 | | BB | 50 | 0 | 0 | 1 | 1 | 1 | | CC | 80 | 0 | 1 | 0 | 1 | 0 | | CC | 90 | 0 | 1 | 0 | 1 | 0 | | DD | 20 | 0 | 0 | 1 | 1 | 0 | | DD | 70 | 0 | 0 | 1 | 1 | 0 | +------+------+------+------+------+------+------+
Window関数のmax関数やmin関数で、論理演算を使用しています。
脳内のイメージは、このようになります。partition byで、脳内で赤線を引くと分かりやすいです。
全称肯定、全称否定、存在肯定、存在否定を、SQLへ変換する公式は下記となります。
| 全称肯定命題 | min(条件) |
| 全称否定命題 | min(条件 = false) |
| 存在肯定命題 | max(条件) |
| 存在否定命題 | max(条件 = false) |
| 存在肯定命題の論理積 | max(条件A) and max(条件B) |
minやmaxの代わりに、sumを使っても、似た結果を取得できますので、使い分けるといいでしょう。
Sample14
select ID,Val, sum((Val < 40) = false) over(partition by ID) as chk1, sum(Val < 40) over(partition by ID) as chk2, sum(Val < 40) over(partition by ID) as chk3, sum((Val < 40) = false) over(partition by ID) as chk4, sum(Val = 10) over(partition by ID) and sum(Val = 50) over(partition by ID) as chk5 from boolCheckT order by ID,Val; +------+------+------+------+------+------+------+ | ID | Val | chk1 | chk2 | chk3 | chk4 | chk5 | +------+------+------+------+------+------+------+ | AA | 10 | 0 | 2 | 2 | 0 | 0 | | AA | 20 | 0 | 2 | 2 | 0 | 0 | | BB | 10 | 1 | 2 | 2 | 1 | 1 | | BB | 30 | 1 | 2 | 2 | 1 | 1 | | BB | 50 | 1 | 2 | 2 | 1 | 1 | | CC | 80 | 2 | 0 | 0 | 2 | 0 | | CC | 90 | 2 | 0 | 0 | 2 | 0 | | DD | 20 | 1 | 1 | 1 | 1 | 0 | | DD | 70 | 1 | 1 | 1 | 1 | 0 | +------+------+------+------+------+------+------+
集約関数のmin関数やmax関数でも似たことができます。
集約関数での全称肯定命題など
select ID,group_concat(cast(Val as char(2))) as ConcatVal, min(Val < 40) as chk1, min((Val < 40) = false) as chk2, max(Val < 40) as chk3, max((Val < 40) = false) as chk4, max(Val = 10) and max(Val = 50) as chk5 from boolCheckT group by ID order by ID; +------+-----------+------+------+------+------+------+ | ID | ConcatVal | chk1 | chk2 | chk3 | chk4 | chk5 | +------+-----------+------+------+------+------+------+ | AA | 10,20 | 1 | 0 | 1 | 0 | 0 | | BB | 10,30,50 | 0 | 0 | 1 | 1 | 1 | | CC | 80,90 | 0 | 1 | 0 | 1 | 0 | | DD | 20,70 | 0 | 0 | 1 | 1 | 0 | +------+-----------+------+------+------+------+------+
select句よりも、下記のようにhaving句で使われることが多いです。
having句での存在肯定命題
select ID,group_concat(cast(Val as char(2))) as ConcatVal from boolCheckT group by ID having max(Val < 40) order by ID; +------+-----------+ | ID | ConcatVal | +------+-----------+ | AA | 10,20 | | BB | 10,30,50 | | DD | 20,70 | +------+-----------+
10. 最頻値（モード）
最頻値（モード）を求めます。以下のサンプルデータを見てください。
create table DayWeatherT(DayCol date,weather char(6));
insert into DayWeatherT values(date '2018-01-02','sunny' ),
(date '2018-01-15','snowy' ),
(date '2018-01-30','snowy' ),
(date '2018-06-01','cloudy'),
(date '2018-06-13','cloudy'),
(date '2018-06-24','rainy' ),
(date '2018-06-30','rainy' ),
(date '2018-07-02','sunny' ),
(date '2018-07-14','sunny' ),
(date '2018-07-23','sunny' ),
(date '2018-07-31','sunny' ),
(date '2018-11-10','cloudy');
まずは、weatherの最頻値（モード）を求めてみます。最頻値が複数ある場合は、複数行返すようにします。
最頻値が必ず1つだけなら、以下で可
select weather,count(*) as cnt from DayWeatherT group by weather order by count(*) desc Limit 1;
Sample15
select weather,cnt
from (select weather,count(*) as cnt,
max(count(*)) over() as maxCnt
from DayWeatherT
group by weather) tmp
where cnt = maxCnt;
+---------+-----+
| weather | cnt |
+---------+-----+
| sunny | 5 |
+---------+-----+
Sample15のサブクエリを使った代替方法
select weather,count(*) as cnt
from DayWeatherT
group by weather
having count(*) >= all(select count(*)
from DayWeatherT
group by weather);
脳内のイメージは、このようになります。group by weather に対応する赤線を引いています。
次に、monthごとの最頻値（モード）を求めています。
Sample16
select monthCol,weather,cnt
from (select Date_Format(DayCol,'%m') as monthCol,weather,
count(*) as cnt,
max(count(*)) over(partition by Date_Format(DayCol,'%m')) as maxCnt
from DayWeatherT
group by monthCol,weather) a
where cnt = maxCnt
order by monthCol,weather;
+----------+---------+-----+
| monthCol | weather | cnt |
+----------+---------+-----+
| 01 | snowy | 2 |
| 06 | cloudy | 2 |
| 06 | rainy | 2 |
| 07 | sunny | 4 |
| 11 | cloudy | 1 |
+----------+---------+-----+
Sample16の相関サブクエリを使った代替方法
select monthCol,weather,count(*) as cnt
from (select Date_Format(DayCol,'%m') as monthCol,weather
from DayWeatherT) a
group by monthCol,weather
having count(*) >= all(select count(*)
from DayWeatherT b
where Date_Format(b.DayCol,'%m') = a.monthCol
group by b.weather)
order by monthCol,weather;
Sample16の脳内のイメージ（第1段階）は、このようになります。group by Date_Format(DayCol,'%m'),weather に対応する赤線を引いています。
Sample16の脳内のイメージ（最終段階）は、こうなります。partition by Date_Format(DayCol,'%m') に対応する超極太赤線を引いています。
11. Range指定で2分前の値を求める
「5. 前後の行の値」で説明したように、 Lag関数を使えば、指定したソートキーの順序での、1行前の行の値や2行前の行の値などが取得できます。
Window関数のRange指定を使えば、指定した列の値（数値型）が、1小さい行の値や、2小さい行の値などを取得できます。 また、指定した列の値（日時型）が、1分前の行の値や、2分前の行の値なども取得できます。以下のサンプルデータがあります。
create table WindowRangeT(DateTimeCol DateTime,Val int);
insert into WindowRangeT values(TimeStamp '2019-05-01 10:00:00',111),
(TimeStamp '2019-05-01 10:02:00',222),
(TimeStamp '2019-05-01 10:05:00',333),
(TimeStamp '2019-05-01 10:09:00',444),
(TimeStamp '2019-05-01 10:10:00',555),
(TimeStamp '2019-05-01 10:11:00',666),
(TimeStamp '2019-05-01 10:12:00',777),
(TimeStamp '2019-05-01 10:15:00',888);
DateTimeColが2分前の行のValを取得してみます。
Sample17
select DateTimeCol,Val,
max(Val) over(order by DateTimeCol
range between InterVal 2 minute Preceding
and InterVal 2 minute Preceding) as ValBefore2Minute
from WindowRangeT
order by DateTimeCol;
+---------------------+------+------------------+
| DateTimeCol | Val | ValBefore2Minute |
+---------------------+------+------------------+
| 2019-05-01 10:00:00 | 111 | NULL |
| 2019-05-01 10:02:00 | 222 | 111 |
| 2019-05-01 10:05:00 | 333 | NULL |
| 2019-05-01 10:09:00 | 444 | NULL |
| 2019-05-01 10:10:00 | 555 | NULL |
| 2019-05-01 10:11:00 | 666 | 444 |
| 2019-05-01 10:12:00 | 777 | 555 |
| 2019-05-01 10:15:00 | 888 | NULL |
+---------------------+------+------------------+
Sample17の相関サブクエリを使った代替方法
select DateTimeCol,Val, (select max(b.Val) from WindowRangeT b where b.DateTimeCol = a.DateTimeCol - InterVal 2 minute) as ValBefore2Minute from WindowRangeT a order by DateTimeCol;
Window関数のorder by以降は、下記のように解釈すると分かりやすいでしょう。
order by以降の解釈
order by DateTimeCol -- DateTimeColの昇順で、
range between -- 値の範囲は、
InterVal 2 minute Preceding -- 小さいほうは、2分前の行から
and InterVal 2 minute Preceding -- 大きいほうは、2分前の行まで
脳内のイメージは、このようになります。
