Puzzle45 - SQLパズル演習

問題

請求データを記録しているテーブルFriendsOfPepperoni
(会員識別番号cust_id、発行日bill_date、金額pizza_amt)について
0～30日前、31～60日前、61～90日前、90日以上前の
4つの期間における各会員の合計請求額を求める

サンプルデータ

 cust_id | bill_date  | pizza_amt
---------+------------+-----------
       1 | 2008-05-07 |     10.00
       1 | 2008-04-01 |     20.00
       1 | 2008-03-01 |     30.00
       1 | 2008-01-01 |     40.00
       2 | 2008-05-06 |     10.00
       2 | 2008-04-01 |     20.00
       2 | 2008-03-01 |     30.00
       2 | 2008-01-01 |     40.00

結果

 cust_id | age1  | age2  | age3  | age4
---------+-------+-------+-------+-------
       1 | 10.00 | 20.00 | 30.00 | 40.00
       2 | 10.00 | 20.00 | 30.00 | 40.00

yukaさんの回答

考え①
1、ある日にちからピザを購入した日にちまでの期間を
　 求めたビューを作成。
2、ビューを用いて、それぞれの期間ごとのpizza_amtの合計
　 を求める。

ビュー

create view Date(cust_id,pizza_amt,ago)
as select cust_id,pizza_amt,datediff('2007-06-22', bill_date) as ago
from FriendsofPepperoni;

SQL文

select cust_id,
sum(case when ago between 0 and 30 then pizza_amt else 0.00 end) as 1ago,
sum(case when ago between 31 and 60 then pizza_amt else 0.00 end) as 2ago,
sum(case when ago between 61 and 90 then pizza_amt else 0.00 end) as 3ago,
sum(case when ago > 90 then pizza_amt else 0.00 end) as 4ago
from Date
group by cust_id;

考え②
①のようなビューは作らず、case式で、bill_dateが
何日前に分類されるかを判別し、それぞれのpizza_amtの合計を求める。

SQL文

select cust_id,
sum(case when bill_date BETWEEN '2007-06-22' -interval 30 day AND '2007-06-22' then  pizza_amt else 0.00 end) as 1ago,
sum(case when bill_date BETWEEN '2007-06-22' -interval 60 day AND '2007-06-22' -interval 31 day then pizza_amt else 0.00 end) as 2ago,
sum(case when bill_date BETWEEN '2007-06-22' -interval 90 day AND '2007-06-22' -interval 61 day then pizza_amt else 0.00 end) as 3ago,
sum(case when bill_date < '2007-06-22' -interval 91 day then pizza_amt else 0.00 end) as 4ago
from FriendsofPepperoni
group by cust_id;

結果（①も②も結果は同じ）

+---------+------+-------+-------+-------+
| cust_id | 1ago | 2ago  | 3ago  | 4ago  |
+---------+------+-------+-------+-------+
|      1  | 0.00 | 10.00 | 20.00 | 70.00 |
|      2  | 0.00 | 10.00 | 20.00 | 70.00 |
+---------+------+-------+-------+-------+

fumikoの回答

方針
①それぞれの期間について集計する
②①をcust_idで結合する

SQL文

select f1.cust_id,pa1,pa2,pa3,pa4
from 
(select cust_id,sum(pizza_amt)as pa1
from friendsofpepperoni
group by cust_id,bill_date
having 0 <=(current_date - bill_date) and (current_date - bill_date) <= 30) as f1,

(select cust_id,sum(pizza_amt)as pa2
from friendsofpepperoni
group by cust_id,bill_date
having 31 <=(current_date - bill_date) and (current_date - bill_date) <= 60) as f2, 

(select cust_id,sum(pizza_amt)as pa3
from friendsofpepperoni
group by cust_id,bill_date
having 61 <=(current_date - bill_date) and (current_date - bill_date) <= 90) as f3, 

(select cust_id,sum(pizza_amt)as pa4
from friendsofpepperoni
group by cust_id,bill_date
having 91 <=(current_date - bill_date) ) as f4
where f1.cust_id = f2.cust_id and
      f2.cust_id = f3.cust_id and
      f3.cust_id = f4.cust_id   

結果

cust_id |  pa1  |  pa2  |  pa3  |  pa4
--------+-------+-------+-------+-------
      1 | 10.00 | 20.00 | 30.00 | 40.00
      2 | 10.00 | 20.00 | 30.00 | 40.00