「Puzzle45」の編集履歴（バックアップ）一覧はこちら

「Puzzle45」(2008/05/22 (木) 00:32:46) の最新版変更点

追加された行は緑色になります。

削除された行は赤色になります。

*問題
請求データを記録しているテーブル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さんの回答
*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

*問題
請求データを記録しているテーブル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