将这段代码整理一下 看得更清晰
时间: 2024-09-26 15:06:55 浏览: 20
这是一个SQL查询,旨在汇总特定时间段内(2024年9月3日到当前日期减一)的业务活跃用户数据,按照日期、用户、渠道(整体、商业化、电销)和营销阶段进行分组,并计算不同来源的业务GMV(总收入)。以下是代码的整理版:
```sql
-- 定义一个临时表t1,按日期、用户、渠道和阶段名称分组,分别统计商业化和电销的总金额、大VIP金额和常规价格金额
CREATE TEMPORARY TABLE t1 AS
(
-- 统计整体业务
SELECT day, u_user, '整体', stage_name_day,
SUM(CASE WHEN business_gmv_attribution IN ('商业化', '电销') THEN normal_price_amount ELSE 0 END) AS totalamount,
SUM(CASE WHEN business_gmv_attribution IN ('商业化', '电销') THEN normal_price_big_vip_amount ELSE 0 END) AS pb_amount,
SUM(CASE WHEN business_gmv_attribution IN ('商业化', '电销') THEN normal_price_routine_amount ELSE 0 END) AS non_pb_amount
FROM aws.business_active_user_last_14_day
WHERE day BETWEEN 20240903 AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
GROUP BY day, u_user, '整体', stage_name_day
-- 商业化部分
UNION ALL
SELECT day, u_user, '商业化', stage_name_day,
SUM(CASE WHEN business_gmv_attribution = '商业化' THEN normal_price_amount ELSE 0 END) AS totalamount,
SUM(CASE WHEN business_gmv_attribution = '商业化' THEN normal_price_big_vip_amount ELSE 0 END) AS pb_amount,
SUM(CASE WHEN business_gmv_attribution = '商业化' THEN normal_price_routine_amount ELSE 0 END) AS non_pb_amount
FROM aws.business_active_user_last_14_day
WHERE day BETWEEN 20240903 AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
GROUP BY day, u_user, '商业化', stage_name_day
-- 电销部分
UNION ALL
SELECT day, u_user, '电销', stage_name_day,
SUM(CASE WHEN business_gmv_attribution = '电销' THEN normal_price_amount ELSE 0 END) AS totalamount,
SUM(CASE WHEN business_gmv_attribution = '电销' THEN normal_price_big_vip_amount ELSE 0 END) AS pb_amount,
SUM(CASE WHEN business_gmv_attribution = '电销' THEN normal_price_routine_amount ELSE 0 END) AS non_pb_amount
FROM aws.business_active_user_last_14_day
WHERE day BETWEEN 20240903 AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
GROUP BY day, u_user, '电销', stage_name_day
);
```
这个整理后的版本将查询结构分成了三个明确的部分,每个部分对应一种渠道,使得代码更易读和维护。