SQL> select count(*),
2 count(nullif("OVERLAPS1","OVERLAPS2")) as OL2,
3 count(nullif("OVERLAPS1","OVERLAPS3")) as OL3,
4 count(nullif("OVERLAPS1","OVERLAPS4")) as OL4,
5 count(nullif("OVERLAPS1","OVERLAPS5")) as OL5,
6 count(nullif("OVERLAPS1","OVERLAPS6")) as OL6,
7 count(nullif("OVERLAPS1","OVERLAPS7")) as OL7
8 from (select d1,d2,d3,d4,
9 case when (d1,d2) OVERLAPS (d3,d4) then 1 else 0 end as "OVERLAPS1",
10 case when d3 < d2 and d1 < d4 then 1 else 0 end as "OVERLAPS2",
11 case when d3 < d2 and d1 <= d4 then 1 else 0 end as "OVERLAPS3",
12 case when d3 <= d2 and d1 < d4 then 1 else 0 end as "OVERLAPS4",
13 case when d3 <= d2 and d1 <= d4 then 1 else 0 end as "OVERLAPS5",
14 case when d3 < d2 and d1 < d4
15 or d1=all(d3,d4) then 1 else 0 end as "OVERLAPS6",
16 case when d3 < d2 and d1 < d4
17 or d1=all(d3,d4)
18 or d3=all(d1,d2) then 1 else 0 end as "OVERLAPS7"
19 from (select Least(d1,d2) as d1,
20 greatest(d1,d2) as d2,
21 Least(d3,d4) as d3,
22 greatest(d3,d4) as d4
23 from(select
24 trunc(SYSDATE+mod(dbms_random.random(),50)) as D1,
25 trunc(SYSDATE+mod(dbms_random.random(),50)) as D2,
26 trunc(SYSDATE+mod(dbms_random.random(),50)) as D3,
27 trunc(SYSDATE+mod(dbms_random.random(),50)) as D4
28 from all_catalog,all_catalog where RowNum <=200000)));
COUNT(*) OL2 OL3 OL4 OL5 OL6 OL7
--------- --------- --------- --------- --------- --------- ---------
200000 23 1354 1414 2745 0 22
SQL> /
COUNT(*) OL2 OL3 OL4 OL5 OL6 OL7
--------- --------- --------- --------- --------- --------- ---------
200000 18 1432 1406 2820 0 23
SQL> /
COUNT(*) OL2 OL3 OL4 OL5 OL6 OL7
--------- --------- --------- --------- --------- --------- ---------
200000 31 1358 1429 2756 0 24
(d1,d2) OVERLAPS (d3,d4)
は、
d3 < d2 and d1 < d4 or d1=all(d3,d4)
と同じ意味のようです。
OverLaps述語は、Oracleのマニュアルにのってないので、
注意して使う必要があります。
11gからは、WM_OVERLAPSがマニュアルにのってるようです。
using analytical function to calculate concurrency between date range
WM_OVERLAPS
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
なお、OverLaps述語は、
d1をコアタイム開始時間
d2をコアタイム終了時間
d3を出社時間
d4を退社時間
とおいて
d3 <= d2 and d1 <= d4
は、
今日のコアタイム終了前に、出社して (d3 <= d2)
今日のコアタイム開始後に、退社した (d1 <= d4)
ならば、
今日のコアタイムに存在したという発想で、
ほぼ代用できます。
なお、
出勤開始 <= フレックス終了 and 出勤終了 >= フレックス開始
と
greatest(出勤開始,フレックス開始) <= Least(出勤終了,フレックス終了)
は、同じ意味となります。
select count(*),
count(nullif("OVERLAPS0","OVERLAPS1")) as test
from (select d1,d2,d3,d4,
case when greatest(d1,d3) <= Least(d2,d4) then 1 else 0 end as "OVERLAPS0",
case when d3 <= d2 and d1 <= d4 then 1 else 0 end as "OVERLAPS1"
from (select Least(d1,d2) as d1,greatest(d1,d2) as d2,
Least(d3,d4) as d3,greatest(d3,d4) as d4
from(select
trunc(SYSDATE+mod(dbms_random.random(),100)) as D1,
trunc(SYSDATE+mod(dbms_random.random(),100)) as D2,
trunc(SYSDATE+mod(dbms_random.random(),100)) as D3,
trunc(SYSDATE+mod(dbms_random.random(),100)) as D4
from all_catalog,all_catalog where RowNum <=90000)));
COUNT(*) TEST
-------- ----
90000 0
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
出勤開始 <= フレックス終了 and 出勤終了 >= フレックス開始
と
greatest(出勤開始,フレックス開始) <= Least(出勤終了,フレックス終了)
は、同じ意味の証明
--------------------------------------------------------------------------
出勤開始を KS
出勤終了を KE
フレックス開始 FS
フレックス終了 FE
とおくと
greatest(出勤開始,フレックス開始) <= Least(出勤終了,フレックス終了)
は以下に変形できる。
KS <= FS and KE <= FE and FS <= KE
or (KS <= FS and KE > FE and FS <= FE)
or (KS > FS and KE <= FE and KS <= KE)
or (KS > FS and KE > FE and KS <= FE)
--------------------------------------------------------------------------
FS <= FE と KS <= KE は明らかなので
KS <= FS and KE <= FE and FS <= KE
or (KS <= FS and KE > FE)
or (KS > FS and KE <= FE)
or (KS > FS and KE > FE and KS <= FE)
--------------------------------------------------------------------------
ブール代数の分配法則より
KS <= FS and (KE <= FE and FS <= KE or KE > FE)
or KS > FS and (KE > FE and KS <= FE or KE <= FE)
--------------------------------------------------------------------------
ブール代数の公式
_
A+A*B = A+B
KS <= FS and (FS <= KE or KE > FE)
or KS > FS and (KS <= FE or KE <= FE)
--------------------------------------------------------------------------
KE > FE ⇒ FS <= KE
KE <= FE ⇒ KS <= FE
なので
命題が成立した状態での、論理和より
KS <= FS and FS <= KE
or KS > FS and KS <= FE
--------------------------------------------------------------------------
命題が成立した状態での、論理積より
KS <= FS and FS <= KE
or KS > FS and KS <= FE and FS <= KE
--------------------------------------------------------------------------
ブール代数の分配法則より
FS <= KE and (KS <= FS or KS > FS and KS <= FE)
--------------------------------------------------------------------------
ブール代数の公式
_
A+A*B = A+B
FS <= KE and (KS <= FS and KS <= FE)
--------------------------------------------------------------------------
KS <= FS ⇒ KS <= FE
なので
FS <= KE and KS <= FE
--------------------------------------------------------------------------
よって
出勤開始 <= フレックス終了 and 出勤終了 >= フレックス開始
と
greatest(出勤開始,フレックス開始) <= Least(出勤終了,フレックス終了)
は、同じ意味
--------------------------------------------------------------------------
10-142 命題成立時のブール代数の同値変形
でよく似た同値変形をしてますね。
select case when lnnvl(greatest(sysdate,sysdate) <= Least(sysdate,sysdate))
then 1 else 0 end aa
from dual;
lnnvl述語と組み合わせて使う用途があるかもしれませんね。
Oracle11gの新機能
WM_OVERLAPSの実験結果
select count(*),
count(nullif("OVERLAPS1","OVERLAPS2")) as OL2,
count(nullif("OVERLAPS1","OVERLAPS3")) as OL3,
count(nullif("OVERLAPS1","OVERLAPS4")) as OL4,
count(nullif("OVERLAPS1","OVERLAPS5")) as OL5,
count(nullif("OVERLAPS1","OVERLAPS6")) as OL6
from (select d1,d2,d3,d4,
--case when (d1,d2) OVERLAPS (d3,d4) then 1 else 0 end as "OVERLAPS1",
WM_OVERLAPS(WM_PERIOD(d1,d2),WM_PERIOD(d3,d4)) as "OVERLAPS1",
case when d3 < d2 and d1 < d4 then 1 else 0 end as "OVERLAPS2",
case when d3 < d2 and d1 <= d4 then 1 else 0 end as "OVERLAPS3",
case when d3 <= d2 and d1 < d4 then 1 else 0 end as "OVERLAPS4",
case when d3 <= d2 and d1 <= d4 then 1 else 0 end as "OVERLAPS5",
case when d3 < d2 and d1 < d4
or d1=all(d3,d4) then 1 else 0 end as "OVERLAPS6"
from (select Least(d1,d2) as d1,
greatest(d1,d2) as d2,
Least(d3,d4) as d3,
greatest(d3,d4) as d4
from(select
trunc(SYSDATE+mod(dbms_random.random(),100)) as D1,
trunc(SYSDATE+mod(dbms_random.random(),100)) as D2,
trunc(SYSDATE+mod(dbms_random.random(),100)) as D3,
trunc(SYSDATE+mod(dbms_random.random(),100)) as D4
from (select 1 from all_catalog union all
select 1 from all_catalog union all
select 1 from all_catalog union all
select 1 from all_catalog))));
COUNT(*) OL2 OL3 OL4 OL5 OL6
--------- --- --- --- --- ---
114484 0 336 379 715 3
WM_OVERLAPS関数とOVERLAPS述語の仕様は微妙に違うようですねぇ
9-6 行事の間に宿泊した客を取得その1
9-7 行事の間に宿泊した客を取得その2