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]
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]
没有找到相关结果
重要提示:提问者不能发表回复,可以通过评论与回答者沟通,沟通后可以通过编辑功能完善问题描述,以便后续其他人能够更容易理解问题.
1 个回复
夏天online 2017-10-12 回答
赞同来自:
如果跨周 会有差异 和实际需求