多个参数分组的累计总和 [英] Cumulative Sum grouped by multiple parameters
本文介绍了多个参数分组的累计总和的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我需要订单数量"的累计金额.
I need Cumulative sum for 'Order Qty'.
当我将此代码与选择的字段"作为销售年份"，销售发票日期"一起使用时， "Order_Qty"正在获得我需要的结果.
When i use this code with Select fields as "Sales_Year","Sales_Billing_Date", "Order_Qty" am getting the result i needed.
使用的代码:
"Sales_Year",
"Sales_Billing_Date",
"Order_Qty" ,
SUM ("Order_Qty") OVER ( ORDER BY "Sales_Billing_Date") AS "SUM_VAL"
FROM
( SELECT
"Sales_Year",
"Sales_Billing_Date" ,
SUM("Order_Qty") AS "Order_Qty" FROM "PS_WARRANTY_ANALYTICS"."PS_PAL_DLR_SALES"
GROUP BY "Sales_Year","Sales_Billing_Date"
)
但是我选择了其他字段(添加了"Equipment_ID")，因此没有按其唯一字段分组.如何才能实现包括该字段在内的累计值 添加代码
But i select more fields(added "Equipment_ID") its not getting grouped by as its unique field. how can in achieve cumulative value including this field code added
"Equipment_ID"
SELECT
"Sales_Year",
"Sales_Billing_Date",
"Equipment_ID",
"Order_Qty" ,
SUM ("Order_Qty") OVER ( ORDER BY "Sales_Billing_Date") AS "SUM_VAL"
FROM
( SELECT
"Sales_Year",
"Sales_Billing_Date" ,
"Equipment_ID",
SUM("Order_Qty") AS "Order_Qty" FROM "PS_WARRANTY_ANALYTICS"."PS_PAL_DLR_SALES"
GROUP BY "Sales_Year","Sales_Billing_Date","Equipment_ID"
)
累计:
不累积:
推荐答案
假设您的子查询返回了类似的内容
Assuming your sub query returns something like this
yyyy orderdate productid sumqty
----------- ----------------------- ----------- -----------
2005 2005-07-01 00:00:00.000 707 24
2005 2005-08-01 00:00:00.000 707 58
2005 2005-09-01 00:00:00.000 707 55
2005 2005-07-01 00:00:00.000 708 27
2005 2005-08-01 00:00:00.000 708 56
2005 2005-09-01 00:00:00.000 708 57
2005 2005-07-01 00:00:00.000 709 38
2005 2005-08-01 00:00:00.000 709 134
2005 2005-09-01 00:00:00.000 709 79
2005 2005-07-01 00:00:00.000 710 5
2005 2005-08-01 00:00:00.000 710 13
2005 2005-09-01 00:00:00.000 710 6
2005 2005-07-01 00:00:00.000 711 33
2005 2005-08-01 00:00:00.000 711 64
2005 2005-09-01 00:00:00.000 711 49
2005 2005-07-01 00:00:00.000 712 40
2005 2005-08-01 00:00:00.000 712 103
2005 2005-09-01 00:00:00.000 712 83
2005 2005-07-01 00:00:00.000 714 16
2005 2005-08-01 00:00:00.000 714 37
2005 2005-09-01 00:00:00.000 714 35
2005 2005-07-01 00:00:00.000 715 49
2005 2005-08-01 00:00:00.000 715 114
2005 2005-09-01 00:00:00.000 715 72
2005 2005-07-01 00:00:00.000 716 19
2005 2005-08-01 00:00:00.000 716 48
2005 2005-09-01 00:00:00.000 716 40
2005 2005-07-01 00:00:00.000 722 8
2005 2005-08-01 00:00:00.000 722 20
2005 2005-09-01 00:00:00.000 722 7
2005 2005-07-01 00:00:00.000 725 15
2005 2005-08-01 00:00:00.000 725 38
2005 2005-09-01 00:00:00.000 725 29
2005 2005-07-01 00:00:00.000 726 9
2005 2005-08-01 00:00:00.000 726 19
2005 2005-09-01 00:00:00.000 726 7
2005 2005-07-01 00:00:00.000 729 16
2005 2005-08-01 00:00:00.000 729 41
2005 2005-09-01 00:00:00.000 729 25
(39 row(s) affected)
然后假设sap的行为类似于sqlserver
And assuming sap behaves like sqlserver then
select * ,
sum(sumqty) over (partition by year(orderdate), orderdate order by productid ) cumtot
from
(
select year(orderdate) yyyy,orderdate,productid,sum(OrderQty) sumqty
from [AdventureWorks2012].[Sales].[SalesOrderHeader] soh
join [AdventureWorks2012].[Sales].[SalesOrderDetail] sod on sod.SalesOrderID = soh.SalesOrderID
where year(orderdate) = 2005 and month(orderdate) in (7,8,9) and day(orderdate) in (1,2,3)
AND PRODUCTID < 730
group by year(orderdate),orderdate,productid
) s
order by yyyy,orderdate,productid
生产
yyyy orderdate productid sumqty cumtot
----------- ----------------------- ----------- ----------- -----------
2005 2005-07-01 00:00:00.000 707 24 24
2005 2005-07-01 00:00:00.000 708 27 51
2005 2005-07-01 00:00:00.000 709 38 89
2005 2005-07-01 00:00:00.000 710 5 94
2005 2005-07-01 00:00:00.000 711 33 127
2005 2005-07-01 00:00:00.000 712 40 167
2005 2005-07-01 00:00:00.000 714 16 183
2005 2005-07-01 00:00:00.000 715 49 232
2005 2005-07-01 00:00:00.000 716 19 251
2005 2005-07-01 00:00:00.000 722 8 259
2005 2005-07-01 00:00:00.000 725 15 274
2005 2005-07-01 00:00:00.000 726 9 283
2005 2005-07-01 00:00:00.000 729 16 299
2005 2005-08-01 00:00:00.000 707 58 58
2005 2005-08-01 00:00:00.000 708 56 114
2005 2005-08-01 00:00:00.000 709 134 248
2005 2005-08-01 00:00:00.000 710 13 261
2005 2005-08-01 00:00:00.000 711 64 325
2005 2005-08-01 00:00:00.000 712 103 428
2005 2005-08-01 00:00:00.000 714 37 465
2005 2005-08-01 00:00:00.000 715 114 579
2005 2005-08-01 00:00:00.000 716 48 627
2005 2005-08-01 00:00:00.000 722 20 647
2005 2005-08-01 00:00:00.000 725 38 685
2005 2005-08-01 00:00:00.000 726 19 704
2005 2005-08-01 00:00:00.000 729 41 745
2005 2005-09-01 00:00:00.000 707 55 55
2005 2005-09-01 00:00:00.000 708 57 112
2005 2005-09-01 00:00:00.000 709 79 191
2005 2005-09-01 00:00:00.000 710 6 197
2005 2005-09-01 00:00:00.000 711 49 246
2005 2005-09-01 00:00:00.000 712 83 329
2005 2005-09-01 00:00:00.000 714 35 364
2005 2005-09-01 00:00:00.000 715 72 436
2005 2005-09-01 00:00:00.000 716 40 476
2005 2005-09-01 00:00:00.000 722 7 483
2005 2005-09-01 00:00:00.000 725 29 512
2005 2005-09-01 00:00:00.000 726 7 519
2005 2005-09-01 00:00:00.000 729 25 544
(39 row(s) affected)
这篇关于多个参数分组的累计总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
