V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
shadow1949
V2EX  ›  程序员

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

  •  
  •   shadow1949 · 11 天前 · 2587 次点击

    如何去统计 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 条附言  ·  11 天前
    大家的回复我都一一看过了,待会一个个试下,这里补充点题目信息。

    遇到周末和节假日要向前顺延天数,然后日期是不重复,可不连续的,然后需要边界条件时,比如查询 1 月 1 日之后的平均数据时,那 1 月 3 日是按 sum/3 来算的(如果没有节假日和周末)

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

    最后辛苦大家了。
    第 2 条附言  ·  11 天前
    这边用 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;
    第 3 条附言  ·  11 天前
    这是 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);
    34 条回复    2022-08-09 01:20:38 +08:00
    dcsuibian
        1
    dcsuibian  
       11 天前 via Android
    SQL 难做的话,考虑放程序里
    shadow1949
        2
    shadow1949  
    OP
       11 天前
    @dcsuibian 好像必须 SQL 脚本,用来生成图表啥的 T T
    lybcyd
        3
    lybcyd  
       11 天前 via Android
    利用 weekday 函数筛选出工作日
    evi1j
        4
    evi1j  
       11 天前 via Android
    select avg(num) from (select num ...... and not exists (周末表) order by 日期 desc ) limit 5 性能贼差🙄
    documentzhangx66
        5
    documentzhangx66  
       11 天前
    在存储过程中,把问题分解为单步过程,利用临时表与变量,像写 java 一样,慢慢写不行嘛
    hay313955795
        6
    hay313955795  
       11 天前
    前五天或者前十天的话 是不是可以这样.

    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
        7
    Qy2FbR  
       11 天前 via Android
    搞个 view 让 所有周末的值都是 0 , 取过去七天和十四天的平均值不就得了
    cnhongwei
        8
    cnhongwei  
       11 天前
    t1 一张虚拟表,有所有的日期, t2 一张虚拟表,有所有的日期,和是否是星期日, t1 和 t2 join ,条件是 t2 的日期 > t1 的日期并小于 t1 的日期 + 14 天(10 天内最多两个周末),这样得到 t3 ,对 t3 做窗口函数,计算 t1 和 t2 日期相差的天数, sum(不是周末)的记录数。再过滤相差天数为 10 的记录,这样得到 t4 开始日期 (来自 t1) 结束日期(来自 t2),这个天再和你现在的表进行 join ,并 sum(num)就可以了。
    wxf666
        9
    wxf666  
       11 天前
    『前五天』包括当天吗?比如,2022-07-23 『前五天』是( 23, 22, 21, 20, 19 )还是( 22, 21, 20, 19, 18 )?

    『跳过周末,往前顺延』要将周末计算在内吗?比如,是(周一日六五四三二)还是(周一五四三二)?
    shadow1949
        10
    shadow1949  
    OP
       11 天前
    @wxf666
    前五天包括当前,是第一个;
    周末不记在内,是第二个。
    gongshuiwen
        11
    gongshuiwen  
       11 天前
    如果只是求日期前 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
        12
    cccmm  
       11 天前 via Android
    date 可重复? date 不一定连续?
    提供一种思路
    用这张带 num 的表 join 可以查询可以找到所有周末的 sql 得到只包含工作日的 num 和 date 的数据,让后按 date groupby 求出同一天的 num 平均值 再做出按日期倒序的 rowno 字段 row_number() over(order by date)
    gongshuiwen
        13
    gongshuiwen  
       11 天前
    @shadow1949 包括当前日期的话改一下查询范围即可:

    SELECT sum( num ) / 5 FROM test
    WHERE date <= NOW() and date > date_sub(NOW(), INTERVAL 7 DAY ) AND weekday( date ) < 5;
    cccmm
        14
    cccmm  
       11 天前 via Android
    取前五天 10 天只需要 rowno 小雨=5 或者大雨等于 10 然后 avg
    andrew2558
        15
    andrew2558  
       11 天前
    ```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
        16
    jucelin  
       11 天前
    必须 SQL 的话,可以用存储过程
    复杂的 SQL 后期根本看不懂
    reter
        17
    reter  
       11 天前
    SQL 适合数据处理,清晰的业务处理,但不适合嵌入复杂的业务逻辑。比如考虑节假日,这明显就不适合纯 SQL 做。
    vvtf
        18
    vvtf  
       11 天前
    # 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
        19
    chendl111  
       11 天前
    用临时表筛选非周末的数据然后查询即可
    wxf666
        20
    wxf666  
       11 天前
    数据库新手试答一下

    前面有大佬说了,任意连续 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
        21
    wxf666  
       11 天前
    马也,题目还有补充信息。。还要算节假日?
    wengyanbin
        22
    wengyanbin  
       11 天前
    我选择把过去五天的日期在 sql 外的程序生成后再传进去,
    select avg(num) from table where date in(生成的日期列表);
    TUNGH
        23
    TUNGH  
       11 天前
    你可以先把节假日数据的 id 找出来,然后 not in 就行了,这样的话就简单很多了
    wxf666
        24
    wxf666  
       11 天前
    @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
        25
    shadowking  
       11 天前
    select avg(num) from
    shadowking
        26
    shadowking  
       11 天前
    @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
        27
    xidianwubo12138  
       11 天前
    spark sql 我倒是有办法给你实现,mysql 不太熟悉
    hjq632233317
        28
    hjq632233317  
       11 天前
    存表把礼拜几存进去呗 想查周几查周几
    wxf666
        29
    wxf666  
       11 天前
    @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
        30
    shadow1949  
    OP
       11 天前 via Android
    @wxf666
    缺失这两天,因为它们属于周末或者节假日,不计算这两天的数据。
    计算结果:
    9.1:12/1 (属于边界值)
    9.20:11/5
    9.21:(11 + 5)/5

    依次往下类推,为什么是除以 5 ,是因为我们算得是近 5 天的平均值,只要当天是工作日,虽然没记录,其实相当于 num=0 。
    wxf666
        31
    wxf666  
       11 天前
    @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
        32
    wxf666  
       9 天前
    @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
        33
    shadow1949  
    OP
       8 天前
    @wxf666
    仔细看了下,发现 MySQL 窗口函数是在 8.0 版本之后才出的。
    我们数据库用得 5 版本,故用不了。
    感谢提供思路,是可行的。
    wxf666
        34
    wxf666  
       8 天前
    @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;
    关于   ·   帮助文档   ·   API   ·   FAQ   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   1229 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 19:17 · PVG 03:17 · LAX 12:17 · JFK 15:17
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.