我使用下面的查询来查找每个类的单个和拆分事务。
首先，我从主sku表中创建了一个包含class1 sku的表，然后又为其余9个类创建了9个表。我必须为每个类运行下面的查询..有没有更好的方法一次为所有类运行一个查询？

SELECT v.transaction_type
     , COUNT(DISTINCT v.transaction_number) AS transaction_count
     , COUNT(DISTINCT v.individual_id) AS customer_count
     , SUM(v.quantity) AS units
     , SUM(v.dollar_value_us) AS sales
     , SUM(v.dollar_value_us - v.cogs * v.quantity) AS profit
  FROM (SELECT td.transaction_number, td.sku, td.quantity, td.dollar_value_us, td.individual_id, td.cogs,
               CASE 
                 WHEN COUNT(ps.sku) OVER (PARTITION BY td.transaction_number) > 0
                 THEN 'Yes'
                 ELSE 'No'
               END AS has_pod_sku,
               CASE  
                 WHEN COUNT(ps.sku) OVER (PARTITION BY td.transaction_number) > 0
                  AND COUNT(ps.sku) OVER (PARTITION BY td.transaction_number)
                    < COUNT(*) OVER (PARTITION BY td.transaction_number)
                 THEN 'Split'
                 ELSE 'Single'
               END AS transaction_type
          FROM dm_owner.transaction td
          LEFT OUTER
          JOIN class1 ps on ps.sku = td.sku

          where TD.brand_org_code = 'brand'
and is_merch = 1
and line_item_amt_type_cd = 'S' 

and trunc(TXN_DATE) between '01-JAN-19' AND '31-DEC-19' 
     ) v
 WHERE has_pod_sku = 'Yes'
 GROUP BY v.transaction_type

这是我创建的示例数据库：
[链接]https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=07e5283cb4c06e4260ff266ff5d36f1a