sql - 在 SQL Server 2005 中按小时、天、月创建订单计数摘要的最有效方法是什么？

Question

给定一个表:

create table #orders (
    orderid int,
    orderdatetime datetime
)

编写 sql 以输出包含当前和前 24 小时的订单计数、当天和前 7 天的总订单数、本周和前 4 周的总订单数、以及当月和前 6 个月的总订单数？

我想知道是否可以使用分析函数将其有效地汇总到单个 sql 中，或者生成 4 组数据的 4 条 sql 语句是否是唯一(或最佳)方式。

此外，考虑到按小时/天/周分组，如何在 sql server 中执行此操作？每次我必须用它们做这样的事情时，日期时间似乎很痛苦......

想法？放入 SSAS 多维数据集并从那里开始？

Answer 1

SELECT  DATEPART(month, orderdatetime), DATEPART(week, orderdatetime), DATEPART(day, orderdatetime), COUNT(*)
FROM    #orders
GROUP BY
        DATEPART(month, orderdatetime), DATEPART(week, orderdatetime), DATEPART(day, orderdatetime) WITH ROLLUP

这会将 COUNT 分组在单个查询中按天、周和月显示。

周汇总将有一个 NULL在 DATEPART(day, orderdatetime)列，月份汇总将有一个 NULL在两个DATEPART(day, orderdatetime)和 DATEPART(week, orderdatetime)列。

要使当前的每一小时、每一天、每一周或每一月都没有间隔，请使用 CTE的:

WITH    q_hours AS
        (
        SELECT  0 AS col_hour
        UNION ALL
        SELECT  col_hour + 1
        FROM    q_hours
        WHERE   col_hour < 22
        ),
        q_days AS
        (
        SELECT  0 AS col_day
        UNION ALL
        SELECT  col_day + 1
        FROM    q_days
        WHERE   col_day < 31
        ),
        q_months AS
        (
        SELECT  0 AS col_month
        UNION ALL
        SELECT  col_month + 1
        FROM    q_months
        WHERE   col_month < 12
        )
SELECT  col_month, col_day, col_hour, COUNT(orderid)
FROM    q_hours
CROSS JOIN
        q_days
CROSS JOIN
        q_months
LEFT JOIN
        #orders
ON      DATEDIFF(month, orderdatetime, GETDATE()) = col_month
        AND DATEDIFF(day, orderdatetime, GETDATE()) % 31 = col_day
        AND DATEDIFF(hour, orderdatetime, GETDATE()) % 24 = col_hour
GROUP BY
        col_month, col_day, col_hour WITH ROLLUP
HAVING  (
        col_month = 0
        AND col_day = 0
        AND col_hour IS NOT NULL
        ) -- all hours within 24 hours from now
        OR
        (
        col_month = 0
        AND col_day <= 7
        AND col_hour IS NULL
        ) -- all days within 7 days from now
        OR
        (
        col_month <= 6
        AND col_day IS NULL
        AND col_hour IS NULL
        ) -- all months within 6 months from now