10-286 最頻値が複数あれば、最小値を持つ方を取得

SQLパズル

namesテーブル
day1        seq  name  kana
----------  ---  ----  ----------
2009-02-01   10  浅井  あざい
2009-02-01   20  浅井  あさい
2009-02-09    1  浅井  あさい
2009-02-19    2  浅井  あざい
2009-03-01    1  金城  きんじょう
2009-03-01    2  王    おう
2009-03-02    1  金城  かねしろ
2009-03-02    2  王    わん
2009-03-03    1  金城  きんじょう
2009-03-04    1  金城  null
2009-03-04    2  王    null
2009-03-04    3  鈴木  null
2009-03-04    4  浅井  null
2009-12-31    1  豊臣  null

2009-03-04のデータが存在するnameを対象として、
nameごとのkanaの最頻値を取得する。
最頻値が複数あれば、
order by day1,seqでソートした時に、最初になるほうを取得する。

出力結果
name  kana
----  ----------
王    おう
金城  きんじょう
浅井  あざい
鈴木  null

データ作成スクリプト

create table names(
day1 date,
seq  number(2),
name varchar2(4),
kana varchar2(10),
primary key(day1,seq));

insert into names values(date '2009-02-01',10,'浅井','あざい');
insert into names values(date '2009-02-01',20,'浅井','あさい');
insert into names values(date '2009-02-09', 1,'浅井','あさい');
insert into names values(date '2009-02-19', 2,'浅井','あざい');
insert into names values(date '2009-03-01', 1,'金城','きんじょう');
insert into names values(date '2009-03-01', 2,'王'  ,'おう');
insert into names values(date '2009-03-02', 1,'金城','かねしろ');
insert into names values(date '2009-03-02', 2,'王'  ,'わん');
insert into names values(date '2009-03-03', 1,'金城','きんじょう');
insert into names values(date '2009-03-04', 1,'金城',null);
insert into names values(date '2009-03-04', 2,'王'  ,null);
insert into names values(date '2009-03-04', 3,'鈴木',null);
insert into names values(date '2009-03-04', 4,'浅井',null);
insert into names values(date '2009-12-31', 1,'豊臣',null);
commit;

SQL

--■■■グループ化する方法■■■
select name,kana
from (select name,kana,
      Row_Number() over(partition by name
      order by count(*) desc,min(day1),
               min(seq) Keep(Dense_Rank First order by day1)) as rn,
      max(max(case when day1 = date '2009-03-04' then 1 end))
      over(partition by name) as IsExistDay
        from names
      group by name,kana)
 where rn=1
   and IsExistDay=1
order by name;

--■■■グループ化しない方法■■■
select name,kana
from (select name,kana,
      Row_Number() over(partition by name order by cnt desc,day1,seq) as rn
      from (select name,kana,day1,seq,
            count(*) over(partition by name,kana) as cnt,
            max(case when day1 = date '2009-03-04' then 1 end)
            over(partition by name) as IsExistDay
              from names)
      where IsExistDay = 1)
 where rn = 1
order by name;

解説

名簿管理でありそうですねぇ
存在肯定命題まで使うと、脳内でイメージするのが難しくなりますねぇ・・・

group byで脳内で赤線をイメージして、
partition byで更に脳内で超極太赤線をイメージする必要があります。

---