时间戳

首页 > 解决方案 > 在 Oracle 中使用聚合函数计算创建视图

sql - 在 Oracle 中使用聚合函数计算创建视图

问题描述

我有下表IS_ID：

CREATE TABLE  
    IS_ID  
    (  
        IDENTIFIER VARCHAR2(50),  
        ALLOCATIONPERCENTAGE NUMBER(19,8),  
        MARKETVALUEHELD NUMBER(19,8),  
        ALLOCATIONASSETTYPE VARCHAR2(100)  
    );

我想通过一些计算基于此表创建一个视图。对于每个标识符，将有两行，第一行用于ALLOCATIONASSETTYPE = 'Cash'，第二行用于ALLOCATIONASSETTYPE = 'Other'

在第一行中ALLOCATIONASSETTYPE = 'Cash'：

例如，对于每个相同的 IDENTIFIER，我想对ALLOCATIONPERCENTAGEwhere求和ALLOCATIONASSETTYPE = 'Cash'。

对于每个相同的标识符，我想总结MARKETVALUEHELDwhere ALLOCATIONASSETTYPE = 'Cash'。

在第二行中ALLOCATIONASSETTYPE = 'Other'：

例如我想做以下计算：

100 - 所有ALLOCATIONPERCENTAGEwhereALLOCATIONASSETTYPE ! = 'Cash'的总和 - 总和ALLOCATIONPERCENTAGEwhereALLOCATIONASSETTYPE = 'Cash'

MARKETVALUEHELD每个相同标识符的总和 - 每个相同标识符的总和MARKETVALUEHELDwhere ALLOCATIONASSETTYPE = 'Cash'-每个相同标识符的总和MARKETVALUEHELDwhereALLOCATIONASSETTYPE != 'Cash'

在这种情况下是否可以创建视图？

标签： sqloracleview

解决方案

您可以使用两个查询的联合，一个聚合现金值，另一个使用条件聚合：

select identifier,
  sum(allocationpercentage),
  sum(marketvalueheld),
  'Cash'
from is_id
where allocationassettype = 'Cash'
group by identifier
union all
select identifier,
  100
    - sum(case when allocationassettype != 'Cash' then allocationpercentage else 0 end)
    - sum(case when allocationassettype = 'Cash' then allocationpercentage else 0 end),
  null,
  'Other'
from is_id
group by identifier;

IDENTIFIER   | SUM(ALLOCATIONPERCENTAGE) | SUM(MARKETVALUEHELD) | 'CASH'
:----------- | ------------------------: | -------------------: | :-----
LU0138075311 |                     .1961 |            242708.64 | Cash  
IE0031069382 |                     .4451 |            552520.67 | Cash  
LU0138075311 |                   58.8989 |                      | Other 
IE0031069382 |                   56.8158 |                      | Other

然后你可以把它变成一个视图：

create view your_view (identifier, allocationpercentage, marketvalueheld, allocationassettype)
as
select identifier,
...

db<>小提琴

是否可以保留现有的标识符，然后对于每个标识符，我们可以为 allocationassettype = 'Cash' 和 allocationassettype = 'Other' 添加另外两行？所以我可以看到每个标识符的现有数据，还可以看到这两个新行？

您可以使用原始数据添加第三个分支，但您可能需要至少更改聚合分支中的“现金”标签，和/或可能需要一个标志来帮助识别哪些是原始数据以及哪些是聚合的；这两者兼而有之：

select identifier,
  allocationpercentage,
  marketvalueheld,
  allocationassettype,
  1 as data_type
from is_id
union all
select identifier,
  sum(allocationpercentage),
  sum(marketvalueheld),
  'Cash total',
  2 as data_type
from is_id
where allocationassettype = 'Cash'
group by identifier
union all
select identifier,
  100
    - sum(case when allocationassettype != 'Cash' then allocationpercentage else 0 end)
    - sum(case when allocationassettype = 'Cash' then allocationpercentage else 0 end),
  null,
  'Other total',
  3 as data_type
from is_id
group by identifier;

然后当这是一个视图时，您可以将其排序为一种合理的结果：

select identifier, allocationpercentage, marketvalueheld, allocationassettype
from your_view
order by identifier, data_type;

尽管现在这听起来像是您应该在数据表示层中真正做的事情，而不是在 SQL 中。

db<>小提琴

对于不存在 allocationassettype != 'Cash' 的标识符，则不会为这些标识符显示具有 'Cash total' 和 'Other total' 的那两个新行

“其他总数”将始终出现。要使“现金总额”也出现，只需将联合的第一个分支也更改为使用条件聚合，并删除其过滤器：

...
UNION ALL
SELECT
    IDENTIFIER,
    null,
    null,
    null,
    null,
    null,
    SUM(case when allocationassettype = 'Cash' then allocationpercentage else 0 end),
    SUM(case when allocationassettype = 'Cash' then marketvalueheld else 0 end),
    'Cash total'
FROM
    IS_ID
GROUP BY
    IDENTIFIER
UNION ALL
...

db<>fiddle（查找 ID 'ABC' 以了解对输出的相关更改）。

推荐阅读