在单个Oracle查询中计算不同组级别的Distinct和Sum

2024-04-29 • 问答

以下是我的查询。无论如何，有没有使用不同级别的组级别。我需要获取product_count列以计算不同的Product_id，但不在account_id and acct_type_id level处。

当前，每行的product_count列为1。我只需要获取不同数量的产品ID，就需要将短期和长期销售汇总为account_id,product_id,acct_type_id级。

所以我希望输出像这样：

order_date account_id short_sales long_sales **product_count** price product_id acct_type_id <br>
31-Oct-19 6698777      -2800         500           **0**            30       100005      3 <br>
31-Oct-19 6998874      -4000         325           **0**            30       100005      2 <br>
31-Oct-19 5555555      -5000        1000           **0**            30       100005      3 <br>
31-Oct-19 4444444      -1000        5000           **1**            30       100005      2


select POS.ORDER_DATE,POS.accOUNT_ID,SUM(POS.SHORT_SALES) as SHORT_SALES,SUM(POS.LONG_SALES) as LONG_SALES,COUNT(Distinct POS.PRODUCT_ID) AS PRODUCT_COUNT,POS.PRICE,POS.PRODUCT_ID,POS.accT_TYPE_ID 
from PRODUCT 

(             
SELECT P.ORDER_DATE,P.accOUNT_ID,P.PRODUCT_ID,P.accT_TYPE_ID,P.PRICE,case when P.POSITION_SD < 0 THEN P.NET_POSITION_SD END AS SHORT_SALES,case when P.POSITION_SD > 0 THEN P.NET_POSITION_SD END AS LONG_SALES 
        FROM PRODUCT P 
        WHERE P.POSITION_DATE = '31-OCT-19'
) POS 
group by POS.ORDER_DATE,POS.accT_TYPE_ID,POS.PRICE 
ORDER BY PRODUCT_ID

您可以使用grouping sets来定义要为其计算小计的列。

列出想要作为小括号的小计的一组列：

with products as (
  select mod ( level,3 ) product_id,mod ( level,6 ) account_id,2 ) acct_type
  from dual
  connect by level <= 10
)
  select product_id,account_id,acct_type,count(*)
  from   products
  group  by grouping sets (
    product_id,( product_id,acct_type )
  );

PRODUCT_ID     ACCOUNT_ID     ACCT_TYPE     COUNT(*)   
            0             0            0           1 
            0             3            1           2 
            0        <null>       <null>           3 
            1             1            1           2 
            1             4            0           2 
            1        <null>       <null>           4 
            2             2            0           2 
            2             5            1           1 
            2        <null>       <null>           3

我认为您可以通过切换COUNT以使用窗口来解决此问题。

在此示例中，我注释掉了您的一些查询，以使其更易于重现，但我更改的唯一部分是COUNT行。

编辑：已更新，仅在其中一行中返回唯一产品ID的计数，其余product_count值设置为0。

-- example data
with product as (select sysdate as order_date,6698777 as account_id,-1400 as short_sales,300 as long_sales,100005 as product_id,30 as price,3 as acct_type_id from dual
    union select sysdate,6698777,-1400,200,100005,30,3 from dual
    union select sysdate,5555555,-5000,1000,6998874,-4000,325,2 from dual)
-- query            
select POS.ORDER_DATE,POS.ACCOUNT_ID,SUM(POS.SHORT_SALES) as SHORT_SALES,SUM(POS.LONG_SALES) as LONG_SALES,count(distinct product_id) over () * case when row_number() over (order by product_id desc) = 1 then 1 else 0 end as product_count,-- changed this line
POS.PRICE,POS.PRODUCT_ID,POS.ACCT_TYPE_ID 
from (             
SELECT P.ORDER_DATE,P.ACCOUNT_ID,P.PRODUCT_ID,P.ACCT_TYPE_ID,P.PRICE,--case when P.POSITION_SD < 0 THEN P.NET_POSITION_SD END AS 
    SHORT_SALES,--case when P.POSITION_SD > 0 THEN P.NET_POSITION_SD END AS 
    LONG_SALES 
        FROM PRODUCT P 
    --    WHERE P.POSITION_DATE = '31-OCT-19'
) POS 
group by POS.ORDER_DATE,POS.ACCT_TYPE_ID,POS.PRICE 
ORDER BY PRODUCT_ID

在单个Oracle查询中计算不同组级别的Distinct和Sum

liwentao19931129 回答：在单个Oracle查询中计算不同组级别的Distinct和Sum

大家都在问