按周细分

Question

下面是一个简单的查询和结果： 是否有一种方法可以按 7 天聚合总事件数，然后对总事件数求和？汇总功能会起作用吗？我正在使用 SQL SERVER 05 & 08. 再次感谢各位。

SELECT DATE_SOLD, count(DISTINCT PRODUCTS) AS PRODUCT_SOLD    
FROM PRODUCTS    
WHERE DATE >='10/1/2009' 
and DATE <'10/1/2010'
GROUP BY DATE_SOLD

结果：

DATE_SOLD            PRODUCT_SOLD
10/1/09          5
10/2/09          11
10/3/09          14
10/4/09          6
10/5/09          11
10/6/09          13
10/7/09          10
Total              70

10/8/09          4
10/9/09          11
10/10/09             8
10/11/09             4
10/12/09             7
10/13/09             4
10/14/09             9
Total              47

Answer 1

我认为您所追求的就是没有您的表格设计可以使用（尽管我不得不承认输出需要清理）。它至少应该可以帮助您找到所需的解决方案。

CREATE TABLE MyTable( 
event_date date,
event_type char(1)
)
GO

INSERT MyTable VALUES ('2009-1-01', 'A')
INSERT MyTable VALUES ('2009-1-11', 'B')
INSERT MyTable VALUES ('2009-1-11', 'C')
INSERT MyTable VALUES ('2009-1-20', 'N')
INSERT MyTable VALUES ('2009-1-20', 'N')
INSERT MyTable VALUES ('2009-5-23', 'D')
INSERT MyTable VALUES ('2009-5-23', 'E')
INSERT MyTable VALUES ('2009-5-10', 'F')
INSERT MyTable VALUES ('2009-5-10', 'F')
GO

WITH T AS (
    SELECT DATEPART(MONTH, event_date) event_month, event_date, event_type 
    FROM MyTable
)   

SELECT CASE WHEN (GROUPING(event_month) = 0) 
            THEN event_month ELSE '99' END AS event_month,
       CASE WHEN (GROUPING(event_date) = 1) 
            THEN '9999-12-31' ELSE event_date END AS event_date,
    COUNT(DISTINCT event_type) AS event_count
FROM T
GROUP BY event_month, event_date WITH ROLLUP
ORDER BY event_month, event_date

这给出了以下输出：

event_month event_date event_count
1           2009-01-01      1
1           2009-01-11      2
1           2009-01-20      1
1           9999-12-31      4
5           2009-05-10      1
5           2009-05-23      2
5           9999-12-31      3
99          9999-12-31      7

其中月份的“99”和年份的“9999-12-31”是总数。

Answer 2

SELECT DATEDIFF(week, 0, DATE_SOLD) Week,
    DATEADD(week, DATEDIFF(week, 0, DATE_SOLD), 0) From,
    DATEADD(week, DATEDIFF(week, 0, DATE_SOLD), 0) + 6 To,
    COUNT(DISTINCT PRODUCTS) PRODUCT_SOLD
FROM dbo.PRODUCTS
WHERE DATE >= '2009-10-01' 
    AND DATE < '2010-10-01'
GROUP BY DATEDIFF(week, 0, DATE_SOLD) WITH ROLLUP
ORDER BY DATEDIFF(week, 0, DATE_SOLD)