2-3-19 月ごとの最終日の売上を集計

SQLパズル

売上履歴テーブル
年月        売上        商品
----------  ----  ----------
2005/10/25  1000  Oracleの本
2005/10/25  1500  数学の本
2005/11/20  1200  パズルの本
2005/11/20  1400  SQLの本
2005/11/28  2300  Javaの本
2005/12/25  1900  英語の本
2005/12/25  2100  数学の本
2005/12/25  3000  Oracleの本
2006/01/25  3500  SQLの本
2006/01/28  1000  パズルの本

月ごとの最終日の売上合計と、売上の数を出力する。

出力結果
年月        売上合計  売上の数
----------  --------  --------
2005/10/25      2500         2
2005/11/28      2300         1
2005/12/25      7000         3
2006/01/28      1000         1

データ作成スクリプト

create table 売上履歴 as
select to_date('20051025','yyyymmdd') as 年月,1000 as 売上,'Oracleの本' as 商品 from dual
union select to_date('20051025','yyyymmdd'),1500,'数学の本'   from dual
union select to_date('20051120','yyyymmdd'),1200,'パズルの本' from dual
union select to_date('20051120','yyyymmdd'),1400,'SQLの本'    from dual
union select to_date('20051128','yyyymmdd'),2300,'Javaの本'   from dual
union select to_date('20051225','yyyymmdd'),1900,'英語の本'   from dual
union select to_date('20051225','yyyymmdd'),2100,'数学の本'   from dual
union select to_date('20051225','yyyymmdd'),3000,'Oracleの本' from dual
union select to_date('20060125','yyyymmdd'),3500,'SQLの本'    from dual
union select to_date('20060128','yyyymmdd'),1000,'パズルの本' from dual;

SQL

--■■■to_char関数でグループ化する方法■■■
select to_char(max(年月),'yyyy/mm/dd') as 年月,
sum(売上) keep(dense_rank Last order by to_char(年月,'dd')) as 売上合計,
count(*)  keep(dense_rank Last order by to_char(年月,'dd')) as 売上の数
from 売上履歴
group by to_char(年月,'yyyymm')
order by 年月;

--■■■trunc関数でグループ化する方法■■■
select to_char(max(年月),'yyyy/mm/dd') as 年月,
sum(売上) keep(dense_rank Last order by to_char(年月,'dd')) as 売上合計,
count(*)  keep(dense_rank Last order by to_char(年月,'dd')) as 売上の数
from 売上履歴
group by trunc(年月,'mm')
order by 年月;

解説

dense_rank Lastを指定して、
最終日の売上を集計してます。

to_char関数もしくは、trunc関数で
年月でグループ化してます。

ちなみに、keepは分析関数でも使うことができます。
マニュアル(英語)

select to_char(年月,'yyyy/mm/dd') as 年月,
売上,商品,
max(to_char(年月,'yyyy/mm/dd')) over(partition by trunc(年月,'mm')) as 最終日,
sum(売上) keep(dense_rank Last order by to_char(年月,'dd')) over(partition by trunc(年月,'mm')) as 売上合計,
count(*)  keep(dense_rank Last order by to_char(年月,'dd')) over(partition by trunc(年月,'mm')) as 売上の数
from 売上履歴 a
order by a.年月,売上;

年月        売上   商品        最終日      売上合計  売上の数
----------  ----  ----------  ----------  --------  --------
2005/10/25  1000  Oracleの本  2005/10/25      2500         2
2005/10/25  1500  数学の本    2005/10/25      2500         2
2005/11/20  1200  パズルの本  2005/11/28      2300         1
2005/11/20  1400  SQLの本     2005/11/28      2300         1
2005/11/28  2300  Javaの本    2005/11/28      2300         1
2005/12/25  1900  英語の本    2005/12/25      7000         3
2005/12/25  2100  数学の本    2005/12/25      7000         3
2005/12/25  3000  Oracleの本  2005/12/25      7000         3
2006/01/25  3500  SQLの本     2006/01/28      1000         1
2006/01/28  1000  パズルの本  2006/01/28      1000         1

---