oracle - 将聚合应用于 obiee 中的维度列时忽略 LAST 函数
问题描述
有一个“SALDO OUT”度量列，它在 BMM 的聚合中应用了日历的 sum 函数和 last 函数。此列来自一个每日更新项目的萨尔多的表，因此当用户运行上个月的分析时，它应用此 LAST 函数并获取该月最后一天的项目的最后萨尔多值，而不仅仅是萨尔多的总和一个月的所有日子。许多使用 BI 的人都熟悉这个概念，并且这一直没有问题。
但是，当我们对分析中的维度列使用 sum(...) 时，不会应用 Last 函数。这个维度表连接到事实表，但它并没有真正对应多对一的关系。例如，对于 SALDOS 事实表中的每个项目，在 Products 维度表中都有一个项目通过 item_id 连接，但 Products dim 表中的 item_id 不是唯一的，因此一个项目 id 可能有多个记录。
这就是为什么当我们使用 Count('Products'.'Item Uid') 来获取项目的计数时，会为每个 item_uid 汇总“SALDO OUT”列值。以下是截图：
对此的物理查询如下（注意名称与表示层不同，vsego_zadolj_out = saldo_out 和 item_uid = guar_id, id_item = loan_id）
with Sawith0 as
(select sum(T4134.Vsego_Zadolj_Out) as C1,
T3703.Name as C2,
T4458.Currency as C3,
T57573.Loan_Id as C4,
T57573.Balance_Acc as C5,
T57573.Client_Name as C6,
T5520.Cal_Day as C7,
T71201.Guar_Id as C8,
T3718.Name as C9,
T3512.Namer as C10,
T3160.Code as C11,
count(case
when T71201.Guar_Type in ('21') then
T71201.Sum_Guar_Eqv
else
0
end) as C12
from (Dm_Ln_Cards_v T4458 /* D2718 Ln Cards */
inner join(((Dm_Calendar_v T5520 /* D03 Calendar */
inner
join(Ibs_Ln_v_Credit_Type T3703 /* D2707 Ln Credit Type */ inner
join(Dm_Filials_v T3160 /* D04 Filials */ inner
join(Dm_Ln_Portfolio_By_Cal_v T57573 /* D2716 Ln Portfolio Dim */
inner join Dm_Ln_Portfolio_By_Cal_v T4134 /* F03 Portfolio By Cal */ on
T4134.Loan_Id = T57573.Loan_Id and
T4134.Report_Day = T57573.Report_Day) on
T3160.Code = T4134.Filial_Code) on T3703.Code = T4134.Ln_Type_Code) on
T4134.Report_Day = T5520.Cal_Day) left outer join Ibs_Ln_v_Sector T3718 /* D2709 Ln Sector */
on T3718.Code = T4134.Sector_Code_Ln) left outer join s_Oked T3512 /* D19 OKED */ on
T3512.Code = T4134.Oked_Code and T3512.Code = T57573.Oked_Code) on
T3718.Code = T4458.Eco_Sec and T4134.Loan_Id = T4458.Loan_Id and
T4458.Loan_Id = T57573.Loan_Id and T3160.Code = T4458.Filial_Code and
T3703.Code = T4458.Loan_Type)
left outer join Dm_Ibs_Ln_Loan_Guar_v T71201 /* D2740 Ln Loan Guar */
on T4134.Loan_Id = T71201.Loan_Id
and T57573.Loan_Id = T71201.Loan_Id
where (T4134.Loan_Id = 2092469 and T4134.Report_Day = to_date('2021-09-06', 'YYYY-MM-DD') and
T4458.Loan_Id = 2092469 and T5520.Cal_Day = to_date('2021-09-06', 'YYYY-MM-DD') and
T57573.Loan_Id = 2092469 and T57573.Report_Day = to_date('2021-09-06', 'YYYY-MM-DD'))
group by T3160.Code,
T3512.Namer,
T3703.Name,
T3718.Name,
T4458.Currency,
T5520.Cal_Day,
T57573.Balance_Acc,
T57573.Client_Name,
T57573.Loan_Id,
T71201.Guar_Id)
select D1.C1 as C1,
D1.C2 as C2,
D1.C3 as C3,
D1.C4 as C4,
D1.C5 as C5,
D1.C6 as C6,
D1.C7 as C7,
D1.C8 as C8,
D1.C9 as C9,
D1.C10 as C10,
D1.C11 as C11,
D1.C12 as C12,
D1.C13 as C13
from (select 0 as C1,
D1.C2 as C2,
D1.C3 as C3,
D1.C4 as C4,
D1.C5 as C5,
D1.C6 as C6,
D1.C7 as C7,
D1.C8 as C8,
D1.C9 as C9,
D1.C10 as C10,
D1.C11 as C11,
D1.C1 as C12,
D1.C12 as C13
from Sawith0 D1
order by C7, C4, C5, C6, C3, C2, C11, C9, C10, C8) D1
where Rownum <= 10000000
此查询在 db 中的结果：
C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12 C13
0 Кредиты, выданные без открытия кредитной линии 840 2092469 15501 "Fayzulloobod"F/X 06.09.2021 1362403 Хлопководство Выращивание хлопчатника 00116 4268400000 1
0 Кредиты, выданные без открытия кредитной линии 840 2092469 15501 "Fayzulloobod"F/X 06.09.2021 1362408 Хлопководство Выращивание хлопчатника 00116 4268400000 1
0 Кредиты, выданные без открытия кредитной линии 840 2092469 15501 "Fayzulloobod"F/X 06.09.2021 1362410 Хлопководство Выращивание хлопчатника 00116 4268400000 1
0 Кредиты, выданные без открытия кредитной линии 840 2092469 15501 "Fayzulloobod"F/X 06.09.2021 1362416 Хлопководство Выращивание хлопчатника 00116 4268400000 1
0 Кредиты, выданные без открытия кредитной линии 840 2092469 15501 "Fayzulloobod"F/X 06.09.2021 1362418 Хлопководство Выращивание хлопчатника 00116 4268400000 1
0 Кредиты, выданные без открытия кредитной линии 840 2092469 15501 "Fayzulloobod"F/X 06.09.2021 1362422 Хлопководство Выращивание хлопчатника 00116 4268400000 1
0 Кредиты, выданные без открытия кредитной линии 840 2092469 15501 "Fayzulloobod"F/X 06.09.2021 1362919 Хлопководство Выращивание хлопчатника 00116 4268400000 1
0 Кредиты, выданные без открытия кредитной линии 840 2092469 15501 "Fayzulloobod"F/X 06.09.2021 1362920 Хлопководство Выращивание хлопчатника 00116 4268400000 1
如您所见，Saldo Out 项目为每个项目 uid 重复，我知道这是连接的自然结果。如果删除了 Item Uid 列，则汇总输出值。
对此的物理查询如下：
with Sawith0 as
(select sum(T4134.Vsego_Zadolj_Out) as C1,
T3703.Name as C2,
T4458.Currency as C3,
T57573.Loan_Id as C4,
T57573.Balance_Acc as C5,
T57573.Client_Name as C6,
T5520.Cal_Day as C7,
T3718.Name as C8,
T3512.Namer as C9,
T3160.Code as C10,
count(case
when T71201.Guar_Type in ('21') then
T71201.Sum_Guar_Eqv
else
0
end) as C11
from (Dm_Ln_Cards_v T4458 /* D2718 Ln Cards */
inner join(((Dm_Calendar_v T5520 /* D03 Calendar */
inner
join(Ibs_Ln_v_Credit_Type T3703 /* D2707 Ln Credit Type */ inner
join(Dm_Filials_v T3160 /* D04 Filials */ inner
join(Dm_Ln_Portfolio_By_Cal_v T57573 /* D2716 Ln Portfolio Dim */
inner join Dm_Ln_Portfolio_By_Cal_v T4134 /* F03 Portfolio By Cal */ on
T4134.Loan_Id = T57573.Loan_Id and
T4134.Report_Day = T57573.Report_Day) on
T3160.Code = T4134.Filial_Code) on T3703.Code = T4134.Ln_Type_Code) on
T4134.Report_Day = T5520.Cal_Day) left outer join Ibs_Ln_v_Sector T3718 /* D2709 Ln Sector */
on T3718.Code = T4134.Sector_Code_Ln) left outer join s_Oked T3512 /* D19 OKED */ on
T3512.Code = T4134.Oked_Code and T3512.Code = T57573.Oked_Code) on
T3718.Code = T4458.Eco_Sec and T4134.Loan_Id = T4458.Loan_Id and
T4458.Loan_Id = T57573.Loan_Id and T3160.Code = T4458.Filial_Code and
T3703.Code = T4458.Loan_Type)
left outer join Dm_Ibs_Ln_Loan_Guar_v T71201 /* D2740 Ln Loan Guar */
on T4134.Loan_Id = T71201.Loan_Id
and T57573.Loan_Id = T71201.Loan_Id
where (T4134.Loan_Id = 2092469 and T4134.Report_Day = to_date('2021-09-06', 'YYYY-MM-DD') and
T4458.Loan_Id = 2092469 and T5520.Cal_Day = to_date('2021-09-06', 'YYYY-MM-DD') and
T57573.Loan_Id = 2092469 and T57573.Report_Day = to_date('2021-09-06', 'YYYY-MM-DD'))
group by T3160.Code,
T3512.Namer,
T3703.Name,
T3718.Name,
T4458.Currency,
T5520.Cal_Day,
T57573.Balance_Acc,
T57573.Client_Name,
T57573.Loan_Id)
select D1.C1 as C1,
D1.C2 as C2,
D1.C3 as C3,
D1.C4 as C4,
D1.C5 as C5,
D1.C6 as C6,
D1.C7 as C7,
D1.C8 as C8,
D1.C9 as C9,
D1.C10 as C10,
D1.C11 as C11,
D1.C12 as C12
from (select 0 as C1,
D1.C2 as C2,
D1.C3 as C3,
D1.C4 as C4,
D1.C5 as C5,
D1.C6 as C6,
D1.C7 as C7,
D1.C8 as C8,
D1.C9 as C9,
D1.C10 as C10,
D1.C1 as C11,
D1.C11 as C12
from Sawith0 D1
order by C7, C4, C5, C6, C3, C2, C10, C8, C9) D1
where Rownum <= 10000000
db 中此查询的结果：
C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12
0 Кредиты, выданные без открытия кредитной линии 840 2092469 15501 "Fayzulloobod"F/X 06.09.2021 Хлопководство Выращивание хлопчатника 00116 34147200000 8
我们期望的结果是这个（应用的最后一个函数）：
C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12
0 Кредиты, выданные без открытия кредитной линии 840 2092469 15501 "Fayzulloobod"F/X 06.09.2021 Хлопководство Выращивание хлопчатника 00116 4268400000 8
事实表“Dm_Ln_Portfolio_By_Cal_v”中的数据每天为每个项目存储：
OPER_DAY LOAN_ID VSEGO_ZADOLJ_OUT
06.08.2021 2092469 424727200000,00
09.08.2021 2092469 425591600000,00
10.08.2021 2092469 425120000000,00
11.08.2021 2092469 426040000000,00
12.08.2021 2092469 426401200000,00
13.08.2021 2092469 426880000000,00
16.08.2021 2092469 427320000000,00
17.08.2021 2092469 426400000000,00
18.08.2021 2092469 426092400000,00
19.08.2021 2092469 425760000000,00
20.08.2021 2092469 425280000000,00
23.08.2021 2092469 425624000000,00
24.08.2021 2092469 425645600000,00
25.08.2021 2092469 425374000000,00
26.08.2021 2092469 426160400000,00
27.08.2021 2092469 425629600000,00
30.08.2021 2092469 426075600000,00
31.08.2021 2092469 426127200000,00
06.09.2021 2092469 426840000000,00
07.09.2021 2092469 427402800000,00
08.09.2021 2092469 427809200000,00
09.09.2021 2092469 427314400000,00
10.09.2021 2092469 426723600000,00
13.09.2021 2092469 426236400000,00
14.09.2021 2092469 426067600000,00
17.09.2021 2092469 427242400000,00
20.09.2021 2092469 426963200000,00
21.09.2021 2092469 427468000000,00
22.09.2021 2092469 427524000000,00
23.09.2021 2092469 427162000000,00
为事实表中的每个项目存储的暗表 dm_ln_Loan_guar_v 中的数据：
LOAN_ID GUAR_ID SUM_GUAR_EQV
2092469 1362919 500000000
2092469 1362408 258000000
2092469 1362410 311900000
2092469 1362416 688000000
2092469 1362418 98000000
2092469 1362422 102500000
2092469 1362920 297600000
2092469 1362403 2244000000
有没有办法让这个加入工作或使用 Last 函数进行分析？Rpd 说应该有一个桥表来使用多对多关系，这是唯一的方法，因为我无法更改所有这些数据来自的源数据库结构
解决方案
推荐阅读
- git - git delete/prune 远程上的死提交
- c - 缺少新的环境变量
- django - Django模板>点击按钮检查条件
- java - intellij ulltimate 2020.2 无法识别基本注释？
- python - 如何拆分，然后在 0 和第 N 个值之间附加 Array，然后在下一组中重复相同的步骤？
- c - 需要循环 switch 语句直到输入 1 或 2
- magento2 - 下单后奥维比亚总计显示错误
- c - 如何使用 Struct 创建带有树的表达式解析器
- c++ - 当调用带有 [&] 捕获子句的 lambda 时，是什么导致了奇怪的行为，其中在 C++ 中使用了当前范围之外的变量？
- python-3.x - import_module 在 __pycache__ 生成后停止工作