sql server 开窗函数 SUM() OVER () 只累加8周的和

0
计算前8周的和  现在用的方法是自关联 大于等于的方式  开窗函数该如何实现呢  谢谢
AND T1.[Week_Nature] >= T2.[Week_Nature]
AND T1.[Week_Nature] - 7 <= T2.[Week_Nature]
 
-- 计算前8周
SELECT
T1.ROWID,
T1.ID,
T1.[Year],
T1.[Week],
T1.[Week_Nature],
T1.[STISum],
SUM(T2.STISum) AS [EightWeekSum],
T1.[BP],
T1.[BP_Name],
T1.[BP_Type],
T1.[ProductGroupNo],
T1.[ChannelNo],
T1.[Province],
T1.[Sub_Region],
T1.[Region],
T1.[FA],
T1.[LineofBusniess],
T1.[ProductGroup],
T1.[ProductCategory],
T1.[ProductBrand],
T1.[ProductSeries],
T1.[ProductSub_Series]
FROM      #5S_YT_STI_T1 T1
LEFT JOIN #5S_YT_STI_T1 T2 ON 
(
T1.[BP] = T2.[BP]
AND T1.[BP_Name] = T2.[BP_Name]
AND T1.[BP_Type] = T2.[BP_Type]
AND T1.[ProductGroupNo] = T2.[ProductGroupNo]
AND T1.[ChannelNo] = T2.[ChannelNo]
AND T1.[Province] = T2.[Province]
AND T1.[Sub_Region] = T2.[Sub_Region]
AND T1.[Region] = T2.[Region]
AND T1.[FA] = T2.[FA]
AND T1.[LineofBusniess] = T2.[LineofBusniess]
AND T1.[ProductGroup] = T2.[ProductGroup]
AND T1.[ProductCategory] = T2.[ProductCategory]
AND T1.[ProductBrand] = T2.[ProductBrand]
AND T1.[ProductSeries] = T2.[ProductSeries]
AND T1.[ProductSub_Series] = T2.[ProductSub_Series]
AND T1.[Year] = T2.[Year]
AND T1.[Week_Nature] >= T2.[Week_Nature]
AND T1.[Week_Nature] - 7 <= T2.[Week_Nature]
)
GROUP BY T1.ROWID, T1.ID, T1.[Year], T1.[Week], T1.[Week_Nature], T1.[STISum], T1.[BP], T1.[BP_Name], T1.[BP_Type], T1.[ProductGroupNo], T1.[ChannelNo], T1.[Province],
T1.[Sub_Region], T1.[Region], T1.[FA], T1.[LineofBusniess], T1.[ProductGroup], T1.[ProductCategory], T1.[ProductBrand], T1.[ProductSeries], T1.[ProductSub_Series]
 
 


微信图片_20171012113318.png
已邀请:
0

夏天online 2017-10-12 回答

找到方法了  加 这个 ROWS BETWEEN 7 PRECEDING AND CURRENT ROW
 
如果跨周  会有差异  和实际需求

要回复问题请先登录注册