Блог

Я пытаюсь найти сумму количеств, количество txns, сумму dollar_value_us и расчет маржи как для разделения, так и для отдельных транзакций.

ниже приведена ссылка на базу данных, которую я создал:

https://dbfiddle.uk/?rdbms=oracle_11.2amp;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';
  

Я ищу вывод, подобный приведенному ниже:

Вместо агрегирования по типу транзакции (разделенной / одиночной) в столбце, 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
  

Рабочий запрос.