基于case when的常用查询

基于case when的常用查询

1.导入数据集

CREATE TABLE orders(
Uid CHAR(7),
Birthday DATE,
Order_Date DATETIME,
Order_Id VARCHAR(15),
Pay_Type TINYINT,#1,3,5,7,10代表微信支付;6,9,10代表快捷支付;2,4,8,11代表支付宝支付
Pay_Amt DECIMAL(10,2),
Is_Discount TINYINT#1：享受折扣 2：无折扣
);
DESC orders;

LOAD DATA INFILE 'E:\Orders.csv'
INTO TABLE orders
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

SELECT * FROM orders;

案例应用：
一、离散数值映射为对应的实际含义

#离散数值与实际含义的映射
SELECT 
  *, 
  CASE
    WHEN Is_Discount = 1 
    THEN '享受折扣' 
    ELSE '无折扣' 
  END AS discount_new,
  CASE
    WHEN Pay_Type IN (1, 3, 5, 7, 10) 
    THEN '微信支付' 
    WHEN Pay_Type IN (6, 9, 12) 
    THEN '快捷支付' 
    ELSE '支付宝支付' 
  END AS Pay_Type_new 
FROM
  orders 
  LIMIT 5;

二、连续数值映射为离散区间
将用户按照出生日期划分为60后，70后，80后。

SELECT 
  *,
  CASE
    WHEN YEAR(`Birthday`) BETWEEN 1960 
    AND 1969 
    THEN '60后' 
    WHEN YEAR(`Birthday`) BETWEEN 1970 
    AND 1979 
    THEN '70后' 
    WHEN YEAR(`Birthday`) BETWEEN 1980 
    AND 1989 
    THEN '80后' 
    ELSE '90后'
    END AS age_group
 FROM orders;

三、构建长型统计表
统计2018年每个月各种支付方式的交易额

 
SELECT 
  MONTH(`Order_Date`) AS MONTH,
  CASE
    WHEN Pay_Type IN (1, 3, 5, 7, 10) 
    THEN '微信支付' 
    WHEN Pay_Type IN (6, 9, 12) 
    THEN '快捷支付' 
    ELSE '支付宝支付'
  END AS Pay_Type_new,
  SUM(`Pay_Amt`) AS Pay_Amt_tot
FROM orders 
WHERE YEAR(`Order_Date`) = 2018 
GROUP BY MONTH,Pay_Type_new;

四、构建宽型统计表

构建上述的宽型统计表

SELECT 
  MONTH(`Order_Date`) AS MONTH,
  SUM(
    CASE
      WHEN Pay_Type IN (1, 3, 5, 7, 10) 
      THEN `Pay_Amt` END
    ) AS 'wechat' 
    SUM(
    CASE
      WHEN Pay_Type IN (6, 9, 12) 
      THEN `Pay_Amt` END
    ) AS 'bank_card'     
    SUM(
    CASE
      WHEN Pay_Type NOT IN (1, 3, 5, 7, 10,6, 9, 12) 
      THEN `Pay_Amt` END
    ) AS 'Ali_pay'    
FROM orders
WHERE YEAR(`Order_Date`) = 2018
GROUP BY MONTH 
ORDER BY MONTH DESC;