用例何时将用户分组到同一列中数据重叠的Bucket中

Question

我希望根据事务类型列将用户划分为三个不同的类别。

1. Web用户
2. 存储用户
3. 混合用户

我的表中有以下列：

user_id | transactionType | ticketNum | saleAmount

目标：我想在确定user_id的saleAmount是网络用户、商店用户还是混合用户后，对其进行分组。目前，transactionType列将每次购买的数据显示为web或存储在单独的行中。

尝试的代码：

cache table service_bucket as
select user_id,
case
when transactionType= 'WEB' AND 'STORE' then "MIXED USER"
when transactionType = 'WEB' then "WEB USER"
when transactionType = 'STORE' then "STORE USER"
else "Mixed user"  end as serviceBucket, count(distinct ticketNum) as tixCount, sum(Quantity) as purchaseQuant
from base_table
group by 1,2
order by 1,2

问题似乎是，我的代码解析每一行，而不是在列中查找给定的user_id。导致每个用户有多行，没有一行以"0"结尾；混合用户"；。

不需要的输出示例：

>//tr>

user_id	serviceBucket	tixCount	purchaseQuant
5555	Web用户	7
5555	存储用户	7
5557	Web用户	4

听起来用户类型列也需要聚合：

CREATE TABLE service_bucket AS
SELECT user_id,
CASE WHEN COUNT(CASE WHEN transactionType = 'WEB' THEN 1 END) > 0 AND
COUNT(CASE WHEN transactionType = 'STORE' THEN 1 END) > 0
THEN 'MIXED USER'
WHEN COUNT(CASE WHEN transactionType = 'WEB' THEN 1 END) > 0
THEN 'WEB'
ELSE 'STORE'
END AS serviceBucket
COUNT(DISTINCT ticketNum) AS tixCount,
SUM(Quantity) AS purchaseQuant
FROM base_table
GROUP BY 1, 2
ORDER BY 1, 2;