SQL server 按周统计 sql查询周次

转载

mob64ca1418aeab 2023-11-06 10:56:47

文章标签 SQL server 按周统计 sql date oracle sql server 文章分类 SQL Server 数据库

问题

计算一年中周内各日期（星期日、星期一 ……星期六）的次数。

解决方案

要计算一年中周内各日期分别有多少个，必须：

1. 生成一年内的所有日期。

2. 设置日期格式，得到每个日期对应为星期几。

3. 计数周内各日期分别有多少个。

DB2

使用递归的WITH子句，以避免对至少包含366行的表进行SELECT。使用函数DAYNAME，确定每个日期为星期几，然后计数周内各日期的次数：

1  with x (start_date,end_date) 
 2  as (
 3  select start_date,
 4         start_date + 1 year end_date
 5    from (
 6  select (current_date -
 7          dayofyear(current_date) day)
 8          +1 day as start_date
 9    from t1    
10         ) tmp
11   union all
12  select start_date + 1 day, end_date
13    from x
14   where start_date + 1 day < end_date
15  )
16  select dayname(start_date),count(*)
17    from x
18   group by dayname(start_date)

MySQL

对表T500进行选择操作，生成的行包含一年的所有日期。使用DATE_FORMAT函数，确定每个日期为星期几，然后计数周内各日期的次数：

1  select date_format(
 2            date_add(
 3                cast(
 4              concat(year(current_date),'-01-01')
 5                     as date),
 6                     interval t500.id-1 day),
 7                     '%W') day,
 8         count(*)
 9    from t500
10   where t500.id <= datediff(
11                        cast(
12                      concat(year(current_date)+1,'-01-01')
13                             as date),
14                        cast(
15                      concat(year(current_date),'-01-01')
16                             as date))
17  group by date_format(
18              date_add(
19                  cast(
20                concat(year(current_date),'-01-01')
21                       as date),
22                       interval t500.id-1 day),
23                       '%W')

Oracle

对于Oracle9i Database或更高版本，可以使用递归的CONNECT BY子句，返回一年内的所有日期。而对于Oracle8i Database或较早版本，则只需对表T500进行选择操作，就能生成包含一年内所有日期的行。另外，使用TO_CHAR函数，可确定每个日期为星期几，然后计数周内各日期的次数：

首先是CONNECT BY解决方案：

1 with x as (
 2 select level lvl
 3   from dual
 4  connect by level <= (
 5    add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')
 6  )
 7 )
 8 select to_char(trunc(sysdate,'y')+lvl-1,'DAY'), count(*)
 9   from x
10  group by to_char(trunc(sysdate,'y')+lvl-1,'DAY')

接下来是Oracle较早版本的解决方案：

1 select to_char(trunc(sysdate,'y')+rownum-1,'DAY'),
2        count(*)
3   from t500
4  where rownum <= (add_months(trunc(sysdate,'y'),12)
5                   - trunc(sysdate,'y'))
6  group by to_char(trunc(sysdate,'y')+rownum-1,'DAY')

PostgreSQL

使用内置函数GENERATE_SERIES，生成包含一年内所有日期的行。然后使用TO_CHAR函数确定每个日期为星期几，然后计数周内各日期的次数。例如：

1  select to_char(
 2            cast(
 3      date_trunc('year',current_date)
 4                 as date) + gs.id-1,'DAY'),
 5         count(*)
 6    from generate_series(1,366) gs(id)
 7   where gs.id <= (cast
 8                    ( date_trunc('year',current_date) +
 9                         interval '12 month' as date) -
10  cast(date_trunc('year',current_date)
11                        as date))
12   group by to_char(
13               cast(
14         date_trunc('year',current_date)
15            as date) + gs.id-1,'DAY')

SQL Server

使用递归的WITH子句，以避免对至少包含366行的表进行SELECT。在不支持WITH子句的SQL Server版本中，需要使用基干表，有关内容可以参考Oracle的第二个解决方案。使用函数DAYNAME，可确定每个日期为星期几，然后计数周内各日期的次数。例如：

1  with x (start_date,end_date) 
 2  as (
 3  select start_date,
 4         dateadd(year,1,start_date) end_date
 5    from (
 6  select cast(
 7         cast(year(getdate(  )) as varchar) + '-01-01'
 8              as datetime) start_date
 9    from t1
10         ) tmp
11  union all
12  select dateadd(day,1,start_date), end_date
13    from x
14   where dateadd(day,1,start_date) < end_date
15  )
16  select datename(dw,start_date),count(*)
17    from x
18   group by datename(dw,start_date) 
19 OPTION (MAXRECURSION 366)

DB2

递归WITH视图X的内联视图TMP将返回当前年份的第一天，如下所示：

select (current_date -
        dayofyear(current_date) day) 
        +1 day as start_date
  from t1 
START_DATE
-----------

01-JAN-2005

下一步，给START_DATE加1年，这样就有了开始日期和结束日期。想要生成一年内的每一天，就必须知道这两个日期。START_DATE和END_DATE如下所示：

select start_date, 
       start_date + 1 year end_date
  from (
select (current_date -
        dayofyear(current_date) day) 
        +1 day as start_date
  from t1
       ) tmp 
START_DATE  END_DATE
----------- -----------
01-JAN-2005 01-JAN-2006

接下来，递归增加START_DATE，每次加1天，直到它等于END_DATE时为止。下面列出了由递归视图X返回的部分行：

with x (start_date,end_date) 
as (
select start_date, 
       start_date + 1 year end_date
  from (
select (current_date -
        dayofyear(current_date) day) 
        +1 day as start_date
  from t1
       ) tmp
 union all
select start_date + 1 day, end_date
  from x
 where start_date + 1 day < end_date
)
select * from x 
START_DATE  END_DATE
----------- -----------
01-JAN-2005 01-JAN-2006
02-JAN-2005 01-JAN-2006
03-JAN-2005 01-JAN-2006
...
29-JAN-2005 01-JAN-2006
30-JAN-2005 01-JAN-2006
31-JAN-2005 01-JAN-2006
...
01-DEC-2005 01-JAN-2006
02-DEC-2005 01-JAN-2006
03-DEC-2005 01-JAN-2006
...
29-DEC-2005 01-JAN-2006
30-DEC-2005 01-JAN-2006
31-DEC-2005 01-JAN-2006

最后一步，对于由递归视图X返回的行，使用函数DAYNAME，然后计数周内各日期的次数。下面列出了其最后结果：

with x (start_date,end_date) 
as (
select start_date, 
       start_date + 1 year end_date
  from (
select (current_date -
        dayofyear(current_date) day) 
        +1 day as start_date
  from t1
       ) tmp
 union all
select start_date + 1 day, end_date
  from x
 where start_date + 1 day < end_date
)
select dayname(start_date),count(*) 
  from x
 group by dayname(start_date) 
START_DATE   COUNT(*)
---------  ----------
FRIDAY            52
MONDAY            52
SATURDAY          53
SUNDAY            52
THURSDAY          52
TUESDAY           52
WEDNESDAY         52

MySQL

该解决方案对表T500进行选择，以便生成包含一年内所有日期的行。第4行的命令返回当前年份的第一天，其方法是求出函数CURRENT_DATE所返回日期的年份，并添加月份和天（采用MySQL的默认数据格式），其结果如下：

select concat(year(current_date),'-01-01')
  from t1 
START_DATE
-----------
01-JAN-2005

得到了当前年份的第一天以后，则可使用DATEADD函数在该日期上分别加上T500.ID中的每个值，以生成该年份的每一天。使用函数DATE_FORMAT，可返回每个日期是星期几。要从表T500中选择想要的行数，只需计算出当前年份的第一天与下一个年份的第一天之间的日期差，并返回相应数目的行（365或366个）。下面列出了部分结果：

select date_format(
          date_add(
              cast(
            concat(year(current_date),'-01-01')
                   as date),
                   interval t500.id-1 day),
                   '%W') day
  from t500
 where t500.id <= datediff(
         cast(
                    concat(year(current_date)+1,'-01-01')
                           as date),
                      cast(
                    concat(year(current_date),'-01-01')
                           as date)) 
DAY
-----------
01-JAN-2005
02-JAN-2005
03-JAN-2005
...
29-JAN-2005
30-JAN-2005
31-JAN-2005
...
01-DEC-2005
02-DEC-2005
03-DEC-2005
...
29-DEC-2005
30-DEC-2005
31-DEC-2005

得到了当前年份的每一天后，则可以计数由函数DAYNAME返回的周内各日期各自的出现次数。最终结果如下所示：

select date_format(
          date_add(
              cast(
            concat(year(current_date),'-01-01')
                   as date),
                   interval t500.id-1 day),
                   '%W') day,
       count(*)
  from t500
 where t500.id <= datediff(
                      cast(
                    concat(year(current_date)+1,'-01-01')
                           as date),
                      cast(
                    concat(year(current_date),'-01-01')
                           as date))
group by date_format(
            date_add(
                cast(
              concat(year(current_date),'-01-01')
                     as date),
                     interval t500.id-1 day),
                     '%W') 
DAY       COUNT(*)
--------- ----------
FRIDAY            52
MONDAY            52
SATURDAY          53
SUNDAY            52
THURSDAY          52
TUESDAY           52
WEDNESDAY         52
Oracle

这个解决方案既提供了对表T500（基干表）的选择操作的方法，也提供了使用递归的CONNECT BY和WITH子句的方法，为当前年的每一天生成一行信息。调用函数TRUNC，可以从当前日期截取当前年份的第一天。

如果采用CONNECT BY/WITH解决方案，则可以使用伪列（preudo-column）LEVEL生成起始值为1的序列数。要生成这种解决方案所要求数目的行，需根据当前年的第一天与下一年的第一天之间相差的天数（365或366个）对ROWNUM或LEVEL进行筛选；然后，通过给当前年的第一天加上ROWNUM或 LEVEL，递增得到一年中的每一天。下面列出了部分结果：

/* Oracle 9i and later */
with x as (
select level lvl
  from dual
 connect by level <= (
   add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')
 )
)
select trunc(sysdate,'y')+lvl-1
  from x

如果采用基干表解决方案，则可以在其内部使用至少包含366行的任意表或视图。因为Oracle引入了函数ROWNUM，所以表中不需要包含从1开始递增的数值。参阅下面的例子，它使用基干表T500返回当前年的每一天：

/* Oracle 8i and earlier */
select trunc(sysdate,'y')+rownum-1 start_date
  from t500
 where rownum <= (add_months(trunc(sysdate,'y'),12)
                  - trunc(sysdate,'y')) 
START_DATE
-----------
01-JAN-2005
02-JAN-2005
03-JAN-2005
...
29-JAN-2005
30-JAN-2005
31-JAN-2005
...
01-DEC-2005
02-DEC-2005
03-DEC-2005
...
29-DEC-2005
30-DEC-2005
31-DEC-2005

无论采用哪种方法，最终都要使用函数TO_CHAR返回每个日期是星期几，并计算各自出现的次数。其结果如下所示：

/* Oracle 9i and later */
with x as (
select level lvl
  from dual
 connect by level <= (
   add_ml> connect by level <= (
   add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')
 )
)
select to_char(trunc(sysdate,'y')+lvl-1,'DAY'), count(*)
  from x
 group by to_char(trunc(sysdate,'y')+lvl-1,'DAY') 
/* Oracle 8i and earlier */
select to_char(trunc(sysdate,'y')+rownum-1,'DAY') start_date,
       count(*)
  from t500
 where rownum <= (add_months(trunc(sysdate,'y'),12)
                  - trunc(sysdate,'y'))
 group by to_char(trunc(sysdate,'y')+rownum-1,'DAY') 
START_DATE   COUNT(*)
---------- ----------
FRIDAY             52
MONDAY             52
SATURDAY           53
SUNDAY             52
THURSDAY           52
TUESDAY            52
WEDNESDAY          52 
PostgreSQL
首先使用DATE_TRUNC函数返回当前日期的年份（如下所示，对T1 进行选择操作，只返回一行信息）：
select cast(
         date_trunc('year',current_date) 
       as date) as start_date
  from t1 
START_DATE
-----------
01-JAN-2005

然后，对至少包含366行的一个行来源（实际上任何表表达式均可）进行选择操作。本解决方案把函数 GENERATE_SERIES 当作行来源使用，当然，也可以使用表T500。然后在当前年的第一天上加上各行的序号，直到返回一年内的每一天（如下所示）：

select cast( date_trunc('year',current_date)
               as date) + gs.id-1 as start_date
  from generate_series (1,366) gs(id)
 where gs.id <= (cast
                  ( date_trunc('year',current_date) +
                       interval '12 month' as date) -
     cast(date_trunc('year',current_date)
                      as date)) 
START_DATE
-----------
01-JAN-2005
02-JAN-2005
03-JAN-2005
...
29-JAN-2005
30-JAN-2005
31-JAN-2005
...
01-DEC-2005
02-DEC-2005
03-DEC-2005
...
29-DEC-2005
30-DEC-2005
31-DEC-2005

最后，使用函数TO_CHAR返回每个日期是星期几，再计数周内各日期的次数。最终结果如下所示：

select to_char(
          cast(
    date_trunc('year',current_date)
               as date) + gs.id-1,'DAY') as start_dates,
       count(*)
  from generate_series(1,366) gs(id)
 where gs.id <= (cast
                  ( date_trunc('year',current_date) +
                       interval '12 month' as date) -
     cast(date_trunc('year',current_date)
                      as date))
 group by to_char(
             cast(
       date_trunc('year',current_date)
          as date) + gs.id-1,'DAY') 
START_DATE   COUNT(*)
---------- ----------
FRIDAY             52
MONDAY             52
SATURDAY           53
SUNDAY             52
THURSDAY           52
TUESDAY            52
WEDNESDAY          52
SQL Server

递归WITH视图X中的内联视图TMP返回当前年份的第一天，如下所示：

select cast(
       cast(year(getdate(  )) as varchar) + '-01-01'
            as datetime) start_date
  from t1 
START_DATE
-----------
01-JAN-2005

在有了当前年份的第一天后，给START_DATE加1年，这样既有开始日期，又有结束日期。要生成一年内的每一天，必须知道这两个值。START_DATE和END_DATE如下所示：

select start_date,
        dateadd(year,1,start_date) end_date
  from (
select cast(
       cast(year(getdate(  )) as varchar) + '-01-01'
            as datetime) start_date
  from t1
       ) tmp 
START_DATE  END_DATE
----------- -----------
01-JAN-2005 01-JAN-2006

接下来，递归递增START_DATE值，每次加1，直到它等于END_DATE为止（不包括END_DATE）。下面给出了由递归视图X返回的部分行：

with x (start_date,end_date)
 as (
 select start_date,
        dateadd(year,1,start_date) end_date
   from (
 select cast(
        cast(year(getdate(  )) as varchar) + '-01-01'
             as datetime) start_date
   from t1
        ) tmp
 union all
 select dateadd(day,1,start_date), end_date
   from x
  where dateadd(day,1,start_date) < end_date
 )
 select * from x
 OPTION (MAXRECURSION 366) 
START_DATE  END_DATE
----------- -----------
01-JAN-2005 01-JAN-2006
02-JAN-2005 01-JAN-2006
03-JAN-2005 01-JAN-2006
...
29-JAN-2005 01-JAN-2006
30-JAN-2005 01-JAN-2006
31-JAN-2005 01-JAN-2006
...
01-DEC-2005 01-JAN-2006
02-DEC-2005 01-JAN-2006
03-DEC-2005 01-JAN-2006
...
29-DEC-2005 01-JAN-2006
30-DEC-2005 01-JAN-2006
31-DEC-2005 01-JAN-2006

最后，对递归视图X返回的行使用函数DATENAME，并计数周内各日期的出现次数，其结果如下：

with x(start_date,end_date)
 as (
 select start_date,
        dateadd(year,1,start_date) end_date
   from (
 select cast(
        cast(year(getdate(  )) as varchar) + '-01-01'
             as datetime) start_date
   from t1
        ) tmp
 union all
 select dateadd(day,1,start_date), end_date
   from x
  where dateadd(day,1,start_date) < end_date
 )
 select datename(dw,start_date), count(*)
   from x
  group by datename(dw,start_date)
 OPTION (MAXRECURSION 366) 
START_DATE   COUNT(*)
---------  ----------
FRIDAY            52
MONDAY            52
SATURDAY          53
SUNDAY            52
THURSDAY          52
TUESDAY           52
WEDNESDAY         52

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。