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

Question

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

它使用分组依据来显示每周的总和：

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

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

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

SET DATEFIRST = 5; --set beginning of each week to Friday
WITH Vars (Friday) -- get current week Fridays Date
... rest of query

但是，由于我编写此查询的接口限制，我无法运行两个单独的语句。它需要是一个没有分号的查询。

我怎样才能做到这一点？

Answer 1

这应该可以做到。 首先在 9 周前的 StartingFriday 进行一次预计算，而不是对每一行都这样做。 然后计算dfYear和dfWeek，给它们别名，其中它们的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