一、問題描述

本題給定了一個名為?Customer?的表，記錄了餐館顧客的交易數據，包括顧客 ID、姓名、訪問日期和消費金額。作為餐館老板，我們的任務是分析營業額的變化增長情況，具體來說，就是計算以 7 天（某日期 + 該日期前的 6 天）為一個時間段的顧客消費平均值，并按訪問日期升序排序，同時要保留兩位小數。

二、表結構分析

Customer?表的結構如下：

列名	類型	說明
customer_id	int	顧客的唯一標識，用于區分不同的顧客。
name	varchar	顧客的姓名。
visited_on	date	顧客訪問餐館的日期，與?customer_id?一起構成表的主鍵，確保每一條記錄的唯一性。
amount	int	顧客在當天的消費總額。

三、解題思路

1. 確定時間段：要計算以 7 天為一個時間段的消費情況，我們需要對每個日期，找到其前 6 天以及當天的所有消費記錄。

2. 計算消費總和：對于每個確定的 7 天時間段，計算該時間段內的消費總額。

3. 計算平均值：根據消費總和，計算出該 7 天時間段的平均消費金額，并保留兩位小數。

4. 結果排序：將計算得到的每個 7 天時間段的訪問日期、消費總額和平均消費金額按訪問日期升序排列輸出。

為了實現以上思路，我們可以使用 SQL 中的窗口函數來簡化計算過程，窗口函數可以在不改變表結構的情況下，對數據進行分組和聚合操作。

四、SQL 代碼實現

-- 選擇訪問日期、消費總額和平均消費金額
SELECT visited_on,amount,-- 保留兩位小數ROUND(amount / 7, 2) AS average_amount
FROM (-- 子查詢計算每個 7 天時間段的消費總額SELECT visited_on,SUM(amount) AS amountFROM (-- 使用窗口函數計算每個日期及其前 6 天的消費總和SELECT visited_on,SUM(amount) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS amountFROM Customer) sub1WHERE visited_on >= (SELECT MIN(visited_on) FROM Customer + INTERVAL 6 DAY)GROUP BY visited_on) sub2
ORDER BY visited_on;

五、代碼詳細解釋

1、最內層子查詢：

SELECT visited_on,SUM(amount) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS amount
FROM Customer

這里使用了窗口函數?SUM(amount) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)。OVER?子句指定了窗口的定義，ORDER BY visited_on?表示按照訪問日期進行排序，ROWS BETWEEN 6 PRECEDING AND CURRENT ROW?表示窗口的范圍是當前行以及其前 6 行。所以這個子查詢的作用是計算每個日期及其前 6 天的消費總和，并將結果命名為?amount。

2、中間子查詢：

SELECT visited_on,SUM(amount) AS amount
FROM (最內層子查詢) sub1
WHERE visited_on >= (SELECT MIN(visited_on) FROM Customer + INTERVAL 6 DAY)
GROUP BY visited_on

這個子查詢首先從最內層子查詢的結果中選擇?visited_on?和?amount?列。WHERE?子句用于篩選出訪問日期大于等于最早訪問日期加上 6 天的記錄，因為在最早訪問日期加上 6 天之前的日期無法構成完整的 7 天時間段。然后使用?GROUP BY visited_on?對結果按訪問日期進行分組，并再次計算每個分組（即每個 7 天時間段）的消費總額，將結果命名為?amount。

3、最外層查詢：

SELECT visited_on,amount,ROUND(amount / 7, 2) AS average_amount
FROM (中間子查詢) sub2
ORDER BY visited_on;

最外層查詢從中間子查詢的結果中選擇?visited_on、amount?列，并使用?ROUND(amount / 7, 2)?計算平均消費金額，保留兩位小數，并將結果命名為?average_amount。最后使用?ORDER BY visited_on?按訪問日期升序排列結果。

六、復雜度分析

1. 時間復雜度：假設?Customer?表中有?n?條記錄。最內層子查詢使用窗口函數，對于每一行數據，窗口函數的計算時間復雜度為?(O(1))（因為窗口范圍固定為 7 行），所以最內層子查詢的時間復雜度為?(O(n))。中間子查詢對最內層子查詢的結果進行分組和篩選，時間復雜度也為?(O(n))。最外層查詢對中間子查詢的結果進行簡單的選擇和排序，排序的時間復雜度為?(O(n log n))。綜合來看，總的時間復雜度為?(O(n log n))。

2. 空間復雜度：在查詢過程中，我們沒有使用額外的與數據規模相關的空間，只是對表中的數據進行了讀取和處理，因此空間復雜度為?(O(1))。

七、測試用例驗證

1、輸入數據：

-- 插入示例數據
INSERT INTO Customer (customer_id, name, visited_on, amount) VALUES
(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);

2、預期輸出：

+--------------+--------------+----------------+
| 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         |
+--------------+--------------+----------------+

3、驗證過程：將上述 SQL 代碼在數據庫中運行，將得到的結果與預期輸出進行對比，如果結果一致，則說明我們的代碼實現是正確的。

感謝各位的閱讀，后續將持續給大家講解力扣中的算法題和數據庫題，如果覺得這篇內容對你有幫助，別忘了點贊和關注，后續還有更多精彩的算法解析與你分享！