要求:判断当前阶段货值的类型，如果当前货值类型(hztype)不是立项，则取立项之后的货值WITH s004 AS (
--取货值每月最新一版日期
SELECT
TO_CHAR (ETLDATE, 'yyyy-mm') yearmonth,
TO_CHAR (MAX(ETLDATE), 'yyyy-mm-dd') ETLDATE
--ROW_NUMBER () OVER (ORDER BY TO_CHAR(MAX(ETLDATE), 'yyyy-mm-dd') DESC) seq
FROM
EDW1_T_FACT_OP_HZ_PHA_HI
GROUP BY
TO_CHAR (ETLDATE, 'yyyy-mm')
),
s005 AS (
--关联最新日期取最近12月的货值
SELECT
TO_CHAR (T1.ETLDATE, 'YYYY-MM') YEARMONTH,
T1.BUSINESSTYPECODE PRODUCTID,
SUM (SELLAREA) ZMJ,
SUM (HZ) hz
FROM
EDW1_T_FACT_OP_HZ_PHA_HI t1
INNER JOIN s004 ON s004.ETLDATE = TO_CHAR (t1.ETLDATE, 'yyyy-mm-dd')
WHERE
t1.PHASEID = '${phaseid}'
${if(len(HZTYPE) == 0,"","AND t1.HZTYPE IN ('" + HZTYPE + "')")}
--AND S004.SEQ 0
GROUP BY
TO_CHAR (T1.ETLDATE, 'YYYY-MM'),
T1.BUSINESSTYPECODE
),
MIN_YEARMONTH as(
-- 取货值表的最小时间记录
SELECT
TO_CHAR(ETLDATE,'yyyy-mm') YEARMONTH,
TO_CHAR(MIN(ETLDATE),'yyyy-mm-dd')
FROM
EDW1_T_FACT_OP_HZ_PHA_HI
group by TO_CHAR(ETLDATE,'yyyy-mm')
),
S006 AS (
--去CRM已售货值
SELECT
SUBSTR (WORKDATE, 1, 7) YEARMONTH,
PRODUCTID,
SUM (AREA) YSMJ,
SUM (RMBHTTOTAL) RMBHTTOTAL
FROM
DM_MKT_FACT_YS
WHERE
PHASEID = '${phaseid}'
AND ISBI = 1
${if(len(HZTYPE) == 0,"","AND 'YS' IN ('" + HZTYPE + "')")}
GROUP BY
SUBSTR (WORKDATE, 1, 7),
PRODUCTID
),
S007 AS (
--取维度表，最近12个月的日期
SELECT
DATE_DIM YEARMONTH
FROM
DIM_DATE
WHERE
DATE_DIM = (SELECT MIN(YEARMONTH) FROM MIN_YEARMONTH)
),
S008 AS (
--汇总累计已售货值
SELECT
S007.YEARMONTH,
PRODUCTID,
SUM (YSMJ) YSMJ,
SUM (S006.RMBHTTOTAL) YSHZ
FROM
S007
LEFT JOIN S006 ON S007.YEARMONTH >= S006.YEARMONTH
GROUP BY
S007.YEARMONTH,
PRODUCTID
),
--去不同的产品类型
S009 AS (SELECT DISTINCT PRODUCTID FROM S005),
S010 AS (
--拼出全维度的产品货值
SELECT
2 ITEMCODE,
'经营货值' ITEMNAME,
S007.YEARMONTH,
S009.PRODUCTID,
HZ HZ,
ZMJ
FROM
S009
LEFT JOIN S007 ON 1 = 1
LEFT JOIN S005 ON S009.PRODUCTID = S005.PRODUCTID
AND S007.YEARMONTH = S005.YEARMONTH
UNION ALL
SELECT
3 ITEMCODE,
'已售货值' ITEMNAME,
YEARMONTH,
PRODUCTID,
YSHZ YSHZ,
YSMJ
FROM
s008
) SELECT
YEARMONTH,
PRODUCTID,
SUM (hz) hz,
SUM (zmj) zmj,
CASE
WHEN NVL (sum(ZMJ), 0) = 0 THEN
NULL
ELSE
sum(HZ) / sum(ZMJ)
END XSJJ
FROM
S010
where length(PRODUCTID)>0
GROUP BY
YEARMONTH,
PRODUCTID
ORDER BY
YEARMONTH,
PRODUCTID
