有一个比较庞大的SQL，可以执行，但是性能不太好。请高手指点一下如何优化这个SQL，可以提高效率。SQL里面使用了大量的 COALESCE 和 LOJs (Left Outer Join's)，希望高人可以帮帮我。十分感谢。
CREATE VIEW STAGING.MAP_CUSTOMER_SUB_BRAND_POS_FACTS_EXTRACT
(SAP_CUSTOMER_NO,
MAP_SUB_BRAND_KEY,
YEAR,
CURR_COV_TYPE_ID,
CURR_COV_TYPE,
CURR_COV_ID,
GEO_IOT_ID,
MAP_ACCOUNT_ID,
MAP_SUB_BRAND_CODE,
MAP_BRAND_KEY,
MAP_BRAND_CODE,
MAP_BRAND_GROUP_KEY,
MAP_BRAND_GROUP,
SUB_BRAND_CY_REV_USD,
SUB_BRAND_REV_USD,
SUB_BRAND_1YR_POS_REV_USD,
ACCT_SB_1YR_POS_REV_RATIO,
COV_SB_1YR_POS_REV_RATIO,
SUB_BRAND_2YR_POS_REV_USD,
ACCT_SB_2YR_POS_REV_RATIO,
COV_SB_2YR_POS_REV_RATIO,
SUB_BRAND_3YR_POS_REV_USD,
ACCT_SB_3YR_POS_REV_RATIO,
COV_SB_3YR_POS_REV_RATIO,
SUB_BRAND_4YR_POS_REV_USD,
ACCT_SB_4YR_POS_REV_RATIO,
COV_SB_4YR_POS_REV_RATIO,
SUB_BRAND_CY_TRANS_REV_USD,
SUB_BRAND_TRANS_REV_USD,
SUB_BRAND_1YR_TRANS_POS_REV,
ACCT_SB_1YR_TRANS_POS_RATIO,
COV_SB_1YR_TRANS_POS_RATIO,
SUB_BRAND_2YR_TRANS_POS_REV,
ACCT_SB_2YR_TRANS_POS_RATIO,
COV_SB_2YR_TRANS_POS_RATIO,
SUB_BRAND_3YR_TRANS_POS_REV,
ACCT_SB_3YR_TRANS_POS_RATIO,
COV_SB_3YR_TRANS_POS_RATIO,
SUB_BRAND_4YR_TRANS_POS_REV,
ACCT_SB_4YR_TRANS_POS_RATIO,
COV_SB_4YR_TRANS_POS_RATIO,
SUB_BRAND_CY_SIGNINGS_AMT_USD,
SUB_BRAND_SIGNINGS_AMT_USD,
SUB_BRAND_1YR_SIGNINGS_POS_AMT,
ACCT_SB_1YR_SIGN_POS_RATIO,
COV_SB_1YR_SIGN_POS_RATIO,
SUB_BRAND_2YR_SIGNINGS_POS_AMT,
ACCT_SB_2YR_SIGN_POS_RATIO,
COV_SB_2YR_SIGN_POS_RATIO,
SUB_BRAND_3YR_SIGNINGS_POS_AMT,
ACCT_SB_3YR_SIGN_POS_RATIO,
COV_SB_3YR_SIGN_POS_RATIO,
SUB_BRAND_4YR_SIGNINGS_POS_AMT,
ACCT_SB_4YR_SIGN_POS_RATIO,
COV_SB_4YR_SIGN_POS_RATIO,
ROW_UPDT_TIMESTAMP ) AS
SELECT HIST.SAP_CUSTOMER_NO,
HIST.MAP_SUB_BRAND_KEY,
map.proc_year_minus_1() AS YEAR,
MCIC.CURR_COV_TYPE_ID,
MCIC.CURR_COV_TYPE,
MCIC.CURR_COV_ID,
MCIC.GEO_IOT_ID,
MCIC.MAP_ACCOUNT_ID,
SB.MAP_SUB_BRAND_CODE,
SB.MAP_BRAND_KEY,
SB.MAP_BRAND_CODE,
SB.MAP_BRAND_GROUP_KEY,
SB.MAP_BRAND_GROUP,
COALESCE(CY.SUB_BRAND_CY_REV_USD, 0) AS SUB_BRAND_CY_REV_USD,
COALESCE(Y1.SUB_BRAND_REV_USD, 0) AS SUB_BRAND_REV_USD,
-- All the YR POS values include the current year-to-date value
-- and the Ratios are calculated using those values with the year-to-date addition
-- 1YR is now the last 1 full year plus the Current Year-To-Date POS total
(COALESCE(Y1.SUB_BRAND_1YR_POS_REV_USD, 0) + COALESCE(CY.SUB_BRAND_CY_POS_REV_USD, 0)) AS SUB_BRAND_1YR_POS_REV_USD,
0 AS ACCT_SB_1YR_POS_REV_RATIO,
0 AS COV_SB_1YR_POS_REV_RATIO,
-- 2YR is now the last 2 full years plus the Current Year-To-Date POS total
(COALESCE(Y2.SUB_BRAND_2YR_POS_REV_USD, 0) + COALESCE(CY.SUB_BRAND_CY_POS_REV_USD, 0)) AS SUB_BRAND_2YR_POS_REV_USD,
0 AS ACCT_SB_2YR_POS_REV_RATIO,
0 AS COV_SB_2YR_POS_REV_RATIO,
-- 3YR is now the last 3 full years plus the Current Year-To-Date POS total
(COALESCE(Y3.SUB_BRAND_3YR_POS_REV_USD, 0) + COALESCE(CY.SUB_BRAND_CY_POS_REV_USD, 0)) AS SUB_BRAND_3YR_POS_REV_USD,
0 AS ACCT_SB_3YR_POS_REV_RATIO,
0 AS COV_SB_3YR_POS_REV_RATIO,
-- 4YR is now the last 4 full years plus the Current Year-To-Date POS total
(COALESCE(Y4.SUB_BRAND_4YR_POS_REV_USD, 0) + COALESCE(CY.SUB_BRAND_CY_POS_REV_USD, 0)) AS SUB_BRAND_4YR_POS_REV_USD,
0 AS ACCT_SB_4YR_POS_REV_RATIO,
0 AS COV_SB_4YR_POS_REV_RATIO,
COALESCE(CY.SUB_BRAND_CY_TRANS_REV_USD, 0) AS SUB_BRAND_CY_TRANS_REV_USD,
COALESCE(Y1.SUB_BRAND_TRANS_REV_USD, 0) AS SUB_BRAND_TRANS_REV_USD,
-- xYR is now the last x full year(s) plus the Current Year-To-Date POS total
(COALESCE(Y1.SUB_BRAND_1YR_TRANS_POS_REV, 0) + COALESCE(CY.SUB_BRAND_CY_TRANS_POS_REV_USD, 0)) AS SUB_BRAND_1YR_TRANS_POS_REV,
0 AS ACCT_SB_1YR_TRANS_POS_RATIO,
0 AS COV_SB_1YR_TRANS_POS_RATIO,
(COALESCE(Y2.SUB_BRAND_2YR_TRANS_POS_REV, 0) + COALESCE(CY.SUB_BRAND_CY_TRANS_POS_REV_USD, 0)) AS SUB_BRAND_2YR_TRANS_POS_REV,
0 AS ACCT_SB_2YR_TRANS_POS_RATIO,
0 AS COV_SB_2YR_TRANS_POS_RATIO,
(COALESCE(Y3.SUB_BRAND_3YR_TRANS_POS_REV, 0) + COALESCE(CY.SUB_BRAND_CY_TRANS_POS_REV_USD, 0)) AS SUB_BRAND_3YR_TRANS_POS_REV,
0 AS ACCT_SB_3YR_TRANS_POS_RATIO,
0 AS COV_SB_3YR_TRANS_POS_RATIO,
(COALESCE(Y4.SUB_BRAND_4YR_TRANS_POS_REV, 0) + COALESCE(CY.SUB_BRAND_CY_TRANS_POS_REV_USD, 0)) AS SUB_BRAND_4YR_TRANS_POS_REV,
0 AS ACCT_SB_4YR_TRANS_POS_RATIO,
0 AS COV_SB_4YR_TRANS_POS_RATIO,
COALESCE(CY.SUB_BRAND_CY_SIGNINGS_AMT_USD, 0) AS SUB_BRAND_CY_SIGNINGS_AMT_USD,
COALESCE(Y1.SUB_BRAND_SIGNINGS_AMT_USD, 0) AS SUB_BRAND_SIGNINGS_AMT_USD,
-- xYR is now the last x full year(s) plus the Current Year-To-Date POS total
(COALESCE(Y1.SUB_BRAND_1YR_SIGNINGS_POS_AMT, 0) + COALESCE(CY.SUB_BRAND_CY_SIGNINGS_POS_AMT, 0)) AS SUB_BRAND_1YR_SIGNINGS_POS_AMT,
0 AS ACCT_SB_1YR_SIGN_POS_RATIO,
0 AS COV_SB_1YR_SIGN_POS_RATIO,
(COALESCE(Y2.SUB_BRAND_2YR_SIGNINGS_POS_AMT, 0) + COALESCE(CY.SUB_BRAND_CY_SIGNINGS_POS_AMT, 0)) AS SUB_BRAND_2YR_SIGNINGS_POS_AMT,
0 AS ACCT_SB_2YR_SIGN_POS_RATIO,
0 AS COV_SB_2YR_SIGN_POS_RATIO,
(COALESCE(Y3.SUB_BRAND_3YR_SIGNINGS_POS_AMT, 0) + COALESCE(CY.SUB_BRAND_CY_SIGNINGS_POS_AMT, 0)) AS SUB_BRAND_3YR_SIGNINGS_POS_AMT,
0 AS ACCT_SB_3YR_SIGN_POS_RATIO,
0 AS COV_SB_3YR_SIGN_POS_RATIO,
(COALESCE(Y4.SUB_BRAND_4YR_SIGNINGS_POS_AMT, 0) + COALESCE(CY.SUB_BRAND_CY_SIGNINGS_POS_AMT, 0)) AS SUB_BRAND_4YR_SIGNINGS_POS_AMT,
0 AS ACCT_SB_4YR_SIGN_POS_RATIO,
0 AS COV_SB_4YR_SIGN_POS_RATIO,
CURRENT TIMESTAMP AS ROW_UPDT_TIMESTAMP
FROM MAPDM.MAP_CUSTOMER_IBM_CLIENT_NEW MCIC,
MAPDM.MAP_SUB_BRAND SB,
( ( ( ( (SELECT DISTINCT SAP_CUSTOMER_NO,
MAP_SUB_BRAND_KEY
-- start with the full subset of Customers and their Brands across all HISTORY years as the base
FROM MAPDM.MAP_CUSTOMER_SUB_BRAND_FACTS_NEW) AS HIST
LEFT OUTER JOIN
-- Get the Current Year attributes
(SELECT SAP_CUSTOMER_NO,
MAP_SUB_BRAND_KEY,
SUB_BRAND_REV_USD AS SUB_BRAND_CY_REV_USD,
-- Current Year POS values are needed to add to the full year POS values
SUB_BRAND_POS_REV_USD AS SUB_BRAND_CY_POS_REV_USD,
SUB_BRAND_TRANS_REV_USD AS SUB_BRAND_CY_TRANS_REV_USD,
-- Current Year POS values are needed to add to the full year POS values
SUB_BRAND_TRANS_POS_REV_USD AS SUB_BRAND_CY_TRANS_POS_REV_USD,
SUB_BRAND_SIGNINGS_AMT_USD AS SUB_BRAND_CY_SIGNINGS_AMT_USD,
-- Current Year POS values are needed to add to the full year POS values
SUB_BRAND_SIGNINGS_POS_AMT_USD AS SUB_BRAND_CY_SIGNINGS_POS_AMT
FROM MAPDM.MAP_CUSTOMER_SUB_BRAND_FACTS_NEW
WHERE YEAR = map.proc_year() ) AS CY
ON (HIST.SAP_CUSTOMER_NO = CY.SAP_CUSTOMER_NO
AND HIST.MAP_SUB_BRAND_KEY = CY.MAP_SUB_BRAND_KEY) )
LEFT OUTER JOIN
-- Get the 1 Year attributes from the minus_1 year - this is the traditional Year for Facts
(SELECT SAP_CUSTOMER_NO,
MAP_SUB_BRAND_KEY,
SUB_BRAND_REV_USD,
SUB_BRAND_POS_REV_USD AS SUB_BRAND_1YR_POS_REV_USD,
SUB_BRAND_TRANS_REV_USD,
SUB_BRAND_TRANS_POS_REV_USD AS SUB_BRAND_1YR_TRANS_POS_REV,
SUB_BRAND_SIGNINGS_AMT_USD,
SUB_BRAND_SIGNINGS_POS_AMT_USD AS SUB_BRAND_1YR_SIGNINGS_POS_AMT
FROM MAPDM.MAP_CUSTOMER_SUB_BRAND_FACTS_NEW
WHERE YEAR = map.proc_year_minus_1() ) AS Y1
ON (HIST.SAP_CUSTOMER_NO = Y1.SAP_CUSTOMER_NO
AND HIST.MAP_SUB_BRAND_KEY = Y1.MAP_SUB_BRAND_KEY) )
LEFT OUTER JOIN
-- Get the 2 Year attributes from the minus_1 and minus_2 years
(SELECT SAP_CUSTOMER_NO,
MAP_SUB_BRAND_KEY,
SUM(SUB_BRAND_POS_REV_USD) AS SUB_BRAND_2YR_POS_REV_USD,
SUM(SUB_BRAND_TRANS_POS_REV_USD) AS SUB_BRAND_2YR_TRANS_POS_REV,
SUM(SUB_BRAND_SIGNINGS_POS_AMT_USD) AS SUB_BRAND_2YR_SIGNINGS_POS_AMT
FROM MAPDM.MAP_CUSTOMER_SUB_BRAND_FACTS_NEW
WHERE YEAR IN ( map.proc_year_minus_1(), map.proc_year_minus_2() )
GROUP BY SAP_CUSTOMER_NO,
MAP_SUB_BRAND_KEY) AS Y2
ON (HIST.SAP_CUSTOMER_NO = Y2.SAP_CUSTOMER_NO
AND HIST.MAP_SUB_BRAND_KEY = Y2.MAP_SUB_BRAND_KEY) )
LEFT OUTER JOIN
-- Get the 3 Year attributes from the minus_1, minus_2, and minus_3 years
(SELECT SAP_CUSTOMER_NO,
MAP_SUB_BRAND_KEY,
SUM(SUB_BRAND_POS_REV_USD) AS SUB_BRAND_3YR_POS_REV_USD,
SUM(SUB_BRAND_TRANS_POS_REV_USD) AS SUB_BRAND_3YR_TRANS_POS_REV,
SUM(SUB_BRAND_SIGNINGS_POS_AMT_USD) AS SUB_BRAND_3YR_SIGNINGS_POS_AMT
FROM MAPDM.MAP_CUSTOMER_SUB_BRAND_FACTS_NEW
WHERE YEAR IN ( map.proc_year_minus_1(), map.proc_year_minus_2(), map.proc_year_minus_3() )
GROUP BY SAP_CUSTOMER_NO,
MAP_SUB_BRAND_KEY) AS Y3
ON (HIST.SAP_CUSTOMER_NO = Y3.SAP_CUSTOMER_NO
AND HIST.MAP_SUB_BRAND_KEY = Y3.MAP_SUB_BRAND_KEY) )
LEFT OUTER JOIN
-- Get the 4 Year attributes from the minus_1, minus_2, minus_3, and minus_4 years
(SELECT SAP_CUSTOMER_NO,
MAP_SUB_BRAND_KEY,
SUM(SUB_BRAND_POS_REV_USD) AS SUB_BRAND_4YR_POS_REV_USD,
SUM(SUB_BRAND_TRANS_POS_REV_USD) AS SUB_BRAND_4YR_TRANS_POS_REV,
SUM(SUB_BRAND_SIGNINGS_POS_AMT_USD) AS SUB_BRAND_4YR_SIGNINGS_POS_AMT
FROM MAPDM.MAP_CUSTOMER_SUB_BRAND_FACTS_NEW
WHERE YEAR IN ( map.proc_year_minus_1(), map.proc_year_minus_2(), map.proc_year_minus_3(), map.proc_year_minus_4() )
GROUP BY SAP_CUSTOMER_NO,
MAP_SUB_BRAND_KEY) AS Y4
ON (HIST.SAP_CUSTOMER_NO = Y4.SAP_CUSTOMER_NO
AND HIST.MAP_SUB_BRAND_KEY = Y4.MAP_SUB_BRAND_KEY)
-- end of the LEFT OUTER JOINs for the data
WHERE HIST.SAP_CUSTOMER_NO = MCIC.SAP_CUSTOMER_NO
AND HIST.MAP_SUB_BRAND_KEY = SB.MAP_SUB_BRAND_KEY
AND SB.MAP_BRAND_KEY NOT IN (0, 12)
AND SB.DISPLAY_INDC = 'Y'
CREATE VIEW STAGING.MAP_CUSTOMER_SUB_BRAND_POS_FACTS_EXTRACT
(SAP_CUSTOMER_NO,
MAP_SUB_BRAND_KEY,
YEAR,
CURR_COV_TYPE_ID,
CURR_COV_TYPE,
CURR_COV_ID,
GEO_IOT_ID,
MAP_ACCOUNT_ID,
MAP_SUB_BRAND_CODE,
MAP_BRAND_KEY,
MAP_BRAND_CODE,
MAP_BRAND_GROUP_KEY,
MAP_BRAND_GROUP,
SUB_BRAND_CY_REV_USD,
SUB_BRAND_REV_USD,
SUB_BRAND_1YR_POS_REV_USD,
ACCT_SB_1YR_POS_REV_RATIO,
COV_SB_1YR_POS_REV_RATIO,
SUB_BRAND_2YR_POS_REV_USD,
ACCT_SB_2YR_POS_REV_RATIO,
COV_SB_2YR_POS_REV_RATIO,
SUB_BRAND_3YR_POS_REV_USD,
ACCT_SB_3YR_POS_REV_RATIO,
COV_SB_3YR_POS_REV_RATIO,
SUB_BRAND_4YR_POS_REV_USD,
ACCT_SB_4YR_POS_REV_RATIO,
COV_SB_4YR_POS_REV_RATIO,
SUB_BRAND_CY_TRANS_REV_USD,
SUB_BRAND_TRANS_REV_USD,
SUB_BRAND_1YR_TRANS_POS_REV,
ACCT_SB_1YR_TRANS_POS_RATIO,
COV_SB_1YR_TRANS_POS_RATIO,
SUB_BRAND_2YR_TRANS_POS_REV,
ACCT_SB_2YR_TRANS_POS_RATIO,
COV_SB_2YR_TRANS_POS_RATIO,
SUB_BRAND_3YR_TRANS_POS_REV,
ACCT_SB_3YR_TRANS_POS_RATIO,
COV_SB_3YR_TRANS_POS_RATIO,
SUB_BRAND_4YR_TRANS_POS_REV,
ACCT_SB_4YR_TRANS_POS_RATIO,
COV_SB_4YR_TRANS_POS_RATIO,
SUB_BRAND_CY_SIGNINGS_AMT_USD,
SUB_BRAND_SIGNINGS_AMT_USD,
SUB_BRAND_1YR_SIGNINGS_POS_AMT,
ACCT_SB_1YR_SIGN_POS_RATIO,
COV_SB_1YR_SIGN_POS_RATIO,
SUB_BRAND_2YR_SIGNINGS_POS_AMT,
ACCT_SB_2YR_SIGN_POS_RATIO,
COV_SB_2YR_SIGN_POS_RATIO,
SUB_BRAND_3YR_SIGNINGS_POS_AMT,
ACCT_SB_3YR_SIGN_POS_RATIO,
COV_SB_3YR_SIGN_POS_RATIO,
SUB_BRAND_4YR_SIGNINGS_POS_AMT,
ACCT_SB_4YR_SIGN_POS_RATIO,
COV_SB_4YR_SIGN_POS_RATIO,
ROW_UPDT_TIMESTAMP ) AS
SELECT HIST.SAP_CUSTOMER_NO,
HIST.MAP_SUB_BRAND_KEY,
map.proc_year_minus_1() AS YEAR,
MCIC.CURR_COV_TYPE_ID,
MCIC.CURR_COV_TYPE,
MCIC.CURR_COV_ID,
MCIC.GEO_IOT_ID,
MCIC.MAP_ACCOUNT_ID,
SB.MAP_SUB_BRAND_CODE,
SB.MAP_BRAND_KEY,
SB.MAP_BRAND_CODE,
SB.MAP_BRAND_GROUP_KEY,
SB.MAP_BRAND_GROUP,
COALESCE(CY.SUB_BRAND_CY_REV_USD, 0) AS SUB_BRAND_CY_REV_USD,
COALESCE(Y1.SUB_BRAND_REV_USD, 0) AS SUB_BRAND_REV_USD,
-- All the YR POS values include the current year-to-date value
-- and the Ratios are calculated using those values with the year-to-date addition
-- 1YR is now the last 1 full year plus the Current Year-To-Date POS total
(COALESCE(Y1.SUB_BRAND_1YR_POS_REV_USD, 0) + COALESCE(CY.SUB_BRAND_CY_POS_REV_USD, 0)) AS SUB_BRAND_1YR_POS_REV_USD,
0 AS ACCT_SB_1YR_POS_REV_RATIO,
0 AS COV_SB_1YR_POS_REV_RATIO,
-- 2YR is now the last 2 full years plus the Current Year-To-Date POS total
(COALESCE(Y2.SUB_BRAND_2YR_POS_REV_USD, 0) + COALESCE(CY.SUB_BRAND_CY_POS_REV_USD, 0)) AS SUB_BRAND_2YR_POS_REV_USD,
0 AS ACCT_SB_2YR_POS_REV_RATIO,
0 AS COV_SB_2YR_POS_REV_RATIO,
-- 3YR is now the last 3 full years plus the Current Year-To-Date POS total
(COALESCE(Y3.SUB_BRAND_3YR_POS_REV_USD, 0) + COALESCE(CY.SUB_BRAND_CY_POS_REV_USD, 0)) AS SUB_BRAND_3YR_POS_REV_USD,
0 AS ACCT_SB_3YR_POS_REV_RATIO,
0 AS COV_SB_3YR_POS_REV_RATIO,
-- 4YR is now the last 4 full years plus the Current Year-To-Date POS total
(COALESCE(Y4.SUB_BRAND_4YR_POS_REV_USD, 0) + COALESCE(CY.SUB_BRAND_CY_POS_REV_USD, 0)) AS SUB_BRAND_4YR_POS_REV_USD,
0 AS ACCT_SB_4YR_POS_REV_RATIO,
0 AS COV_SB_4YR_POS_REV_RATIO,
COALESCE(CY.SUB_BRAND_CY_TRANS_REV_USD, 0) AS SUB_BRAND_CY_TRANS_REV_USD,
COALESCE(Y1.SUB_BRAND_TRANS_REV_USD, 0) AS SUB_BRAND_TRANS_REV_USD,
-- xYR is now the last x full year(s) plus the Current Year-To-Date POS total
(COALESCE(Y1.SUB_BRAND_1YR_TRANS_POS_REV, 0) + COALESCE(CY.SUB_BRAND_CY_TRANS_POS_REV_USD, 0)) AS SUB_BRAND_1YR_TRANS_POS_REV,
0 AS ACCT_SB_1YR_TRANS_POS_RATIO,
0 AS COV_SB_1YR_TRANS_POS_RATIO,
(COALESCE(Y2.SUB_BRAND_2YR_TRANS_POS_REV, 0) + COALESCE(CY.SUB_BRAND_CY_TRANS_POS_REV_USD, 0)) AS SUB_BRAND_2YR_TRANS_POS_REV,
0 AS ACCT_SB_2YR_TRANS_POS_RATIO,
0 AS COV_SB_2YR_TRANS_POS_RATIO,
(COALESCE(Y3.SUB_BRAND_3YR_TRANS_POS_REV, 0) + COALESCE(CY.SUB_BRAND_CY_TRANS_POS_REV_USD, 0)) AS SUB_BRAND_3YR_TRANS_POS_REV,
0 AS ACCT_SB_3YR_TRANS_POS_RATIO,
0 AS COV_SB_3YR_TRANS_POS_RATIO,
(COALESCE(Y4.SUB_BRAND_4YR_TRANS_POS_REV, 0) + COALESCE(CY.SUB_BRAND_CY_TRANS_POS_REV_USD, 0)) AS SUB_BRAND_4YR_TRANS_POS_REV,
0 AS ACCT_SB_4YR_TRANS_POS_RATIO,
0 AS COV_SB_4YR_TRANS_POS_RATIO,
COALESCE(CY.SUB_BRAND_CY_SIGNINGS_AMT_USD, 0) AS SUB_BRAND_CY_SIGNINGS_AMT_USD,
COALESCE(Y1.SUB_BRAND_SIGNINGS_AMT_USD, 0) AS SUB_BRAND_SIGNINGS_AMT_USD,
-- xYR is now the last x full year(s) plus the Current Year-To-Date POS total
(COALESCE(Y1.SUB_BRAND_1YR_SIGNINGS_POS_AMT, 0) + COALESCE(CY.SUB_BRAND_CY_SIGNINGS_POS_AMT, 0)) AS SUB_BRAND_1YR_SIGNINGS_POS_AMT,
0 AS ACCT_SB_1YR_SIGN_POS_RATIO,
0 AS COV_SB_1YR_SIGN_POS_RATIO,
(COALESCE(Y2.SUB_BRAND_2YR_SIGNINGS_POS_AMT, 0) + COALESCE(CY.SUB_BRAND_CY_SIGNINGS_POS_AMT, 0)) AS SUB_BRAND_2YR_SIGNINGS_POS_AMT,
0 AS ACCT_SB_2YR_SIGN_POS_RATIO,
0 AS COV_SB_2YR_SIGN_POS_RATIO,
(COALESCE(Y3.SUB_BRAND_3YR_SIGNINGS_POS_AMT, 0) + COALESCE(CY.SUB_BRAND_CY_SIGNINGS_POS_AMT, 0)) AS SUB_BRAND_3YR_SIGNINGS_POS_AMT,
0 AS ACCT_SB_3YR_SIGN_POS_RATIO,
0 AS COV_SB_3YR_SIGN_POS_RATIO,
(COALESCE(Y4.SUB_BRAND_4YR_SIGNINGS_POS_AMT, 0) + COALESCE(CY.SUB_BRAND_CY_SIGNINGS_POS_AMT, 0)) AS SUB_BRAND_4YR_SIGNINGS_POS_AMT,
0 AS ACCT_SB_4YR_SIGN_POS_RATIO,
0 AS COV_SB_4YR_SIGN_POS_RATIO,
CURRENT TIMESTAMP AS ROW_UPDT_TIMESTAMP
FROM MAPDM.MAP_CUSTOMER_IBM_CLIENT_NEW MCIC,
MAPDM.MAP_SUB_BRAND SB,
( ( ( ( (SELECT DISTINCT SAP_CUSTOMER_NO,
MAP_SUB_BRAND_KEY
-- start with the full subset of Customers and their Brands across all HISTORY years as the base
FROM MAPDM.MAP_CUSTOMER_SUB_BRAND_FACTS_NEW) AS HIST
LEFT OUTER JOIN
-- Get the Current Year attributes
(SELECT SAP_CUSTOMER_NO,
MAP_SUB_BRAND_KEY,
SUB_BRAND_REV_USD AS SUB_BRAND_CY_REV_USD,
-- Current Year POS values are needed to add to the full year POS values
SUB_BRAND_POS_REV_USD AS SUB_BRAND_CY_POS_REV_USD,
SUB_BRAND_TRANS_REV_USD AS SUB_BRAND_CY_TRANS_REV_USD,
-- Current Year POS values are needed to add to the full year POS values
SUB_BRAND_TRANS_POS_REV_USD AS SUB_BRAND_CY_TRANS_POS_REV_USD,
SUB_BRAND_SIGNINGS_AMT_USD AS SUB_BRAND_CY_SIGNINGS_AMT_USD,
-- Current Year POS values are needed to add to the full year POS values
SUB_BRAND_SIGNINGS_POS_AMT_USD AS SUB_BRAND_CY_SIGNINGS_POS_AMT
FROM MAPDM.MAP_CUSTOMER_SUB_BRAND_FACTS_NEW
WHERE YEAR = map.proc_year() ) AS CY
ON (HIST.SAP_CUSTOMER_NO = CY.SAP_CUSTOMER_NO
AND HIST.MAP_SUB_BRAND_KEY = CY.MAP_SUB_BRAND_KEY) )
LEFT OUTER JOIN
-- Get the 1 Year attributes from the minus_1 year - this is the traditional Year for Facts
(SELECT SAP_CUSTOMER_NO,
MAP_SUB_BRAND_KEY,
SUB_BRAND_REV_USD,
SUB_BRAND_POS_REV_USD AS SUB_BRAND_1YR_POS_REV_USD,
SUB_BRAND_TRANS_REV_USD,
SUB_BRAND_TRANS_POS_REV_USD AS SUB_BRAND_1YR_TRANS_POS_REV,
SUB_BRAND_SIGNINGS_AMT_USD,
SUB_BRAND_SIGNINGS_POS_AMT_USD AS SUB_BRAND_1YR_SIGNINGS_POS_AMT
FROM MAPDM.MAP_CUSTOMER_SUB_BRAND_FACTS_NEW
WHERE YEAR = map.proc_year_minus_1() ) AS Y1
ON (HIST.SAP_CUSTOMER_NO = Y1.SAP_CUSTOMER_NO
AND HIST.MAP_SUB_BRAND_KEY = Y1.MAP_SUB_BRAND_KEY) )
LEFT OUTER JOIN
-- Get the 2 Year attributes from the minus_1 and minus_2 years
(SELECT SAP_CUSTOMER_NO,
MAP_SUB_BRAND_KEY,
SUM(SUB_BRAND_POS_REV_USD) AS SUB_BRAND_2YR_POS_REV_USD,
SUM(SUB_BRAND_TRANS_POS_REV_USD) AS SUB_BRAND_2YR_TRANS_POS_REV,
SUM(SUB_BRAND_SIGNINGS_POS_AMT_USD) AS SUB_BRAND_2YR_SIGNINGS_POS_AMT
FROM MAPDM.MAP_CUSTOMER_SUB_BRAND_FACTS_NEW
WHERE YEAR IN ( map.proc_year_minus_1(), map.proc_year_minus_2() )
GROUP BY SAP_CUSTOMER_NO,
MAP_SUB_BRAND_KEY) AS Y2
ON (HIST.SAP_CUSTOMER_NO = Y2.SAP_CUSTOMER_NO
AND HIST.MAP_SUB_BRAND_KEY = Y2.MAP_SUB_BRAND_KEY) )
LEFT OUTER JOIN
-- Get the 3 Year attributes from the minus_1, minus_2, and minus_3 years
(SELECT SAP_CUSTOMER_NO,
MAP_SUB_BRAND_KEY,
SUM(SUB_BRAND_POS_REV_USD) AS SUB_BRAND_3YR_POS_REV_USD,
SUM(SUB_BRAND_TRANS_POS_REV_USD) AS SUB_BRAND_3YR_TRANS_POS_REV,
SUM(SUB_BRAND_SIGNINGS_POS_AMT_USD) AS SUB_BRAND_3YR_SIGNINGS_POS_AMT
FROM MAPDM.MAP_CUSTOMER_SUB_BRAND_FACTS_NEW
WHERE YEAR IN ( map.proc_year_minus_1(), map.proc_year_minus_2(), map.proc_year_minus_3() )
GROUP BY SAP_CUSTOMER_NO,
MAP_SUB_BRAND_KEY) AS Y3
ON (HIST.SAP_CUSTOMER_NO = Y3.SAP_CUSTOMER_NO
AND HIST.MAP_SUB_BRAND_KEY = Y3.MAP_SUB_BRAND_KEY) )
LEFT OUTER JOIN
-- Get the 4 Year attributes from the minus_1, minus_2, minus_3, and minus_4 years
(SELECT SAP_CUSTOMER_NO,
MAP_SUB_BRAND_KEY,
SUM(SUB_BRAND_POS_REV_USD) AS SUB_BRAND_4YR_POS_REV_USD,
SUM(SUB_BRAND_TRANS_POS_REV_USD) AS SUB_BRAND_4YR_TRANS_POS_REV,
SUM(SUB_BRAND_SIGNINGS_POS_AMT_USD) AS SUB_BRAND_4YR_SIGNINGS_POS_AMT
FROM MAPDM.MAP_CUSTOMER_SUB_BRAND_FACTS_NEW
WHERE YEAR IN ( map.proc_year_minus_1(), map.proc_year_minus_2(), map.proc_year_minus_3(), map.proc_year_minus_4() )
GROUP BY SAP_CUSTOMER_NO,
MAP_SUB_BRAND_KEY) AS Y4
ON (HIST.SAP_CUSTOMER_NO = Y4.SAP_CUSTOMER_NO
AND HIST.MAP_SUB_BRAND_KEY = Y4.MAP_SUB_BRAND_KEY)
-- end of the LEFT OUTER JOINs for the data
WHERE HIST.SAP_CUSTOMER_NO = MCIC.SAP_CUSTOMER_NO
AND HIST.MAP_SUB_BRAND_KEY = SB.MAP_SUB_BRAND_KEY
AND SB.MAP_BRAND_KEY NOT IN (0, 12)
AND SB.DISPLAY_INDC = 'Y'
基本思路是在left join TB on 连接的列 上建立索引具体问题还得具体分析，LZ可以测试一下，建立上索引，看看速度是否有提高....另外察看执行计划 Ctrl+L 看看对应此视图处理过程中都是如何完成的会对优化有所帮助。参考索引常规设计指南看看
1.不宜创建索引的情形
(1)经常插入，修改和删除的表
(2)数据量比较小的表，因为查询优化器在搜索索引时所花费的时间可能会大于遍历全表的数据所需要的时间2.适合创建索引的情形
(1)为where子句中出现的列创建索引
(2)创建组合索引
(3)为group by 子句中出现的列创建索引3.聚集索引的设计原则
(1)该列的数值是唯一的或者很少有重复的记录
(2)经常使用between ...and..按顺序查询的列
(3)定义identity的唯一列.
(4)经常用于对数据进行排序的列.---无法使用索引的select语句
1.对索引列使用了函数,如：
select * from tb where max(id)=1002.对索引列使用了'%xx'，如：
select * from tb where id like '%1'
需要注意的不是所有使用like关键字的select 语句都无法使用索引,比如
select * from tb where id like '1%'就可以使用索引3.在where子句中对列进行类型转换(其实也是使用到了函数)4.在组合索引的第1列不是使用最多的列,如在下面3个查询语句中建立组合索引,按顺序包含col2,col1,id列；
select * from tb where id='1' and col1='aa'
select id,sum(col1) from tb group by id
select * from tb where id='2' and col2='bb'
则第一句和第二句无法使用到索引 所以需要注意组合索引的顺序5.在where 子句中使用in关键字的某些句子
当在in关键字后面使用嵌套的select语句，将无法使用在该列上定义的索引
如：
select
*
from
ta
where
id
in
(select id from tb where ....)--这样可以用到索引
select * from tb where id in('1','2')