10-84 集計して前月分も表示

SQLパズル

売上明細テーブル
売上者  月度  金額
------  ----  ----
  佐藤  0512   100
  佐藤  0512   200
  佐藤  0601   400
  佐藤  0603   600
  山田  0601   200
  山田  0601   300
  山田  0602   100
  鈴木  0601   200

売上者、月度ごとの金額を集計し、
前月分も表示する

出力結果
売上者  月度   今月金額  前月金額
------  ----  --------  --------
  佐藤  0512       300         0
  佐藤  0601       400       300
  佐藤  0603       600         0
  山田  0601       500         0
  山田  0602       100       500
  鈴木  0601       200         0

データ作成スクリプト

create table 売上明細(
売上者 char(4),
月度   char(4),
金額   number(3));

insert into 売上明細 values('佐藤','0512',100);
insert into 売上明細 values('佐藤','0512',200);
insert into 売上明細 values('佐藤','0601',400);
insert into 売上明細 values('佐藤','0603',600);
insert into 売上明細 values('山田','0601',200);
insert into 売上明細 values('山田','0601',300);
insert into 売上明細 values('山田','0602',100);
insert into 売上明細 values('鈴木','0601',200);
commit;

SQL

--■■■分析関数を使う方法1■■■
select 売上者,月度,金額 as 今月金額,
case when Lag(月度) over(partition by 売上者 order by 月度)
        = to_char(add_months(to_date(月度,'yymm'),-1),'yymm')
     then Lag(金額) over(partition by 売上者 order by 月度) else 0 end as 前月金額
from (select 売上者,月度,sum(金額) as 金額
        from 売上明細
      group by 売上者,月度)
order by 売上者,月度;

--■■■分析関数を使う方法2■■■
select 売上者,月度,sum(金額) as 今月金額,
case when Lag(月度) over(partition by 売上者 order by 月度)
        = to_char(add_months(to_date(月度,'yymm'),-1),'yymm')
then Lag(sum(金額)) over(partition by 売上者 order by 月度) else 0 end as 前月金額
from 売上明細
group by 売上者,月度
order by 売上者,月度;

--■■■相関サブクエリを使う方法■■■
select distinct 売上者,月度,
(select sum(b.金額) from 売上明細 b
  where b.売上者 = a.売上者
    and b.月度 = a.月度) as 今月金額,
(select nvl(sum(b.金額),0) from 売上明細 b
  where b.売上者 = a.売上者
    and b.月度 = to_char(add_months(to_date(a.月度,'yymm'),-1),'yymm')) as 前月金額
from 売上明細 a
order by 売上者,月度;

--■■■12進数に変換する方法■■■
select 売上者,月度,sum(金額) as 今月金額,
nvl(max(sum(金額))
over(partition by 売上者
     order by to_number(substr(月度,1,2))*12
             +to_number(substr(月度,3,2))
        range between 1 preceding
                  and 1 preceding),0) as 前月金額
  from 売上明細
group by 売上者,月度
order by 売上者,月度;

解説

add_months関数で、前月の月度を取得してもいいし、
12進数に変換してもいいでしょう。

---