7-74 売上と最新の名称を取得

SQLパズル

amountTable
Code  yyyymmdd  amount  Name
----  --------  ------  ------
AAAA  20070101     100  AName1
AAAA  20070105     200  AName1
AAAA  20070106     400  AName1
AAAA  20070206     500  AName1
AAAA  20070208     600  AName1
AAAA  20070209     700  AName1
AAAA  20070306     800  AName9
AAAA  20070307     900  AName3
BBBB  20070101     111  BName6
BBBB  20070105     222  BName6
BBBB  20070106     333  BName6
BBBB  20070307     444  BName6

Codeごとに、年月ごとのamountの総額と、
Codeごとの、yyyymmddが最大の行のName
を以下の形式で出力する。

出力結果
Code  yyyymm  amount  Name
----  ------  ------  ------
AAAA  200701     700  AName3
AAAA  200702    1800  AName3
AAAA  200703    1700  AName3
BBBB  200701     666  BName6
BBBB  200703     444  BName6

データ作成スクリプト

create table amountTable as
select 'AAAA' as Code,20070101 as yyyymmdd,100 as amount,'AName1' as Name from dual
union select 'AAAA',20070105,200,'AName1' from dual
union select 'AAAA',20070106,400,'AName1' from dual
union select 'AAAA',20070206,500,'AName1' from dual
union select 'AAAA',20070208,600,'AName1' from dual
union select 'AAAA',20070209,700,'AName1' from dual
union select 'AAAA',20070306,800,'AName9' from dual
union select 'AAAA',20070307,900,'AName3' from dual
union select 'BBBB',20070101,111,'BName6' from dual
union select 'BBBB',20070105,222,'BName6' from dual
union select 'BBBB',20070106,333,'BName6' from dual
union select 'BBBB',20070307,444,'BName6' from dual;

SQL

--■■■相関サブクエリを使う方法■■■
select Code,trunc(yyyymmdd/100) as yyyymm,
sum(amount) as amount,
(select b.Name from amountTable b
  where b.Code = a.Code
    and b.yyyymmdd = (select max(c.yyyymmdd)
                        from amountTable c
                       where c.Code = b.Code)) as Name
  from amountTable a
group by Code,trunc(yyyymmdd/100)
order by Code,trunc(yyyymmdd/100);

--■■■分析関数を使う方法1■■■
select Code,trunc(yyyymmdd/100) as yyyymm,
sum(amount) as amount,Name
from (select Code,yyyymmdd,amount,
      Last_Value(Name)
      over(partition by Code order by yyyymmdd
           Rows between Unbounded Preceding and Unbounded Following) as Name
        from amountTable)
group by Code,trunc(yyyymmdd/100),Name
order by Code,trunc(yyyymmdd/100);

--■■■分析関数を使う方法2■■■
select Code,trunc(yyyymmdd/100) as yyyymm,
sum(amount) as amount,
Last_Value(max(Name) Keep (Dense_Rank Last order by yyyymmdd))
over(partition by Code order by trunc(yyyymmdd/100)
Rows between Unbounded Preceding and Unbounded Following) as Name
  from amountTable
group by Code,trunc(yyyymmdd/100)
order by Code,trunc(yyyymmdd/100);

解説

相関サブクエリを使う方法では、
相関サブクエリで、グループごとの固定値(Name)を求めてます。

---