如何在特定的时间间隔内正确地按日期时间列进行分组？

Question

我正在 MSSQL 中创建一个存储过程，我想按间隔天对结果进行分组，但我不确定这是否正确：（

这是一部分）

    INSERT @Results
    (
        [Day],
        [Month],
        [Year],
        [Result]
    )
    SELECT
        DATEPART(DD, DATEADD(MI, @GmtOffset, EventDate)),
        DATEPART(MM, DATEADD(MI, @GmtOffset, EventDate)),
        DATEPART(YY, DATEADD(MI, @GmtOffset, EventDate)),
        Result = CASE WHEN @Metric = 'Take Rate' THEN NULL ELSE COUNT(*) END
    FROM BundleEvent
    WHERE BundleEventTypeId = CASE WHEN @Metric = 'Take Rate' THEN @TypeTakeId ELSE @BundleEventTypeId END
        AND EventDate >= @StartTime AND EventDate <= @EndTime
    GROUP BY
        DATEPART(YY, DATEADD(MI, @GmtOffset, EventDate)),
        DATEPART(MM, DATEADD(MI, @GmtOffset, EventDate)),
        DATEPART(DD, DATEADD(MI, @GmtOffset, EventDate))

我担心它最终会按日期对数据进行分组年或月并不重要。有谁知道我这样做是否正确？ EventDate 是我想要按日期间隔进行GROUP BY 的DateTime 字段。

谢谢

编辑： 这是正确的方法，简单的格式交换...有时我需要更多咖啡。

INSERT @Results
(
    [Date],
    [Result]
)
SELECT
    CAST(DATEADD(MI, @GmtOffset, BundleEvent.EventDate) AS DATE),
    Result = CASE WHEN @Metric = 'Take Rate' THEN 0 ELSE COUNT(*) END
FROM dbo.BundleEvent WITH (NOLOCK)
JOIN dbo.BundleUser WITH (NOLOCK)
    ON BundleEvent.BundleId = BundleUser.BundleId
JOIN dbo.Bundle WITH (NOLOCK)
    ON BundleEvent.BundleId = Bundle.BundleId
WHERE BundleEvent.EventDate >= @StartTimeGmt AND BundleEvent.EventDate <= @EndTimeGmt
    AND BundleEvent.BundleEventTypeId = CASE WHEN @Metric = 'Take Rate' THEN @TypeTakeId ELSE @BundleEventTypeId END
    AND BundleUser.UserId = CASE WHEN @UserId IS NULL THEN BundleUser.UserId ELSE @UserId END
    AND Bundle.BundleType = 1
GROUP BY
    CAST(DATEADD(MI, @GmtOffset, BundleEvent.EventDate) AS DATE)

然后我与格式交换进行了子比较：

            CAST(
                (SELECT COUNT(*)
                FROM dbo.BundleEvent WITH (NOLOCK)
                JOIN dbo.BundleUser WITH (NOLOCK)
                    ON BundleEvent.BundleId = BundleUser.BundleId
                JOIN dbo.Bundle WITH (NOLOCK)
                    ON BundleEvent.BundleId = Bundle.BundleId
                WHERE CAST(DATEADD(MI, @GmtOffset, BundleEvent.EventDate) AS DATE) = [Date]
                AND BundleEvent.BundleEventTypeId = @TypeTakeId
                AND BundleUser.UserId = CASE WHEN @UserId IS NULL THEN BundleUser.UserId ELSE @UserId END
                AND Bundle.BundleType = 1)
                AS DECIMAL(5,2)

因此本质上查询是正确匹配并使用新格式的。

Answer 1

由于您没有使用 and 聚合（除了 Count(*)），您确定需要 GROUP BY 吗？使用 DISTINCT 将为所有匹配日期生成一个插入条目；也许类似于：

DECLARE @DATE_GMT datetime
SET @DATE_GMT=DATEADD(MI, @GmtOffset, EventDate)
INSERT @Results
(
    [Day],
    [Month],
    [Year],
    [Result]
)
SELECT DISTINCT
    DATEPART(DD, @DATE_GMT),
    DATEPART(MM, @DATE_GMT),
    DATEPART(YY, @DATE_GMT),
    Result = CASE WHEN @Metric = 'Take Rate' THEN NULL ELSE COUNT(*) END
FROM BundleEvent
WHERE BundleEventTypeId = CASE WHEN @Metric = 'Take Rate' THEN @TypeTakeId ELSE @BundleEventTypeId END
    AND EventDate Between @StartTime AND @EndTime