sql - 仅从同一表中的其他某些行中减去仅某些行的聚合？

Question

product     saletype    qty
-----------------------------
product1    regular     10
product1    sale        1
product1    feature     2

我有一个如上所示的销售表，产品可以通过 3 种不同方式中的一种进行销售(正常价格、促销价格或特色价格)。

所有销售，无论类型如何，都会累积为常规销售，但销售和功能也累积为自己的“销售类型”。因此，在上面的示例中，我总共销售了 10 种产品(7 种常规产品、1 种促销产品、2 种特色产品)。

我想尽可能高效地返回减去其他两列的常规数量，以及其他销售类型。这是我目前的做法:

create table query_test
(product varchar(20), saletype varchar(20), qty int);

insert into query_test values 
('product1','regular',10),
('product1','sale',1),
('product1','feature',2)

select
    qt.product,
    qt.saletype,
    CASE WHEN qt.saletype = 'regular' THEN sum(qt.qty)-sum(lj.qty) ELSE sum(qt.qty) END as [qty]
from
    query_test qt
    left join
    (
        select product, sum(qty) as [qty]
        from query_test
        where saletype in ('sale','feature')
        group by product
    ) lj on lj.product=qt.product
group by
    qt.product, qt.saletype;

...这产生了我所追求的:

product     saletype    qty
-----------------------------
product1    feature     2
product1    regular     7
product1    sale        1

但我觉得必须有一种比本质上查询相同信息两次更好的方法。

Answer 1

您可以使用窗口函数 sum 和一些算术来执行此操作。

select product,
saletype,
case when saletype='regular' then 2*qty-sum(qty) over(partition by product) 
else qty end as qty
from query_test

这假设销售类型“常规”至多有一行。