如何在SQL中汇总7天
我试图在SQL Server中汇总2013财年的7天数据(开始日期为10/1/2012,结束于2013年9月30日),但至今还没有运气。有人可以请看看。以下是我的示例数据。如何在SQL中汇总7天
DATE BREAD MILK
10/1/12 1 3
10/2/12 2 4
10/3/12 2 3
10/4/12 0 4
10/5/12 4 0
10/6/12 2 1
10/7/12 1 3
10/8/12 2 4
10/9/12 2 3
10/10/12 0 4
10/11/12 4 0
10/12/12 2 1
10/13/12 2 1
所以,我期望的输出会是这样:
DATE BREAD MILK
10/1/12 1 3
10/2/12 2 4
10/3/12 2 3
10/4/12 0 4
10/5/12 4 0
10/6/12 2 1
Total 11 15
10/7/12 1 3
10/8/12 2 4
10/9/12 2 3
10/10/12 0 4
10/11/12 4 0
10/12/12 2 1
10/13/12 2 1
Total 13 16
--------through 9/30/2013
请注意,自2013财年开始于2012年10月1日和2012年9月30日结束,2013财年的第一周6天而不是7天。
我正在使用SQL Server 2008.
您可以通过每周的日期值添加一个新的计算列将它们分组,总结其他列,这样的事情:
SELECT DATEPART(ww, DATEADD(d,-2,[DATE])) AS WEEK_NO,
SUM(Bread) AS Bread_Total, SUM(Milk) as Milk_Total
FROM YOUR_TABLE
GROUP BY DATEPART(ww, DATEADD(d,-2,[DATE]))
注意:我用DATEADD
和减去2天设定周一到周一的第一天根据你的日期。如果需要,您可以修改它。
这里是它如何能够做到的总体思路:
你需要一个派生列的每一行,以确定哪些财政本周记录属于。一般来说,您可以从10/1中减去该记录的日期,获取已过去的天数,再除以7,然后确定结果。
然后你可以将GROUP BY派生列并使用SUM聚合函数。
最大的折痕是,你开始的6天。您可能需要添加一些逻辑,以确保星期几从星期天开始或在您使用的任何一天,但这应该让您开始。
上面的WITH ROLLUP建议可以帮助;您需要保存数据并根据需要进行转换。
你需要做的最重要的事情就是正确识别你的周。如果您没有将这些内容加载到表格中,以便您可以识别它们,则可以即时构建它们。下面是其中一种做法:
CREATE TABLE #fy (fyear int, fstart datetime, fend datetime);
CREATE TABLE #fylist(fyyear int, fydate DATETIME, fyweek int);
INSERT INTO #fy
SELECT 2012, '2011-10-01', '2012-09-30'
UNION ALL
SELECT 2013, '2012-10-01', '2013-09-30';
INSERT INTO #fylist
(fyyear, fydate)
SELECT fyear, DATEADD(DAY, Number, DATEADD(DAY, -1, fy.fstart)) AS fydate
FROM Common.NUMBERS
CROSS APPLY (SELECT * FROM #fy WHERE fyear = 2013) fy
WHERE fy.fend >= DATEADD(DAY, Number, DATEADD(DAY, -1, fy.fstart));
WITH weekcalc AS
(
SELECT DISTINCT DATEPART(YEAR, fydate) yr, DATEPART(week, fydate) dt
FROM #fylist
),
ridcalc AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY yr, dt) AS rid, yr, dt
FROM weekcalc
)
UPDATE #fylist
SET fyweek = rid
FROM #fylist
JOIN ridcalc
ON DATEPART(YEAR, fydate) = yr
AND DATEPART(week, fydate) = dt;
SELECT list.fyyear, list.fyweek, p.[date], COUNT(bread) AS Bread, COUNT(Milk) AS Milk
FROM products p
JOIN #fylist list
ON p.[date] = list.fydate
GROUP BY list.fyyear, list.fyweek, p.[date] WITH ROLLUP;
以上Common.Numbers
参考的是,我用这样的事情(从1到1M)一个简单的数字表。您也可以根据需要即时创建它。
使用选项与GROUP BY ROLLUP操作
SELECT CASE WHEN DATE IS NULL THEN 'Total' ELSE CONVERT(nvarchar(10), DATE, 101) END AS DATE,
SUM(BREAD) AS BREAD, SUM(MILK) AS MILK
FROM dbo.test54
GROUP BY ROLLUP(DATE),(DATENAME(week, DATE))
演示上SQLFiddle
结果:
DATE BREAD MILK
10/01/2012 1 3
10/02/2012 2 4
10/03/2012 2 3
10/04/2012 0 4
10/05/2012 4 0
10/06/2012 2 1
Total 11 15
10/07/2012 1 3
10/08/2012 4 7
10/10/2012 0 4
10/11/2012 4 0
10/12/2012 2 1
10/13/2012 2 1
Total 13 16
非常感谢,也感谢其他的温柔。它完美的作品。再次感谢。 – joe 2013-03-26 11:10:15
@joe FYI,如果您的数据在某些年份(例如2012,2013等),那么您需要此GROUP BY子句:GROUP BY ROLLUP(DATE),(DATENAME(year,DATE)),(DATENAME(week,日期)) – 2013-03-26 11:40:24
只需创建一个'week'列,并使用窗口函数来总结'bread'和'奶''按星期'划分'。 – 2013-03-25 16:15:49
你是否有一个包含财务日期信息的日历表?这应该有所需的“周”栏,这应该使其余的微不足道。 – 2013-03-25 16:38:38
不,我不。谢谢 – joe 2013-03-26 11:09:05