with tmp as(select b.bk_portfolio,b.portfolio_name ,b.mkt_issue_code,b.issue_short_name ,b.qnty ,case when b.sk_issue_type LIKE '10201%' then c.ISSUE_SCALE*10000/c.PAR_VALUE -- 发行规模(万元)->元，再除以面值，得到数量 else d.TOTALSHARE -- 总股本(股) end as total_qnty ,a.threshold_value ,a.dk_rule_type ,a.meno ,t1.sk_date as sk_date_newfrom vf_crs_idm_FACT_PORT_ISSUEHOLD_D b -- 持仓表left join (select t.mkt_issue_code,t.sk_date from (select mkt_issue_code, sk_date, row_number() over(partition by mkt_issue_code order by sk_date desc) as rn from crs_fact_port_invtran_d where sk_date<=replace('${结束日期}','-','')) t where t.rn=1) t1-- 交易表 on b.mkt_issue_code=t1.mkt_issue_codeleft join report_2020001_threshold_config_bak a -- 阈值配置表 on a.dk_table_id = 'ldxfxgl_7' and a.dk_rule_type = '公司全部管理资产持有单只证券的市值占该证券市值'LEFT JOIN crs_DIM_ISSUE_BONDINFO c -- 债券基本信息 on b.SK_ISSUE = c.SK_ISSUE-- LEFT JOIN crs_DIM_ISSUE_STOCKINFO d -- 股票基本信息 -- on b.SK_ISSUE = d.SK_ISSUELEFT JOIN crs_fact_ip_org_stock_stru d -- 上市公司股本结构 ON b.sk_invpty_of_issuer=d.sk_invpty -- 参与者代码关联AND d.effective_from<= replace('${结束日期}','-','') -- 日期限制范围AND d.effective_to >= replace('${结束日期}','-','') -- 日期限制范围where b.sk_date= replace('${结束日期}','-','') AND b.clear_mktval <> 0 -- 净价市值 and (b.sk_port_type like '1%' -- 1保险资金类 or b.sk_port_type like '2%') -- 2资管产品类 AND (b.sk_issue_type LIKE '10101%' -- 股票 or (b.sk_issue_type LIKE '10201%' -- 债券 and b.sk_issue_type not LIKE '1020101%') -- 去除政府债券 ) and b.SK_PORTFOLIO_OF_PARENT is nullunion allselect b.bk_portfolio,b.portfolio_name ,b.mkt_issue_code,b.issue_short_name ,b.qnty ,e.total_SHARES as total_qnty -- 基金单位总额 ,a.threshold_value ,a.dk_rule_type ,a.meno ,t1.sk_date as sk_date_newfrom vf_crs_idm_FACT_PORT_ISSUEHOLD_D b -- 持仓表left join (select t.mkt_issue_code,t.sk_date from (select mkt_issue_code, sk_date, row_number() over(partition by mkt_issue_code order by sk_date desc) as rn from crs_fact_port_invtran_d where sk_date<=replace('${结束日期}','-','')) t where t.rn=1) t1-- 交易表 on b.mkt_issue_code=t1.mkt_issue_codeleft join report_2020001_threshold_config_bak a -- 阈值配置表 on a.dk_table_id = 'ldxfxgl_7' and a.dk_rule_type = '公司全部管理资产持有单只股票型或混合型证券投资基金的市值占该证券投资基金市值'LEFT JOIN crs_DIM_ISSUE_FUNDINFO e -- 基金基本信息 on b.SK_ISSUE = e.SK_ISSUEwhere b.sk_date= replace('${结束日期}','-','') AND b.clear_mktval <> 0 -- 净价市值 and (b.sk_port_type like '1%' -- 1保险资金类 or b.sk_port_type like '2%') -- 2资管产品类 AND (b.sk_issue_type LIKE '103030101%' -- 封闭式-股票型基金 or b.sk_issue_type LIKE '103030201%' -- 开放式-股票型基金 or b.sk_issue_type LIKE '103030102%' -- 封闭式-混合型基金 or b.sk_issue_type LIKE '103030202%' -- 开放式-混合型基金 ) and b.SK_PORTFOLIO_OF_PARENT is nullunion allselect b.bk_portfolio,b.portfolio_name ,b.mkt_issue_code,b.issue_short_name ,b.qnty ,e.total_SHARES as total_qnty -- 基金单位总额 ,a.threshold_value ,a.dk_rule_type ,a.meno ,t1.sk_date as sk_date_newfrom vf_crs_idm_FACT_PORT_ISSUEHOLD_D b -- 持仓表left join (select t.mkt_issue_code,t.sk_date from (select mkt_issue_code, sk_date, row_number() over(partition by mkt_issue_code order by sk_date desc) as rn from crs_fact_port_invtran_d where sk_date<=replace('${结束日期}','-','')) t where t.rn=1) t1-- 交易表 on b.mkt_issue_code=t1.mkt_issue_codeleft join report_2020001_threshold_config_bak a -- 阈值配置表 on a.dk_table_id = 'ldxfxgl_7' and a.dk_rule_type = '公司全部管理资产持有单只债券型或货币市场型证券投资基金的市值占该证券投资基金市值'LEFT JOIN crs_DIM_ISSUE_FUNDINFO e -- 基金基本信息 on b.SK_ISSUE = e.SK_ISSUEwhere b.sk_date= replace('${结束日期}','-','') AND b.clear_mktval <> 0 -- 净价市值 and (b.sk_port_type like '1%' -- 1保险资金类 or b.sk_port_type like '2%') -- 2资管产品类 AND (b.sk_issue_type LIKE '103030103%' -- 封闭式-债券型基金 or b.sk_issue_type LIKE '103030203%' -- 开放式-债券型基金 or b.sk_issue_type LIKE '103030204%' -- 开放式-货币式基金 ) and b.SK_PORTFOLIO_OF_PARENT is null)select '公司全部管理资产' as profolio_name ,GROUP_CONCAT(distinct(CONCAT(t.mkt_issue_code,'-',t.issue_short_name))) AS issue_short_name ,sum(t.qnty) as qnty ,t.total_qnty ,round((sum(t.qnty)/t.total_qnty)*100,4) as zb ,CASE WHEN round((sum(t.qnty)/t.total_qnty)*100,4) <= t.threshold_value THEN 0 ELSE 1 END AS dk_flag ,t.threshold_value ,t.dk_rule_type ,t.meno ,t.sk_date_newfrom tmp tgroup by CONCAT(t.mkt_issue_code,'-',t.issue_short_name) ,t.total_qnty ,t.threshold_value ,t.dk_rule_type,t.meno ,t.sk_date_new
