查找他们购买了一件物品和其他物品的交易和客户的计数SQL

Question

我有一个表，其中包含客户的交易详细信息：

Customernum   sls   unit    txn no   SKU      txn_date  
1       10     30      567    903633   2019-02-01   yes
1       20     30      567    123767   2019-02-01   yes
1       50     40      567    126658   2019-03-01   yes
1       10     40      345    773633   2019-02-10   yes
1       12     30      345    965322   2019-02-10 
1       10     50      678    838364   2019-02-15   yes
1       10     70      975    983636   2019-02-28   yes
2       11     80      910    903633   2019-02-11 
2       11     90      910    566373   2019-02-12   yes
3       11     62      855    678364   2019-02-12

我有另一张有SKU的桌子：

sku     Desc
123767  APP
903633  CD
773633  APP
838364  APP
983636  APP
566373  APP
126658  APP

我需要从上述交易中找到交易，该交易中有903633，以及SKU表中的其他SKU，以及具有上表中多个SKU的交易。

所以我想回答的两个问题是：

• 多少笔交易只包括一件服装和CD
• 有多少笔交易包括一件以上带有CD的服装

我尝试了以下查询，但没有成功：

select a.Customernum, a.txnno, td.sku,
case when ps.sku is not null
then 'Yes'
else 'No'
end as is_sku,
case when count(b.sku) over (partition by a.txnno) > 0
then 'Yes'
else 'No'
end as has_sku
from table a
left join sku b on b.sku = a.sku;

我期待以下结果：

结果1-有多少笔交易只包括一件服装和CD

customernum  txnno    unit    sls
2           910       80      11
2           910       90      11
1           678       50      10
1           975       70      10

结果2-有多少笔交易包括一件以上带有CD的服装。

customernum  txnno    unit    sls
1           567       30      10
1           567       30      20
1           567       40      50

Answer 1

使用两个级别的聚合。子查询统计"app"one_answers"cd"的数量。外部过滤器仅为一个"cd"(如果您需要至少一个，则可以使用>=(，并按"应用程序"的数量进行聚合：

select num_app, count(*) as num_customers
from (select t.customernum,
sum(case when s.desc = 'APP' then 1 else 0 end) as num_app,
sum(case when s.desc = 'CD' then 1 else 0 end) as num_cd
from transactions t join
skus s
on t.sku = s.sku
) c
where num_cd = 1
group by num_app
order by num_app;

Answer 2

您可以使用窗口函数进行筛选。

下面的查询将为您提供所有涉及sku903633和另一个sku:的事务

select *
from (
select
t.*,
count(*) over(partition by txnno) cnt,
max(case when sku = 903633 then 1 end) has_cd
from mytable t
) t
where has_cd = 1 and cnt = 2

要获得涉及sku903633和至少两个其他sku的事务，只需将cnt = 2更改为cnt > 2即可。