基于SQL的分析挖掘案例

基于SQL的分析挖掘案例

背景: 一个餐厅,经营一段时间的销售数据, 根据销售数据分析客户喜好, 以提升餐厅的营业水平(素材来自网络) ;

1- 数据准备

-- t_sales: 销售订单表(customer_id: 客户id, order_date: 订单日期, product_id: 商品id)
WITH t_sales AS (
    SELECT * FROM (
        VALUES('A', '2023-11-01', '1'), ('C', '2023-11-07', '1') 
            , ('A', '2023-11-01', '2'), ('C', '2023-11-11', '2') 
            , ('A', '2023-11-07', '2'), ('C', '2023-11-15', '2') 
            , ('A', '2023-11-10', '3'), ('C', '2023-11-19', '3') 
            , ('A', '2023-11-11', '3'), ('D', '2023-11-02', '3') 
            , ('A', '2023-11-11', '3'), ('C', '2023-12-02', '3') 
            , ('B', '2023-11-01', '2'), ('D', '2023-11-05', '2') 
            , ('B', '2023-11-02', '2'), ('D', '2023-11-08', '2') 
            , ('B', '2023-11-04', '1'), ('D', '2023-11-09', '1') 
            , ('B', '2023-11-11', '1'), ('D', '2023-11-11', '1') 
            , ('B', '2023-11-16', '3'), ('E', '2023-11-01', '3') 
            , ('B', '2023-12-01', '3'), ('D', '2023-12-18', '3') 
            , ('C', '2023-11-01', '3'), ('E', '2023-11-03', '3') 
            , ('C', '2023-11-01', '3'), ('E', '2023-11-08', '3') 
            , ('C', '2023-11-07', '3'), ('E', '2023-11-12', '3') 
    ) AS tbl_name(customer_id, order_date, product_id)
)

-- t_product: 商品表(product_id: 商品id, product_name: 商品名字, price: 单价)
, t_product AS (
    SELECT * FROM (
        VALUES('1', 'pasta', '20')
            , ('2', 'salad', '25')
            , ('3', 'sauce', '22')
    ) AS tbl_name(product_id, product_name, price)
)

-- t_member: 会员表(member_id: 会员id, join_date: 加入日期)
, t_member AS (
    SELECT * FROM (
        VALUES('A', '2023-11-07')
            , ('C', '2023-11-09')
        	, ('D', '2023-11-10')
    ) AS tbl_name(member_id, join_date)
)

2- 题目

1. 每位客户消费总额是多少

SELECT a.customer_id, SUM(b.price) AS sales_amount 
FROM t_sales a 
JOIN t_product b 
ON a.product_id = b.product_id
GROUP BY a.customer_id
;

2. 每位客户总共消费天数

SELECT a.customer_id, COUNT(DISTINCT order_date) AS date_ct
FROM t_sales a 
GROUP BY a.customer_id
;

3. 每位客户购买的第一份食物是什么

SELECT customer_id, product_name
FROM ( -- 使用开窗函数,按照日期排序,获得购买食物排序
    SELECT 
          customer_id, product_id
        , RANK() OVER(PARTITION BY customer_id ORDER BY order_date) AS rk
    FROM t_sales 
) a 
JOIN t_product b 
ON a.product_id = b.product_id
WHERE a.rk = 1
GROUP BY customer_id, product_name
;

4. 最受欢迎的食物是哪一个, 一共被购买了多少次, 每位顾客购买了多少次

SELECT 
      customer_id, product_name, customer_buy_ct, buy_ct
FROM (
    SELECT 
          customer_id, product_id, customer_buy_ct, buy_ct
        , RANK() OVER(ORDER BY buy_ct DESC) AS rk 
    FROM (
        SELECT customer_id, product_id
            , COUNT(1) OVER(PARTITION BY customer_id, product_id) AS customer_buy_ct -- 每位顾客购买次数
            , COUNT(1) OVER(PARTITION BY product_id) AS buy_ct -- 总共购买次数
        FROM t_sales
    )
    GROUP BY customer_id, product_id, customer_buy_ct, buy_ct
) a 
JOIN t_product b 
ON a.product_id = b.product_id
WHERE a.rk = 1
ORDER BY customer_id
;

5. 每位顾客最喜欢的食物分别是什么

SELECT customer_id, product_name, customer_buy_ct
FROM (
    SELECT 
          customer_id, product_name, customer_buy_ct
        , RANK() OVER(PARTITION BY customer_id ORDER BY customer_buy_ct DESC) AS rk
    FROM (
        SELECT 
              customer_id, product_name
            , COUNT(1) AS customer_buy_ct -- 购买次数
        FROM t_sales a 
        JOIN t_product b
        ON a.product_id = b.product_id
        GROUP BY customer_id, product_name
    ) a 
) b 
WHERE rk =1
;

6. 顾客成为会员后, 最先购买的是什么

SELECT customer_id, product_name, order_date
FROM (
    SELECT 
          customer_id, product_name, order_date
        , RANK() OVER(PARTITION BY customer_id ORDER BY order_date) AS rk 
    FROM (
        SELECT 
              customer_id, order_date, product_id, join_date
        FROM t_member a 
        LEFT JOIN t_sales b ON a.member_id = b.customer_id
    ) a 
    JOIN t_product b ON a.product_id = b.product_id
    WHERE order_date >= join_date
) t 
WHERE rk = 1
GROUP BY customer_id, product_name, order_date
;

7. 顾客在成为会员之前, 最后购买的产品是什么

SELECT customer_id, product_name, order_date
FROM (
    SELECT 
          customer_id, product_name, order_date
        , RANK() OVER(PARTITION BY customer_id ORDER BY order_date DESC ) AS rk 
    FROM (
        SELECT 
              customer_id, order_date, product_id, join_date
        FROM t_member a 
        LEFT JOIN t_sales b ON a.member_id = b.customer_id
    ) a 
    JOIN t_product b ON a.product_id = b.product_id
    WHERE order_date < join_date
) t 
WHERE rk = 1
GROUP BY customer_id, product_name, order_date
;

8. 顾客在成为会员之前, 总的购买产品数,和总的消费金额

SELECT 
      customer_id
    , COUNT(a.product_id) AS sales_quantity
    , SUM(price) AS sales_amount
FROM (
    SELECT 
          customer_id, order_date, product_id, join_date
    FROM t_member a 
    LEFT JOIN t_sales b ON a.member_id = b.customer_id
) a 
JOIN t_product b ON a.product_id = b.product_id
WHERE order_date < join_date
GROUP BY customer_id
;

9. 每消费1元有10积分, sauce 有双倍积分, 每位顾客有多少积分

SELECT 
      customer_id
    , SUM(CASE WHEN product_name = 'sauce' THEN price * 2 * 10 ELSE price * 10 END) AS total_scores
FROM (
    SELECT customer_id, product_name, price
    FROM t_sales a 
    JOIN t_product b ON a.product_id = b.product_id
) a 
GROUP BY customer_id
;

10. 每消费1元有10积分, sauce 有双倍积分, 成为会员后所有产品都是双倍积分, 统计在11月份之前, 每位顾客积分是多少

SELECT 
      customer_id
    -- 判断是否是 'sauce' 或者 是成为会员后的订单, 统计积分
    , SUM(CASE WHEN (product_name = 'sauce' OR order_date >= join_date) THEN price * 2 * 10 ELSE price * 10 END) AS total_scores
FROM (
    SELECT customer_id, product_name, price, order_date
        , NVL(join_date, '9999-12-31') AS join_date  -- 不是会员的日期置为最大日期
    FROM t_sales a 
    JOIN t_product b ON a.product_id = b.product_id
    LEFT JOIN t_member c ON a.customer_id = c.member_id
    WHERE order_date <= '2023-11-30'  -- 11月份订单
) a
GROUP BY customer_id
;

11. 创建新的宽表, 包含字段 customer_id, product_name, price, order_date, member

SELECT 
      customer_id, product_name, price, order_date
    -- 判断是否是 会员后的订单
    , CASE WHEN order_date >= join_date THEN 'Y' ELSE 'N' END AS member
FROM (
    SELECT customer_id, product_name, price, order_date
        , NVL(join_date, '9999-12-31') AS join_date  -- 不是会员的日期置为最大日期
    FROM t_sales a 
    JOIN t_product b ON a.product_id = b.product_id
    LEFT JOIN t_member c ON a.customer_id = c.member_id
) a
;

12. 对顾客购买产品按时间升序排序, 区分会员与非会员, 非会员不参与排序, 记为NULL

SELECT
      customer_id, product_name, price, order_date, member
      -- 直接在上一个结果上进行子查询, 是会员的参与排序
    , CASE WHEN member = 'Y' THEN (RANK() OVER(PARTITION BY customer_id, member ORDER BY order_date)) ELSE NULL END AS rk
FROM (
    SELECT 
          customer_id, product_name, price, order_date
        , CASE WHEN order_date >= join_date THEN 'Y' ELSE 'N' END AS member
    FROM (
        SELECT customer_id, product_name, price, order_date
            , NVL(join_date, '9999-12-31') AS join_date  -- 不是会员的日期置为最大日期
        FROM t_sales a 
        JOIN t_product b ON a.product_id = b.product_id
        LEFT JOIN t_member c ON a.customer_id = c.member_id
    ) a
) b
;

end