SQLパズル 45 ペパロニピザ　その２の解法メモ

Last updated at 2024-11-11Posted at 2024-11-10

概要

SQLパズル　p189 45 ペパロニピザ　その２の解法メモです。
CASE式と集約関数(SUM)の使用方法が肝ですね。

要件　

行→列に変換したい。
範囲指定した値毎に列に配置したい。
a,b,c(a<b<c)の数字に対して
0<値<aの場合　１列目
a<値<bの場合　２列目
b<値<cの場合　３列目
値>cの場合　　４列目

今回の場合は以下とする。
a=30 b=60 c=90
値=現在の日付と過去の日付の差
現在の日付は20070601とする

図

SQL

DDL


DROP TABLE IF EXISTS FriendsofPepperoni;
--重複行があるというので、主キーなし
CREATE TABLE FriendsofPepperoni
(cust_id INTEGER,
 bill_date DATE,
 pizza_amt DECIMAL(5,2));

--答え３用マスタテーブル
DROP TABLE IF EXISTS ReportRanges;
CREATE TABLE ReportRanges
(day_count CHAR(32), 
 start_cnt INTEGER, 
 end_cnt   INTEGER);

INSERT INTO FriendsofPepperoni VALUES(1, '2007-05-07', 10);
INSERT INTO FriendsofPepperoni VALUES(1, '2007-04-01', 20);
INSERT INTO FriendsofPepperoni VALUES(1, '2007-03-01', 30);
INSERT INTO FriendsofPepperoni VALUES(1, '2007-01-01', 40);
INSERT INTO FriendsofPepperoni VALUES(2, '2007-05-06', 50);
INSERT INTO FriendsofPepperoni VALUES(2, '2007-04-01', 60);
INSERT INTO FriendsofPepperoni VALUES(2, '2007-03-01', 70);
INSERT INTO FriendsofPepperoni VALUES(2, '2007-01-01', 80);

--答え３用
INSERT INTO ReportRanges VALUES('under Thirty days', 00, 30);
INSERT INTO ReportRanges VALUES('Sixty days', 31, 60);
INSERT INTO ReportRanges VALUES('Ninty days', 61, 90);

答えのSQL

-- その２答え
-- case式を使用
SELECT cust_id,
        SUM(CASE WHEN bill_date
                       BETWEEN cast( '20070601' as date )  - INTERVAL '30' DAY
                            AND cast( '20070601' as date )
                       THEN pizza_amt ELSE 0.00 END) AS age1,
        SUM(CASE WHEN bill_date
                      BETWEEN cast( '20070601' as date ) - INTERVAL '60' DAY
                            AND cast( '20070601' as date ) - INTERVAL '31' DAY
                        THEN pizza_amt ELSE 0.00 END) AS age2,
        SUM(CASE WHEN bill_date
                      BETWEEN cast( '20070601' as date )- INTERVAL '90' DAY
                            AND cast( '20070601' as date ) - INTERVAL '61' DAY
                      THEN pizza_amt ELSE 0.00 END) AS age3,
        SUM(CASE WHEN bill_date < cast( '20070601' as date )  - INTERVAL '91' DAY
                      THEN pizza_amt ELSE 0.00 END) AS age4
        FROM FriendsOfPepperoni
        GROUP BY cust_id
        ORDER BY cust_id;

-- 各テーブルがどのageに分類されるか確認する
SELECT *,(cast( '20070601' as date ) - bill_date) as diff
	,CASE WHEN cast( '20070601' as date ) - bill_date BETWEEN '0'  AND '30' THEN 'age1'
		  WHEN cast( '20070601' as date ) - bill_date BETWEEN '31'  AND '60' THEN 'age2'
	 	  WHEN cast( '20070601' as date ) - bill_date BETWEEN '61'  AND  '90' THEN 'age3'
		  WHEN cast( '20070601' as date ) - bill_date > '91' THEN 'age4'
		  ELSE '' END
FROM FriendsOfPepperoni;