SQL思路解析：窗口滑動的應用

🎯 問題目標

第一步：從數據中我們能直接得到什么？

第二步：我們想要的“7天窗口”長什么樣？

第三步：SQL 怎么表達“某一天的前六天”？

🔍JOIN 比窗口函數更靈活

第四步：每個窗口要計算什么？

第五步：怎么避免不滿 7 天的窗口？

最終完整 SQL

表: 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 是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

?來源：Leecode

🎯 問題目標

先問自己最本質的問題：

?我想得到的到底是什么？

你想得到：

某一天（比如 2019-01-07）為窗口最后一天
以它為終點往前推 6 天（共 7 天）的所有消費數據
求這 7 天的總消費額和平均消費額（平均保留兩位小數）
然后按日期升序列出每個窗口的情況

第一步：從數據中我們能直接得到什么？

我們原始數據是：

| customer_id | name  | visited_on | amount |
|-------------|-------|------------|--------|
| 1           | Jhon  | 2019-01-01 | 100    |
| 2           | Daniel| 2019-01-02 | 110    |
| ...         | ...   | ...        | ...    |

這是“按顧客”記錄的交易數據。

原始數據是“每個顧客某天消費了多少”，而我們不關心顧客是誰，只關心每一天總共有多少消費。?

為了達成這個目標，你最小的可操作單位是：

? 每一天的“總營業額”

所以，第一步我們應該做的是：

SELECTvisited_on,SUM(amount) AS total_amount
FROM Customer
GROUP BY visited_on

?得到了：

| visited_on | total_amount |
|------------|--------------|
| 2019-01-01 | 100          |
| 2019-01-02 | 110          |
| 2019-01-03 | 120          |
| ...        | ...          |

第二步：我們想要的“7天窗口”長什么樣？

比如你想分析 2019-01-07 這個窗口，它包括：

2019-01-01
2019-01-02
2019-01-03
2019-01-04
2019-01-05
2019-01-06
2019-01-07

我們要把這 7 天的金額加總后求平均。

換句話說，對于每一個日期 D，你要去找所有日期 D'，滿足：

D' >= D - 6 天 AND D' <= D，然后求 sum(amount)

第三步：SQL 怎么表達“某一天的前六天”？

想象一下，窗口要對比誰和誰？

我們要讓每一行（例如日期是 2019-01-10）“看見”自己之前 6 天的數據。但 SQL 是面向集合的語言，每一行默認不能看見其他行。

怎么讓一行“看到”它前面的幾天？答案是：自連接（JOIN）！

SELECTc1.visited_on,             -- 作為窗口的當前“右端點”c2.visited_on,             -- 被掃描比較的行
FROM (SELECT visited_on, SUM(amount) AS daily_totalFROM CustomerGROUP BY visited_on
) c1
JOIN (SELECT visited_on, SUM(amount) AS daily_totalFROM CustomerGROUP BY visited_on
) c2ON c2.visited_on BETWEEN DATE_SUB(c1.visited_on, INTERVAL 6 DAY) AND c1.visited_on

這個 JOIN 的意思是：

對于每一行 c1，找出所有 c2，使得 c2.visited_on 落在 c2?之前 6 天之內。

也就是說，每一行 c1?會配對出一個 7 天的“時間窗口”數據集 c2。

就像下面這個例子：

c1.visited_on	c2.visited_on（符合條件）
2019-01-07	2019-01-01 ~ 2019-01-07
2019-01-08	2019-01-02 ~ 2019-01-08
2019-01-09	2019-01-03 ~ 2019-01-09
2019-01-10	2019-01-04 ~ 2019-01-10

你可以理解為：“c1?的每一天”，都配對了“過去七天的 c2”，這就模擬出“滑動窗口”的行為了！

🔍JOIN 比窗口函數更靈活

在“時間窗口”這種分析中，數據可能并不是每天都有，或者每天不止一條記錄，比如：

| visited_on   | amount |
|--------------|--------|
| 2024-01-01   | 100    |
| 2024-01-01   | 80     |
| 2024-01-03   | 200    |

這種不連續、一天多條的情況，用 OVER (ORDER BY visited_on ROWS ...) 是不靠譜的，因為行數 ≠ 時間！

而 JOIN 這種方式，直接按時間范圍配對，不依賴數據是否連續，每天有多少條都不影響。

第四步：每個窗口要計算什么？

你想要的就是：

c1.visited_on：當前窗口的最后一天
SUM(c2.amount)：這 7 天的總金額
ROUND(SUM(c2.amount) / 7, 2)：這 7 天的平均值（保留兩位小數）

第五步：怎么避免不滿 7 天的窗口？

比如當你分析 2019-01-02 時，它前面只有兩天的數據（01、02），這是 不滿 7 天的窗口，要排除掉。

這時候就要加一條語句：

HAVING COUNT(DISTINCT c2.visited_on) = 7

?意思是：只有當這 7 天真的有 7 個不同的日期數據，才納入最終結果。

最終完整 SQL

把上述分析組合起來，完整 SQL 如下：

SELECT c1.visited_on,SUM(c2.daily_total) AS amount,ROUND(SUM(c2.daily_total)/7, 2) AS average_amount
FROM (SELECT visited_on, SUM(amount) AS daily_totalFROM CustomerGROUP BY visited_on
) c1
JOIN (SELECT visited_on, SUM(amount) AS daily_totalFROM CustomerGROUP BY visited_on
) c2ON c2.visited_on BETWEEN DATE_SUB(c1.visited_on, INTERVAL 6 DAY) AND c1.visited_on
GROUP BY c1.visited_on
HAVING COUNT(c2.visited_on) = 7
ORDER BY c1.visited_on;

問題層級	解釋
本質問題	想知道某天 + 前六天的消費總和和平均
可直接獲取的數據	每天的顧客消費記錄（可匯總）
怎么形成7天窗口	用自連接 + 日期范圍：`BETWEEN D - 6 AND D`
如何計算	匯總 amount，平均除以 7 并 ROUND
如何過濾不滿7天窗口	`HAVING COUNT(DISTINCT c2.visited_on) = 7`
最終排序	按 visited_on 升序展示

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/diannao/85668.shtml
繁體地址，請注明出處：http://hk.pswp.cn/diannao/85668.shtml
英文地址，請注明出處：http://en.pswp.cn/diannao/85668.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！