Sql计算一年中周内各日期的次数

问题

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

解决方案

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

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。然后在当前年的第一天上加上各行的序号（原文是加1，不妥，译者注），直到返回一年内的每一天（如下所 示）：

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