基于SQL的分析挖掘案例

发布时间：2024年01月24日

基于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
;
customer_idsales_amount
A136.0
B134.0
C180.0
D134.0
E88.0
  1. 每位客户总共消费天数
SELECT a.customer_id, COUNT(DISTINCT order_date) AS date_ct
FROM t_sales a 
GROUP BY a.customer_id
;
customer_iddate_ct
A4
B6
C6
D6
E4
  1. 每位客户购买的第一份食物是什么
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
;
customer_idproduct_name
Apasta
Asalad
Bsalad
Csauce
Dsauce
Esauce
  1. 最受欢迎的食物是哪一个, 一共被购买了多少次, 每位顾客购买了多少次
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
;
customer_idproduct_namecustomer_buy_ctbuy_ct
Asauce316
Bsauce216
Csauce516
Dsauce216
Esauce416
  1. 每位顾客最喜欢的食物分别是什么
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
;
customer_idproduct_namecustomer_buy_ct
Asauce3
Bpasta2
Bsalad2
Bsauce2
Csauce5
Dpasta2
Dsalad2
Dsauce2
Esauce4
  1. 顾客成为会员后, 最先购买的是什么
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
;
customer_idproduct_nameorder_date
Asalad2023-11-07
Csalad2023-11-11
Dpasta2023-11-11
  1. 顾客在成为会员之前, 最后购买的产品是什么
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
;
customer_idproduct_nameorder_date
Apasta2023-11-01
Asalad2023-11-01
Cpasta2023-11-07
Csauce2023-11-07
Dpasta2023-11-09
  1. 顾客在成为会员之前, 总的购买产品数,和总的消费金额
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
;
customer_idsales_quantitysales_amount
A245.0
C486.0
D492.0
  1. 每消费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
;
customer_idtotal_scores
A2020.0
B1780.0
C2900.0
D1780.0
E1760.0
  1. 每消费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
;
customer_idtotal_scores
A2270.0
B1340.0
C2960.0
D1540.0
E1760.0
  1. 创建新的宽表, 包含字段 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
;
customer_idproduct_namepriceorder_datemember
Apasta202023-11-01N
Asalad252023-11-01N
Asalad252023-11-07Y
Asauce222023-11-10Y
Asauce222023-11-11Y
Asauce222023-11-11Y
Bsalad252023-11-01N
Bsalad252023-11-02N
Bpasta202023-11-04N
Bpasta202023-11-11N
Bsauce222023-11-16N
Bsauce222023-12-01N
Cpasta202023-11-07N
Csalad252023-11-11Y
Csalad252023-11-15Y
Csauce222023-11-19Y
Csauce222023-12-02Y
Csauce222023-11-01N
Csauce222023-11-01N
Csauce222023-11-07N
Dsauce222023-11-02N
Dsalad252023-11-05N
Dsalad252023-11-08N
Dpasta202023-11-09N
Dpasta202023-11-11Y
Dsauce222023-12-18Y
Esauce222023-11-01N
Esauce222023-11-03N
Esauce222023-11-08N
Esauce222023-11-12N
  1. 对顾客购买产品按时间升序排序, 区分会员与非会员, 非会员不参与排序, 记为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
;
customer_idproduct_namepriceorder_datememberrk
Asalad252023-11-01N\N
Apasta202023-11-01N\N
Asalad252023-11-07Y1
Asauce222023-11-10Y2
Asauce222023-11-11Y3
Asauce222023-11-11Y3
Bsalad252023-11-01N\N
Bsalad252023-11-02N\N
Bpasta202023-11-04N\N
Bpasta202023-11-11N\N
Bsauce222023-11-16N\N
Bsauce222023-12-01N\N
Csauce222023-11-01N\N
Csauce222023-11-01N\N
Csauce222023-11-07N\N
Cpasta202023-11-07N\N
Csalad252023-11-11Y1
Csalad252023-11-15Y2
Csauce222023-11-19Y3
Csauce222023-12-02Y4
Dsauce222023-11-02N\N
Dsalad252023-11-05N\N
Dsalad252023-11-08N\N
Dpasta202023-11-09N\N
Dpasta202023-11-11Y1
Dsauce222023-12-18Y2
Esauce222023-11-01N\N
Esauce222023-11-03N\N
Esauce222023-11-08N\N
Esauce222023-11-12N\N
end
文章来源:https://blog.csdn.net/Taerge0110/article/details/135816002
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
最新文章