mysql 脚本;有一个订单表,有渠道,用户,支付日期,支付年月,子品牌,商品ID,商品实付金额,计算近一年各渠道购买人数,支付金额,及同比数据;计算近一年各渠道每个月购买人数,支付金额,及同比数据;计算近一年各渠道每个子品牌购买人数,支付金额,及同比数据
时间: 2024-03-03 10:47:44 浏览: 60
好的,以下是可能的 MySQL 脚本实现:
```sql
-- 计算近一年各渠道购买人数、支付金额及同比数据
SELECT
渠道,
COUNT(DISTINCT 用户) AS 购买人数,
SUM(商品实付金额) AS 支付金额,
SUM(
CASE WHEN YEAR(支付日期) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR)
THEN 商品实付金额
ELSE 0
END
) AS 去年支付金额,
SUM(商品实付金额) / SUM(
CASE WHEN YEAR(支付日期) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR)
THEN 商品实付金额
ELSE 0
END
) AS 支付金额同比增长率
FROM 订单表
WHERE 支付日期 >= CURRENT_DATE - INTERVAL 1 YEAR
GROUP BY 渠道;
-- 计算近一年各渠道每个月购买人数、支付金额及同比数据
SELECT
渠道,
YEAR(支付日期) AS 支付年份,
MONTH(支付日期) AS 支付月份,
COUNT(DISTINCT 用户) AS 购买人数,
SUM(商品实付金额) AS 支付金额,
SUM(
CASE WHEN YEAR(支付日期) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR)
AND MONTH(支付日期) = MONTH(CURRENT_DATE - INTERVAL 1 YEAR)
THEN 商品实付金额
ELSE 0
END
) AS 去年支付金额,
SUM(商品实付金额) / SUM(
CASE WHEN YEAR(支付日期) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR)
AND MONTH(支付日期) = MONTH(CURRENT_DATE - INTERVAL 1 YEAR)
THEN 商品实付金额
ELSE 0
END
) AS 支付金额同比增长率
FROM 订单表
WHERE 支付日期 >= CURRENT_DATE - INTERVAL 1 YEAR
GROUP BY 渠道, YEAR(支付日期), MONTH(支付日期);
-- 计算近一年各渠道每个子品牌购买人数、支付金额及同比数据
SELECT
渠道,
子品牌,
COUNT(DISTINCT 用户) AS 购买人数,
SUM(商品实付金额) AS 支付金额,
SUM(
CASE WHEN YEAR(支付日期) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR)
AND MONTH(支付日期) = MONTH(CURRENT_DATE - INTERVAL 1 YEAR)
THEN 商品实付金额
ELSE 0
END
) AS 去年支付金额,
SUM(商品实付金额) / SUM(
CASE WHEN YEAR(支付日期) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR)
AND MONTH(支付日期) = MONTH(CURRENT_DATE - INTERVAL 1 YEAR)
THEN 商品实付金额
ELSE 0
END
) AS 支付金额同比增长率
FROM 订单表
WHERE 支付日期 >= CURRENT_DATE - INTERVAL 1 YEAR
GROUP BY 渠道, 子品牌;
```
需要注意的是,在这个脚本中,假设订单表的名称为 `订单表`,字段名称及数据类型如下:
- 渠道:`VARCHAR(50)`
- 用户:`VARCHAR(50)`
- 支付日期:`DATE`
- 支付年月:`VARCHAR(7)`
- 子品牌:`VARCHAR(50)`
- 商品ID:`VARCHAR(50)`
- 商品实付金额:`DECIMAL(10,2)`
阅读全文