SQL 苦手来请教各位大佬了。

[shadow1949]

如何去统计 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 月 1 日之后的平均数据时，那 1 月 3 日是按 sum/3 来算的（如果没有节假日和周末）

平时写 SQL 真少，想了半天没有想出来如何实现。T T

最后辛苦大家了。

这边用 MySQL 实现了，但是最后 Leader 决定，让我用 Java 代码来实现……
以下是实现（仅供参考，没考虑其他，只想着实现了）：
1. 先获取所有工作日，然后编号排序（包括无数据的工作日）
2. 然后关联 num 表，没有数据的工作日补 0
3. 将最后查出来的表进行自关联，1 条日期数据可对应 5 条数据（近 5 天），再聚合取平均

-- create procedure
delimiter $$$
create procedure workday()
begin

declare mydate DATE;
set mydate='2022-09-01';

start transaction;
-- workday table
CREATE TEMPORARY TABLE workday (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

while (mydate < '2022-10-01') do
if (select count(1) from weekend where `date` = mydate) <= 0 then
INSERT INTO `ovo`.`workday`(`date`) VALUES (mydate);
end if;
set mydate= date_add(mydate, interval 1 day);
end while;

create TEMPORARY table newTable
(
select w.id id, w.date date, if(n.num is null,0,n.num) num from workday w left join num n
on w.date = n.date order by w.id desc
);

create TEMPORARY table newTable2
(
select w.id id, w.date date, if(n.num is null,0,n.num) num from workday w left join num n
on w.date = n.date order by w.id desc
);

commit;

end
$$$
delimiter;


-- invork procedure
call workday();


-- query sql
SELECT t.id,t.date,t.num,sum(t.avg_cnt)/count(1) avg_num
from
(select b.id,b.date,b.num,a.num as avg_cnt from newTable as a
right join newTable2 b on b.id between a.id and a.id + 4
order by b.id) t
where t.num > 0
group by t.id, t.date, t.num
order by t.id;

这是 Demo 数据，有兴趣的小伙伴可以自己试试。

CREATE TABLE `num` (
`date` date DEFAULT NULL,
`num` int(11) NOT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `ovo`.`num`(`date`, `num`, `id`) VALUES ('2022-09-21', 5, 1);
INSERT INTO `ovo`.`num`(`date`, `num`, `id`) VALUES ('2022-09-20', 11, 2);
INSERT INTO `ovo`.`num`(`date`, `num`, `id`) VALUES ('2022-09-03', 22, 3);
INSERT INTO `ovo`.`num`(`date`, `num`, `id`) VALUES ('2022-09-01', 12, 4);
INSERT INTO `ovo`.`num`(`date`, `num`, `id`) VALUES ('2022-09-22', 23, 5);
INSERT INTO `ovo`.`num`(`date`, `num`, `id`) VALUES ('2022-09-23', 42, 6);
INSERT INTO `ovo`.`num`(`date`, `num`, `id`) VALUES ('2022-09-24', 11, 7);


CREATE TABLE `weekend` (
`date` date DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;


INSERT INTO `ovo`.`weekend`(`date`, `id`) VALUES ('2022-09-03', 1);
INSERT INTO `ovo`.`weekend`(`date`, `id`) VALUES ('2022-09-04', 2);
INSERT INTO `ovo`.`weekend`(`date`, `id`) VALUES ('2022-09-10', 3);
INSERT INTO `ovo`.`weekend`(`date`, `id`) VALUES ('2022-09-11', 4);
INSERT INTO `ovo`.`weekend`(`date`, `id`) VALUES ('2022-09-12', 5);
INSERT INTO `ovo`.`weekend`(`date`, `id`) VALUES ('2022-09-17', 6);
INSERT INTO `ovo`.`weekend`(`date`, `id`) VALUES ('2022-09-18', 7);
INSERT INTO `ovo`.`weekend`(`date`, `id`) VALUES ('2022-09-24', 8);
INSERT INTO `ovo`.`weekend`(`date`, `id`) VALUES ('2022-09-25', 9);

[dcsuibian]

SQL 难做的话，考虑放程序里

[shadow1949]

@dcsuibian 好像必须 SQL 脚本，用来生成图表啥的 T T

[lybcyd]

利用 weekday 函数筛选出工作日

[evi1j]

select avg(num) from (select num ...... and not exists (周末表) order by 日期 desc ) limit 5 性能贼差🙄

[documentzhangx66]

在存储过程中，把问题分解为单步过程，利用临时表与变量，像写 java 一样，慢慢写不行嘛

[hay313955795]

前五天或者前十天的话 是不是可以这样.

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

[Qy2FbR]

搞个 view 让 所有周末的值都是 0 ， 取过去七天和十四天的平均值不就得了

[cnhongwei]

t1 一张虚拟表，有所有的日期, t2 一张虚拟表，有所有的日期，和是否是星期日, t1 和 t2 join ，条件是 t2 的日期 > t1 的日期并小于 t1 的日期 + 14 天(10 天内最多两个周末),这样得到 t3 ，对 t3 做窗口函数，计算 t1 和 t2 日期相差的天数, sum(不是周末)的记录数。再过滤相差天数为 10 的记录，这样得到 t4 开始日期 (来自 t1) 结束日期(来自 t2)，这个天再和你现在的表进行 join ，并 sum(num)就可以了。

[wxf666]

『前五天』包括当天吗？比如，2022-07-23 『前五天』是（ 23, 22, 21, 20, 19 ）还是（ 22, 21, 20, 19, 18 ）？

『跳过周末，往前顺延』要将周末计算在内吗？比如，是（周一日六五四三二）还是（周一五四三二）？

[shadow1949]

@wxf666
前五天包括当前，是第一个；
周末不记在内，是第二个。

[gongshuiwen]

如果只是求日期前 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;
```

[cccmm]

date 可重复？ date 不一定连续？
提供一种思路
用这张带 num 的表 join 可以查询可以找到所有周末的 sql 得到只包含工作日的 num 和 date 的数据，让后按 date groupby 求出同一天的 num 平均值 再做出按日期倒序的 rowno 字段 row_number() over(order by date)

[gongshuiwen]

@shadow1949 包括当前日期的话改一下查询范围即可：

SELECT sum( num ) / 5 FROM test
WHERE date <= NOW() and date > date_sub(NOW(), INTERVAL 7 DAY ) AND weekday( date ) < 5;

[cccmm]

取前五天 10 天只需要 rowno 小雨=5 或者大雨等于 10 然后 avg

[andrew2558]

```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 中可行

[jucelin]

必须 SQL 的话，可以用存储过程
复杂的 SQL 后期根本看不懂

[reter]

SQL 适合数据处理，清晰的业务处理，但不适合嵌入复杂的业务逻辑。比如考虑节假日，这明显就不适合纯 SQL 做。

[vvtf]

# 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;

[chendl111]

用临时表筛选非周末的数据然后查询即可

[wxf666]

数据库新手试答一下

前面有大佬说了，任意连续 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

[wxf666]

马也，题目还有补充信息。。还要算节假日？

[wengyanbin]

我选择把过去五天的日期在 sql 外的程序生成后再传进去，
select avg(num) from table where date in(生成的日期列表);

[TUNGH]

你可以先把节假日数据的 id 找出来,然后 not in 就行了,这样的话就简单很多了

[wxf666]

@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 （因为包括当天）

[shadowking]

select avg(num) from

[shadowking]

@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 ，大概是这样吧，但是性能就不能看了

[xidianwubo12138]

spark sql 我倒是有办法给你实现，mysql 不太熟悉

[hjq632233317]

存表把礼拜几存进去呗 想查周几查周几

[wxf666]

@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 语句』搞定这个问题。等我试试

[shadow1949]

@wxf666
缺失这两天，因为它们属于周末或者节假日，不计算这两天的数据。
计算结果：
9.1：12/1 （属于边界值）
9.20：11/5
9.21：(11 ＋ 5)/5
…
依次往下类推，为什么是除以 5 ，是因为我们算得是近 5 天的平均值，只要当天是工作日，虽然没记录，其实相当于 num=0 。

[wxf666]

@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

[wxf666]

@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 」之类的结果了。

但对你的需求无影响，只是方便查看平均值的计算过程是否正确。

[shadow1949]

@wxf666
仔细看了下，发现 MySQL 窗口函数是在 8.0 版本之后才出的。
我们数据库用得 5 版本，故用不了。
感谢提供思路，是可行的。

[wxf666]

@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;