10-151 特殊な数列を作成その2

SQLパズル

データディクショナリを使って、
以下の数列を作成する。

出力結果
ColA  ColB
----  ----
   1     1
   2     2
   3     3
   4     4
   5     5
   6     6
   7     7
   8     1
   9     2
  10     3
  11     4
  12     5
  13     6
  14     1
  15     2
  16     3
  17     4
  18     5
  19     1
  20     2
  21     3
  22     4
  23     1
  24     2
  25     3
  26     1
  27     2
  28     1

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

SQL

--■■■データディクショナリを使う方法■■■
select a.ColA,row_number() over(partition by max(b.ColB) order by a.ColA) as ColB
  from (select RowNum as ColA from all_catalog a where RowNum <= 7*(7+1)/2) a,
       (select RowNum as ColB,sum(RowNum) over (order by RowNum desc) as summary
          from all_catalog
         where RowNum <= 7) b
 where a.ColA <= b.summary
group by a.ColA
order by a.ColA;

--■■■model句を使う方法(10g以降)■■■
select ColA,ColB-Counter+1 as ColB
  from dual
 model
 dimension by (1 as ColA)
 measures(0 as ColB,7 as Counter)
 rules ITERATE (28)(
 Counter[iteration_number+1] = case when iteration_number = 0 then Counter[cv()]
                                    when Counter[cv()-1]  = 1 then ColB[cv()-1]-1
                                    else Counter[cv()-1]-1 end,
 ColB[iteration_number+1] = case when iteration_number = 0 then Counter[cv()]
                                 when Counter[cv()-1]  = 1 then ColB[cv()-1]-1
                                 else ColB[cv()-1] end);

解説

前のSQLパズルでのクエリで、
分析関数を使うようにしてます。

---