8-42 その行までの最大の重複数でグループ化

SQLパズル

LocaleTable
Seq  RowKey  Locale
---  ------  ------
  1       5  US    ← この行までの、最大の(RowKey,Localeの重複数)は1
  2       5  JA    ← この行までの、最大の(RowKey,Localeの重複数)は1
  3       4  US    ← この行までの、最大の(RowKey,Localeの重複数)は1
  4       3  US    ← この行までの、最大の(RowKey,Localeの重複数)は1
  5       2  US    ← この行までの、最大の(RowKey,Localeの重複数)は1
  6       1  US    ← この行までの、最大の(RowKey,Localeの重複数)は1
  7       1  JA    ← この行までの、最大の(RowKey,Localeの重複数)は1
  8       2  US    ← この行までの、最大の(RowKey,Localeの重複数)は2
  9       3  US    ← この行までの、最大の(RowKey,Localeの重複数)は2
 10       5  US    ← この行までの、最大の(RowKey,Localeの重複数)は2
 11       4  US    ← この行までの、最大の(RowKey,Localeの重複数)は2
 12       3  US    ← この行までの、最大の(RowKey,Localeの重複数)は3
 13       2  US    ← この行までの、最大の(RowKey,Localeの重複数)は3
 14       1  US    ← この行までの、最大の(RowKey,Localeの重複数)は3
 15       3  JA    ← この行までの、最大の(RowKey,Localeの重複数)は3

Seqの昇順で、その行までの、最大の(RowKey,Localeの重複数)でグループ化し、
各グループの最小のSeqと最大のSeqと行数を出力する。

出力結果
minSeq  maxSeq  cnt
------  ------  ---
     1       7    7
     8      11    4
    12      15    4

データ作成スクリプト

create table LocaleTable(Seq primary key,RowKey,Locale) as
select  1,5,'US' from dual union
select  2,5,'JA' from dual union
select  3,4,'US' from dual union
select  4,3,'US' from dual union
select  5,2,'US' from dual union
select  6,1,'US' from dual union
select  7,1,'JA' from dual union
select  8,2,'US' from dual union
select  9,3,'US' from dual union
select 10,5,'US' from dual union
select 11,4,'US' from dual union
select 12,3,'US' from dual union
select 13,2,'US' from dual union
select 14,1,'US' from dual union
select 15,3,'JA' from dual;

SQL

--■■■分析関数を使う方法■■■
select min(Seq) as minSeq,max(Seq) as maxSeq,count(*) as cnt
from (select Seq,max(sameCnt) over(order by Seq) as maxSameCnt
      from (select Seq,
            count(*) over(partition by RowKey,Locale order by Seq) as sameCnt
            from LocaleTable))
group by maxSameCnt
order by maxSameCnt;

--■■■分析関数を使わない方法■■■
select min(Seq) as minSeq,max(Seq) as maxSeq,count(*) as cnt
from (select Seq,RowKey,Locale,
      (select max(count(*))
         from LocaleTable b,LocaleTable c
        where a.Seq >= b.Seq
          and b.Seq >= c.Seq
        group by b.Seq,c.RowKey,c.Locale) as maxSameCnt
        from LocaleTable a)
group by maxSameCnt
order by maxSameCnt;

解説

分析関数を使うのが現実的でしょうね。

Process Range IDs --- I solved with using OLAP

---