LeetCode mysql 刷題四：餐館營業額變化增長——用自連和視窗函式 4 種 sql 實現過去 7 天的營業額

題目

你是餐館的老闆，現在你想分析一下可能的營業額變化增長（每天至少有一位顧客）。

計算以 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

本題考察的知識是如何累加一段時間區間內的值

有兩種實現方式：

使用視窗函式，視窗函式比較好理解使用 6 PRECEDING AND current ROW 就能查詢出來了（方案一）
使用自連，連線條件不太容易想到，需要使用 DATEDIFF 函式，這個函式可以計算兩個日期之間的天數，然後使用 BETWEEN 條件（方案二和方案三）

解析

要知道過去 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         |
+-------------+--------------+

使用視窗函式，計算過去 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         |
+-------------+--------------+

計算過去 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       |
+-------------+-------------+----------------+

篩選出計算資料大於等於七天的資料

  - 需要知道表中日期最小的一天，作為臨時表 `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

LeetCode mysql 刷題四：餐館營業額變化增長——用自連和視窗函式 4 種 sql 實現過去 7 天的營業額

題目

解析

方法二

方法三

往期 MySQL 題目

相關文章