2021-11-04

hive 取每个月的月底最后一天
SELECT A.SUMDATE AS SUMDATE
,A.FUNDCODE AS FUNDCODE
,A.FUND_TYPE_CODE AS FUND_TYPE_CODE
,A.FUND_TYPE_NAME AS FUND_TYPE_NAME
,A.YIELD_W AS YIELD_W
,A.YIELD_W_RANKS AS YIELD_W_RANKS
,A.YIELD_M AS YIELD_M
,A.YIELD_M_RANKS AS YIELD_M_RANKS
,A.YIELD_Q AS YIELD_Q
,A.YIELD_Q_RANKS AS YIELD_Q_RANKS
,A.YIELD_HALFYEAR AS YIELD_HALFYEAR
,A.YIELD_HALFYEAR_RANKS AS YIELD_HALFYEAR_RANKS
,A.YIELD_CURYEAR AS YIELD_CURYEAR
,A.F_MAXDOWNSIDE_THISYEART AS F_MAXDOWNSIDE_THISYEART
,A.YIELD_CURYEAR_RANKS AS YIELD_CURYEAR_RANKS
,A.YIELD_Y AS YIELD_Y
,A.YIELD_Y_RANKS AS YIELD_Y_RANKS
,A.YIELD_2Y AS YIELD_2Y
,A.YIELD_2Y_RANKS AS YIELD_2Y_RANKS
,A.YIELD_3Y AS YIELD_3Y
,A.YIELD_3Y_RANKS AS YIELD_3Y_RANKS
,A.YIELD_5Y AS YIELD_5Y
,A.YIELD_5Y_RANKS AS YIELD_5Y_RANKS
,A.YIELD_SETUP_SINCE AS YIELD_SETUP_SINCE
,A.YIELD_SETUP_SINCE_ANNUALIZED AS YIELD_SETUP_SINCE_ANNUALIZED
FROM (SELECT D.SK_DATE AS SUMDATE
,ROW_NUMBER() OVER (PARTITION BY a.FUNDCODE,substr(cast (D.SK_DATE as string),5,2) ORDER BY D.SK_DATE DESC) AS RN_1
,A.FUNDCODE
,A.LEVELID AS FUND_TYPE_CODE
,A.LEVELNAME AS FUND_TYPE_NAME
,NULL AS YIELD_W
,NULL AS YIELD_W_RANKS
,NULL AS YIELD_M
,NULL AS YIELD_M_RANKS
,NULL AS YIELD_Q
,NULL AS YIELD_Q_RANKS
,NULL AS YIELD_HALFYEAR
,NULL AS YIELD_HALFYEAR_RANKS
,CASE WHEN A.YEARNETCHGRATE IS NOT NULL AND A.YEARNETCHGRATERANK IS NULL AND B.BK_PORTFOLIO IS NULL THEN NULL ELSE A.YEARNETCHGRATE END AS YIELD_CURYEAR
,NULL AS F_MAXDOWNSIDE_THISYEART–最大回撤
,A.YEARNETCHGRATERANK AS YIELD_CURYEAR_RANKS
,NULL AS YIELD_Y
,NULL AS YIELD_Y_RANKS
,NULL AS YIELD_2Y
,NULL AS YIELD_2Y_RANKS
,NULL AS YIELD_3Y
,NULL AS YIELD_3Y_RANKS
,NULL AS YIELD_5Y
,NULL AS YIELD_5Y_RANKS
,NULL AS YIELD_SETUP_SINCE
,NULL AS YIELD_SETUP_SINCE_ANNUALIZED
FROM (SELECT A.*,ROW_NUMBER() OVER (PARTITION BY FUNDCODE ORDER BY SUMDATE DESC) AS RN
FROM MD_DM.FACT_CGS_FNDNAVMONTH A
LEFT JOIN MD_DM.DIM_PORTFOLIO B
ON A.FUNDCODE = B.BK_PORTFOLIO
AND LIQUIDATION_DATE BETWEEN CAST(CONCAT(CAST(CAST(SUBSTR(CAST(#http(http://cdp.yhfund.com.cn:8080/bigdata/ctl/MD_DM/bus_date_int)# AS STRING),1,4) AS INT) AS STRING),‘0101’) AS INT)
AND CAST(#http(http://cdp.yhfund.com.cn:8080/bigdata/ctl/MD_DM/bus_date_int)# AS INT)
WHERE YEARNETCHGRATE<>‘0’
AND SUMDATE<=LIQUIDATION_DATE
AND SUMDATE BETWEEN CAST(CONCAT(CAST(CAST(SUBSTR(CAST(#http(http://cdp.yhfund.com.cn:8080/bigdata/ctl/MD_DM/bus_date_int)# AS STRING),1,4) AS INT) AS STRING),‘0101’) AS INT)
AND CAST(#http(http://cdp.yhfund.com.cn:8080/bigdata/ctl/MD_DM/bus_date_int)# AS INT)
) A
LEFT JOIN MD_DM.DIM_PORTFOLIO B
ON CASE WHEN A.FUNDCODE=‘000662’ THEN ‘000657’ ELSE A.FUNDCODE END = B.BK_PORTFOLIO
INNER JOIN MD_DM.DIM_DATE D ON 1=1 AND D.SK_DATE BETWEEN (CAST(REGEXP_REPLACE(SUBSTR(CAST(DATE_ADD(LAST_DAY(FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(A.SUMDATE AS STRING),‘yyyyMMdd’),‘yyyy-MM-dd’)),+1) AS STRING),1,10),’-’,’’) AS INT))
AND (CAST(CONCAT(CAST(CAST(SUBSTR(CAST(#http(http://cdp.yhfund.com.cn:8080/bigdata/ctl/MD_DM/bus_date_int)# AS STRING),1,4) AS INT) AS STRING),‘1231’) AS INT))
WHERE RN = 1) A where A.RN_1=1