常用表记录 - KGLEE666/Citics_Wiki GitHub Wiki
CRM基础表
CRM机构表：intf_crm.t_citics_inr_inst或者intf_guozhenhai.dim_orginfo_kpi_2022 CRM员工信息表：intf_crm.t_empe CRM客户标签表的字典表： intf_crm.t_stm_entr 客户盈亏表：intf_crm.cust_fnd_algr_pl pl当日盈亏 字典表：select * from bf_ap_crm.txtdm limit 10
查部门、岗位
PG库
- select * from edm_base.t_empe_inr_inst_tree limit 10
- select a.empe_no,a.empe_name,b.dept_name,c.jobcname from edm_base.t_empe a left join edm_base.t_department b on a.dept_no=b.dept_no left join edm_base.interface_job c on a.hrs_post_code=c.gwdm where jobcname like '%部门%'
查CRM标签
select dict_eng_nm,entr_val,entr_chn_nm from intf_crm.t_stm_entr where dict_eng_nm='PD_TP_CODE' group by 1,2,3 --A_SPCLT_IVSR_PD_TP_CODE --dict_eng_nm --OCP_CODE,OCP_VLD_CODE --职业 --PD_TP_CODE
CRM客户标签表
intf_crm.CRM_CUST_LBL_SMY 只更新月末和前一天数据
(select * from intf_crm.CRM_CUST_LBL_SMY where IP_TP_CODE='1' and AFFL_BSN_DEPT_ID='18' ) --IP_TP_CODE 0个人，1机构，3产品
埋点数据
Impala上： app埋点：edm_realtime.rtdw_dwd_hma_records_his 微信埋点：edm_realtime.rtdw_dwd_hwa_records_his
交易记录
sr_hs_his.his_ofdeliver --场外公募流水 sr_hs_his.his_deliver --场内交易流水
select a.*,b.持仓总量,a.总成交量*1.0/b.持仓总量 as 换手率 from
(select init_date,count(distinct stock_code) 交易标的数量,count(serial_no) 交易次数,sum(case when entrust_bs='1' then 1 else 0 end ) 买入次数,sum(case when entrust_bs='2' then 1 else 0 end ) 卖出次数,sum( case when entrust_bs='1' then business_balance else 0 end) 买入金额,sum( case when entrust_bs='2' then business_balance else 0 end) 卖出金额,avg(business_balance) 平均成交金额,sum(abs(business_amount)) 总成交量,avg(abs(business_amount)) 平均成交量 from sr_hs_his.his_deliver where fund_account = '103800000218' group by 1 order by init_date) a
left join (
select ods_biz_date,sum(current_amount) 持仓总量 from SR_HS_ASSET.STOCK where fund_account='103800000218' group by 1 order by ods_biz_date ) b on date(a.init_date)=b.ods_biz_date
order by a.init_date
sr_hs_his.his_entrust --委托表
select init_date,count(distinct entrust_no) 委托笔数,sum(entrust_amount) 委托数量,avg(entrust_amount) 平均每笔委托数量,sum(business_balance) 委托金额,sum(case when entrust_bs='1' then 1 else 0 end ) 买入次数,sum(case when entrust_bs='2' then 1 else 0 end ) 卖出次数,sum( case when entrust_bs='1' then business_balance else 0 end) 买入金额,sum( case when entrust_bs='2' then business_balance else 0 end) 卖出金额 from sr_hs_his.his_entrust where fund_account = '103800000218' and init_date>=20231227 group by 1 order by 1
intf_crm.s_crm_cust_pd_ind_smy CRM持仓表 SR_HS_ASSET.STOCK --场内持仓 SR_HS_ASSET.OFSTOCK --场外持仓 sr_kd_otc.otc_asset_log_v2 --otc交易流水 ，查私募
查流水表里的stock_type
select a.subentry,a.dict_prompt from
bf_hs_user.sysdictionary a
inner join
bf_hs_user.fieldtoname b
on a.dict_entry=b.dict_entry
where b.english_name ='stock_type'
-- and a.subentry='p'
order by a.subentry
客户持仓与定投查询
--客户试点持仓产品
SELECT A.CLIENT_ID,a.fund_code
FROM SR_HS_ASSET.OFSTOCK A --持仓表 +OF的是公募
INNER JOIN bf_ap_crm.tjrcpsxbq C --产品信息
ON a.fund_code=C.cpdm
WHERE A.ODS_BIZ_DATE = DATE '20211029'
and A.CURRENT_SHARE>0
and cplx='1' --公募
and cplb='2' --权益类、固收类（不含货币）
group by 1,2
--定投客户
select a.client_id from sr_hs_his.his_ofdeliver a
INNER JOIN bf_ap_crm.tjrcpsxbq C
ON a.fund_code=C.cpdm
WHERE A.ods_biz_date BETWEEN DATE '20210101' AND DATE '20211031'
and A.business_flag = '139' --定投
and cplx='1' --公募
and cplb='2' --权益类、固收类（不含货币）
group by 1
--港股通
select CLIENT_ID,current_amount from SR_HS_ASSET.STOCK
WHERE ODS_BIZ_DATE = '2023-04-26' and exchange_type in ('G','S') --沪港通和深港通
and current_amount>0
分支机构表
intf_crm.t_citics_inr_inst，查询分支id和名称的对应关系：
Select t1.*
from intf_crm.t_citics_inr_inst t1
join intf_crm.t_citics_inr_inst t2
on t1.supr_inst_id=t2.inr_inst_id
intf_guozhenhai.t_sac_company_branch 分支机构名称、地址、省市，在impala上
客户经理
--查在职员工部门、岗位信息，PG库
select a.*,b.jobcname,c.dept_name from
(select * from edm_base.t_empe where empe_status<>'5' limit 1000) a
left join edm_base.interface_job b on a.hrs_post_code=b.gwdm
left join edm_base.t_department c on a.dept_no=c.dept_no
select * from intf_guozhenhai.kpi_empe_2019
where kpi_id = 28 --员工创收
intf_guozhenhai.dim_empeinfo intf_crm.empe_kpi_hr_proc 员工职级考核表 intf_crm.empe_kpi_hr 员工九宫图得分表，每月更新 bf_ap_crm.EMPE_KPI_HR_XZS 九宫图校招生表 bf_ap_crm.empe_kpi_hr 九宫图历史数据，更全
团队
bf_ap_crm.ttdbm 团队长 bf_ap_crm.ttdcy 团队成员表 BF_KY_HRS.IVW_BRANCH_TEAMLEADER 佘沛一的团队管理岗表 intf_lizikang.td_base_info 经过梳理的每月存续的团队列表 intf_lizikang.tdcy_base_info 经过梳理的每月存续的团队成员列表
经纪关系
bf_ap_crm.TKHGX 客户经纪关系表 bf_ap_crm.tkhgxlx 关系类型字典表 sr_ap_crm.TKHGXLX 历史关系类型字典表 sr_ap_crm.TKHGX 历史客户经纪关系表
--关联关系类型和关系名称
SELECT ryxx,
khh_xc ,
gxlx ,
t2.name as gx_nm,
tjrq,
(current_date-date(tjrq))/365 as fw_year,
bz
FROM bf_ap_crm.tkhgx t1 left join bf_ap_crm.tkhgxlx t2 on t1.gxlx=t2.ibm
客户经理收入结构
--员工收入结构
drop table if exists tmp_empesr_part1;
create local TEMPORARY table tmp_empesr_part1
as
select rybh as empe_no,sum(COALESCE(tdsr,0)) as 通道创收,
sum(COALESCE(cpsr,0)) as 产品创收,
sum(COALESCE(lrjlx,0)) as 融资创收,
sum(COALESCE(jgsr,0)) as 机构创收,
sum(COALESCE(cshj,0)) as 创收合计,
--个人
sum(case when organ_flag = '0' then COALESCE(tdsr,0) else 0 end) 通道创收_个人,
sum(case when organ_flag = '0' then COALESCE(cpsr,0) else 0 end) 产品创收_个人,
sum(case when organ_flag = '0' then COALESCE(lrjlx,0) else 0 end) 融资创收_个人,
sum(case when organ_flag = '0' then COALESCE(jgsr,0) else 0 end) 机构创收_个人,
sum(case when organ_flag = '0' then COALESCE(cshj,0) else 0 end) 创收合计_个人,
--机构
sum(case when organ_flag = '1' then COALESCE(tdsr,0) else 0 end) 通道创收_机构,
sum(case when organ_flag = '1' then COALESCE(cpsr,0) else 0 end) 产品创收_机构,
sum(case when organ_flag = '1' then COALESCE(lrjlx,0) else 0 end) 融资创收_机构,
sum(case when organ_flag = '1' then COALESCE(jgsr,0) else 0 end) 机构创收_机构,
sum(case when organ_flag = '1' then COALESCE(cshj,0) else 0 end) 创收合计_机构,
--产品
sum(case when organ_flag = '3' or organ_flag is null then COALESCE(tdsr,0) else 0 end) 通道创收_产品,
sum(case when organ_flag = '3' or organ_flag is null then COALESCE(cpsr,0) else 0 end) 产品创收_产品,
sum(case when organ_flag = '3' or organ_flag is null then COALESCE(lrjlx,0) else 0 end) 融资创收_产品,
sum(case when organ_flag = '3' or organ_flag is null then COALESCE(jgsr,0) else 0 end) 机构创收_产品,
sum(case when organ_flag = '3' or organ_flag is null then COALESCE(cshj,0) else 0 end) 创收合计_产品,
--财富
sum(case when BKR_BSN_CUST_BLG_TO_CODE = '1' then COALESCE(tdsr,0) else 0 end) 通道创收_财富,
sum(case when BKR_BSN_CUST_BLG_TO_CODE = '1' then COALESCE(cpsr,0) else 0 end) 产品创收_财富,
sum(case when BKR_BSN_CUST_BLG_TO_CODE = '1' then COALESCE(lrjlx,0) else 0 end) 融资创收_财富,
sum(case when BKR_BSN_CUST_BLG_TO_CODE = '1' then COALESCE(jgsr,0) else 0 end) 机构创收_财富,
sum(case when BKR_BSN_CUST_BLG_TO_CODE = '1' then COALESCE(cshj,0) else 0 end) 创收合计_财富
from sr_ap_crm.T_BB_RYKHCS t1 left outer join bf_hs_asset.client t2 on t1.khbh = t2.client_id
left outer join (select hs_cust_id,BKR_BSN_CUST_BLG_TO_CODE
from intf_crm.crm_cust_lbl_smy
where statc_dt = to_date(20220729,'yyyymmdd')
) t4 on t1.khbh = t4.hs_cust_id
where yf between substring('20220101' from 1 for 6) and substring('20220729' from 1 for 6)
group by 1;
bf_ap_crm.lccpxsgxqr_tjry 私募推介人员表（ yxry是工号，qz可能是分仓比例，lccpxsgxqr_id应该是推介关系） 金融产品销售（缺私募二级市场产品）： intf_guozhenhai.dim_empeinfo 客户经理信息 每月更新 intf_crm.t_empe 客户经理基本信息，包括年龄等 intf_guozhenhai.bms_cpxs_empe_cust 客户经理-客户-产品统计汇总 intf_guozhenhai.bms_ttlc_branch_fkq 天天利财14天期及以上考核销售表 intf_guozhenhai.bms_cpxs_serial 销售明细
intf_guozhenhai.empe_hyd 员工活跃度
grant select on intf_guozhenhai.empe_hyd to rl_lizikang
statc_m, --处理日期
fgs_name, --"分公司"
branch_name, --"营业部"
t1.empe_no, --"工号"
empe_nm, --"姓名"
jobcname, --"岗位"
crm_empe_lvl_name, --"职级"
highlevel, --"学历"
rztotal, --"入职天数"
lybs, --"来源标识"
--depccode, --"部门代码"
--depcname, --"部门"
grhydnjq, --个人活跃度部加权 当做 综合活跃度
lshyd, --零售活跃度
lshyd_zh , --零售账户活跃度
lshyd_yw , --零售业务活跃度
cfhyd , --财富活跃度
cfhyd_zh , --财富账户活跃度
cfhyd_yw , --财富业务活跃度
jghyd , --机构活跃度
jghyd_zh , --机构账户活跃度
jghyd_yw , --机构业务活跃度
zghyd, --资格活跃度
cast(percent_rank() over(order by hydjq desc) as numeric(5,2)), --活跃度_全体分位排序,
cast(percent_rank() over(PARTITION by fgs_name order by hydjq desc) as numeric(5,2)), --活跃度_同区域分位排序,
cast(percent_rank() over(PARTITION by jobcname order by hydjq desc) as numeric(5,2)), --活跃度_同岗位分位排序,
cast(percent_rank() over(PARTITION by crm_empe_lvl_name order by hydjq desc) as numeric(5,2)), --活跃度_同职级分位排序,
t2.branch_no,
t2.inr_inst_id
intf_guozhenhai.empe_fx 员工分型
otc销售记录 sr_kd_otc.otc_asset_log_v2
HS数据字典
select * from
(select * from bf_hs_user.fieldtoname where english_name='entrust_bs') t1
inner join bf_hs_user.sysdictionary t2 on t1.dict_entry=t2.dict_entry
OTC数据字典
select *
from bf_kd_otc.t_dict_items
where dict_code = 'trd_id'
客户
柜台
bf_hs_asset.bankexchaccount 用资金账号关联，银行信息 bf_hs_asset.client organ_flag 个人、机构、产品，对应ip_tp_code
上市公司客户
用rl_gp_reader查询intf_wuzhengliang.wmc_ashare_comp_busi_cover
产品
BF_LD_OAS.OTC_SYPZ_PRODUCT_V BF_LD_OAS.V_OTC_COMM_PRODUCT OA产品上架流程 bf_gg_iss.t_fund_info 朝阳永续产品信息 bf_ap_crm.tjrcpsxbq 产品标签表
select IBM,NOTE from bf_ap_crm.txtdm where fldm = 'CPBQLX' --cplX
select IBM,NOTE from bf_ap_crm.txtdm where fldm = 'CPBQLB' --cplb
BF_KD_OTC.otc_Asset_log_v2 OTC持仓表 bf_kd_otc.otc_inst_base_info_v2 OTC产品信息表 bf_kd_otc.otc_his_inst_mkt_info_v2 OTC净值表 SR_KD_OTC.OTC_HIS_INST_MKT_INFO_V2 OTC净值表 bf_kd_otc.otc_inst_mkt_info_v2 OTC最新净值表 bf_wd_iss.AIndexEODPrices WIND在GP上的指数表 intf_lizikang.trade_date_pweek 私募周频交易日表 intf_guozhenhai.sm_product_import_v2 林少驰导入的表 intf_guozhenhai.bms_ttlc_branch_fkq 天天利财14天期以上销售汇总表（按营业部） intf_guozhenhai.bms_ttlc_cust 天天利财销售表月频
--私募周频日期序列，act_date是实际取数的日期，trd_date是展示的日期序列，取每周五日期，放假也有数
drop table if EXISTS all_friday_step_3;
create local temporary table all_friday_step_3
as
SELECT
CASE
--如果上周五是节假日 需要在这单独列出 可能需要改为前一天 或者上上周五
WHEN ss.date=date'20191004' THEN date'20190927'
WHEN ss.date=date'20200124' THEN date'20200123' --也可能是20200117
WHEN ss.date=date'20200131' THEN date'20200123' --春节假期
WHEN ss.date=date'20200501' THEN date'20200430'
WHEN ss.date=date'20200626' THEN date'20200624'
WHEN ss.date=date'20201002' THEN date'20200925'
WHEN ss.date=date'20200930' THEN date'20200925'
when ss.date = date'20210212' then date'20210210'
when ss.date = date'20211001' then date'20210930'
when ss.date = date'20220204' then date'20220128'
when ss.date = date'20220603' then date'20220602'
when ss.date = date'20221007' then date'20220930'
when ss.date = date'20230127' then date'20230120'
when ss.date = date'20230623' then date'20230616'
when ss.date = date'20230929' then date'20230922'
when ss.date = date'20231006' then date'20230922'
else ss.date end as act_date,ss.date trd_date
,rank() over (ORDER BY ss.date DESC) rk
FROM
(
SELECT
date(substr(term,1,10)) as date
,extract(DOW FROM date(substr(term,1,10))) as weekday
FROM bf_ky_hrs.acalendar
ORDER BY 1 DESC
) ss
WHERE ss.date <= CURRENT_DATE--current_date
AND ss.weekday = 5
ORDER BY 1 DESC
distributed by(date);
产品库
select t3.inst_code,t1.*,t2.profit_type_otc,t2.FIN_CAT1 from
(select sub_prod_id,origin_prod_code,short_name_xet,full_name,group_by_type_1,case when group_by_type_1 in ('组合基金','其他策略') and risk_level in ('R4','R5') then group_by_type_1||'-R4/R5'when group_by_type_1 in ('组合基金','其他策略') and risk_level in ('R1','R2','R3') then group_by_type_1||'-R1/R2/R3' else group_by_type_2 end group_by_type_2,is_fof,is_customize_prod,profit_type,risk_level,list_date,life_cycle_status from bf_cs_dp.cpk_sub_prod where is_citics='1' --代销
and life_cycle_status not in ('10','50') --剔除待发行和已清盘
and prod_type='私募'
) t1
left join bf_cs_dp.cpk_sub_prod_tags t2 on t1.sub_prod_id = t2.sub_prod_id
left join bf_kd_otc.otc_inst_base_info_v2 t3 on t1.origin_prod_code = t3.inst_id
where t2.FIN_CAT1 <> 'PVC' --剔除收益凭证
净值数据
--OTC净值
select a.pd_code,a.pd_name,a.fund_date,b.inst_sno,c.last_net,c.accu_net from intf_guozhenhai.sm_product_import_v2 a
left join bf_kd_otc.otc_inst_base_info_v2 b on a.pd_code=b.inst_code
left join (select * from SR_KD_OTC.OTC_HIS_INST_MKT_INFO_V2 where net_date=20240208) c on b.inst_sno=c.inst_sno
--CPK净值
select a.inst_code,c.full_name,d.net_date,d.prod_nav,d.accrued_nav,d.div_reinvest_nav from bf_kd_otc.otc_inst_base_info_v2 a
left join BF_CS_DP.cpk_sub_prod c on a.inst_id=c.origin_prod_code
left join SR_CS_DP.cpk_sub_prod_nav d on c.sub_prod_id=d.sub_prod_id
where a.inst_code in ('SNE365','SQP397','ZTE01S','ZTE024','SXX393','909910','9099CX')
group by 1,2,3,4,5,6 order by 1,2,3
基金投顾
INTF_CRM.PRO_ROBOTS --CRM信息表 INTF_CRM.PORTFOLIO_HIS_DATA --最新一期数据 INTF_CRM.SR_PORTFOLIO_HIS_DATA --历史数据 INTF_CRM.PORTFOLIO_DATA_VIEW --历史数据 INTF_CRM.PORTFOLIO_TECH --风险收益指标 BF_HS_ITS.ITS_COMB_INFO --自有组合表 BF_JD_ITS.ITS_COMB_INFO --京东代销组合表 https://drive.weixin.qq.com/s?k=ALEA5gf3AAoRVC8wmeAGwA7wbqAFM
家庭信托
BF_LD_OAS.OTC_INST_TRUST_LISTING_CRM_V 家庭信托OA上架流程 BF_AP_COS.TXTYW_XTYXSQ 家庭信托COS系统业务审批表
OTC取家庭信托产品
--要去重
select distinct sett_date,trd_id,inst_code,inst_sname from BF_KD_OTC.otc_Asset_log_v2 where inst_cls='54' and trd_id='130'
相关表（在CRM库中的表名）
--otc 产品
SELECT * FROM bf_kd_otc.otc_inst_base_info_v2 where inst_cls='54';
--otc产品扩展信息
SELECT * FROM bf_kd_otc.otc_inst_ext_info_v2 where inst_sno in
(SELECT inst_sno FROM bf_kd_otc.otc_inst_base_info_v2 where inst_cls='54');
--otc 黑白名单设置
SELECT * FROM bf_kd_otc.otc_inst_qlf_invst_cfg_v2 where inst_sno in
(SELECT inst_sno FROM bf_kd_otc.otc_inst_base_info_v2 where inst_cls='54');
--otc购买记录
--预约委托表，采用预约认购方式的产品预约委托
SELECT * FROM bf_kd_otc.otc_book_trd_orders_v2 a where a.cancel_flag='0' and a.canceled_flag='0' and a.trd_id='110' and a.inst_cls='54';
--认申赎委托表，不预约的产品的委托，以及预约的产品预约结束后转单后生成的正式委托
--对于预约的产品，会有滞后，要等预约结束做了转单后才会到认申赎委托表中
SELECT * FROM bf_kd_otc.otc_trd_orders_v2 a where a.cancel_flag='0' and a.canceled_flag='0' and a.trd_id='110' and a.inst_cls='54';
--历史
SELECT * FROM bf_kd_otc.otc_his_trd_orders_v2 a where a.cancel_flag='0' and a.canceled_flag='0' and a.trd_id='110' and a.inst_cls='54';
--OA产品
select * from bf_ld_oas.OTC_INST_TRUST_LISTING_CRM_V
--cos申请
select * from bf_ap_cos.txtyw_xtyxsq a
分支绩效
intf_guozhenhai.dm_kpi_branch 分支指标库 分型 营业部分型：intf_guozhenhai.branch_fx 员工分型: intf_guozhenhai.empe_fx
其他
bf_ky_hrs.acalendar ：xtype=1代表周末非交易日，=2代表法定节假日，=0是交易日 bf_kd_otc.otc_his_book_trd_orders_v2 otc系统记录 sr_hs_his.his_fundjour INTF_CRM.T_RETAIL_COIN_DAILY 瑞豆评分数据 INTF_CRM.TLSXNZBDFPM 零售效能评分数据
WIND
1.取上市公司董监高
SELECT
S_INFO_WINDCODE,
ANN_DATE,
S_INFO_MANAGER_NAME,
S_INFO_MANAGER_GENDER,
S_INFO_MANAGER_EDUCATION,
S_INFO_MANAGER_NATIONALITY,
S_INFO_MANAGER_BIRTHYEAR,
S_INFO_MANAGER_STARTDATE,
S_INFO_MANAGER_LEAVEDATE,
CASE
S_INFO_MANAGER_TYPE
WHEN 0 THEN
'董事会成员'
WHEN 1 THEN
'高管成员'
WHEN 2 THEN
'监事会成员'
WHEN 4 THEN
'投资决策委员会'
WHEN 5 THEN
'核心技术人员'
WHEN 6 THEN
'境外投资决策委员会'
WHEN 7 THEN
'其他'
END manager_type,
S_INFO_MANAGER_POST,
S_INFO_MANAGER_INTRODUCTION,
MANID
FROM
AShareManagement where S_INFO_MANAGER_LEAVEDATE is null
2.取减持公告预披露数据
select a.ANN_DT,a.S_INFO_WINDCODE,HOLDER_NAME,HOLDER_STATUS,HOLDER_TYPE,HOLD_NUMBER,HOLD_PROPORTION,TRANSACT_OBJECTIVE,TRANSACT_STOCK_SOURCE,TRANSACT_PERIOD_DESCRIPTION,TRANSACTION_MODE,PLAN_TRANSACT_MAX_NUM,PLAN_TRANSACT_MAX_RATIO,PLAN_MAX_HOLD_RATIO,TOT_ACTUAL_TRANSACT_NUM,VARIABLE_PRICE_MEMO,CHANGE_START_DATE,CHANGE_END_DATE,b.SPONSOR,e.S_TYPNAME,f.s_info_province
from ASarePlanTrade a
left join IPOCompRFA b on a.S_INFO_WINDCODE=b.S_INFO_WINDCODE
left join (
select c.S_INFO_WINDCODE,d.S_TYPNAME from AShareMarginSubject c
inner join AShareTypeCode d on c.S_MARGIN_SHARETYPE=d.S_ORIGIN_TYPCODE) e on a.S_INFO_WINDCODE=e.S_INFO_WINDCODE
left join AShareIntroduction f on a.s_info_windcode=f.S_INFO_WINDCODE
where a.ANN_DT BETWEEN '20211201' and '20220115' and a.TRANSACT_TYPE='减持'
group by a.ANN_DT,a.S_INFO_WINDCODE,HOLDER_NAME,HOLDER_STATUS,HOLDER_TYPE,HOLD_NUMBER,HOLD_PROPORTION,TRANSACT_OBJECTIVE,TRANSACT_STOCK_SOURCE,TRANSACT_PERIOD_DESCRIPTION,TRANSACTION_MODE,PLAN_TRANSACT_MAX_NUM,PLAN_TRANSACT_MAX_RATIO,PLAN_MAX_HOLD_RATIO,TOT_ACTUAL_TRANSACT_NUM,VARIABLE_PRICE_MEMO,CHANGE_START_DATE,CHANGE_END_DATE,b.SPONSOR,e.S_TYPNAME,f.s_info_province
order by ANN_DT
3.取上市公司公告信息
select b.ID,b.S_INFO_WINDCODE,b.ANN_DT,b.N_INFO_TITLE,b.N_INFO_FCODE,c.N_INFO_NAME,c.N_INFO_LEVELNUM,b.N_INFO_ANNLINK from AShareAnnInf b left join AShareAnnColumn c on b.N_INFO_FCODE=c.N_INFO_FCODE where c.N_INFO_FCODE in ('5402000000','5401000000','5403000000')
--公告栏目代码与公告栏目名称对应表
select N_INFO_FCODE,N_INFO_NAME from AShareAnnColumn group by N_INFO_FCODE,N_INFO_NAME
--半年报 5402000000 季报 5401000000 年报 5403000000
4.取金融资讯
select * from FinancialNews where PUBLISHDATE>to_date('2022-01-30 00:00:00','yyyy-mm-dd hh24:mi:ss') and rownum<20000
5.取上市公司购买私募信息
--认购信息
select * from
(select * from
(select OBJECT_ID,EVENT_ID,S_ISSUER_ID,S_INFO_WINDCODE,ANN_DATE,BUY_COMPANY_NAME,case S_UNDERLYING when 280005000 then '基金专户' when 280010000 then '投资公司理财产品' else null end as s_type,case S_RELATION_TYPCODE when 323001000 then '公司本身' when 323002000 then '公司股东' when 323004000 then '控股参股公司' else null end as S_RELATION_TYPCODE,S_ISSUER_NAME,S_FULLNAME,S_SUBSCRIPTION_DATE,S_SUBSCRIPTION_AMOUNT,S_INCOMESTARTDATE,S_INCOMEENDDATE,S_COMMISSION,S_INFO_CODE from AShareCorporateFinance where s_underlying=280005000 or s_underlying=280010000)a left join
(select FUNDMANAGEMENTCOMPID,ADVISOR,F_REGIST_NUMBER,F_PRODUCT_TYPE,F_INFO_WINDCODE,F_INFO_FULLNAME,F_INFO_NAME,F_INFO_CORP_FUNDMANAGEMENTCOMP,F_INFO_CUSTODIANBANK,F_INFO_FIRSTINVESTTYPE,F_INFO_SETUPDATE from ChinaHedgeFundDescription)b on a.S_FULLNAME=b.F_INFO_FULLNAME
left join
(SELECT
OBJECT_ID,
F_INFO_CORP_FUNDMANAGEMENTCOMP as "基金管理人全称 ( 中文 )",
F_INFO_ORG_CODE as "组织机构代码",
F_INFO_ESTABLISH_TIME as "成立时间",
F_INFO_ADDRESS as "注册地址",
F_INFO_OFFICE as "办公地址",
F_INFO_REGCAPITAL as "注册资本",
F_INFO_COMP_PROPERTY as "企业性质",
F_INFO_MANAGED_FUND_TYPE as "管理基金主要类别",
F_INFO_TOTALEMPLOYEES as "员工人数",
F_INFO_MANAGEMENT_SCALE as "公司当前管理规模",
F_MANAGEMENT_SCALE_INTERVAL as "当前管理规模区间",
F_INFO_COMPANYID
FROM
CHFManagerInformation )c on a.S_ISSUER_ID=c.F_INFO_COMPANYID) t1
left join
(--私募费率
select S_INFO_WINDCODE,CHANGE_DT,CHARGEWAY,
sum(case when FEETYPECODE =602001000 then FEERATIO else null end ) as rgfl,
sum(case when FEETYPECODE =602002000 then FEERATIO else null end ) as sgfl,
sum(case when FEETYPECODE =602003000 then FEERATIO else null end ) as shfl,
sum(case when FEETYPECODE =602004000 then FEERATIO else null end ) as glrfl,
sum(case when FEETYPECODE =602005000 then FEERATIO else null end ) as yjbcfl,
sum(case when FEETYPECODE =602006000 then FEERATIO else null end ) as tgfl,
sum(case when FEETYPECODE =602007000 then FEERATIO else null end ) as khfwfl,
sum(case when FEETYPECODE =602008000 then FEERATIO else null end ) as wytcfl,
sum(case when FEETYPECODE =602009000 then FEERATIO else null end ) as zrsxfl,
sum(case when FEETYPECODE =602010000 then FEERATIO else null end ) as xtglfl,
sum(case when FEETYPECODE =602011000 then FEERATIO else null end ) as zhglrgfl,
sum(case when FEETYPECODE =602012000 then FEERATIO else null end ) as stfl,
sum(case when FEETYPECODE =602014000 then FEERATIO else null end ) as zhfl,
sum(case when FEETYPECODE =602015000 then FEERATIO else null end ) as xsfwfl
from ChinaHedgeFundFee group by S_INFO_WINDCODE,CHANGE_DT,CHARGEWAY) t2 on t1.F_INFO_WINDCODE = t2.S_INFO_WINDCODE
left join
(
--基金经理
select F_INFO_WINDCODE,ANN_DATE,F_INFO_FUNDMANAGER,F_INFO_MANAGER_GENDER,F_INFO_MANAGER_BIRTHYEAR,F_INFO_MANAGER_EDUCATION,F_INFO_MANAGER_STARTDATE,F_INFO_MANAGER_LEAVEDATE from ChinaHedgeFundManager
)t3 on t1.F_INFO_WINDCODE = t3.F_INFO_WINDCODE
left join (select F_INFO_WINDCODE ,
S_INFO_SECTOR ,
Industriesname ,
S_INFO_SECTORENTRYDT ,
S_INFO_SECTOREXITDT ,
CUR_SIGN
from ChinaHedgeFundSector
inner join AShareIndustriesCode
on S_INFO_SECTOR=IndustriesCode
) t4 on t1.F_INFO_WINDCODE = t4.F_INFO_WINDCODE
;
--基金管理人信息
SELECT
OBJECT_ID,
F_INFO_CORP_FUNDMANAGEMENTCOMP as "基金管理人全称 ( 中文 )",
F_INFO_ORG_CODE as "组织机构代码",
F_INFO_ESTABLISH_TIME as "成立时间",
F_INFO_ADDRESS as "注册地址",
F_INFO_OFFICE as "办公地址",
F_INFO_REGCAPITAL as "注册资本",
F_INFO_COMP_PROPERTY as "企业性质",
F_INFO_MANAGED_FUND_TYPE as "管理基金主要类别",
F_INFO_TOTALEMPLOYEES as "员工人数",
F_INFO_MANAGEMENT_SCALE as "公司当前管理规模",
F_MANAGEMENT_SCALE_INTERVAL as "当前管理规模区间",
F_INFO_COMPANYID
FROM
CHFManagerInformation ;
--私募基金基本资料
select FUNDMANAGEMENTCOMPID,ADVISOR,F_REGIST_NUMBER,F_PRODUCT_TYPE,F_INFO_WINDCODE,F_INFO_FULLNAME,F_INFO_NAME,F_INFO_CORP_FUNDMANAGEMENTCOMP,F_INFO_CUSTODIANBANK,F_INFO_FIRSTINVESTTYPE,F_INFO_SETUPDATE,F_INFO_MATURITYDATE,F_PCHREDM_PCHSTARTDATE, UNLOCKPERIOD from ChinaHedgeFundDescription
--私募费率
select S_INFO_WINDCODE,CHANGE_DT,CHARGEWAY,
sum(case when FEETYPECODE =602001000 then FEERATIO else null end ) as rgfl,
sum(case when FEETYPECODE =602002000 then FEERATIO else null end ) as sgfl,
sum(case when FEETYPECODE =602003000 then FEERATIO else null end ) as shfl,
sum(case when FEETYPECODE =602004000 then FEERATIO else null end ) as glrfl,
sum(case when FEETYPECODE =602005000 then FEERATIO else null end ) as yjbcfl,
sum(case when FEETYPECODE =602006000 then FEERATIO else null end ) as tgfl,
sum(case when FEETYPECODE =602007000 then FEERATIO else null end ) as khfwfl,
sum(case when FEETYPECODE =602008000 then FEERATIO else null end ) as wytcfl,
sum(case when FEETYPECODE =602009000 then FEERATIO else null end ) as zrsxfl,
sum(case when FEETYPECODE =602010000 then FEERATIO else null end ) as xtglfl,
sum(case when FEETYPECODE =602011000 then FEERATIO else null end ) as zhglrgfl,
sum(case when FEETYPECODE =602012000 then FEERATIO else null end ) as stfl,
sum(case when FEETYPECODE =602014000 then FEERATIO else null end ) as zhfl,
sum(case when FEETYPECODE =602015000 then FEERATIO else null end ) as xsfwfl
from ChinaHedgeFundFee group by S_INFO_WINDCODE,CHANGE_DT,CHARGEWAY
--基金经理
select F_INFO_WINDCODE,ANN_DATE,F_INFO_FUNDMANAGER,F_INFO_MANAGER_GENDER,F_INFO_MANAGER_BIRTHYEAR,F_INFO_MANAGER_EDUCATION,F_INFO_MANAGER_STARTDATE,F_INFO_MANAGER_LEAVEDATE from ChinaHedgeFundManager
--获取中国私募基金板块名称
select F_INFO_WINDCODE ,
S_INFO_SECTOR ,
Industriesname ,
S_INFO_SECTORENTRYDT ,
S_INFO_SECTOREXITDT ,
CUR_SIGN
from ChinaHedgeFundSector
inner join AShareIndustriesCode
on S_INFO_SECTOR=IndustriesCode
6.取ETF信息
select * from ChinaMutualFundDescription where substr(F_INFO_WINDCODE,1,2)in('51','15') and IS_INDEXFUND=1 and F_INFO_TYPE='契约型开放式' and instr(F_INFO_FULLNAME,'交易型开放式')>0
--下面这个取的更多，包括QDII
select a.f_info_windcode,b.F_INFO_FULLNAME,F_INFO_NAME,s_info_sector,F_INFO_CORP_FUNDMANAGEMENTID,F_INFO_CORP_FUNDMANAGEMENTCOMP,F_INFO_CUSTODIANBANKID,F_INFO_CUSTODIANBANK,F_INFO_FIRSTINVESTTYPE,F_INFO_SETUPDATE,F_INFO_ISSUEDATE,F_ISSUE_TOTALUNIT,F_INFO_MANAGEMENTFEERATIO,F_INFO_CUSTODIANFEERATIO,F_INFO_BENCHMARK,F_INFO_EXCHMARKET,F_INFO_FIRSTINVESTSTYLE,F_INFO_TYPE from
(select F_INFO_WINDCODE,S_INFO_SECTOR from ChinaMutualFundSector where s_info_sector='2001020200' and cur_sign=1)a
left join ChinaMutualFundDescription b on a.F_INFO_WINDCODE = b.F_INFO_WINDCODE
7.取券商数据
SACSecuritiesFirmsBusinessData是年报数据，38个指标，102家券商 ListedBrokerBusinessData 是半年报和年报数据，20多个指标
8.取ETF成分股
with tmp_sh as
(select * from
(select S_INFO_WINDCODE,F_PRT_ENDDATE,S_INFO_STOCKWINDCODE,F_PRT_STKVALUE,F_PRT_STKQUANTITY,F_PRT_STKVALUETONAV,STOCK_PER,FLOAT_SHR_PER, max(F_PRT_ENDDATE)over(partition by S_INFO_WINDCODE order by F_PRT_ENDDATE desc )rn from ChinaMutualFundStockPortfolio ) a
inner join (select a.f_info_windcode,b.F_INFO_FULLNAME,F_INFO_NAME,s_info_sector,F_INFO_CORP_FUNDMANAGEMENTID,F_INFO_CORP_FUNDMANAGEMENTCOMP,F_INFO_CUSTODIANBANKID,F_INFO_CUSTODIANBANK,F_INFO_FIRSTINVESTTYPE,F_INFO_SETUPDATE,F_INFO_ISSUEDATE,F_ISSUE_TOTALUNIT,F_INFO_MANAGEMENTFEERATIO,F_INFO_CUSTODIANFEERATIO,F_INFO_BENCHMARK,F_INFO_EXCHMARKET,F_INFO_FIRSTINVESTSTYLE,F_INFO_TYPE from
(select F_INFO_WINDCODE,S_INFO_SECTOR from ChinaMutualFundSector where s_info_sector='2001020200' and cur_sign=1)a
left join ChinaMutualFundDescription b on a.F_INFO_WINDCODE = b.F_INFO_WINDCODE) b on a.S_INFO_WINDCODE=b.F_INFO_WINDCODE where F_PRT_ENDDATE=rn)
select a.*,b.S_INFO_INDEXWINDCODE,b.S_INFO_INDEXWEG,S_INFO_BGNDT,S_INFO_ENDDT, S_INFO_COMPNAME,S_INFO_CODE,S_INFO_NAME,S_INFO_EXCHMARKET,S_INFO_INDEXCODE,case when S_INFO_INDEXCODE = '647005000' then '主题指数' when S_INFO_INDEXCODE = '647001000'then '规模指数' when S_INFO_INDEXCODE = '647002000' then '行业指数' when S_INFO_INDEXCODE = '647008000' then '商品指数' when S_INFO_INDEXCODE = '647007000' then '债券指数' when S_INFO_INDEXCODE = '647002000' then '行业指数' when S_INFO_INDEXCODE = '647010000' then '外汇指数' when S_INFO_INDEXCODE = '647009000' then '综合指数' when S_INFO_INDEXCODE = '647006000' then '基金指数' when S_INFO_INDEXCODE = '647004002' then '价值指数' when S_INFO_INDEXCODE = '647004001' then '成长指数' when S_INFO_INDEXCODE = '647004000' then '风格指数' else null end as S_INFO_INDEXSTYLE from tmp_sh a
left join ChinaMutualFundBenchMark b on a.S_INFO_WINDCODE = b.S_INFO_WINDCODE
left join AIndexDescription c on b.S_INFO_INDEXWINDCODE = c.S_INFO_WINDCODE where S_INFO_ENDDT is null
9.查券商公募基金代销数量
with tmp_a as (
select t1.F_INFO_WINDCODE,t2.F_INFO_FULLNAME 基金名称,t2.F_INFO_TYPE 基金类型,t2.F_INFO_ISSUEDATE 发行日期,t2.F_ISSUE_TOTALUNIT 发行份额, t2.F_INFO_CORP_FUNDMANAGEMENTCOMP 管理人,t2.F_INFO_FIRSTINVESTSTYLE 投资风格,t1.F_ANN_DATE,replace(replace(replace(t1.F_AGENCY_NAME,'股份有限公司',''),'有限公司',''),'有限责任公司','') 代销机构,t1.F_BEGIN_DATE,t1.F_END_DATE from
(select F_INFO_WINDCODE,F_ANN_DATE,F_AGENCY_NAME,F_BEGIN_DATE,F_END_DATE from CMFSellingAgents where CUR_SIGN=1 and F_BEGIN_DATE BETWEEN '20220101' and '20220331') t1
left join ChinaMutualFundDescription t2 on t1.F_INFO_WINDCODE=t2.F_INFO_WINDCODE )
select 代销机构,
sum(case when 投资风格='被动指数型' then 1 else 0 end ) as 被动指数型,
sum(case when 投资风格='混合型' or 投资风格='股债配置型' or 投资风格='灵活配置型' or 投资风格='平衡型' or 投资风格='价值型' or 投资风格='稳定型' or 投资风格='成长收益复合型' or 投资风格='主题型' or 投资风格='平稳型' or 投资风格='增值型' or 投资风格='稳健型'then 1 else 0 end ) as 混合型,
sum(case when 投资风格='债券型' or 投资风格='积极配置型' or 投资风格='强化收益型' or 投资风格='收益型' or 投资风格='稳健增长型' or 投资风格='价值增长型' or 投资风格='优选增长型' or 投资风格='增强收益型' or 投资风格='稳定增值型' or 投资风格='增强债券型' or 投资风格='增强型' or 投资风格='积极配置型'then 1 else 0 end ) as 债券型,
sum(case when 投资风格='股票型' or 投资风格='普通股票型' or 投资风格='积极型'then 1 else 0 end ) as 股票型,
sum(case when 投资风格='成长型' then 1 else 0 end )as 成长型,
sum(case when 投资风格='能源化工期货型' or 投资风格='豆粕期货型' or 投资风格='有色金属期货型' then 1 else 0 end )as 期货型
from
(select case when 代销机构 like '%中信证券%' then '中信证券' when 代销机构 like '%申万宏源%' then '申万宏源' when 代销机构 like '%方正证券%' then '方正证券' when 代销机构='广州证券' then '中信证券'when 代销机构='中信华南证券' then '中信证券' when 代销机构='中信山东证券' then '中信证券' when 代销机构='中信(山东)证券' then '中信证券' when 代销机构='中国中金财富证券' then '中金公司' when 代销机构='中国证券金融' then '中金公司' when 代销机构='中金财富证券' then '中金公司' when 代销机构 like '%东方财富%' then '东方财富' when 代销机构 like '%中信建投%' then '中信建投' when 代销机构 like '%华泰%' then '华泰证券' when 代销机构 like'%银河%' then '中国银河' when 代销机构='中银国际证券' then '中银证券' when 代销机构 like '%大同%' then '大同证券'else 代销机构 end as 代销机构, 投资风格,F_INFO_WINDCODE
from tmp_a ) t1
group by 代销机构 having INSTR(代销机构, '证券')>0