Oracle以周五为一周的第一天统计数量

七的不是海

于 2022-12-09 16:35:25 发布

阅读量475

点赞数

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/weixin_50478033/article/details/128254272

版权

需求：以每年的1月1号，如2022-01-01，开始为第一周，第二周从每年的第一个周五开始计算，若每年的1月1号刚好是周五的情况，则第一周从每年的第一个周五开始计算；每年的最后一个周五到下一年的1月1号为最后一周。

1、取某天之后的下一个周五的日期

select next_day(sysdate, '星期五')  from dual

2、取某一年的所有周五的日期

SELECT WW
  FROM (select ww
          from (select distinct (TRUNC(dt, 'd') + 5) ww
                  from (select (to_date('2022-01-01', 'yyyy-mm-dd') + rownum - 1) dt
                          from dual
                        connect by rownum <=
                                   to_date('2022-12-31', 'yyyy-mm-dd') -
                                   to_date('2022-01-01', 'yyyy-mm-dd') + 1)) aa
         order by ww)

比如查询2022年里的所有周五的日期，会发现查出了2021年最后一个周五的日期【2021-12-31】，不符合需求

3、解决方案如下：

添加【WW BETWEEN TO_DATE('2022-01-01', 'yyyy-mm-dd') AND TO_DATE('2022-12-31', 'yyyy-mm-dd')】筛选出这一年的所有周五，单独查出1月1号，

SELECT TO_DATE('2022-01-01', 'yyyy-mm-dd') AS WW FROM DUAL

再通过UNION ALL的方式连接起来，记得去重，即可得到对应的周次

SELECT *
  FROM (WITH S1 AS (SELECT WW
                      FROM (select ww
                              from (select distinct (TRUNC(dt, 'd') + 5) ww
                                      from (select (to_date('2022-01-01',
                                                            'yyyy-mm-dd') +
                                                   rownum - 1) dt
                                              from dual
                                            connect by rownum <=
                                                       to_date('2022-12-31',
                                                               'yyyy-mm-dd') -
                                                       to_date('2022-01-01',
                                                               'yyyy-mm-dd') + 1)) aa
                             order by ww)
                     WHERE 1 = 1
                       AND WW BETWEEN TO_DATE('2022-01-01', 'yyyy-mm-dd') AND
                           TO_DATE('2022-12-31', 'yyyy-mm-dd')), S2 AS (SELECT TO_DATE('2022-01-01',
                                                                                       'yyyy-mm-dd') AS WW
                                                                          FROM DUAL)
         SELECT WW, TO_CHAR(WW, 'yyyy') || '-' || 'WK' || RN AS WK
           FROM (SELECT DISTINCT WW,
                                 ROW_NUMBER() OVER(PARTITION BY TO_CHAR(WW, 'yyyy') ORDER BY WW ASC) AS RN
                   FROM (SELECT *
                           FROM S2
                         UNION ALL
                         SELECT * FROM S1)))
          ORDER BY WW ASC

4、跨年查询

若要查询多个年份的周次，只需要将每年的1月1号通过UNION ALL连接起开即可，

SELECT *
  FROM (WITH S1 AS (SELECT WW
                      FROM (select ww
                              from (select distinct (TRUNC(dt, 'd') + 5) ww
                                      from (select (to_date('2022-01-01',
                                                            'yyyy-mm-dd') +
                                                   rownum - 1) dt
                                              from dual
                                            connect by rownum <=
                                                       to_date('2023-12-31',
                                                               'yyyy-mm-dd') -
                                                       to_date('2022-01-01',
                                                               'yyyy-mm-dd') + 1)) aa
                             order by ww)
                     WHERE 1 = 1
                       AND WW BETWEEN TO_DATE('2022-01-01', 'yyyy-mm-dd') AND
                           TO_DATE('2023-12-31', 'yyyy-mm-dd')), S2 AS (SELECT TO_DATE('2022-01-01',
                                                                                       'yyyy-mm-dd') AS WW
                                                                          FROM DUAL
                                                                        UNION ALL
                                                                        SELECT TO_DATE('2023-01-01',
                                                                                       'yyyy-mm-dd') AS WW
                                                                          FROM DUAL)
         SELECT WW, TO_CHAR(WW, 'yyyy') || '-' || 'WK' || RN AS WK
           FROM (SELECT DISTINCT WW,
                                 ROW_NUMBER() OVER(PARTITION BY TO_CHAR(WW, 'yyyy') ORDER BY WW ASC) AS RN
                   FROM (SELECT *
                           FROM S2
                         UNION ALL
                         SELECT * FROM S1)))
          ORDER BY WW ASC

最终效果如下：

5、对具体时间进行判断

取具体时间所在的那一周的第一天的日期，最后通过每一周的第一天的日期进行关联，

SELECT CASE
         WHEN SYSDATE >=
              TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || '-01-01', 'yyyy-MM-dd') AND
              SYSDATE <
              next_day(to_date(TO_CHAR(SYSDATE, 'yyyy') || '-01-01',
                               'yyyy-mm-dd'),
                       '星期五') THEN
          TO_CHAR(SYSDATE, 'yyyy-MM-dd')
         WHEN TRIM(TO_CHAR(SYSDATE, 'DAY')) = '星期五' THEN
          TO_CHAR(SYSDATE, 'yyyy-MM-dd')
         ELSE
          TO_CHAR((TRUNC(SYSDATE, 'WW') - 1), 'yyyy-MM-dd')
       END
  FROM DUAL