将不同的时间段分组应用于一组数据

The following was a pattern I started to use two years ago and it is repeated over and over in my legacy code.

以下是我两年前开始使用的模式，它在我的遗留代码中反复重复。

It effectively groups the same data using different time periods.

它使用不同的时间段有效地分组相同的数据。

Is there a standard way I should be approaching this or is this long winded method as good as I'll get?

有没有一种标准的方法我应该接近这个，还是这种长篇大论的方法和我能得到的一样好？

Another way of putting this question is how can the following be made more concise?
All 4 queries come out of the same data source and all four go into the same output table can these 4 queries be amalgamated into 1 shorter script?

提出这个问题的另一种方法是如何使以下更简洁？所有4个查询都来自相同的数据源，并且所有4个查询都进入相同的输出表，这4个查询是否可以合并为1个较短的脚本？

DECLARE @myDate DATETIME = CONVERT(DATETIME,CONVERT(VARCHAR(11),GETDATE(),106)); 
DECLARE @myFirstDateLastMth CHAR(8) =CONVERT(CHAR(6),DATEADD(mm,-1,@myDate-1),112) + '01';
DECLARE @myFirstDateCurrentMth CHAR(8) =CONVERT(CHAR(6),DATEADD(mm,0,@myDate-1),112) + '01'; 

DELETE FROM WH.dbo.tb_myTable

--day on day==========
INSERT INTO WH.dbo.tb_myTable
SELECT 
  TimePeriod =
    CASE 
        WHEN x.DateKey = CONVERT(VARCHAR(11),@myDate - 1,112) THEN 'Day'                    
        WHEN x.DateKey = CONVERT(VARCHAR(11),@myDate - 2,112) THEN 'Day-1'  
    END,
  Game              = x.Name,
  Score             = SUM(x.Score),
  Ticks             = SUM(x.Ticks),
  ScorePerTick = SUM(x.Score)/SUM(x.Ticks)
FROM #LimitedBetinfo x
WHEREx.DateKey >= CONVERT(VARCHAR(11),@myDate - 2,112)
GROUP BY
  CASE 
    WHEN x.DateKey = CONVERT(VARCHAR(11),@myDate - 1,112) THEN 'Day'                    
    WHEN x.DateKey = CONVERT(VARCHAR(11),@myDate - 2,112) THEN 'Day-1'  
  END,
  x.Name;

--wk on wk==========
INSERT INTO WH.dbo.tb_myTable
SELECT 
  TimePeriod =
        CASE 
        WHEN x.DateKey >= CONVERT(VARCHAR(11),@myDate - 7,112) THEN 'Week'                  
        WHEN x.DateKey < CONVERT(VARCHAR(11),@myDate - 7,112) 
                    AND x.DateKey >= CONVERT(VARCHAR(11),@myDate - 14,112)  
                            THEN 'Week-1'
    END,
  Game               = x.Name,
  Score              = SUM(x.Score),
  Ticks              = SUM(x.Ticks),
  ScorePerTick = SUM(x.Score)/SUM(x.Ticks)
FROM #LimitedBetinfo x
WHERE   x.DateKey >= CONVERT(VARCHAR(11),@myDate - 14,112)
GROUP BY
  CASE 
    WHEN x.DateKey >= CONVERT(VARCHAR(11),@myDate - 7,112) THEN 'Week'                  
    WHEN x.DateKey < CONVERT(VARCHAR(11),@myDate - 7,112) 
                AND x.DateKey >= CONVERT(VARCHAR(11),@myDate - 14,112)  
                        THEN 'Week-1'
    END,
  g.Name;                   


--mth on mth==========
INSERT INTO WH.dbo.tb_myTable
SELECT 
  TimePeriod =
    CASE 
        WHEN x.DateKey >= CONVERT(VARCHAR(11),@myDate - 28,112) THEN 'Month'                    
        WHEN x.DateKey < CONVERT(VARCHAR(11),@myDate - 28,112) 
                        AND x.DateKey >= CONVERT(VARCHAR(11),@myDate - 56,112)  
                                THEN 'Month-1'
    END,
  Game               = x.Name,
  Score              = SUM(x.Score),
  Ticks              = SUM(x.Ticks),
  ScorePerTick = SUM(x.Score)/SUM(x.Ticks)
FROM #LimitedBetinfo x
WHERE   x.DateKey >=  CONVERT(VARCHAR(11),@myDate - 56,112)
GROUP BY
  CASE 
    WHEN x.DateKey >= CONVERT(VARCHAR(11),@myDate - 28,112) THEN 'Month'                    
    WHEN x.DateKey < CONVERT(VARCHAR(11),@myDate - 28,112) 
                AND x.DateKey >= CONVERT(VARCHAR(11),@myDate - 56,112)  
                        THEN 'Month-1'
  END,
  g.Name;                   


--MTD and PrevCalMonth==========
INSERT INTO WH.dbo.tb_myTable
SELECT 
  TimePeriod
  = CASE 
        WHEN  x.DateKey >= @myFirstDateCurrentMth   THEN 'MTD'
        WHEN  x.DateKey < @myFirstDateCurrentMth  
                AND  x.DateKey >=@myFirstDateLastMth THEN 'PrevCalMonth'                                      
  END,
  Game              = x.Name,
  Score             = SUM(x.Score),
  Ticks             = SUM(x.Ticks),
  ScorePerTick = SUM(x.Score)/SUM(x.Ticks)
FROM #LimitedBetinfo x
WHERE   x.DateKey >=  CONVERT(CHAR(6),DATEADD(mm,-1,@myDate-1),112) + '01'
GROUP BY
  CASE 
    WHEN  x.DateKey >= @myFirstDateCurrentMth   THEN 'MTD'
    WHEN  x.DateKey < @myFirstDateCurrentMth  
            AND  x.DateKey >=@myFirstDateLastMth THEN 'PrevCalMonth'            
  END,
  g.Name;   

5 个解决方案

#1

Use parameters - VALUES As a Table Source and apply them as parameters in CROSS APPLY with derived table

使用参数 - VALUES作为表源并将其作为参数应用于带有派生表的CROSS APPLY中

DECLARE @myDate datetime = CAST(GETDATE() AS date);
IF OBJECT_ID('WH.dbo.tb_myTable') IS NOT NULL DROP TABLE WH.dbo.tb_myTable
SELECT TimePeriod, Game, Score, Ticks, ScorePerTicks
INTO WH.dbo.tb_myTable
FROM (VALUES('Day', DATEADD(day, -1, @myDate), @myDate),
            ('Day-1', DATEADD(day, -2, @myDate), DATEADD(day, -2, @myDate)),
            ('Week', DATEADD(day, -7, @myDate), @myDate),
            ('Week-1', DATEADD(day, -14, @myDate), DATEADD(day, -8, @myDate)),
            ('Month', DATEADD(day, -28, @myDate), @myDate),
            ('Month-1', DATEADD(day, -56, @myDate), DATEADD(day, -29, @myDate)),
            ('MTD', DATEADD(DAY, 1 - DAY(@myDate), @myDate), @myDate), 
            ('PrevCalMonth', DATEADD(DAY, 1 - DAY(@myDate), DATEADD(MONTH, -1, @myDate)), DATEADD(DAY,  - DAY(@myDate), @myDate)))
RParameters(TimePeriod, BDate, EDate)
  CROSS APPLY (SELECT x.Name AS Game,
                      SUM(x.Score) AS Score,
                      SUM(x.Ticks) AS Ticks,
                      SUM(x.Score) / SUM(x.Ticks) AS ScorePerTicks
               FROM #LimitedBetinfo x
               WHERE DateKey BETWEEN RParameters.BDate AND RParameters.EDate
               GROUP BY Name) AS o

Demo on SQLFiddle

在SQLFiddle上演示

#2

I would make it a single insert statement.

我会把它作为一个插入语句。

Would prefer for now not to use the group by grouping sets, cube, or rollup as that I don't see how I could limit the rows calculated over individual day groups from being less than those calculated over larger time period groups.

现在更愿意不通过对集合，多维数据集或汇总进行分组来使用该组，因为我没有看到如何限制在单个日期组中计算的行数小于在较大时间段组中计算的行数。

So, to keep that from happening you could create a common-table-expression (;WITH mycte AS (...subquery...)), temp table, table variable, or XML formatted text object that would contain the time periods, one row/element for each.

因此，为了防止这种情况发生，您可以创建一个common-table-expression（; WITH mycte AS（...子查询...）），临时表，表变量或包含时间段的XML格式文本对象，每行一行/元素。

This script can also be run with more or less time periods defined to get all results with only one trip from the app to the server.

此脚本也可以在定义的更多或更少时间段内运行，以便从应用程序到服务器只进行一次旅行即可获得所有结果。

Here's an example with temp table, that could also be easily made into a table variable:

这是一个带临时表的例子，也可以很容易地变成表变量：

--Define time periods
CREATE TABLE #TempTimePeriods (
    TimePeriod VARCHAR(20) PRIMARY KEY,
    TPBegin VARCHAR(11) NOT NULL,
    TPEnd VARCHAR(11) NULL
);

DECLARE @myDate DATETIME = '2012-10-10';
DECLARE @myDateMinusOne DATETIME = DATEADD(dd, -1, @myDate);
INSERT INTO #TempTimePeriods ( TimePeriod, TPBegin, TPEnd )
SELECT [TimePeriod], CONVERT(VARCHAR(11), TPBegin, 112) TPBegin, CONVERT(VARCHAR(11), TPEnd, 112) TPEnd
FROM (
    SELECT 'Day'          [TimePeriod], @myDate - 1 TPBegin, @myDate -  1 TPEnd UNION ALL
    SELECT 'Day-1'        [TimePeriod], @myDate - 2 TPBegin, @myDate -  2 TPEnd UNION ALL
    SELECT 'Week'         [TimePeriod], @myDate - 7 TPBegin,                                  NULL TPEnd UNION ALL
    SELECT 'Week-1'       [TimePeriod], @myDate - 14 TPBegin, @myDate -  8 TPEnd UNION ALL
    SELECT 'Month'        [TimePeriod], @myDate - 28 TPBegin,                                  NULL TPEnd UNION ALL
    SELECT 'Month-1'      [TimePeriod], @myDate - 56 TPBegin, @myDate - 29 TPEnd UNION ALL
    SELECT 'MTD'          [TimePeriod], DATEADD(dd, -1 * DAY(@myDateMinusOne) + 1, @myDateMinusOne) TPBegin, NULL TPEnd UNION ALL
    SELECT 'PrevCalMonth' [TimePeriod], DATEADD(mm,-1,DATEADD(dd, -1 * DAY(@myDateMinusOne) + 1, @myDateMinusOne)) TPBegin, DATEADD(dd, -1 * DAY(@myDateMinusOne), @myDateMinusOne) TPEnd
) TT;  

And here is the main query...

这是主要的查询......

--compute/insert results
INSERT INTO WH.dbo.tb_myTable
SELECT TimePeriods.TimePeriod,
x.Name Game,
SUM(x.Score) Score,
SUM(x.Ticks) Ticks,
CASE WHEN SUM(x.Ticks) != 0 THEN SUM(x.Score)/SUM(x.Ticks) END ScorePerTick
FROM #TempTimePeriods TimePeriods
--for periods with no data use left outer join to return 0-value results, otherwise inner join
LEFT OUTER JOIN #LimitedBetInfo x 
ON x.DateKey >= [TimePeriods].TPBegin
AND (
    [TimePeriods].TPEnd IS NULL
    OR x.DateKey <= [TimePeriods].TPEnd
)
GROUP BY TimePeriods.TimePeriod, x.Name

You could also eliminate the the #TempTimePeriods table using a Common-Table-Expression below:

您还可以使用下面的Common-Table-Expression消除#TempTimePeriods表：

DECLARE @myDate DATETIME = '2012-10-10';
DECLARE @myDateMinusOne DATETIME = DATEADD(dd, -1, @myDate);
;WITH TimePeriods AS (
    SELECT [TimePeriod], CONVERT(VARCHAR(11), TPBegin, 112) TPBegin, CONVERT(VARCHAR(11), TPEnd, 112) TPEnd
    FROM (
        SELECT 'Day'          [TimePeriod], @myDate - 1 TPBegin, @myDate -  1 TPEnd UNION ALL
        SELECT 'Day-1'        [TimePeriod], @myDate - 2 TPBegin, @myDate -  2 TPEnd UNION ALL
        SELECT 'Week'         [TimePeriod], @myDate - 7 TPBegin,                                  NULL TPEnd UNION ALL
        SELECT 'Week-1'       [TimePeriod], @myDate - 14 TPBegin, @myDate -  8 TPEnd UNION ALL
        SELECT 'Month'        [TimePeriod], @myDate - 28 TPBegin,                                  NULL TPEnd UNION ALL
        SELECT 'Month-1'      [TimePeriod], @myDate - 56 TPBegin, @myDate - 29 TPEnd UNION ALL
        SELECT 'MTD'          [TimePeriod], DATEADD(dd, -1 * DAY(@myDateMinusOne) + 1, @myDateMinusOne) TPBegin, NULL TPEnd UNION ALL
        SELECT 'PrevCalMonth' [TimePeriod], DATEADD(mm,-1,DATEADD(dd, -1 * DAY(@myDateMinusOne) + 1, @myDateMinusOne)) TPBegin, DATEADD(dd, -1 * DAY(@myDateMinusOne), @myDateMinusOne) TPEnd
    ) TT
)
INSERT INTO WH.dbo.tb_myTable
SELECT TimePeriods.TimePeriod,
x.Name Game,
SUM(x.Score) Score,
SUM(x.Ticks) Ticks,
CASE WHEN SUM(x.Ticks) != 0 THEN SUM(x.Score)/SUM(x.Ticks) END ScorePerTick
FROM [TimePeriods]
--for periods with no data use left outer join to return 0-value results, otherwise inner join
LEFT OUTER JOIN #LimitedBetInfo x
ON x.DateKey >= [TimePeriods].TPBegin
AND (
     [TimePeriods].TPEnd IS NULL
     OR x.DateKey <= [TimePeriods].TPEnd
)
GROUP BY [TimePeriods].TimePeriod, x.Name

And lastly you could define the time periods in an XML string-handy for passing to a stored procedure if that's your preference and proceed as follows:

最后，您可以在XML字符串中定义时间段 - 方便传递给存储过程（如果这是您的首选项）并按以下步骤操作：

--example XML string with time period definitions
DECLARE @TimePeriodsXml NVARCHAR(MAX) = '
<TimePeriod name="Day" tpbegin="20121010" tpend="20121010" />
<TimePeriod name="Day-1" tpbegin="20121009" tpend="20121009" />
<TimePeriod name="Week" tpbegin="20121004"/>
<TimePeriod name="Week-1" tpbegin="20120927" tpend="20121004" />
<TimePeriod name="Month" tpbegin="20120913" />
<TimePeriod name="Month-1" tpbegin="20120815" tpend="20120912" />
<TimePeriod name="MTD" tpbegin="20121001" />
<TimePeriod name="PrevCalMonth" tpbegin="20120901" tpend="20120930" />
';

and the main query modified to read the XML:

并修改主要查询以读取XML：

SELECT TimePeriods.TimePeriod,
x.Name Game,
SUM(x.Score) Score,
SUM(x.Ticks) Ticks,
CASE WHEN SUM(x.Ticks) != 0 THEN SUM(x.Score)/SUM(x.Ticks) END ScorePerTick
FROM (
    SELECT
    E.TimePeriod.value('./@name', 'VARCHAR(20)') TimePeriod,
    E.TimePeriod.value('./@tpbegin', 'VARCHAR(20)') TPBegin,
    E.TimePeriod.value('./@tpend', 'VARCHAR(20)') TPEnd
    FROM (
        SELECT CAST(@TimePeriodsXml AS XML) tpxml
    ) TT
    CROSS APPLY tpxml.nodes('/TimePeriod') AS E(TimePeriod)
) TimePeriods
--for periods with no data use left outer join to return 0-value results, otherwise inner join
LEFT OUTER JOIN #LimitedBetInfo x
ON x.DateKey >= [TimePeriods].TPBegin
AND (
     [TimePeriods].TPEnd IS NULL
     OR x.DateKey <= [TimePeriods].TPEnd
)
GROUP BY TimePeriods.TimePeriod, x.Name

For an example of how the XML stringed query could be turned into a procedure, to support a single parameter of 1 or more time periods:

有关如何将XML stringed查询转换为过程的示例，以支持1个或多个时间段的单个参数：

CREATE PROCEDURE dbo.GetTimePeriodAggregates
@TimePeriodsXmlString NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
SELECT TimePeriods.TimePeriod,
x.Name Game,
SUM(x.Score) Score,
SUM(x.Ticks) Ticks,
CASE WHEN SUM(x.Ticks) != 0 THEN SUM(x.Score)/SUM(x.Ticks) END ScorePerTick
FROM (
    SELECT
    E.TimePeriod.value('./@name', 'VARCHAR(20)') TimePeriod,
    E.TimePeriod.value('./@tpbegin', 'VARCHAR(20)') TPBegin,
    E.TimePeriod.value('./@tpend', 'VARCHAR(20)') TPEnd
    FROM (
        SELECT CAST(@TimePeriodsXml AS XML) tpxml
    ) TT
    CROSS APPLY tpxml.nodes('/TimePeriod') AS E(TimePeriod)
) TimePeriods
LEFT OUTER JOIN #LimitedBetInfo x
ON x.DateKey BETWEEN TimePeriods.TPBegin AND TimePeriods.TPEnd
GROUP BY TimePeriods.TimePeriod, x.Name
END

Which could be run as:

哪个可以运行：

--This declare is just an example, it could be instead a parameter passed from an application
DECLARE @ThisExecutionsXmlString NVARCHAR(MAX) = N'
<TimePeriod name="Day" tpbegin="20121010" tpend="20121010" />
<TimePeriod name="Day-1" tpbegin="20121009" tpend="20121009" />
<TimePeriod name="Week" tpbegin="20121004"/>
<TimePeriod name="Week-1" tpbegin="20120927" tpend="20121004" />
<TimePeriod name="Month" tpbegin="20120913" />
<TimePeriod name="Month-1" tpbegin="20120815" tpend="20120912" />
<TimePeriod name="MTD" tpbegin="20121001" />
<TimePeriod name="PrevCalMonth" tpbegin="20120901" tpend="20120930" />
';

INSERT INTO WH.dbo.tb_myTable
EXEC dbo.GetTimePeriodAggregates @TimePeriodsXmlString=@ThisExecutionsXmlString

#3

You can create this stored procedure

您可以创建此存储过程

CREATE PROCEDURE InsertData
    @minLimit date,
    @maxLimit date,
    @minTerm nvarchar(50),
    @maxTerm nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO tb_myTable
    SELECT 
        [TimePeriod]        = CASE WHEN x.DateKey >= @maxLimit THEN @maxTerm ELSE @minTerm END,
        [Game]              = x.Name,
        [Score]             = SUM(x.[Score]),
        [Ticks]             = SUM(x.[Ticks]),
        [ScorePerTick]      = SUM(x.[Score])/SUM(x.[Ticks])

    FROM #LimitedBetinfo x
    WHERE x.DateKey >= @minLimit
    GROUP BY 
        CASE WHEN x.DateKey >= @maxLimit THEN @maxTerm ELSE @minTerm END,
        x.Name

END
GO

And use like this

并使用这样的

TRUNCATE TABLE tb_myTable

DECLARE @today date = cast(getdate() as date)
DECLARE @yesterday date = dateadd(day, -1, @today)
EXECUTE dbo.InsertData @yesterday, @today, N'Day-1', N'Day' 

DECLARE @thisweek date = DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0)
DECLARE @lastweek date = DATEADD(ww, -1, @thisweek)
EXECUTE dbo.InsertData @lastweek, @thisweek, N'Week-1', N'Week' 

DECLARE @prev28 date = dateadd(day, -28, @today)
DECLARE @prev56 date = dateadd(day, -56, @today)
EXECUTE dbo.InsertData @prev56, @prev28, N'Month-1', N'Month' 

DECLARE @thismonth date = DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)
DECLARE @lastmonth date = DATEADD(mm, -1, @thismonth)
EXECUTE dbo.InsertData @lastmonth, @thismonth, N'PrevCalMonth', N'MTD' 

#4

A possible improvement on fred's answer. Not in terms of speed, just readability / modifiability by removing the extra CASE. As a suggestion, I also replaced the passing of both strings (e.g. DAY and DAY-1) with a single string and to have the other just be a concat; this would however cause PrevCalMonth to be displayed as MTD-1 instead (though there are some work-arounds for this).

fred的答案可能有所改进。不是速度方面，只是通过删除额外的CASE可读性/可修改性。作为一个建议，我还用一个字符串替换了两个字符串（例如DAY和DAY-1）的传递，并使另一个字符串只是一个连续点;然而，这会导致PrevCalMonth显示为MTD-1（尽管有一些解决方法）。

CREATE PROCEDURE InsertData
  @minLimit date, @maxLimit date, @string nvarchar(50)
AS
  INSERT INTO tb_myTable
  SELECT TimePeriod, Name, SUM(Score) Score, SUM(Ticks) Ticks, 
    SUM(Score)/SUM(Ticks) ScorePerTick 
  FROM
  (
    SELECT *, /* or 'Name, Score, Ticks,' */
      TimePeriod = CASE WHEN x.DateKey >= @maxLimit THEN @string ELSE @string+'-1' END
    FROM #LimitedBetinfo x
    WHERE x.DateKey >= @minLimit
  ) A
  GROUP BY TimePeriod, Name
GO

And use like this:

并使用这样的：

TRUNCATE TABLE tb_myTable

DECLARE @today date = cast(getdate() as date)
DECLARE @yesterday date = dateadd(day, -1, @today)
EXECUTE dbo.InsertData @yesterday, @today, N'Day' 

DECLARE @thisweek date = DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0)
DECLARE @lastweek date = DATEADD(ww, -1, @thisweek)
EXECUTE dbo.InsertData @lastweek, @thisweek, N'Week' 

DECLARE @prev28 date = dateadd(day, -28, @today)
DECLARE @prev56 date = dateadd(day, -56, @today)
EXECUTE dbo.InsertData @prev56, @prev28, N'Month' 

DECLARE @thismonth date = DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)
DECLARE @lastmonth date = DATEADD(mm, -1, @thismonth)
EXECUTE dbo.InsertData @lastmonth, @thismonth, N'MTD' 

#5

It seems that this may be the job for CUBE groupings.

这似乎可能是CUBE分组的工作。

Sorry, I will not give you exact solution to your problem, but the MOCKUP form of select should be like:

对不起，我不会给你问题的确切解决方案，但选择的MOCKUP形式应该是：

select * from
(
    select *,count(*) amount from
    (
        select datepart(HOUR, login_time) as hour, 
               datepart(MINUTE, login_time) as minute, 
               cmd as name
        from sys.sysprocesses
    ) tmp
    group by cube(tmp.hour, tmp.minute, tmp.name)
) tmp2
where tmp2.name is not null and
      (
      (tmp2.hour is not null and tmp2.minute is null) or
      (tmp2.hour is null and tmp2.minute is not null)
      )

One minus - that cube generates too much data for your problem here. So it needs to be filtered out. A big plus would be that you will only need just ONE select into temporary table.

一个减号 - 该立方体在这里为您的问题生成了太多数据。所以需要过滤掉它。一个很大的优点是你只需要一个选择临时表。