トップページに戻る
次のSQLパズルへ
前のSQLパズルへ
10-349 旅人算メソッドで連続日付をグループ化
SQLパズル
DateTable
StaD EndD
---------- ----------
2009-05-01 2009-05-11
2009-05-12 2009-05-12
2009-05-13 2009-05-13
2009-05-14 2009-05-20
2009-07-01 2009-09-06
2011-03-20 2011-03-31
2011-08-06 2011-08-22
2011-08-23 2011-08-26
2011-08-27 2011-08-27
連続した日付を下記のようにまとめて出力する。
各レコードの日付の開始と終了は、
別レコードの日付の開始と終了とOverLapしてないものとする。
出力結果
StaD EndD cnt
---------- ---------- ---
2009-05-01 2009-05-20 4
2009-07-01 2009-09-06 1
2011-03-20 2011-03-31 1
2011-08-06 2011-08-27 3
データ作成スクリプト
create table DateTable(StaD,EndD) as
select date '2009-05-01',date '2009-05-11' from dual union
select date '2009-05-12',date '2009-05-12' from dual union
select date '2009-05-13',date '2009-05-13' from dual union
select date '2009-05-14',date '2009-05-20' from dual union
select date '2009-07-01',date '2009-09-06' from dual union
select date '2011-03-20',date '2011-03-31' from dual union
select date '2011-08-06',date '2011-08-22' from dual union
select date '2011-08-23',date '2011-08-26' from dual union
select date '2011-08-27',date '2011-08-27' from dual;
SQL
alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd';
--■■■旅人算メソッドを使う方法■■■
with tmp as(
select StaD,EndD,
EndD - sum(EndD-StaD+1) over(order by StaD) as distance
from DateTable)
select min(StaD) as StaD,max(EndD) as EndD,count(*) as cnt
from tmp
group by distance
order by StaD;
--■■■旅人算メソッドを使わない方法■■■
with tmp1 as(
select StaD,EndD,
case when Lag(EndD) over(order by StaD)+1
= StaD then 0 else 1 end as WillSum
from DateTable),
tmp2 as(
select StaD,EndD,
sum(WillSum) over(order by StaD) as GID
from tmp1)
select min(StaD) as StaD,max(EndD) as EndD,count(*) as cnt
from tmp2
group by GID
order by StaD;
解説
旅人算メソッドでは、
旅人Xと旅人Aをイメージしています。
旅人Xの移動量は、1以上です。(endD)
旅人Aの移動量は、1以上です。(sum(endD-staD+1) over(order by staD))
そして、2人の旅人の距離でグループ化してます。
別の考え方として、その行までのトータル日数を
sum(EndD-StaD+1) over(order by StaD) として、
その行のEndDとの差でグループ化すれば、
連続した日付期間ごとになるという考え方もあります。
下記のSQLが理解を深めることでしょう。
select StaD,EndD,
EndD-StaD+1 as "EndD-StaD+1",
sum(EndD-StaD+1) over(order by StaD) as SumDaysDiff,
EndD - sum(EndD-StaD+1) over(order by StaD) as distance
from DateTable
order by StaD;
StaD EndD EndD-StaD+1 SumDaysDiff distance
---------- ---------- ----------- ----------- ----------
2009-05-01 2009-05-11 11 11 2009-04-30
2009-05-12 2009-05-12 1 12 2009-04-30
2009-05-13 2009-05-13 1 13 2009-04-30
2009-05-14 2009-05-20 7 20 2009-04-30
2009-07-01 2009-09-06 68 88 2009-06-10
2011-03-20 2011-03-31 12 100 2010-12-21
2011-08-06 2011-08-22 17 117 2011-04-27
2011-08-23 2011-08-26 4 121 2011-04-27
2011-08-27 2011-08-27 1 122 2011-04-27
その行のStaDと前の行のEndDが等しくないと連続を認めない場合は、
下記のように、+1を除外すればいいです。(旅人Aの移動量が1だけ減るということです)
with DateTable(StaD,EndD) as(
select date '2009-05-01',date '2009-05-11' from dual union
select date '2009-05-11',date '2009-05-15' from dual union
select date '2009-05-16',date '2009-05-20' from dual union
select date '2009-05-20',date '2009-05-30' from dual union
select date '2009-07-01',date '2009-09-06' from dual)
select StaD,EndD,
EndD-StaD as "EndD-StaD",
sum(EndD-StaD) over(order by StaD) as SumDaysDiff,
EndD - sum(EndD-StaD) over(order by StaD) as distance
from DateTable
order by StaD;
StaD EndD EndD-StaD SumDaysDiff distance
---------- ---------- --------- ----------- ----------
2009-05-01 2009-05-11 10 10 2009-05-01
2009-05-11 2009-05-15 4 14 2009-05-01
2009-05-16 2009-05-20 4 18 2009-05-02
2009-05-20 2009-05-30 10 28 2009-05-02
2009-07-01 2009-09-06 67 95 2009-06-03