时间戳

首页 > 解决方案 > SQL - 按周分组在特定工作日开始而不涉及两个事务？

sql-server - SQL - 按周分组在特定工作日开始而不涉及两个事务？

问题描述

我正在编写一个查询，返回过去 10 周周五至周四的行总和。

它使用 group by 来显示每周的总和：

WITH Vars (Friday) -- get current week Fridays Date
 AS (
  SELECT CAST(DATEADD(DAY,(13 - (@@DATEFIRST + DATEPART(WEEKDAY,GETDATE())))%7,GETDATE()) AS DATE) As 'Friday' 
)
SELECT datepart(week, DateField) AS WeekNum, COUNT(*) AS Counts
FROM Table
WHERE DateField >= DATEADD(week,-9, (SELECT Friday from Vars)) 
GROUP BY datepart(week, DateField)
ORDER BY WeekNum DESC

问题是每周从星期一开始，因此 Group By 不会按照我想要的方式对日期进行分组。我希望将一周定义为周五至周四。

一种解决方法是使用 DATEFIRST。例如：

SET DATEFIRST = 5; --set beginning of each week to Friday

WITH Vars (Friday) -- get current week Fridays Date
   ... rest of query

但是，由于我正在编写此查询的界面的限制，我不能运行两个单独的语句。它必须是一个不带分号的查询。

我怎样才能做到这一点？

标签： sql-server

解决方案

这应该这样做。首先在 9 周前的 StartingFriday 进行一次预计算，而不是对每一行都进行预计算。然后计算 dfYear 和 dfWeek 给他们 alias-es，他们的 DateField 在开始星期五之后。最后，Count/GroupBy/OrderBy。

    Declare @StartingFriday as date =
             DATEADD(week,-9, (DATEADD(day, - ((Datepart(WEEKDAY,GETDATE()) +1) %  7) , GETDATE()))  ) ;

    SELECT  dfYear, dfWeek,  COUNT(*) AS Counts
    FROM 
        (Select  -- compute these here, and use alias in Select, GroupBy, OrderBy
             (Datepart(Year,(DATEADD(day, - ((Datepart(WEEKDAY,DateField) +1) %  7) , DateField)) ) )as dfYear
            ,(Datepart(Week,(DATEADD(day, - ((Datepart(WEEKDAY,DateField) +1) %  7) , DateField)) ) )as dfWeek
        From Table
        WHERE @StartingFriday <= DateField 
        ) as aa
    group by dfYear, dfWeek
    order by dfYear desc, dfWeek desc

-- we want the weeknum of the (Friday on or before the DateField)  
-- the % (percent sign) is the math MODULO operator.
-- used to get back to the nearest Friday, 
--     day= Fri Sat Sun Mon Tue Wed Thu 
-- weekday=  6   7   1   2   3   4   5
-- plus 1 =  7   8   2   3   4   5   6
-- Modulo7=  0   1   2   3   4   5   6
-- which are the days to subtract from DateField
-- to get to its Friday start of its week.

我对此做了一些测试

  declare @dt as date = '8/17/18';

  select ((DATEPART(WEEKDAY,@dt) +1) %  7) as wd
         ,(DATEADD(day, - ((Datepart(WEEKDAY,@dt) +1) %  7) , @dt)) as Fri
         ,(Datepart(Week,(DATEADD(day, - ((Datepart(WEEKDAY,@dt) +1) %  7) , @dt)) ) )as wk
         ,DATEADD(week,-9, (DATEADD(day, - ((Datepart(WEEKDAY,@dt) +1) %  7) , @dt))  ) as StartingFriday

推荐阅读