来自SQL的周智能数据。 [英] Week wise data from SQL.
本文介绍了来自SQL的周智能数据。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的数据类似于
date数
-----------------------
2016-01-01 1
2016-01-02 1
2016-01-03 2
2016-01-04 1
2016-01-05 3
2016-01-06 4
2016-01-07 2
2016-01-08 5
2016-01-09 1
2016-01-10 2
2016-01-11 2
2016-01-12 1
2016-01-13 3
i需要一周的数据,第一周我需要第一周的数据,第二周我需要第1周+第2周和第3周我需要第1周+第2周+第3周数据
周明智(周一至周日)
我需要输出如下:
date | 2016年1月1日| 2016-01-04 | 2016-01-11
count | 4 | 22 | 28
解决方案
您需要做的就是创建名为运行总和,运行总计等的查询。 />
检查以下内容:
[ ^ ]
SQL SERVER - 如何在SQL Server中查找运行总计 - 使用Pinal Dave访问SQL权限 [ ^ ]
然后,你必须转移数据。这就是全部!
尝试:
DECLARE @ tmp 表(adate DATE ,acount INT )
INSERT INTO @ tmp (adate,acount)
VALUES (' 2016-01-01', 1 ),
(' 2016-01-02', 1 ),
(' 2016-01-03',< span class =code-digit> 2 ),
(' 2016-01- 04', 1 ),
('' 2016-01-05', 3 ),
(' 2016-01-06', 4 ),
(' 2016-01-07', 2 ),
(' 2016-01-08', 5 ),
(' 2016-01-09', 1 ),
(' 2016-01-10', 2 ),
(' < span class =code-string> 2016-01-11', 2 ),
(' 2016-01-12', 1 ),
( ' 2016-01-13', 3 )
SET DATEFIRST 1 ;
SELECT [ 1 ],[ 2 ],[ 3 ]
FROM (
SELECT a.weekno,
( SELECT SUM(acount) AS runningsum
FROM @ tmp AS b
WHERE DATEPART(wk,adate)< = a.weekno) AS RunningSum
FROM (
SELECT adate,acount,DATEPART(wk ,adate) AS weekno
FROM @ tmp
) AS a
GROUP BY a.weekno
) AS DT
PIVOT(SUM(runningsum) FOR weekno IN ([ 1 ],[ 2 ],[ 3 ])) AS pt
结果:
1 2 3
4 22 28
Hi,
I have data like
date count
-----------------------
2016-01-01 1
2016-01-02 1
2016-01-03 2
2016-01-04 1
2016-01-05 3
2016-01-06 4
2016-01-07 2
2016-01-08 5
2016-01-09 1
2016-01-10 2
2016-01-11 2
2016-01-12 1
2016-01-13 3
i want week wise data and for 1st week i need 1st week data, for 2nd week i need 1st week + 2nd week and for 3rd week i need 1st week + 2nd week + 3rd week data
week wise (Monday to sunday)
I need output as below:
date | 2016-01-01 | 2016-01-04 | 2016-01-11
count | 4 | 22 | 28
解决方案
All what you need to do is to create query called "running sum", "running totals", etc.
Check these:
Calculating simple running totals in SQL Server[^]
SQL SERVER - How to Find Running Total in SQL Server - Journey to SQL Authority with Pinal Dave[^]
Then, you have to pivot data. That's all!
Try:
DECLARE @tmp TABLE(adate DATE, acount INT) INSERT INTO @tmp(adate, acount) VALUES('2016-01-01', 1), ('2016-01-02', 1), ('2016-01-03', 2), ('2016-01-04', 1), ('2016-01-05', 3), ('2016-01-06', 4), ('2016-01-07', 2), ('2016-01-08', 5), ('2016-01-09', 1), ('2016-01-10', 2), ('2016-01-11', 2), ('2016-01-12', 1), ('2016-01-13', 3) SET DATEFIRST 1; SELECT [1], [2], [3] FROM ( SELECT a.weekno, (SELECT SUM(acount) AS runningsum FROM @tmp AS b WHERE DATEPART(wk, adate) <= a.weekno) AS RunningSum FROM ( SELECT adate, acount, DATEPART(wk, adate) AS weekno FROM @tmp ) AS a GROUP BY a.weekno ) AS DT PIVOT (SUM(runningsum) FOR weekno IN ([1], [2], [3])) AS pt
Result:
1 2 3 4 22 28
这篇关于来自SQL的周智能数据。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文