SQL查询在整个数据集的固定范围内平均

Question

我有一个这样的sql表:

日期tbody> <<tr>

温度
12-22-2022	23
12-21-2022	26
12-20-2022	27
12-19-2022	25
12-18-2022	26
12-17-2022	27
12-16-2022	29日
12-15-2022	28
12-14-2022	29日
12-13-2022	30
12-12-2022	27
12-11-2022	29日
12-10-2022	29日
12-09-2022	28
12-08-2022	31日
12-07-2022	32
12-06-2022	33
12-05-2022	34
12-04-2022	33
12-03-2022	32
12-02-2022	29日
12-01-2022	33
11-30-2022	31日
11-29-2022	33
11-28-2022	32

您可以按照以下步骤解决此问题:

• 转换您的日期格式"从VARCHAR到DATE
• 每7天在您的日期生成分区
• 每个分区的聚合，通过获取最大日期和平均温度

为了生成您的分区，您使用:

• ROW_NUMBER窗口函数，如果你的日期值总是连续的(每天都会出现一个新的温度)
• UNIX_TIMESTAMP函数，如果您的日期值不总是连续的(您忘记记录某些天的温度值)，则将日期标准化为实际周。

第一解决方案(小提琴):

WITH cte AS (
SELECT STR_TO_DATE(date_, '%m-%d-%Y') AS date_as_date,  
temperature 
FROM tab
), partitioned_dates AS (
SELECT *, (ROW_NUMBER() OVER(ORDER BY date_as_date DESC) -1) DIV 7 AS rn
FROM cte
)
SELECT MAX(date_as_date) AS week_ending_in_date,
AVG(temperature)  AS avg_temperature
FROM partitioned_dates
GROUP BY rn

第二方案(小提琴):

WITH cte AS (
SELECT STR_TO_DATE(date_, '%m-%d-%Y')                 AS date_as_date,
UNIX_TIMESTAMP(STR_TO_DATE(date_, '%m-%d-%Y')) AS integer_date,  
temperature 
FROM tab
), partitioned_dates AS (
SELECT *, (MAX(integer_date) OVER(ORDER BY integer_date DESC) - integer_date) DIV (60*60*24*7) AS rn
FROM cte
)
SELECT MAX(date_as_date) AS week_ending_in_date,
AVG(temperature)  AS avg_temperature
FROM partitioned_dates
GROUP BY rn

额外提示:

• 使用MySQL标准的日期:你存储在%mm-%dd-%yyyy格式，MySQL标准的日期是%dd-%mm-%yyyy。
• 不要将日期存储为VARCHAR。MySQL有DATE日期类型，它允许你使用一个广泛的函数工具包。
• 不要使用与保留的MySQL关键字匹配的名字来命名你的表和字段。具体来说，日期"字段名可能与DATE保留字冲突，您可能会遇到语法错误。

遵循MySQL规则将大大帮助你操作和节省你自己很多时间。