第106回 Oracleのお勉強 GROUP BYを使って取得するものは一度に取得できないか考える
次の表で考えてみよう。
SQL> select * from ( 2 select sysdate 日付, 1 管理番号 from dual union 3 select sysdate 日付, 2 管理番号 from dual union 4 select sysdate 日付, 3 管理番号 from dual union 5 select sysdate+1 日付, 1 管理番号 from dual union 6 select sysdate+1 日付, 2 管理番号 from dual union 7 select sysdate+1 日付, 3 管理番号 from dual 8 ); 日付 管理番号 -------- ---------- 09-05-30 1 09-05-30 2 09-05-30 3 09-05-31 1 09-05-31 2 09-05-31 3 6行が選択されました。
日付ごとに最小管理番号、最大管理番号を求める
まず、効率の悪い例
最小を求める
SQL> select 2 to_char(t1.日付, 'YYYY/MM/DD') 日付, 3 min(t1.管理番号) 最小管理番号 4 from ( 5 select sysdate 日付, 1 管理番号 from dual union 6 select sysdate 日付, 2 管理番号 from dual union 7 select sysdate 日付, 3 管理番号 from dual union 8 select sysdate+1 日付, 1 管理番号 from dual union 9 select sysdate+1 日付, 2 管理番号 from dual union 10 select sysdate+1 日付, 3 管理番号 from dual 11 ) t1 12 group by 13 to_char(t1.日付, 'YYYY/MM/DD') 14 order by 15 to_char(t1.日付, 'YYYY/MM/DD') 16 ; 日付 最小管理番号 -------------------- ------------ 2009/05/30 1 2009/05/31 1
最大を求める
SQL> select 2 to_char(t2.日付, 'YYYY/MM/DD') 日付, 3 max(t2.管理番号) 最大管理番号 4 from ( 5 select sysdate 日付, 1 管理番号 from dual union 6 select sysdate 日付, 2 管理番号 from dual union 7 select sysdate 日付, 3 管理番号 from dual union 8 select sysdate+1 日付, 1 管理番号 from dual union 9 select sysdate+1 日付, 2 管理番号 from dual union 10 select sysdate+1 日付, 3 管理番号 from dual 11 ) t2 12 group by 13 to_char(t2.日付, 'YYYY/MM/DD') 14 order by 15 to_char(t2.日付, 'YYYY/MM/DD') 16 ; 日付 最大管理番号 -------------------- ------------ 2009/05/30 3 2009/05/31 3
最小と最大を日付ごとに結合
SQL> select 2 t1_min.日付 日付, 3 t1_min.最小管理番号 最小管理番号, 4 t2_max.最大管理番号 最大管理番号 5 from 6 ( 7 select 8 to_char(t1.日付, 'YYYY/MM/DD') 日付, 9 min(t1.管理番号) 最小管理番号 10 from ( 11 select sysdate 日付, 1 管理番号 from dual union 12 select sysdate 日付, 2 管理番号 from dual union 13 select sysdate 日付, 3 管理番号 from dual union 14 select sysdate+1 日付, 1 管理番号 from dual union 15 select sysdate+1 日付, 2 管理番号 from dual union 16 select sysdate+1 日付, 3 管理番号 from dual 17 ) t1 18 group by 19 to_char(t1.日付, 'YYYY/MM/DD') 20 ) t1_min, 21 ( 22 select 23 to_char(t2.日付, 'YYYY/MM/DD') 日付, 24 max(t2.管理番号) 最大管理番号 25 from ( 26 select sysdate 日付, 1 管理番号 from dual union 27 select sysdate 日付, 2 管理番号 from dual union 28 select sysdate 日付, 3 管理番号 from dual union 29 select sysdate+1 日付, 1 管理番号 from dual union 30 select sysdate+1 日付, 2 管理番号 from dual union 31 select sysdate+1 日付, 3 管理番号 from dual 32 ) t2 33 group by 34 to_char(t2.日付, 'YYYY/MM/DD') 35 ) t2_max 36 where 37 t1_min.日付 = t2_max.日付 38 order by 39 t1_min.日付 40 ; 日付 最小管理番号 最大管理番号 -------------------- ------------ ------------ 2009/05/30 1 3 2009/05/31 1 3
しかし、これはやはりかなり非効率だ。
効率的なやり方 （1度のGROUP BYで最小と最大はとれる）
SQL> select 2 to_char(t1.日付, 'YYYY/MM/DD') 日付, 3 min(t1.管理番号) 最小管理番号, 4 max(t1.管理番号) 最大管理番号 5 from ( 6 select sysdate 日付, 1 管理番号 from dual union 7 select sysdate 日付, 2 管理番号 from dual union 8 select sysdate 日付, 3 管理番号 from dual union 9 select sysdate+1 日付, 1 管理番号 from dual union 10 select sysdate+1 日付, 2 管理番号 from dual union 11 select sysdate+1 日付, 3 管理番号 from dual 12 ) t1 13 group by 14 to_char(t1.日付, 'YYYY/MM/DD') 15 order by 16 to_char(t1.日付, 'YYYY/MM/DD') 17 ; 日付 最小管理番号 最大管理番号 -------------------- ------------ ------------ 2009/05/30 1 3 2009/05/31 1 3
これが効率のよいやり方（というか普通はこうする）。
