SET @end_date = '2023-03-31'; SET @start_date = DATE_ADD(@end_date + INTERVAL 1 DAY, INTERVAL -12 MONTH); SET @last_end_date = LAST_DAY(DATE_ADD(@end_date , INTERVAL -12 MONTH)); SET @last_start_date = DATE_ADD(@end_date + INTERVAL 1 DAY, INTERVAL -24 MONTH); #1.近一年所有店铺gmv相关数据; SELECT 购买渠道, ifnull(支付年月,'时间合计') AS 支付年月, ifnull(子品牌,'子品牌合计') AS 子品牌, COUNT(DISTINCT 用户id) AS 购买人数, SUM(商品实付金额) AS 购买金额, SUM(商品实付金额) / COUNT(DISTINCT 用户id) AS 客单价 FROM valid_order WHERE 支付日期 BETWEEN @start_date AND @end_date GROUP BY 1,2,3 WITH ROLLUP #与上半部分区别是月份和子品牌顺序调换，主要是因为合计人数会因为去重逻辑不同而产生差异 UNION ALL SELECT 购买渠道, null AS 支付年月, COALESCE(子品牌,'子品牌合计') AS 子品牌, COUNT(DISTINCT 用户id) AS 购买人数, SUM(商品实付金额) AS 购买金额, SUM(商品实付金额) / COUNT(DISTINCT 用户id) AS 客单价 FROM valid_order WHERE 支付日期 BETWEEN @start_date AND @end_date GROUP BY 1,2,3;优化代码

可以尝试以下几点优化：

1. 使用变量代替函数计算，例如将 `LAST_DAY` 函数替换为 `DATE_FORMAT(DATE_ADD(@end_date , INTERVAL -12 MONTH), '%Y-%m-31')`。

2. 将条件筛选放在子查询中进行，以减少 `valid_order` 表中的数据量。例如：

   SELECT 购买渠道, ifnull(支付年月,'时间合计') AS 支付年月, ifnull(子品牌,'子品牌合计') AS 子品牌, COUNT(DISTINCT 用户id) AS 购买人数, SUM(商品实付金额) AS 购买金额, SUM(商品实付金额) / COUNT(DISTINCT 用户id) AS 客单价 
   FROM ( 
       SELECT * 
       FROM valid_order 
       WHERE 支付日期 BETWEEN @start_date AND @end_date 
   ) t 
   GROUP BY 1,2,3 WITH ROLLUP 

3. 将多个查询语句合并为单个查询，使用 `UNION ALL` 进行拼接。

   SELECT 购买渠道, ifnull(支付年月,'时间合计') AS 支付年月, ifnull(子品牌,'子品牌合计') AS 子品牌, COUNT(DISTINCT 用户id) AS 购买人数, SUM(商品实付金额) AS 购买金额, SUM(商品实付金额) / COUNT(DISTINCT 用户id) AS 客单价 
   FROM ( 
       SELECT * 
       FROM valid_order 
       WHERE 支付日期 BETWEEN @start_date AND @end_date 
   ) t 
   GROUP BY 1,2,3 WITH ROLLUP 
   UNION ALL 
   SELECT 购买渠道, null AS 支付年月, COALESCE(子品牌,'子品牌合计') AS 子品牌, COUNT(DISTINCT 用户id) AS 购买人数, SUM(商品实付金额) AS 购买金额, SUM(商品实付金额) / COUNT(DISTINCT 用户id) AS 客单价 
   FROM ( 
       SELECT * 
       FROM valid_order 
       WHERE 支付日期 BETWEEN @start_date AND @end_date 
   ) t 
   GROUP BY 1,2,3;