Oracle汇总折扣

Question

需要以下查询帮助以获得汇总折扣。

我有一件商品，根据购买数量有一些折扣。

Stretch / FromUnit / DiscType / Discount
1 / 0 / Default / 5
1 / 0 / Extra / 15
2 / 7 / Extra / 17
3 / 10 / Extra / 20
1 / 0 / Bonus / 5

我需要一个查询来获得以下结果:

Stretch / FromUnit / Discount
1 / 0 / 25
2 / 7 / 27
3 / 10 / 30

正如你所看到的，默认折扣(5)适用于所有数量，奖励折扣也是如此，我需要将其与其他折扣结合起来，这取决于它们被分配的延伸。

像下面这样使用"sum over partition"找到一些方法，但我找不到正确的方法…

select stretch, fromUnit, sum(discount) over (partition by stretch) discount
from (select t.*
,row_number() over (partition by stretch, fromUnit, DiscType
order by rownum) as rn
from table t);

希望我提前解释了自己和感谢。

Answer 1

将非Extra添加到Extra

select t.Stretch, t.FromUnit, t.Discount + ne.s
from mytable t
cross join (
select sum(Discount) s
from mytable
where DiscType != 'Extra'
) ne
where t.DiscType = 'Extra'

Answer 2

您可以使用:

SELECT Stretch,
SUM(FromUnit) AS FromUnit,
SUM(Discount) + MAX(extra_discount) AS discount
FROM   (
SELECT t.*,
SUM(CASE WHEN DiscType IN ('Default', 'Bonus') THEN Discount END)
OVER () AS extra_discount
FROM   table_name t
)
WHERE DiscType NOT IN ('Default', 'Bonus')
GROUP BY Stretch

对于样本数据:

CREATE TABLE table_name ( Stretch, FromUnit, DiscType, Discount ) AS
SELECT 1,  0, 'Default',  5 FROM DUAL UNION ALL
SELECT 1,  0, 'Extra',   15 FROM DUAL UNION ALL
SELECT 2,  7, 'Extra',   17 FROM DUAL UNION ALL
SELECT 3, 10, 'Extra',   20 FROM DUAL UNION ALL
SELECT 1,  0, 'Bonus',    5 FROM DUAL;

输出:

tbody> <<tr>1

STRETCH	FROMUNIT	DISCOUNT
2	7	27
3	10	30
	0	25