题目

表: Customer

Column Name	Type
customer_id	int
name	varchar
visited_on	date
amount	int

在 SQL 中，(customer_id, visited_on) 是该表的主键。 该表包含一家餐馆的顾客交易数据。 visited_on 表示 (customer_id) 的顾客在 visited_on 那天访问了餐馆。 amount 是一个顾客某一天的消费总额。

你是餐馆的老板，现在你想分析一下可能的营业额变化增长（每天至少有一位顾客）。

计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值。average_amount 要 保留两位小数。

结果按 visited_on 升序排序。

返回结果格式的例子如下。

示例 1:

输入： Customer 表:

customer_id	name	visited_on	amount
1	Jhon	2019-01-01	100
2	Daniel	2019-01-02	110
3	Jade	2019-01-03	120
4	Khaled	2019-01-04	130
5	Winston	2019-01-05	110
6	Elvis	2019-01-06	140
7	Anna	2019-01-07	150
8	Maria	2019-01-08	80
9	Jaze	2019-01-09	110
1	Jhon	2019-01-10	130
3	Jade	2019-01-10	150

输出：

visited_on	amount	average_amount
2019-01-07	860	122.86
2019-01-08	840	120
2019-01-09	840	120
2019-01-10	1000	142.86

解释： 第一个七天消费平均值从 2019-01-01 到 2019-01-07 是 (100 + 110 + 120 + 130 + 110 + 140 + 150)/7 = 122.86 第二个七天消费平均值从 2019-01-02 到 2019-01-08 是 (110 + 120 + 130 + 110 + 140 + 150 + 80)/7 = 120 第三个七天消费平均值从 2019-01-03 到 2019-01-09 是 (120 + 130 + 110 + 140 + 150 + 80 + 110)/7 = 120 第四个七天消费平均值从 2019-01-04 到 2019-01-10 是 (130 + 110 + 140 + 150 + 80 + 110 + 130 + 150)/7 = 142.86

题解

解法1

-- 按照正常思维
SELECT `t1`.`visited_on`,
       SUM(`t3`.`amount`)               AS `amount`,
       ROUND(SUM(`t3`.`amount`) / 7, 2) AS `average_amount`
       -- 销售日列表
FROM (SELECT DISTINCT `visited_on` FROM `Customer` ORDER BY `visited_on`) `t1`
         -- 最早销售日
         INNER JOIN (SELECT MIN(`visited_on`) AS `min` FROM `Customer`) `t2` ON TRUE
         -- 每日销售额 关联时 日期差不能超过7天 避免出现负数用了日期比较
         INNER JOIN (SELECT `visited_on`, SUM(`amount`) AS `amount` FROM `Customer` GROUP BY `visited_on`) `t3`
                    ON `t1`.`visited_on` >= `t3`.`visited_on` AND DATEDIFF(`t1`.`visited_on`, `t3`.`visited_on`) <= 6
     -- 还需要排除和最早销售日间隔低于7天的
WHERE DATEDIFF(`t1`.`visited_on`, `t2`.`min`) >= 6
GROUP BY `t1`.`visited_on`
ORDER BY `t1`.`visited_on`

解法2

-- 使用滑动窗口
SELECT `t`.`visited_on`, `t`.`amount`, ROUND(`t`.`amount` / 7, 2) AS `average_amount`
FROM (SELECT `t`.`visited_on`,
             DATEDIFF(`t`.`visited_on`, `t2`.`min`)                                            AS `cnt`,
             -- 按照日期滑动窗口当日加上前6天的累计值
             SUM(`t`.`amount`) OVER (ORDER BY `t`.`visited_on` RANGE INTERVAL 6 DAY PRECEDING) AS `amount`
             -- 按照每日汇总销售额
      FROM (SELECT `visited_on`, SUM(`amount`) AS `amount` FROM `Customer` GROUP BY `visited_on`) `t`
               -- 最早销售日 用于判断窗口是否足够7天
               INNER JOIN (SELECT MIN(`visited_on`) AS `min` FROM `Customer`) `t2` ON TRUE
      ORDER BY `t`.`visited_on`) `t`
     -- 和最早销售日间隔不能低于6天
WHERE `t`.`cnt` >= 6