問題
請求データを記録しているテーブル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
最終更新:2008年05月22日 00:32