以下是SQL代码:
WITH TMP_TABLE_INFO AS
(SELECT DISTINCT T.CITY_NAME,
T1.PRICE_DATE,
T1.YM_ID,
T2.VERSION_CODE,
T2.VERSION_ID,
PKG_COMMON_ROUTINE.FUNC_NEW_MSRP(T1.PRICE_DATE,
T2.VERSION_ID) AS MSRP
FROM SJZX.V_REGION T
LEFT JOIN SJZX.T_NEW_ENERGY_REPORT_VERSION T1
ON 1 = 1
LEFT JOIN SJZX.V_VERSION T2
ON T2.VERSION_CODE = T1.VERSION_CODE
WHERE T.CITY_NAME IN ('北京市',
'上海市',
'广州市',
'深圳市',
'天津市',
'成都市',
'西安市',
'杭州市',
'武汉市',
'济南市',
'昆明市',
'青岛市',
'合肥市',
'重庆市',
'长沙市',
'郑州市',
'南京市',
'长春市',
'苏州市',
'南昌市',
'宁波市',
'福州市')
AND T1.YM_ID IN (201812, 201901, 201902)),
TMP_MSRP AS
(SELECT DISTINCT YM_ID, SUB_MODEL_ID, 最低MSRP, 最高MSRP
FROM (SELECT YM_ID,
VERSION_CODE,
VERSION_ID,
SUB_MODEL_ID,
MAX(VERSION_ID) KEEP(DENSE_RANK FIRST ORDER BY MSRP) OVER(PARTITION BY YM_ID, SUB_MODEL_ID) AS 最低MSRP,
MAX(VERSION_ID) KEEP(DENSE_RANK LAST ORDER BY MSRP) OVER(PARTITION BY YM_ID, SUB_MODEL_ID) AS 最高MSRP
FROM (SELECT DISTINCT T.VERSION_CODE,
T1.VERSION_ID,
T1.SUB_MODEL_ID,
T.YM_ID,
PKG_COMMON_ROUTINE.FUNC_NEW_MSRP(T.PRICE_DATE,
T1.VERSION_ID) AS MSRP
FROM SJZX.T_NEW_ENERGY_REPORT_VERSION T
LEFT JOIN SJZX.V_VERSION T1
ON T.VERSION_CODE = T1.VERSION_CODE
WHERE T.YM_ID IN (201812, 201901, 201902)))),
TMP_CITY_PRICE AS
(SELECT A.PRICE_DATE,
B.VERSION_CODE,
B.VERSION_ID,
B.SUB_MODEL_ID,
C.CITY_NAME,
C.CITY_ENAME,
A.MARKET_PRICE * 10000 TP
FROM SJZX.V_TP_CITY A, SJZX.V_VERSION B, SJZX.V_REGION C
WHERE A.VERSION_ID = B.VERSION_ID
AND A.CITY_ID = C.CITY_ID
AND A.PRICE_INPUT_DATE_ID IN (2)
AND A.PRICE_DATE BETWEEN 20181230 AND 20190228
AND B.VERSION_CODE IN
(SELECT DISTINCT VERSION_CODE FROM SJZX.T_NEW_ENERGY_REPORT_VERSION)
AND C.CITY_NAME IN ('北京市',
'上海市',
'广州市',
'深圳市',
'天津市',
'成都市',
'西安市',
'杭州市',
'武汉市',
'济南市',
'昆明市',
'青岛市',
'合肥市',
'重庆市',
'长沙市',
'郑州市',
'南京市',
'长春市',
'苏州市',
'南昌市',
'宁波市',
'福州市')),
TMP_MIX AS
(SELECT T.YM_ID,
T1.SUB_MODEL_ID,
T.VERSION_CODE,
T2.SALES_QTY / NULLIF(SUM(T2.SALES_QTY)
OVER(PARTITION BY T1.SUB_MODEL_ID, T.YM_ID),
0) AS MIX
FROM SJZX.T_NEW_ENERGY_REPORT_VERSION T
LEFT JOIN SJZX.V_VERSION T1
ON T1.VERSION_CODE = T.VERSION_CODE
LEFT JOIN SJZX.V_ISABS_VERSION_SALES T2
ON T2.VERSION_ID = T1.VERSION_ID
AND T2.YM_ID = T.YM_ID
WHERE T.YM_ID BETWEEN 201812 AND 201902),
TMP_SUBSIDIES AS
(SELECT TO_CHAR(T1.PRICE_DATE, 'YYYYMMDD') AS PRICE_DATE,
T3.CITY_NAME,
T1.VERSION_ID,
T1.LOCAL_SUBSIDIES,
T1.NATION_SUBSIDIES,
T2.OEM_SUBSIDY,
T2.FAVOURABLE_CBT,
AVG(T2.GUIDING_PRICE) MSRP,
AVG(T1.TP * 10000) CP
FROM SJZX.FDW_NEW_ENERGY_PRICE_MANAGE T1
LEFT JOIN SJZX.FDW_NEW_ENERGY_CAR_TYPE T2
ON T1.VERSION_ID = T2.VERSION_ID
AND T1.CITY_ID = T2.CITY_ID
AND T1.PRICE_DATE BETWEEN T2.START_EXECUTE_DATE AND T2.END_EXECUTE_DATE
LEFT JOIN SJZX.V_REGION T3
ON T3.CITY_ID = T1.CITY_ID
GROUP BY T1.VERSION_ID,
T3.CITY_NAME,
T1.PRICE_DATE,
T1.LOCAL_SUBSIDIES,
T1.NATION_SUBSIDIES,
T2.OEM_SUBSIDY,
T2.FAVOURABLE_CBT)
SELECT T0.VERSION_CODE,
CASE
WHEN T.PRODUCTION_AND_MARKETING LIKE '%在销%' THEN
'On sale'
ELSE
'Stopped Sale'
END SALES_STATUS,
T0.CITY_NAME,
SUBSTR(T0.YM_ID, 1, 4) AS S_YEAR,
TO_NUMBER(SUBSTR(T0.YM_ID, 5, 2)) AS S_MONTH,
T0.YM_ID,
CASE
WHEN T.MANF_BRAND_PROP_NAME LIKE '%进口%' THEN
'IMP'
ELSE
'CKD'
END PRODUCT_TYPE,
'' AS DEALER,
T.MANF_GROUP_ENAME,
T.BRAND_ORIG_ENAME,
T.MANF_ENAME,
T.BRAND_ENAME,
T.MODEL_ENAME,
T.SUB_MODEL_ENAME,
T.VERSION_FULL_ENAME,
CASE
WHEN T0.VERSION_ID = T2.最低MSRP THEN
'Entry'
WHEN T0.VERSION_ID = T2.最高MSRP THEN
'Top'
ELSE
'-'
END ENTRY_LINE,
T.SEGMENT_NAME,
T.BODY_TYPE_ENAME,
TO_DATE(T.LAUNCH_DATE, 'YYYY-MM-DD') AS LAUNCH_DATE,
T.FUEL_TYPE_ENAME,
T1.电机,
T1.排量,
T1.最大功率,
T1.马力,
T1.电机马力,
T1.电机的最大功率,
T1."纯电续航里程(km)",
T1."电池电量(电能)(kwh)",
T1.驱动形式,
T1.车长,
T1.车宽,
T1.车高,
T1.轴距,
T1.车辆总质量,
T1.整备质量,
T1."最高车速(km/h)",
T1."纯电行驶最高速度(km/h)",
T1.电池包能量密度,
T5.CP,
T0.MSRP,
T3.MIX,
T4.TP,
T5.NATION_SUBSIDIES,
T5.LOCAL_SUBSIDIES,
T5.FAVOURABLE_CBT,
T5.OEM_SUBSIDY
FROM TMP_TABLE_INFO T0
LEFT JOIN SJZX.V_VERSION T
ON T.VERSION_CODE = T0.VERSION_CODE
LEFT JOIN SJZX.V_NEW_ENERGY_REPORT_CONFIG T1
ON T1.VERSION_ID = T.VERSION_ID
LEFT JOIN TMP_MSRP T2
ON T2.SUB_MODEL_ID = T.SUB_MODEL_ID
AND T2.YM_ID = T0.YM_ID
LEFT JOIN TMP_MIX T3
ON T3.YM_ID = T0.YM_ID
AND T3.VERSION_CODE = T0.VERSION_CODE
LEFT JOIN TMP_CITY_PRICE T4
ON T4.PRICE_DATE = T0.PRICE_DATE
AND T4.VERSION_CODE = T0.VERSION_CODE
AND T4.CITY_NAME = T0.CITY_NAME
LEFT JOIN TMP_SUBSIDIES T5
ON T5.VERSION_ID = T.VERSION_ID
AND T5.PRICE_DATE = T0.PRICE_DATE
AND T5.CITY_NAME = T0.CITY_NAME
ORDER BY T0.YM_ID, T.MANF_ENAME, T.BRAND_ENAME, T0.CITY_NAME
求大神解决~~~~~~感激不尽!!!!!