pgSQL 產品組規格單查詢紀錄
Sep 7, 2018 · 2 min read
WITH tmp AS(
SELECT ZDATE,tej_fund_id,val
FROM fund.fdnav_fdmod
WHERE keyin>=’10000101' AND STATE=’N’
)
,tmp1 AS(
SELECT a.ZDATE,b.fund_id,a.val,’’ as FLAG
,case
when b.tej_area=’TWN_LOCAL’ then ‘1’
when b.tej_area=’TWN_FOREIGN’ then ‘2’
when b.tej_area=’TWN_CHN_LOCAL’ then ‘3’
when b.tej_area=’HKG_LOCAL’ then ‘4’
when b.tej_area=’CHN_FOREIGN’ then ‘5’ end as rank
FROM tmp as a
LEFT JOIN global.fund.attr_areafund as b
ON a.tej_fund_id = b.tej_fund_id
WHERE a.ZDATE <= current_date — interval ‘3 month’
)
,tmp2 as(
SELECT *, Row_Number() Over (PARTITION BY fund_id ORDER BY rank) as um2
FROM tmp1
)
SELECT zdate,fund_id,val,’’ as FLAG
FROM tmp2
WHERE um2=1