我有下表:
descr = 'O'
对于id_isin
两个表中的公共字段值来说,对于该ratio
字段,请检查该字段并仅采用ratio
字段值较低的行。descr = 'O'
,如果在一个表中的id_isin存在但不是在另一然后采取那些行(双向)descr ! = 'O'
,请从table中获取所有这些行IS_ID_TST
。例如,下面是视图的预期输出:
ID_ISIN QUOTE_CRNY DESCR RATIO ALLOCATIONASSETTYPE
L000123 USD O 0.0769 Other total
L000129 USD O 0.0669 Other total
D123458 USD O 0.64039 Other total
M123456 USD O 5.64039 Other total
F563458 USD C 0.84039 Other total
G123456 USD null 0.04039 Other total
L000123 USD C 5.0769 Other total
我可以根据此条件创建视图吗?
我想出了以下查询,请检查我是否对查询发表了评论。询问是否不符合您的要求或进行任何澄清。
CREATE VIEW v_combined_data AS
WITH combined_data
AS
(
SELECT t1.fund_isin
,t1.fund_quote_crny
,t1.member_descr
,t1.member_ratio
,t1.allocationassettype
,t2.fund_isin fund_isin_tst
,t2.fund_quote_crny fund_quote_crny_tst
,t2.member_descr member_descr_tst
,t2.member_ratio member_ratio_tst
,t2.allocationassettype allocationassettype_tst
FROM is_id t1
FULL OUTER JOIN is_id_tst t2
ON t1.fund_isin = t2.fund_isin
AND t1.fund_quote_crny = t2.fund_quote_crny
AND t1.member_descr = t2.member_descr
)
-- for member_descr = 'O' and for common fund_isin field value from both tables,
-- check the member_ratio field and take only the row where member_ratio field value is low.
SELECT d.fund_isin
,d.fund_quote_crny
,d.member_descr
,LEAST(d.member_ratio,d.member_ratio_tst) member_ratio
,d.allocationassettype
FROM combined_data d
WHERE d.member_descr = 'O'
AND d.fund_isin IS NOT NULL
AND d.fund_isin_tst IS NOT NULL
UNION ALL
--for member_descr = 'O' and if the fund_isin exist in one table but not in another then take those rows(bidirectional)
--exists in IS_ID and not in IS_ID_TST
SELECT d.fund_isin
,d.fund_quote_crny
,d.member_descr
,d.member_ratio
,d.allocationassettype
FROM combined_data d
WHERE d.member_descr = 'O'
AND NOT EXISTS (SELECT 1
FROM combined_data ci
WHERE ci.fund_isin_tst = d.fund_isin
AND ci.fund_quote_crny_tst = d.fund_quote_crny
AND ci.member_descr_tst = 'O')
UNION ALL
--for member_descr = 'O' and if the fund_isin exist in one table but not in another then take those rows(bidirectional)
--exists in IS_ID_TST and not in IS_ID
SELECT d.fund_isin_tst
,d.fund_quote_crny_tst
,d.member_descr_tst
,d.member_ratio_tst
,d.allocationassettype_tst
FROM combined_data d
WHERE d.member_descr_tst = 'O'
AND NOT EXISTS (SELECT 1
FROM combined_data ci
WHERE ci.fund_isin = d.fund_isin_tst
AND ci.fund_quote_crny = d.fund_quote_crny_tst
AND ci.member_descr = 'O')
UNION ALL
--for all the rows where member_descr ! = 'O', take all those rows from table IS_ID_TST
SELECT d.fund_isin_tst
,d.fund_quote_crny_tst
,d.member_descr_tst
,d.member_ratio_tst
,d.allocationassettype_tst
FROM combined_data d
WHERE d.fund_isin_tst IS NOT NULL
AND (d.member_descr_tst != 'O' OR d.member_descr_tst IS NULL);
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句