每行10个计算字段
每行10个计算字段
提问于 2014-03-25 19:58:23
我需要编写一个显示所有帐号的查询，然后需要显示从特定开始日期起10天内的下10个期末余额。
我有一个包含帐户的维度，一个包含日期的维度，以及一个存储每个帐户的期初余额和期末余额的事实余额表。
输出需要如下所示：
BANK_NAME | ACCOUNT_NR | CB DATE1 | CB DATE1 +1 | CB DATE1 +2 ....|到10
我试着写了一个这样的查询：
select information.bankname,information.branchname,information.holdername,information.accountnumber, information.CURRENCY, CB1.amount1, CB2.amount2, CB3.amount3, CB4.amount4, CB5.amount5, CB6.amount6, CB7.amount7, CB8.amount8, CB9.amount9, CB10.amount10
from
--eerste gegevens selecteren
(select a.BANK_NAME bankname, a.BRANCH_NAME branchname, a.HOLDER_NAME holdername, a.ACCOUNT_NUMBER accountnumber, a.CURRENCY currency
--relaties worden gelegd
from DIM_ACCOUNT a, FACT_AS_BALANCE b, DIM_DATE d
where
b.balance_date_id = d.ID and
b.account_id = a.ID and
) information,
(select FACT_AS_BALANCE.CB_AMOUNT amount1
--relaties worden gelegd
from DIM_ACCOUNT a, FACT_AS_BALANCE b, DIM_DATE d
where
b.balance_date_id = d.ID and
b.account_id = a.ID and
(d.CAL_DATE = TO_DATE ('20130101', 'YYYYMMDD'))) CB1,
(select FACT_AS_BALANCE.CB_AMOUNT amount2
--relaties worden gelegd
from DIM_ACCOUNT a, FACT_AS_BALANCE b, DIM_DATE d
where
b.balance_date_id = d.ID and
b.account_id = a.ID and
(d.CAL_DATE = TO_DATE ('20130101', 'YYYYMMDD') + 1)) CB2,
(select FACT_AS_BALANCE.CB_AMOUNT amount3
--relaties worden gelegd
from DIM_ACCOUNT a, FACT_AS_BALANCE b, DIM_DATE d
where
b.balance_date_id = d.ID and
b.account_id = a.ID and
(d.CAL_DATE = TO_DATE ('20130101', 'YYYYMMDD') + 2)) CB3,
(select FACT_AS_BALANCE.CB_AMOUNT amount4
--relaties worden gelegd
from DIM_ACCOUNT a, FACT_AS_BALANCE b, DIM_DATE d
where
b.balance_date_id = d.ID and
b.account_id = a.ID and
(d.CAL_DATE = TO_DATE ('20130101', 'YYYYMMDD') + 3)) CB4,
(select FACT_AS_BALANCE.CB_AMOUNT amount5
--relaties worden gelegd
from DIM_ACCOUNT a, FACT_AS_BALANCE b, DIM_DATE d
where
b.balance_date_id = d.ID and
b.account_id = a.ID and
(d.CAL_DATE = TO_DATE ('20130101', 'YYYYMMDD') + 4)) CB5,
(select FACT_AS_BALANCE.CB_AMOUNT amount6
--relaties worden gelegd
from DIM_ACCOUNT a, FACT_AS_BALANCE b, DIM_DATE d
where
b.balance_date_id = d.ID and
b.account_id = a.ID and
(d.CAL_DATE = TO_DATE ('20130101', 'YYYYMMDD') + 5)) CB6,
(select FACT_AS_BALANCE.CB_AMOUNT amount7
--relaties worden gelegd
from DIM_ACCOUNT a, FACT_AS_BALANCE b, DIM_DATE d
where
b.balance_date_id = d.ID and
b.account_id = a.ID and
(d.CAL_DATE = TO_DATE ('20130101', 'YYYYMMDD') + 6)) CB7,
(select FACT_AS_BALANCE.CB_AMOUNT amount8
--relaties worden gelegd
from DIM_ACCOUNT a, FACT_AS_BALANCE b, DIM_DATE d
where
b.balance_date_id = d.ID and
b.account_id = a.ID and
(d.CAL_DATE = TO_DATE ('20130101', 'YYYYMMDD') + 7)) CB8,
(select FACT_AS_BALANCE.CB_AMOUNT amount9
--relaties worden gelegd
from DIM_ACCOUNT a, FACT_AS_BALANCE b, DIM_DATE d
where
b.balance_date_id = d.ID and
b.account_id = a.ID and
(d.CAL_DATE = TO_DATE ('20130101', 'YYYYMMDD') + 8)) CB9,
(select FACT_AS_BALANCE.CB_AMOUNT amount10
--relaties worden gelegd
from DIM_ACCOUNT a, FACT_AS_BALANCE b, DIM_DATE d
where
b.balance_date_id = d.ID and
b.account_id = a.ID and
(d.CAL_DATE = TO_DATE ('20130101', 'YYYYMMDD') + 9)) CB10;我不知道这是不是正确的方法。但我真的看不到其他方法了。
我该怎么做呢？
回答 2
Stack Overflow用户
发布于 2014-03-25 22:28:23
如果您使用的是Oracle 11+，则可以使用PIVOT子句，如下所示：
WITH Date_Range as (SELECT id, cal_Date
FROM dim_Date
WHERE cal_Date >= TO_DATE('20130101', 'yyyymmdd')
AND cal_Date < TO_DATE('20130101', 'yyyymmdd') + 10)
SELECT *
FROM (SELECT Account.bank_name, Account.branch_name,
Account.holder_name, Account.account_number,
Account.currency, Balance.cb_amount, Date_Range.cal_Date
FROM dim_Account Account
JOIN fact_as_Balance Balance
ON Balance.account_id = Account.id
JOIN Date_Range
ON Date_Range.id = Balance.balance_date_id) Information
PIVOT(MAX(cb_amount)) FOR cal_Date IN (SELECT cal_Date FROM Date_Range)未测试，因为没有样本数据可供参考。这应该为所有日期动态生成列。
(请注意，我反对使用SELECT *，但我不确定如果不指定透视列是否仍然包含它们-我们不一定能猜测列名……)
Stack Overflow用户
发布于 2014-03-25 20:55:55
我认为你想要这样的东西：
SELECT
a.bank_name bankname,
a.branch_name branchname,
a.holder_name holdername,
a.account_number accountnumber,
a.currency currency,
SUM(CASE WHEN d.cal_date = to_date ('20130101', 'yyyymmdd')
THEN fact_as_balance.cb_amount
ELSE 0 end) amount1,
SUM(CASE WHEN d.cal_date = to_date ('20130101', 'yyyymmdd') + 1
THEN fact_as_balance.cb_amount
ELSE 0 end) amount2,
SUM(CASE WHEN d.cal_date = to_date ('20130101', 'yyyymmdd') + 1
THEN fact_as_balance.cb_amount
ELSE 0 end) amount3,
(continue for columns 4-10)
FROM
dim_account a
JOIN fact_as_balance b ON a.id = b.account_id
JOIN dim_date d ON b.balance_date_id = d.id
GROUP BY
a.bank_name bankname,
a.branch_name branchname,
a.holder_name holdername,
a.account_number accountnumber,
a.currency currency页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/22633923
复制相关文章
相似问题
腾讯云开发者
