I have a large data set which for each day in the data set I need to calculate the aggregate activity for the previous 5, 7, and 10 days for every single row, and not just against the current date and this is not a straight running total overall time.. but for a specific period.
我有一个大型数据集,我需要为数据集中的每一天计算每一行的前5天,7天和10天的总活动,而不是仅针对当前日期,这不是直接运行总的总时间..但是在特定时期。
I have the daily aggregates built for the activity but having trouble aggregating date and object and then calculating the past X intervals for each individual row.
我有为活动构建的每日聚合,但无法汇总日期和对象,然后计算每个行的过去X间隔。
I am using MS SQL Server 2008 R2.
我正在使用MS SQL Server 2008 R2。
So for example using -1 day (yesterday), -2 days, -3 days
例如,使用-1天(昨天), - 2天,-3天
-
Day -1 (Previous Day/Yesterday)
第-1天(前一天/昨天)
- Object A 2
- Object B 231
- Object C 232
对象A 2
对象B 231
对象C 232
-
Day -2
- Object A 5
- Object B 117
- Object C 127
对象A 5
对象B 117
对象C 127
- Day -3
- Object A 9
- Object B 200
- Object C 175
对象A 9
对象B 200
对象C 175
第2天对象A 5对象B 117对象C 127
第-3天对象A 9对象B 200对象C 175
My expected result would be running on current day (but NOT current date)
我的预期结果将在当天运行(但不是当前日期)
- Date, _TID , 1D, 2D, 3D (Columns)
- Date, Object A, 2, 7, 16
- Date, Object B, 231, 579, 779
- Date, Object C, 232, 359, 534
日期,_TID,1D,2D,3D(列)
日期,对象A,2,7,16
Date,Object B,231,579,779
Date,Object C,232,359,534
I looked at How to Query Totals for Last 7 Days and the better SQL SUM of Last X Records but that is a sum total of all activity over the last X period and across all records and returned it for all rows, where as I need the query to aggregate for the period specific to the object.
我查看了如何查询过去7天的总计和最后X个记录的更好的SQL SUM,但这是过去X期间和所有记录中所有活动的总和,并返回所有行,我需要查询以聚合特定于对象的时间段。
;WITH [stSUM] AS
(
SELECT
TX._TID
,CAST(TX.DT_Req AS DATE) as AgrGrp
,TX.DayofYr
,TX.MofY
,TX.DayofM
,TX.DayofW
,TX.TrdPayDay
,TX.Hdy_Govt
,TX.Hdy_Novel
,SUM(CASE WHEN TX.TX_BG = 'Evt' THEN 1 ELSE 0 END) as EvtCnt
,SUM(CASE WHEN TX.TX_Yes=1 THEN TX.Rqt_Amt ELSE 0 END) as Amt
FROM CRS1.dbo.JustData
Group By
CAST(TX.DT_Req AS DATE)
,TX._TID
,TX.DayofYr
,TX.MofY
,TX.DayofM
,TX.DayofW
,TX.TrdPayDay
,TX.Hdy_Govt
,TX.Hdy_Novel
)
SELECT AgrGrp, _TID,
(select sum(Amt) from (select top 7 * from stSUM bd2 where bd2.AgrGrp <= AgrGrp order by AgrGrp desc) t1 )
as Measured7,
(select sum(Amt) from (select top 5 * from stSUM bd2 where bd2.AgrGrp <= AgrGrp order by AgrGrp desc) t2 )
as Measured5
FROM [stSUM]
GROUP BY AgrGrp, _TID
ORDER BY AgrGrpBut it produces output like this which is incorrect as each row should have a different value only reflecting the content of that _TID for the previous days. That is Measured7 is back to 2014-12-01 and Measured5 is back to 2014-12-03.
但它会生成这样的输出,这是不正确的,因为每行应该有一个不同的值,仅反映前几天_TID的内容。这是Measured7回到2014-12-01,Measured5回到2014-12-03。
AgrGrp _TID Measured7 Measured5
2014-12-08 0101 14400 8620
2014-12-08 0558 14400 8620
2014-12-08 0537 14400 86201 个解决方案
#1
1
My approach was to summarize the day ranges I want in sub queries using a BETWEEN dates.
我的方法是使用BETWEEN日期在子查询中总结我想要的日期范围。
;with DayInfo as
(
select cast(runtime as date) [Date],
targetType,
round(sum(duration),0) as duration
from Logs
group by cast(runtime as date),
targetType)
select di.Date,
di.targetType,
di.duration,
d2.d2DurationSum,
d7.d7DurationSum
from DayInfo di
join (select d2.Date,
d2.targetType,
sum(dd2.duration) d2DurationSum
from DayInfo d2
join DayInfo dd2
on dd2.Date between dateadd(day, -1, d2.Date) and d2.Date
and d2.targetType = dd2.targetType
group by d2.Date,
d2.targetType) as d2
on di.Date = d2.Date
and di.targetType = d2.targetType
join (select d7.Date,
d7.targetType,
sum(dd7.duration) d7DurationSum
from DayInfo d7
join DayInfo dd7
on dd7.Date between dateadd(day, -6, d7.Date) and d7.Date
and d7.targetType = dd7.targetType
group by d7.Date,
d7.targetType) as d7
on di.Date = d7.Date
and di.targetType = d7.targetType
order by 1, 2
This gives the output like you described.
这给出了你描述的输出。
#1
1
My approach was to summarize the day ranges I want in sub queries using a BETWEEN dates.
我的方法是使用BETWEEN日期在子查询中总结我想要的日期范围。
;with DayInfo as
(
select cast(runtime as date) [Date],
targetType,
round(sum(duration),0) as duration
from Logs
group by cast(runtime as date),
targetType)
select di.Date,
di.targetType,
di.duration,
d2.d2DurationSum,
d7.d7DurationSum
from DayInfo di
join (select d2.Date,
d2.targetType,
sum(dd2.duration) d2DurationSum
from DayInfo d2
join DayInfo dd2
on dd2.Date between dateadd(day, -1, d2.Date) and d2.Date
and d2.targetType = dd2.targetType
group by d2.Date,
d2.targetType) as d2
on di.Date = d2.Date
and di.targetType = d2.targetType
join (select d7.Date,
d7.targetType,
sum(dd7.duration) d7DurationSum
from DayInfo d7
join DayInfo dd7
on dd7.Date between dateadd(day, -6, d7.Date) and d7.Date
and d7.targetType = dd7.targetType
group by d7.Date,
d7.targetType) as d7
on di.Date = d7.Date
and di.targetType = d7.targetType
order by 1, 2
This gives the output like you described.
这给出了你描述的输出。