时间戳

首页 > 解决方案 > 如何在 SQL 中聚合数量？

sql - 如何在 SQL 中聚合数量？

问题描述

我正在尝试查找拆分和单笔交易的数量总和、txns 计数、dollar_value_us 总和以及保证金的计算。

下面是我创建的数据库的链接：

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=97be115a173cd7dbdb3e4a6a6ed35a16

我已经有这个查询来查找单个和拆分的事务数：

select count(distinct case when is_split = 'Yes' then transaction_number end) as split,
  count(distinct case when is_split = 'No' then transaction_number end) as single
from (
  select td.transaction_number, td.sku,
    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 'Yes'
         else 'No'
    end as is_split
  from transaction_detail_mv td
  left join pod_sku ps on ps.sku = td.sku
)
where has_pod_sku = 'Yes';

我正在使用以下代码进行聚合：

    select count(distinct case when is_split = 'Yes' then transaction_number end) as split,
  count(distinct case when is_split = 'No' then transaction_number end) as single,
  sum(case when is_split = 'Yes' then quantity end) as quantity_sp,
  sum(case when is_split = 'No' then quantity end) as quantity_sn,
  sum(case when is_split = 'Yes' then dollar_value_us end) as spend_sp,
  sum(case when is_split = 'No' then dollar_value_us end) as spend_sn,
  count(distinct case when is_split = 'Yes' then individual_id end) as indiv_sp,
  count(distinct case when is_split = 'No' then individual_id end) as indiv_sn,
  sum(case when is_split = 'Yes' then (DOLLAR_VALUE_US-(COGS*quantity)) end) as MARGIN_sp,
  sum(case when is_split = 'No' then (DOLLAR_VALUE_US-(COGS*quantity)) end) as MARGIN_sN

from (
      select td.transaction_number, td.sku,
        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 'Yes'
             else 'No'
        end as is_split
      from transaction_detail_mv td
      left join pod_sku ps on ps.sku = td.sku
    )
    where has_pod_sku = 'Yes';

我正在寻找如下输出：

在此处输入图像描述

标签： sqloracle-sqldeveloper

解决方案

而不是在列中聚合每个事务类型（拆分/单一），GROUP BY事务类型：

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 transaction_detail_mv td
          LEFT OUTER
          JOIN pod_sku ps on ps.sku = td.sku
     ) v
 WHERE has_pod_sku = 'Yes'
 GROUP BY v.transaction_type

工作查询。

推荐阅读