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));
运行结果: