|
sql_id= 28dj9rpq8sz34
INSERT INTO "HF_BI". "EDW_DA_MEMBER_NEW"
WITH A AS ( SELECT TO_CHAR( JOIN_DATE,
'yyyy' ) AS JOIN_YEAR,
TO_CHAR( JOIN_DATE,
'yyyy-mm' ) AS JOIN_MONTH,
TRUNC( JOIN_DATE,
'MM' ) AS MONTH_FIRST,
TO_CHAR( LAST_DAY( JOIN_DATE ),
'dd' ) AS DAYS, JOIN_CHANNEL, CHANNEL_NAME, A.MER_ID,
B.SHOP_CODE AS SHOP_ID, B.SHOP_NAME, F.BIG_AREA_NAME, F.AREA_NAME,
F.AREA_ID, F.BIGAREA_ID, E.CITY, COUNT( DISTINCT USER_ID ) AS MEMBERS
FROM "ODS_DA_USER_INFO" A
LEFT JOIN ODS_DA_SHOP_INFO B
ON A.SHOP_ID = B.SHOP_ID
LEFT JOIN ODS_DA_CHANNEL C
ON A.JOIN_CHANNEL = C.ID
LEFT JOIN ODS_DA_LEVEL_INFO D
ON A.LEVEL_ID = D.LEVEL_ID
LEFT JOIN VIEW_DIM_SHOP_MC E
ON B.SHOP_CODE = E.SHOP_ID
LEFT JOIN DIM_AREA F
ON E.AREA_ID = F.AREA_ID
WHERE TO_CHAR( JOIN_DATE, 'yyyy-mm' ) = :B1
~~~~~~~~~~~~~~=>建立函数索引没有.
GROUP BY TO_CHAR( JOIN_DATE,
'yyyy' ),
TO_CHAR( JOIN_DATE,
'yyyy-mm' ),
TO_CHAR( LAST_DAY( JOIN_DATE ),
'dd' ),
TRUNC( JOIN_DATE,
'MM' ), JOIN_CHANNEL, CHANNEL_NAME, A.MER_ID, B.SHOP_CODE, B.SHOP_NAME,
F.BIG_AREA_NAME, F.AREA_ID, F.BIGAREA_ID, F.AREA_NAME, E.CITY ),
SS AS ( SELECT DISTINCT SUBSTR( DATE_CODE,
0, 7 ) AS MONTH_ID,
SUBSTR( DATE_CODE,
0, 4 ) AS YEAR_ID,
TO_DATE( SUBSTR( DATE_CODE,
0, 4 ) || '-01-01', 'yyyy-mm-dd' ) AS YEAR_DAY,
LAST_DAY( TO_DATE( SUBSTR( DATE_CODE,
0, 7 ) || '-01', 'yyyy-mm-dd' ) ) AS MONTH_DAY,
LAST_DAY( TO_DATE( SUBSTR( DATE_CODE,
0, 7 ) || '-01',
'yyyy-mm-dd' ) ) - TO_DATE( SUB STR( DATE_CODE,
0, 4 ) || '-01-01', 'yyyy-mm-dd' ) AS YEAR_DAYS
FROM "DIM_DATE"
WHERE DATE_CODE >= '2018-01-01'
AND DATE_CODE <= TO_CHAR( CURRENT_DATE, 'yyyy-mm-dd' )
ORDER BY MONTH_ID ),
B AS ( SELECT A.MER_ID,
TO_CHAR( JOIN_DATE,
'yyyy-mm' ) AS JOIN_MONTH,
TRUNC( JOIN_DATE,
'MM' ) AS MONTH_FIRST,
TO_CHAR( LAST_DAY( JOIN_DATE ),
'dd' ) AS DAYS, COUNT( DISTINCT USER_ID ) AS MEMBERS
FROM "ODS_DA_USER_INFO" A
LEFT JOIN ODS_DA_SHOP_INFO B
ON A.SHOP_ID = B.SHOP_ID
LEFT JOIN ODS_DA_CHANNEL C
ON A.JOIN_CHANNEL = C.ID
LEFT JOIN ODS_DA_LEVEL_INFO D
ON A.LEVEL_ID = D.LEVEL_ID
LEFT JOIN VIEW_DIM_SHOP_MC E
ON B.SHOP_CODE = E.SHOP_ID
LEFT JOIN DIM_AREA F
ON E.AREA_ID = F.AREA_ID
WHERE TO_CHAR( JOIN_DATE, 'yyyy-mm' ) = :B1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
GROUP BY A.MER_ID,
TO_CHAR( JOIN_DATE,
'yyyy-mm' ),
TO_CHAR( LAST_DAY( JOIN_DATE ),
'dd' ),
TRUNC( JOIN_DATE,
'MM' ) ),
TT AS ( SELECT A.MER_ID,
TO_CHAR( JOIN_DATE,
'yyyy-mm' ) AS JOIN_MONTH,
TRUNC( JOIN_DATE,
'MM' ) AS MONTH_FIRST, JOIN_CHANNEL, CHANNEL_NAME,
TO_CHAR( LAST_DAY( JOIN_DATE ),
'dd' ) AS DAYS, COUNT( DISTINCT USER_ID ) AS MEMBERS
FROM "ODS_DA_USER_INFO" A
LEFT JOIN ODS_DA_SHOP_INFO B
ON A.SHOP_ID = B.SHOP_ID
LEFT JOIN ODS_DA_CHANNEL C
ON A.JOIN_CHANNEL = C.ID
LEFT JOIN ODS_DA_LEVEL_INFO D
ON A.LEVEL_ID = D.LEVEL_ID
LEFT JOIN VIEW_DIM_SHOP_MC E
ON B.SHOP_CODE = E.SHOP_ID
LEFT JOIN DIM_AREA F
ON E.AREA_ID = F.AREA_ID
WHERE TO_CHAR( JOIN _DATE, 'yyyy-mm' ) = :B1
GROUP BY A.MER_ID,
TO_CHAR( JOIN_DATE,
'yyyy-mm' ),
TO_CHAR( LAST_DAY( JOIN_DATE ),
'dd' ), JOIN_CHANNEL, CHANNEL_NAME,
TRUNC( JOIN_DATE,
'MM' ) ),
C AS ( SELECT A.MONTH_ID,
A.YEAR_ID, A.YEAR_DAYS, B.MER_ID,
TO_CHAR( JOIN_DATE,
'yyyy' ) AS JOIN_YEAR, COUNT( DISTINCT USER_ID ) AS MEMBERS
FROM SS A
LEFT JOIN "ODS_DA_USER_INFO" B
ON A.YEAR_ID = TO_CHAR( B.JOIN_DATE, 'yyyy' )
AND TO_CHAR( JOIN_DATE, 'yyyy-mm' ) <= A.MONTH_ID
WHERE A.MONTH_ID = :B1
GROUP BY A.MONTH_ID, A.YEAR_ID, A.YEAR_DAYS, B.MER_ID,
TO_CHAR( JOIN_DATE,
'yyyy' ) ),
D AS ( SELECT A.MER_ID, COUNT( DISTINCT USER_ID ) AS MEMBERS
FROM "ODS_DA_USER_INFO" A
GROUP BY MER_ID )
SELECT AA.JOIN_YEAR, AA.JOIN_MONTH, AA.DAYS, AA.JOIN_CHANNEL,
AA.CHANNEL_NAME, AA.SHOP_ID, AA.SHOP_NAME, AA.CITY, AA.BIG_AREA_NAME,
AA.AREA_NAME, AA.MEMBERS AS M_MEMBERS, BB.MEMBERS AS LM_MEMBERS,
CC.MEMBERS AS LY_MEMEBERS, B.MEMBERS AS M_ALL_MEMBERS,
C.YEAR_DAYS AS YEAR_DAYS, C.MEMBERS AS Y_ALL_MEMBERS,
CD.MEMBERS AS LY_ALL_MEMBERS, D.MEMBERS AS ALL_MEMBERS,
TT.MEMBERS AS M_CHANNEL_ALL_MEMBERS, AA.AREA_ID, AA.BIGAREA_ID,
AA.MER_ID
FROM A AA
LEFT JOIN A BB
ON AA.MONTH_FIRST = ADD_MONTHS( BB.MONTH_FIRST, 1 )
AND AA.JOIN_CHANNEL = BB.JOIN_CHANNEL
AND AA.MER_ID = BB.MER_ID
AND AA.SHOP_ID = BB.SHOP_ID
LEFT JOIN A CC
ON AA.MONTH_FIRST = ADD_MONTHS( CC.MONTH_FIRST, 12 )
AND A A.JOIN_CHANNEL = CC.JOIN_CHANNEL
AND AA.MER_ID = CC.MER_ID
AND AA.SHOP_ID = CC.SHOP_ID
LEFT JOIN B
ON AA.JOIN_MONTH = B.JOIN_MONTH
AND AA.MER_ID = B.MER_ID
LEFT JOIN C
ON AA.JOIN_YEAR = C.JOIN_YEAR
AND AA.MER_ID = C.MER_ID
AND AA.JOIN_MONTH = C.MONTH_ID
LEFT JOIN C CD
ON AA.JOIN_MONTH = TO_CHAR(ADD_MONTHS(TO_DATE(CD.MONTH_ID||'-01',
'yyyy-mm-dd'), 12), 'yyyy-mm')
AND AA.MER_ID = CD.MER_ID
LEFT JOIN D
ON 1 = 1
AND AA.MER_ID = D.MER_ID
LEFT JOIN TT
ON AA.JOIN_MONTH = TT.JOIN_MONTH
AND AA.JOIN_CHANNEL = TT.JOIN_CHANNEL
AND AA.MER_ID = TT.MER_ID
ORDER BY AA.JOIN_YEAR, AA.JOIN_MONTH |
|