dayTable day1 ---------------- 2008-09-11 23:58 2008-09-12 03:58 2008-09-12 08:00 2008-09-12 09:00 2008-09-12 09:59 2008-09-12 10:00 2008-09-12 10:01 2008-09-12 10:02 2008-09-13 00:03 2008-09-13 09:00 2008-09-13 09:59 2008-09-13 10:00 2008-09-13 10:01 2008-09-13 10:02 2008-09-13 23:59 2008-09-26 00:03 2008-09-26 09:00 2008-09-26 09:59 2008-09-26 10:00 2008-09-26 10:01 2008-09-26 10:02 2008-09-26 23:59 2008-09-27 00:03 2008-09-27 09:00 2008-09-27 09:59 2008-09-27 10:00 2008-09-27 10:01 2008-09-27 10:02 2008-09-27 23:59 午前10時を基準としてグループ化して day1の最小と最大と、件数を出力する。 出力結果 minDay1 maxDay1 cnt ---------------- ---------------- --- 2008-09-11 23:58 2008-09-12 09:59 5 2008-09-12 10:00 2008-09-13 09:59 6 2008-09-13 10:00 2008-09-13 23:59 4 2008-09-26 00:03 2008-09-26 09:59 3 2008-09-26 10:00 2008-09-27 09:59 7 2008-09-27 10:00 2008-09-27 23:59 4
create table dayTable(day1) as
select to_date('2008-09-11 23:58','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-12 03:58','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-12 08:00','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-12 09:00','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-12 09:59','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-12 10:00','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-12 10:01','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-12 10:02','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-13 00:03','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-13 09:00','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-13 09:59','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-13 10:00','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-13 10:01','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-13 10:02','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-13 23:59','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-26 00:03','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-26 09:00','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-26 09:59','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-26 10:00','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-26 10:01','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-26 10:02','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-26 23:59','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-27 00:03','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-27 09:00','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-27 09:59','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-27 10:00','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-27 10:01','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-27 10:02','yyyy-mm-dd hh24:mi') from dual union
select to_date('2008-09-27 23:59','yyyy-mm-dd hh24:mi') from dual;
col minDay1 for a20
col maxDay1 for a20
select to_char(min(day1),'yyyy-mm-dd hh24:mi') as minDay1,
to_char(max(day1),'yyyy-mm-dd hh24:mi') as maxDay1,count(*) as cnt
from dayTable
group by trunc(day1 - interval '10' hour)
order by minDay1;
10時間引いてからtrunc関数で時間を切り捨てた値で、グループ化しています。
MySQLの算術四捨五入でよく使われるテクニックです。
元になっているのは、高校数学のグラフのX軸方向への平行移動の考え方です。
3-40 指定月を年度開始とした年度を求める
10-213 グラフの平行移動を意識したクエリ
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
1の位で、Oracle流四捨五入とMySQL流四捨五入と七捨八入を行うサンプル
select RowNum,
Round(RowNum ,-1) as "Oracle流四捨五入",
trunc(RowNum+5,-1) as "MySQL流四捨五入",
trunc(RowNum+2,-1) as 七捨八入
from dict
where RowNum <= 20;
RowNum Oracle流四捨五入 MySQL流四捨五入 七捨八入
------ ---------------- --------------- --------
1 0 0 0
2 0 0 0
3 0 0 0
4 0 0 0
5 10 10 0
6 10 10 0
7 10 10 0
8 10 10 10
9 10 10 10
10 10 10 10
11 10 10 10
12 10 10 10
13 10 10 10
14 10 10 10
15 20 20 10
16 20 20 10
17 20 20 10
18 20 20 20
19 20 20 20
20 20 20 20