如何按交易分组条件聚合列：披萨与配菜订单计算需求

需求说明 #

• 从交易数据中按uniqueorderid分组，提取pizza（披萨）和sides（配菜）两类产品，需区分三种交易类型并生成对应聚合结果：
  1. 仅含披萨、无配菜
  2. 仅含配菜、无披萨
  3. 同时含披萨和配菜
• 聚合计算规则：
  • 仅含披萨的订单：对该订单下所有tcounts求和后除以2，取上限值
  • 仅含配菜的订单：对该订单下所有tcounts求和后除以3，取上限值
  • 同时含两者的订单：
    • 若披萨的tcounts总和>2，订单所有tcounts求和后除以2，取上限值
    • 若披萨的tcounts总和<2，订单所有tcounts求和后除以3，取上限值

问题描述 #

初始编写的SQL代码无法正确实现上述条件聚合逻辑，存在错误聚合数据的问题，初始代码如下：

with q as (
select uniqueorderid, types, counts
,SUM(tcounts)  OVER (PARTITION BY UniqueOrderId) as counts
from k
order by uniqueorderid
)

select uniqueorderid, types, tcounts, ocounts
, case
when types = 'pizza' and 'sides' not in ( SELECT types FROM k ) THEN ceiling(tcounts/2)
end as counts

from q
order by uniqueorderid

样本数据 #

uniqueorderid,types,tcounts
7e,pizza,2.0
f4,sides,0.5
8c,sides,1.5
8c,pizza,3.0
bb,pizza,1.0
04,pizza,2.0

正确解决方案代码 #

SELECT uniqueorderid
       ,CASE 
         WHEN SUM(CASE WHEN types = 'pizza' THEN tcounts ELSE 0 END) = 0 
              AND SUM(CASE WHEN types = 'sides' THEN tcounts ELSE 0 END) > 0 
              THEN ceiling(SUM(tcounts) / 3.0)
         WHEN SUM(CASE WHEN types = 'sides' THEN tcounts ELSE 0 END) = 0 
              AND SUM(CASE WHEN types = 'pizza' THEN tcounts ELSE 0 END) > 0 
              THEN ceiling(SUM(tcounts) / 2.0)
         WHEN SUM(CASE WHEN types = 'pizza' THEN tcounts ELSE 0 END) > 0 
              AND SUM(CASE WHEN types = 'sides' THEN tcounts ELSE 0 END) > 0 
              THEN 
              CASE 
                WHEN SUM(CASE WHEN types = 'pizza' THEN tcounts ELSE 0 END) > 2 
                     THEN ceiling(SUM(tcounts) / 2.0 )
                ELSE ceiling(SUM(tcounts) / 3.0 )
              END
         ELSE NULL
       END AS pcounts
FROM k
GROUP BY uniqueorderid
order by uniqueorderid

内容的提问来源于stack exchange，提问作者tamarajqawasmeh