10-309 model句のforループでのinterVal型指定

SQLパズル

dayTable
timeCol
-------------------
2009-06-18 07:44:13
2009-06-18 07:44:38
2009-06-18 07:44:51
2009-06-18 07:45:11
2009-06-18 07:45:22

7時40分から7時50分までのレコード数を、
1分区切りで、下記の形式で出力する。

出力結果
timeCol              cnt
-------------------  ---
2009/06/18 07:40:00    0
2009/06/18 07:41:00    0
2009/06/18 07:42:00    0
2009/06/18 07:43:00    0
2009/06/18 07:44:00    3
2009/06/18 07:45:00    2
2009/06/18 07:46:00    0
2009/06/18 07:47:00    0
2009/06/18 07:48:00    0
2009/06/18 07:49:00    0
2009/06/18 07:50:00    0

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table dayTable(timeCol) as
select to_date('2009-06-18 07:44:13','yyyy-mm-dd hh24:mi:ss') from dual union all
select to_date('2009-06-18 07:44:38','yyyy-mm-dd hh24:mi:ss') from dual union all
select to_date('2009-06-18 07:44:51','yyyy-mm-dd hh24:mi:ss') from dual union all
select to_date('2009-06-18 07:45:11','yyyy-mm-dd hh24:mi:ss') from dual union all
select to_date('2009-06-18 07:45:22','yyyy-mm-dd hh24:mi:ss') from dual;

SQL

--■■■count関数の対象でシンボリック参照を使用■■■
select to_char(timeCol, 'YYYY/MM/DD HH24:MI:SS') as timeCol,cnt
  from dayTable
 model return updated rows
dimension by(trunc(time_col,'mi') as timeCol,RowNum as rn)
measures(0 as cnt)
rules(
cnt[
for timeCol from to_date('2009-06-18 07:40:00','yyyy-mm-dd hh24:mi:ss')
              to to_date('2009-06-18 07:50:00','yyyy-mm-dd hh24:mi:ss')
increment interVal '1' minute,0] = count(*)[timeCol=CV(),any])
order by timeCol;

--■■■count関数の対象で位置参照を使用■■■
select to_char(timeCol, 'YYYY/MM/DD HH24:MI:SS') as timeCol,cnt
  from dayTable
 model return updated rows
dimension by(trunc(time_col,'mi') as timeCol,RowNum as rn)
measures(0 as cnt)
rules(
cnt[
for timeCol from to_date('2009-06-18 07:40:00','yyyy-mm-dd hh24:mi:ss')
              to to_date('2009-06-18 07:50:00','yyyy-mm-dd hh24:mi:ss')
increment interVal '1' minute,0] = count(*)[CV(),any])
order by timeCol;

解説

位置参照が使える時は、使ったほうが分かりやすそうですねぇ

---