关于后端:LeetCode-mysql-刷题四餐馆营业额变化增长用自连和窗口函数-4-种-sql-实现过去-7-天的营业额

37次阅读

共计 6599 个字符，预计需要花费 17 分钟才能阅读完成。

题目

题目链接：餐馆营业额变动增长

你是餐馆的老板，当初你想剖析一下可能的营业额变动增长（每天至多有一位顾客）。

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

后果按 visited_on 升序排序。

返回后果格局的例子如下。

Create table If Not Exists Customer (customer_id int, name varchar(20), visited_on date, amount int);
Truncate table Customer;
insert into Customer (customer_id, name, visited_on, amount) values ('1', 'Jhon', '2019-01-01', '100');
insert into Customer (customer_id, name, visited_on, amount) values ('2', 'Daniel', '2019-01-02', '110');
insert into Customer (customer_id, name, visited_on, amount) values ('3', 'Jade', '2019-01-03', '120');
insert into Customer (customer_id, name, visited_on, amount) values ('4', 'Khaled', '2019-01-04', '130');
insert into Customer (customer_id, name, visited_on, amount) values ('5', 'Winston', '2019-01-05', '110');
insert into Customer (customer_id, name, visited_on, amount) values ('6', 'Elvis', '2019-01-06', '140');
insert into Customer (customer_id, name, visited_on, amount) values ('7', 'Anna', '2019-01-07', '150');
insert into Customer (customer_id, name, visited_on, amount) values ('8', 'Maria', '2019-01-08', '80');
insert into Customer (customer_id, name, visited_on, amount) values ('9', 'Jaze', '2019-01-09', '110');
insert into Customer (customer_id, name, visited_on, amount) values ('1', 'Jhon', '2019-01-10', '130');
insert into Customer (customer_id, name, visited_on, amount) values ('3', 'Jade', '2019-01-10', '150');

-- 工夫不间断的例子
insert into Customer (customer_id, name, visited_on, amount) values ('1', 'Jhon', '2019-01-01', '100');
insert into Customer (customer_id, name, visited_on, amount) values ('4', 'Khaled', '2019-01-04', '130');
insert into Customer (customer_id, name, visited_on, amount) values ('5', 'Winston', '2019-01-05', '110');
insert into Customer (customer_id, name, visited_on, amount) values ('6', 'Elvis', '2019-01-06', '140');
insert into Customer (customer_id, name, visited_on, amount) values ('7', 'Anna', '2019-01-07', '150');
insert into Customer (customer_id, name, visited_on, amount) values ('8', 'Maria', '2019-01-08', '80');
insert into Customer (customer_id, name, visited_on, amount) values ('9', 'Jaze', '2019-01-09', '110');
insert into Customer (customer_id, name, visited_on, amount) values ('1', 'Jhon', '2019-01-10', '130');
insert into Customer (customer_id, name, visited_on, amount) values ('3', 'Jade', '2019-01-10', '150');
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         |
+-------------+--------------+--------------+-------------+
在 SQL 中，(customer_id, visited_on) 是该表的主键。该表蕴含一家餐馆的顾客交易数据。visited_on 示意 (customer_id) 的顾客在 visited_on 那天拜访了餐馆。amount 是一个顾客某一天的生产总额。输入：+--------------+--------------+----------------+
| 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 是 restaurant-growth/restaurant-growth/ (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. 应用窗口函数，窗口函数比拟好了解应用 6 PRECEDING AND current ROW 就能查找进去了（计划一）
  2. 应用自连，连贯条件不太容易想到，须要应用 DATEDIFF 函数，这个函数能够计算两个日期之间的天数，而后应用 BETWEEN 条件（计划二和计划三）

解析

  1. 要晓得过来 7 天的均匀消费额，须要先晓得每天的总消费额，作为长期表 tmp1
    select visited_on, sum(amount) sum_amount from Customer group by visited_on

    +-------------+--------------+
| visited_on  | sum_amount   |
+-------------+--------------+
| 2019-01-01  |  100         |
| 2019-01-02  |  110         |
| 2019-01-03  |  120         |
| 2019-01-04  |  130         |
| 2019-01-05  |  110         |
| 2019-01-06  |  140         |
| 2019-01-07  |  150         |
| 2019-01-08  |  80          |
| 2019-01-09  |  110         |
| 2019-01-10  |  280         |
+-------------+--------------+

  2. 应用窗口函数，计算过来 7 天的总的消费额，作为长期表 tmp2
    select sum(sum_amount) sum_amount over (order by to_days(visited_on) range between 6 preceding and current row) as sum_amount from tmp1

    | visited_on  | sum_amount   |
+-------------+--------------+
| 2019-01-01  | 100          |
| 2019-01-02  | 210          |
| 2019-01-03  | 330          |
| 2019-01-04  | 460          |
| 2019-01-05  | 570          |
| 2019-01-06  | 710          |
| 2019-01-07  | 860          |
| 2019-01-08  | 840          |
| 2019-01-09  | 840          |
| 2019-01-10  | 1000         |
+-------------+--------------+

  3. 计算过来 7 天的均匀消费额，作为长期表 tmp3
    select visited_on, sum_amount amount, sum_amount / 7 as average_amount from tmp2

    | visited_on  | sum_amount   | average_amount |
+-------------+--------------+----------------+
| 2019-01-01  | 100            | 14.2857        |
| 2019-01-02  | 210            | 30.0000        |
| 2019-01-03  | 330            | 47.1429        |
| 2019-01-04  | 460            | 65.7143        |
| 2019-01-05  | 570            | 81.4286        |
| 2019-01-06  | 710            | 101.4286       |
| 2019-01-07  | 860            | 122.8571       |
| 2019-01-08  | 840            | 120.0000       |
| 2019-01-09  | 840            | 120.0000       |
| 2019-01-10  | 1000         | 142.8571       |
+-------------+-------------+----------------+
  4. 筛选出计算数据大于等于七天的数据 
  - 须要晓得表中日期最小的一天，作为长期表 `tmp4`
     `select min(visited_on) min_visited_on from Customer`
  ```
  | min_visited_on  |
  +-----------------+
  | 2019-01-01      |
  +-----------------+
  ```
  - 应用 `datediff(expr1, expr2)` 函数，计算两个日期之间的天数，这里须要大于等于 `6` 天
     `select visited_on, amount, round(average_amount, 2) average_amount from tmp3 where datediff(visited_on, (select min(visited_on) from Customer)) >= 6`
  ```
  | visited_on  | amount       | average_amount |
  +-------------+--------------+----------------+
  | 2019-01-07    | 860          |  122.8571      |
  | 2019-01-08    | 840          |  120.0000      |
  | 2019-01-09    | 840          |  120.0000      |
  | 2019-01-10    | 1000         |  142.8571      |
  +-------------+--------------+----------------+
  ```

最终 sql 语句如下：

SELECT
   visited_on,
   sum_amount amount,
   ROUND(sum_amount / 7, 2) average_amount
FROM (
   SELECT
      visited_on,
      SUM(sum_amount) OVER (ORDER BY to_days(visited_on) RANGE BETWEEN 6 PRECEDING AND current ROW ) sum_amount
   FROM (
      SELECT
         visited_on,
         SUM(amount) sum_amount
      FROM Customer
      GROUP BY visited_on
   ) tmp1
) tmp2
WHERE DATEDIFF(visited_on, ( SELECT MIN( visited_on) FROM Customer )) >= 6;

下面 sql 能够简化一下，不过有问题，就是如果工夫不间断，排序不会跳过。

也就是说 rk > 7 只能筛选出间断 7 天的数据 

SELECT
   visited_on,
   amount,
   SUM(amount / 7, 2) average_amount
FROM (
   SELECT
      visited_on,
      RANK() OVER ( ORDER BY visited_on) AS rk,
      SUM(SUM( amount)) OVER (ORDER BY visited_on RANGE INTERVAL 7-1 DAY PRECEDING) AS amount
   FROM Customer GROUP BY visited_on
) AS tep WHERE rk >= 7 ORDER BY 1

办法二

此办法是应用自连，连贯的条件是工夫间断 7 天，这个办法如果工夫不间断，就会有问题 

WITH t AS (SELECT visited_on, SUM( amount) amount FROM Customer GROUP BY visited_on
)
SELECT a.visited_on, SUM(b.amount) amount, ROUND(AVG( b.amount), 2 ) average_amount
FROM t a, t b
WHERE DATEDIFF(a.visited_on, b.visited_on) BETWEEN 0 AND 6
GROUP BY a.visited_on COUNT(*) = 7;

办法三 

SELECT
   a.visited_on,
   sum(b.amount) AS amount,
   round(sum( b.amount) / 7, 2 ) AS average_amount
FROM
   (SELECT DISTINCT visited_on FROM Customer) a
   JOIN Customer b ON datediff(a.visited_on, b.visited_on) BETWEEN 0 AND 6
WHERE
   a.visited_on >= (SELECT min( visited_on) FROM Customer ) + 6
GROUP BY a.visited_on
ORDER BY visited_on

往期 MySQL 题目

  1. MySQL 题目
  2. LeetCode mysql 刷题一：计算非凡奖金 | 买下所有产品的客户
  3. LeetCode mysql 刷题二：电影评分——判断日期的五种办法
  4. LeetCode mysql 刷题三：确认率——MySQL 中的 null 解决 | 判断三角形的四种办法

正文完
后端
发表至： 后端
2023-09-23
 0
关于后端:总结-canal-使用过程中的几个问题值得思考一下
关于后端:Golang负载均衡器Balancer的源码解读
关于后端:前端学Ruby全栈论坛地宫项目二
关于后端:开源C语言库Melon红黑树