如何去统计 date 的前五天和前 10 天中,num 的平均值?需要跳过周末,往前顺延天数(有 SQL 可查询到所有周末日期)
id num date
1 5 2022-07-23
2 11 2022-07-21
3 22 2022-06-11
4 12 2022-06-10
1
dcsuibian 2022-08-05 05:53:16 +08:00 via Android
SQL 难做的话,考虑放程序里
|
2
shadow1949 OP @dcsuibian 好像必须 SQL 脚本,用来生成图表啥的 T T
|
3
lybcyd 2022-08-05 07:27:10 +08:00 via Android
利用 weekday 函数筛选出工作日
|
4
evi1j 2022-08-05 07:46:11 +08:00 via Android
select avg(num) from (select num ...... and not exists (周末表) order by 日期 desc ) limit 5 性能贼差🙄
|
5
documentzhangx66 2022-08-05 08:09:26 +08:00
在存储过程中,把问题分解为单步过程,利用临时表与变量,像写 java 一样,慢慢写不行嘛
|
6
hay313955795 2022-08-05 08:15:20 +08:00
前五天或者前十天的话 是不是可以这样.
select AVG(num) from table INNER JOIN (select id from table where date_format(date,'%w')<6 and date >=DATE_SUB(NOW(),INTERVAL 30 DAY) ORDER BY date desc limit 5/10 )t1 on table.id = t1.id date_format(date,'%w')<6 查询日期中非周末的数据 and date >=DATE_SUB(NOW(),INTERVAL 30 DAY) 根据 5 天或者 10 天的取值范围 确定一个稍微大范围的日期 比如 5 天那取的时间范围就是 10 天. 10 天里最多也就 4 天是周末.抛去 4 天还有 6 天也能满足.当然这个时间范围可以再进行调整. 我用的是 mysql |
7
Qy2FbR 2022-08-05 08:16:25 +08:00 via Android
搞个 view 让 所有周末的值都是 0 , 取过去七天和十四天的平均值不就得了
|
8
cnhongwei 2022-08-05 08:23:06 +08:00
t1 一张虚拟表,有所有的日期, t2 一张虚拟表,有所有的日期,和是否是星期日, t1 和 t2 join ,条件是 t2 的日期 > t1 的日期并小于 t1 的日期 + 14 天(10 天内最多两个周末),这样得到 t3 ,对 t3 做窗口函数,计算 t1 和 t2 日期相差的天数, sum(不是周末)的记录数。再过滤相差天数为 10 的记录,这样得到 t4 开始日期 (来自 t1) 结束日期(来自 t2),这个天再和你现在的表进行 join ,并 sum(num)就可以了。
|
9
wxf666 2022-08-05 08:26:16 +08:00
『前五天』包括当天吗?比如,2022-07-23 『前五天』是( 23, 22, 21, 20, 19 )还是( 22, 21, 20, 19, 18 )?
『跳过周末,往前顺延』要将周末计算在内吗?比如,是(周一日六五四三二)还是(周一五四三二)? |
10
shadow1949 OP |
11
gongshuiwen 2022-08-05 08:51:04 +08:00
如果只是求日期前 5 天,可以查询日期前 7 天的记录,然后排除掉周末即可,原因在于任意连续的 7 天必定只包含一个周末,同理 10 天的话查询 14 天的数据,如果是非 5 的整数倍天数该方法不适用。
这是查询当前日期前 5 天的示例,注意不包括当天,查指定日期则把 NOW()替换掉即可: ```SQL SELECT sum( num ) / 5 FROM test WHERE date < NOW() and date >= date_sub(NOW(), INTERVAL 7 DAY ) AND weekday( date ) < 5; ``` |
12
cccmm 2022-08-05 08:52:17 +08:00 via Android
date 可重复? date 不一定连续?
提供一种思路 用这张带 num 的表 join 可以查询可以找到所有周末的 sql 得到只包含工作日的 num 和 date 的数据,让后按 date groupby 求出同一天的 num 平均值 再做出按日期倒序的 rowno 字段 row_number() over(order by date) |
13
gongshuiwen 2022-08-05 08:53:21 +08:00
@shadow1949 包括当前日期的话改一下查询范围即可:
SELECT sum( num ) / 5 FROM test WHERE date <= NOW() and date > date_sub(NOW(), INTERVAL 7 DAY ) AND weekday( date ) < 5; |
14
cccmm 2022-08-05 08:53:55 +08:00 via Android
取前五天 10 天只需要 rowno 小雨=5 或者大雨等于 10 然后 avg
|
15
andrew2558 2022-08-05 08:54:16 +08:00
```mssql
SELECT AVG(num * 1.0) AS numavg FROM ( SELECT TOP 5 *, 1 AS t FROM test1 -- 过滤掉记录中所有星期六,星期天的 id WHERE id NOT IN ( -- 获取所有星期六星期日的 id SELECT id FROM test1 WHERE DATEPART(weekday, date) IN (6, 7) ) ORDER BY date ) tt GROUP BY t ``` 此代码在 sql 中可行 |
16
jucelin 2022-08-05 09:00:50 +08:00
必须 SQL 的话,可以用存储过程
复杂的 SQL 后期根本看不懂 |
17
reter 2022-08-05 09:07:45 +08:00
SQL 适合数据处理,清晰的业务处理,但不适合嵌入复杂的业务逻辑。比如考虑节假日,这明显就不适合纯 SQL 做。
|
18
vvtf 2022-08-05 09:10:52 +08:00
# 5 天, 因为去掉周末, 所以倒推 7 天, 10 天倒推 14 天
select avg(num) from table_name where `date`>date_sub({someday},interval 7 day) and `date`<={someday} and weekday(`date`)<5; # 10 天 select avg(num) from table_name where `date`>date_sub({someday},interval 14 day) and `date`<={someday} and weekday(`date`)<5; |
19
chendl111 2022-08-05 09:27:25 +08:00
用临时表筛选非周末的数据然后查询即可
|
20
wxf666 2022-08-05 09:39:14 +08:00
数据库新手试答一下
前面有大佬说了,任意连续 7 天必包含周六周日。只需算过去 7 天非周六周日的平均数即可 『 MySQL 语法(应该是这样吧)』: WITH orig_data(date, num) AS ( VALUES ROW('2022-07-29', 1), ROW('2022-07-23', 5), ROW('2022-07-30', 2), ROW('2022-07-21', 11), ROW('2022-07-31', 3), ROW('2022-06-11', 22), ROW('2022-08-01', 4), ROW('2022-06-10', 12), ROW('2022-08-02', 5), ROW('2022-08-03', 6), ROW('2022-08-04', 7), ROW('2022-08-05', 8) ) SELECT *, avg(CASE WHEN weekday(date) < 5 THEN num ELSE null END) OVER (ORDER BY date(date) RANGE INTERVAL 6 DAY PRECEDING) avg FROM orig_data 『结果』: date num avg ————— —— ———— 2022-06-10 12 12.0000 2022-06-11 22 12.0000 2022-07-21 11 11.0000 2022-07-23 5 11.0000 2022-07-29 1 1.0000 2022-07-30 2 1.0000 2022-07-31 3 1.0000 2022-08-01 4 2.5000 2022-08-02 5 3.3333 2022-08-03 6 4.0000 2022-08-04 7 4.6000 2022-08-05 8 6.0000 |
21
wxf666 2022-08-05 09:43:10 +08:00
马也,题目还有补充信息。。还要算节假日?
|
22
wengyanbin 2022-08-05 09:56:22 +08:00
我选择把过去五天的日期在 sql 外的程序生成后再传进去,
select avg(num) from table where date in(生成的日期列表); |
23
TUNGH 2022-08-05 10:37:55 +08:00
你可以先把节假日数据的 id 找出来,然后 not in 就行了,这样的话就简单很多了
|
24
wxf666 2022-08-05 11:07:06 +08:00
@shadow1949 ,我还好奇一件事
假如表中就两项: 1. 2022-08-05 (周五) num: 100 2. 2022-08-06 (周六) num: 200 周六前五天,num 的平均值,你计划算出啥结果? 1. null 2. 不出现在结果列表 3. (100) / 1 = 100 (因为跳过周末) 4. (200 + 100) / 2 = 150 (因为包括当天) |
25
shadowking 2022-08-05 11:23:17 +08:00
select avg(num) from
|
26
shadowking 2022-08-05 11:28:00 +08:00
@shadowking select avg(num) from num_table t1 where t1.date not in (select date from weekday_table t2 where t2.date < now()) and t1.date < now() order by t1.date desc limit 5 ,大概是这样吧,但是性能就不能看了
|
27
xidianwubo12138 2022-08-05 15:45:31 +08:00
spark sql 我倒是有办法给你实现,mysql 不太熟悉
|
28
hjq632233317 2022-08-05 19:14:24 +08:00
存表把礼拜几存进去呗 想查周几查周几
|
29
wxf666 2022-08-05 23:08:04 +08:00
@shadow1949 ,为嘛你第二条附言里的『 query sql 』,结果看着不太对呢?
id date num avg_num —— ———— ——— ———— 01 2022-09-01 12 12.0000 13 2022-09-20 11 2.2000 『不应该是 11 / 1 = 11 么』 14 2022-09-21 05 3.2000 『(11+5) / 2 = 8 ?』 15 2022-09-22 23 7.8000 『(11+5+23) / 3 = 13 ?』 16 2022-09-23 42 16.2000 『(11+5+23+42) / 4 = 20.25 ?』 结果里缺失了『 2022-09-03 』『 2022-09-24 』,看来 24 楼里的问题,你的计划应该是『节假日不出现在结果列表里』 我总觉得可以『「一条」普通 SQL 语句』搞定这个问题。等我试试 |
30
shadow1949 OP @wxf666
缺失这两天,因为它们属于周末或者节假日,不计算这两天的数据。 计算结果: 9.1:12/1 (属于边界值) 9.20:11/5 9.21:(11 + 5)/5 … 依次往下类推,为什么是除以 5 ,是因为我们算得是近 5 天的平均值,只要当天是工作日,虽然没记录,其实相当于 num=0 。 |
31
wxf666 2022-08-06 02:22:48 +08:00
@shadow1949 搞出来了,『「一条」普通 SQL 语句』
不用「建表、存储过程、事务」,连 SQLite 都能胜任的,普通语句。 去掉「节假日数据」和「测试数据」后,大概 20 行 『 SQLite 语法(排版原因,记得去掉每行开头的 全角空格),改成 MySQL 应该也很容易』 WITH RECURSIVE -- 节假日数据(默认周六周日是假日。若有调休、其他假日,在此表指定) holiday(date, is_holiday) AS ( VALUES ('2022-09-12', true), ('2022-10-01', true), ('2022-10-04', true), ('2022-10-07', true), ('2022-10-02', true), ('2022-10-05', true), ('2022-10-08', false), ('2022-10-03', true), ('2022-10-06', true), ('2022-10-09', false) ), -- 测试数据(日期,数据) test_data(date, num) AS ( VALUES ('2022-09-01', 12), ('2022-09-22', 23), ('2022-09-03', 22), ('2022-09-23', 42), ('2022-09-20', 11), ('2022-09-24', 11), ('2022-09-21', 5) ), -- 九月份日历(此表有 30 行数据) calendar(date) AS ( SELECT '2022-09-01' UNION ALL SELECT date(date, '+1 day') FROM calendar WHERE date < '2022-09-30' ), -- 根据日历,生成工作日数据(日期,该天是否为工作日) workday(date, is_workday) AS ( SELECT date, COALESCE(NOT is_holiday, 0 + strftime('%w', date) BETWEEN 1 AND 5) FROM calendar LEFT JOIN holiday USING(date) ) -- 1. 根据工作日日历,测试数据中丢弃节假日的行,补充其他缺失工作日的行(这些新行的 num IS NULL ) -- 2. 利用范围为当前行及之前 4 行的窗口函数,按照日期顺序,滑动计算窗口内的平均值 -- 3. 丢弃第 1 步中,补充的行(即 num IS NULL 的行) SELECT * FROM ( SELECT date, num, avg(COALESCE(num, 0)) OVER win avg, format('(%s) / %d', group_concat(num, '+') OVER win, COUNT(*) OVER win) expr FROM workday LEFT JOIN test_data USING(date) WHERE is_workday WINDOW win AS (ORDER BY date ROWS 4 PRECEDING) ) WHERE num IS NOT NULL; 『输出』 date num avg expr ————— —— —— ————————— 2022-09-01 12 12.0 (12) / 1 2022-09-20 11 02.2 (11) / 5 2022-09-21 05 03.2 (11+5) / 5 2022-09-22 23 07.8 (11+5+23) / 5 2022-09-23 42 16.2 (11+5+23+42) / 5 |
32
wxf666 2022-08-07 10:32:32 +08:00
@shadow1949 ,改写成了 MySQL ,将查询放进了一个视图中。
如果用 SQL 的话,以后一行「 select * from view_xxx 」就可得到结果了 使用前,记得将「调休」「非周末的其他假日」添加进『 holiday 表』 『「 select * from view_xxx 」结果』 date num avg_5 avg_10 ————— —— ———— ———— 2022-09-01 12 12.0000 12.0000 2022-09-20 11 02.2000 01.1000 2022-09-21 05 03.2000 01.6000 2022-09-22 23 07.8000 03.9000 2022-09-23 42 16.2000 08.1000 『 MySQL 语法(排版原因,记得去掉每行开头的 全角空格)』 -- 节假日数据(默认周六周日是假日。若有调休、其他假日,在此表指定) CREATE TABLE holiday(date DATE PRIMARY KEY, is_holiday BOOL NOT NULL) AS SELECT * FROM ( VALUES -- 中秋放假 ROW('2022-09-12', true), -- 国庆放假和调休 ROW('2022-10-01', true), ROW('2022-10-04', true), ROW('2022-10-07', true), ROW('2022-10-02', true), ROW('2022-10-05', true), ROW('2022-10-08', false), ROW('2022-10-03', true), ROW('2022-10-06', true), ROW('2022-10-09', false) ) AS v(date, is_holiday); -- 原始数据(日期、数据) CREATE TABLE orig_data(date DATE PRIMARY KEY, num INT NOT NULL) AS SELECT * FROM ( VALUES ROW('2022-09-01', 12), ROW('2022-09-03', 22), ROW('2022-09-20', 11), ROW('2022-09-21', 5), ROW('2022-09-22', 23), ROW('2022-09-23', 42), ROW('2022-09-24', 11) ) AS v(date, num); -- 原始数据的各种平均值视图 CREATE VIEW avgs_of_data AS WITH RECURSIVE -- 根据原始数据的日期范围,生成日历 -- (如果超过 1000 天,记得调整 cte_max_recursion_depth ) calendar(date) AS ( SELECT min(date) FROM orig_data UNION ALL SELECT DATE_ADD(date, INTERVAL 1 DAY) FROM calendar WHERE date < (SELECT max(date) FROM orig_data) ), -- 根据日历,生成工作日数据(日期、该天是否为工作日) workday(date, is_workday) AS ( SELECT date, COALESCE(NOT is_holiday, weekday(date) < 5) FROM calendar LEFT JOIN holiday USING(date) ), -- 为每个工作日,计算最近 5 或 10 个工作日内的平均值(日期、数值、5 工作日均值、10 工作日均值) avgs_of_workday(date, num, avg_5, avg_10) AS ( SELECT date, num, avg(COALESCE(num, 0)) OVER (ORDER BY date ROWS 4 PRECEDING), avg(COALESCE(num, 0)) OVER (ORDER BY date ROWS 9 PRECEDING) FROM workday LEFT JOIN orig_data USING(date) WHERE is_workday ) -- 去除没有数据的工作日 SELECT * FROM avgs_of_workday WHERE num IS NOT NULL; MySQL 不支持在「窗口函数」中使用「 group_concat 」,所以没有「(11+5+23) / 5 」之类的结果了。 但对你的需求无影响,只是方便查看平均值的计算过程是否正确。 |
33
shadow1949 OP |
34
wxf666 2022-08-09 01:20:38 +08:00
@shadow1949 用不了窗口函数,强行自己模拟,代码看起来会很臭。。
换个思路,如果你能自己维护个『第几个工作日表』,也能很舒服。比如: 日期 第几个工作日 ————— ——————— 09-09 周五 1 09-10 中秋 (不要这行) 09-11 周日 (不要这行) 09-12 周一 (不要这行) 09-13 周二 2 09-14 周三 3 09-15 周四 4 09-16 周五 5 09-17 周六 (不要这行) 『 大致 SQL (排版原因,记得去掉每行开头的 全角空格)』 WITH workday_data(date, nth, num) AS ( SELECT date, nth, num FROM nth_workday LEFT JOIN orig_data USING(date) ) SELECT date, num, (SELECT avg(COALESCE(num, 0)) FROM workday_data WHERE nth BETWEEN today.nth - 4 AND today.nth) avg_5, (SELECT avg(COALESCE(num, 0)) FROM workday_data WHERE nth BETWEEN today.nth - 9 AND today.nth) avg_10 FROM workday_data today WHERE num IS NOT NULL; |