MySQL 统计上周上上周金额

1.需求如下：

有一个cus_day_shipment表，字段有use_id(客户编码），create_time(消费时间），delivered_amount（消费金额），在不改变源数据结构的情况下，统计这个客户在上周，上上周，上上上周，上上上上周的金额，具体示例如下：

2.函数详解

需要用到以下两个函数

①MySQL的YEARWEEK函数用于返回一个日期的年份和周数。

语法： YEARWEEK(date, [mode])

参数：

• date：必需。要计算年份和周数的日期。
• mode：可选。指定返回的周数的模式。默认为0。
  • 0：周一作为每周的第一天，周日作为每周的最后一天。
  • 1：周日作为每周的第一天，周六作为每周的最后一天。
  • 2：周日作为每周的第一天，周日作为每周的最后一天。

返回值： 返回一个整数，表示指定日期的年份和周数的组合。返回的格式为YYYYWW，其中YYYY是四位数的年份，WW是两位数的周数。

②DATE_SUB函数是MySQL中的一个日期函数，用于在指定的日期上减去指定的时间间隔。

语法： DATE_SUB(date, INTERVAL expression unit)

参数说明：

• date：要进行减法操作的日期或日期时间值。
• expression：要减去的时间间隔的数值表达式。
• unit：要减去的时间间隔的单位，可以是YEAR、MONTH、DAY、HOUR、MINUTE、WEEK,SECOND等。

返回值： 返回一个新的日期或日期时间值，该值是在指定的日期上减去指定的时间间隔后得到的。

示例：

 SELECT DATE_SUB('2021-01-01', INTERVAL 1 YEAR);
-- 返回值：2020-01-01

SELECT DATE_SUB('2021-01-01', INTERVAL 1 MONTH);
-- 返回值：2020-12-01

SELECT DATE_SUB('2021-01-01', INTERVAL 1 WEEK);
-- 返回值：2020-12-25

SELECT DATE_SUB('2021-01-01', INTERVAL 7 DAY);
-- 返回值：2020-12-25

以上示例中，DATE_SUB函数分别将指定的日期减去了1年、1个月,1周和7天。

3.实现步骤

①利用上面两个函数即可求出消费时间的周数和减去7天的周数

select create_time, YEARWEEK(create_time) as 今年第几周,
 DATE_SUB(t1.create_time, INTERVAL 1 WEEK) 日期减去一周,
 YEARWEEK(DATE_SUB(t1.create_time, INTERVAL 1 WEEK)) 今年第几周再减一周 from cus_day_shipment t1

②求出每周的总金额 

③ 利用关联条件，取跟源表相差一周的金额 

最终代码：

SELECT   
    t1.use_id,    
    t1.create_time,    
    t1.delivered_amount,  
    t2_1week.last_week_amount AS 上周金额,  
    t2_2weeks.two_weeks_ago_amount AS 上上周金额,  
    t2_3weeks.three_weeks_ago_amount AS 上上上周金额,  
    t2_4weeks.four_weeks_ago_amount AS 上上上上周金额  
FROM   
    cus_day_shipment t1  
LEFT JOIN (  
    SELECT   
        use_id,  
        YEARWEEK(create_time) AS yw,  
        SUM(delivered_amount) AS last_week_amount  
    FROM   
        cus_day_shipment  
    GROUP BY   
        use_id, yw  
) t2_1week ON t1.use_id = t2_1week.use_id AND t2_1week.yw = YEARWEEK(DATE_SUB(t1.create_time, INTERVAL 1 WEEK))  
LEFT JOIN (  
    SELECT   
        use_id,  
        YEARWEEK(create_time) AS yw,  
        SUM(delivered_amount) AS two_weeks_ago_amount  
    FROM   
        cus_day_shipment  
    GROUP BY   
        use_id, yw  
) t2_2weeks ON t1.use_id = t2_2weeks.use_id AND t2_2weeks.yw = YEARWEEK(DATE_SUB(t1.create_time, INTERVAL 2 WEEK))  
LEFT JOIN (  
    SELECT   
        use_id,  
        YEARWEEK(create_time) AS yw,  
        SUM(delivered_amount) AS three_weeks_ago_amount  
    FROM   
        cus_day_shipment  
    GROUP BY   
        use_id, yw  
) t2_3weeks ON t1.use_id = t2_3weeks.use_id AND t2_3weeks.yw = YEARWEEK(DATE_SUB(t1.create_time, INTERVAL 3 WEEK))  
LEFT JOIN (  
    SELECT   
        use_id,  
        YEARWEEK(create_time) AS yw,  
        SUM(delivered_amount) AS four_weeks_ago_amount  
    FROM   
        cus_day_shipment  
    GROUP BY   
        use_id, yw  
) t2_4weeks ON t1.use_id = t2_4weeks.use_id AND t2_4weeks.yw = YEARWEEK(DATE_SUB(t1.create_time, INTERVAL 4 WEEK));

运行结果：