0

I have to create a report to summarize some metric results over the last 12 months by account, each time a metric is processed is added to the account attainment with the last 12 months results. The issue here is that sometimes metrics are not processed very often. Sometimes the last result of a metric can be several months ago.

I have created the following code for oracle to:

  1. get the last results for each metric
  2. move to the left the columns according to the last_analyzed date so I can always have the current month results in month_12_A column and so.

But the problem is that the query seems to be very large and not so efficient.

I'm starting with SQL so I would appreciate any kind of help on how could I improve this query

note: for the previous months I fill the most recent data with 1 as that is the code for N/A

SELECT 
D.*
FROM 
(
SELECT
AA.CUSTOMER_ID,
AA.METRIC_ID,
AA.ANALYZED_DATE,
AA.MONTH_01_A,
AA.MONTH_02_A,
AA.MONTH_03_A,
AA.MONTH_04_A,
AA.MONTH_05_A,
AA.MONTH_06_A,
AA.MONTH_07_A,
AA.MONTH_08_A,
AA.MONTH_09_A,
AA.MONTH_10_A,
AA.MONTH_11_A,
AA.MONTH_12_A,
ROW_NUMBER () OVER (PARTITION BY METRIC_ID ORDER BY ANALYZED_DATE DESC) AS SEQ
FROM ACCOUNT_ATTAINMENT AA
) D
WHERE SEQ =1
AND TO_NUMBER(TO_CHAR(ADD_MONTHS(ANALYZED_DATE,0),'YYYYMM')) = TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,0),'YYYYMM'))
UNION
SELECT 
D.*
FROM 
(
SELECT
AA.CUSTOMER_ID,
AA.METRIC_ID,
AA.ANALYZED_DATE,
AA.MONTH_02_A AS MONTH_01_A,
AA.MONTH_03_A AS MONTH_02_A,
AA.MONTH_04_A AS MONTH_03_A,
AA.MONTH_05_A AS MONTH_04_A,
AA.MONTH_06_A AS MONTH_05_A,
AA.MONTH_07_A AS MONTH_06_A,
AA.MONTH_08_A AS MONTH_07_A,
AA.MONTH_09_A AS MONTH_08_A,
AA.MONTH_10_A AS MONTH_09_A,
AA.MONTH_11_A AS MONTH_10_A,
AA.MONTH_12_A AS MONTH_11_A,
1 AS MONTH_12_A,
ROW_NUMBER () OVER (PARTITION BY METRIC_ID ORDER BY ANALYZED_DATE DESC) AS SEQ
FROM ACCOUNT_ATTAINMENT AA
) D
WHERE SEQ =1
AND TO_NUMBER(TO_CHAR(ADD_MONTHS(ANALYZED_DATE,0),'YYYYMM')) = TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM'))
UNION
SELECT 
D.*
FROM 
(
SELECT
AA.CUSTOMER_ID,
AA.METRIC_ID,
AA.ANALYZED_DATE,
AA.MONTH_03_A   AS  MONTH_01_A,
AA.MONTH_04_A   AS  MONTH_02_A,
AA.MONTH_05_A   AS  MONTH_03_A,
AA.MONTH_06_A   AS  MONTH_04_A,
AA.MONTH_07_A   AS  MONTH_05_A,
AA.MONTH_08_A   AS  MONTH_06_A,
AA.MONTH_09_A   AS  MONTH_07_A,
AA.MONTH_10_A   AS  MONTH_08_A,
AA.MONTH_11_A   AS  MONTH_09_A,
AA.MONTH_12_A   AS  MONTH_10_A,
1   AS  MONTH_11_A,
1   AS  MONTH_12_A,
ROW_NUMBER () OVER (PARTITION BY METRIC_ID ORDER BY ANALYZED_DATE DESC) AS SEQ
FROM ACCOUNT_ATTAINMENT AA
) D
WHERE SEQ =1
AND TO_NUMBER(TO_CHAR(ADD_MONTHS(ANALYZED_DATE,0),'YYYYMM')) = TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-2),'YYYYMM'))
UNION
SELECT 
D.*
FROM 
(
SELECT
AA.CUSTOMER_ID,
AA.METRIC_ID,
AA.ANALYZED_DATE,
AA.MONTH_04_A   AS  MONTH_01_A,
AA.MONTH_05_A   AS  MONTH_02_A,
AA.MONTH_06_A   AS  MONTH_03_A,
AA.MONTH_07_A   AS  MONTH_04_A,
AA.MONTH_08_A   AS  MONTH_05_A,
AA.MONTH_09_A   AS  MONTH_06_A,
AA.MONTH_10_A   AS  MONTH_07_A,
AA.MONTH_11_A   AS  MONTH_08_A,
AA.MONTH_12_A   AS  MONTH_09_A,
1   AS  MONTH_10_A,
1   AS  MONTH_11_A,
1   AS  MONTH_12_A,
ROW_NUMBER () OVER (PARTITION BY METRIC_ID ORDER BY ANALYZED_DATE DESC) AS SEQ
FROM ACCOUNT_ATTAINMENT AA
) D
WHERE SEQ =1
AND TO_NUMBER(TO_CHAR(ADD_MONTHS(ANALYZED_DATE,0),'YYYYMM')) = TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-3),'YYYYMM'))
UNION
SELECT 
D.*
FROM 
(
SELECT
AA.CUSTOMER_ID,
AA.METRIC_ID,
AA.ANALYZED_DATE,
AA.MONTH_05_A   AS  MONTH_01_A  ,
AA.MONTH_06_A   AS  MONTH_02_A  ,
AA.MONTH_07_A   AS  MONTH_03_A  ,
AA.MONTH_08_A   AS  MONTH_04_A  ,
AA.MONTH_09_A   AS  MONTH_05_A  ,
AA.MONTH_10_A   AS  MONTH_06_A  ,
AA.MONTH_11_A   AS  MONTH_07_A  ,
AA.MONTH_12_A   AS  MONTH_08_A  ,
1   AS  MONTH_09_A  ,
1   AS  MONTH_10_A  ,
1   AS  MONTH_11_A  ,
1   AS  MONTH_12_A  ,
ROW_NUMBER () OVER (PARTITION BY METRIC_ID ORDER BY ANALYZED_DATE DESC) AS SEQ
FROM ACCOUNT_ATTAINMENT AA
) D
WHERE SEQ =1
AND TO_NUMBER(TO_CHAR(ADD_MONTHS(ANALYZED_DATE,0),'YYYYMM')) = TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-4),'YYYYMM'))
UNION
SELECT 
D.*
FROM 
(
SELECT
AA.CUSTOMER_ID,
AA.METRIC_ID,
AA.ANALYZED_DATE,
AA.MONTH_06_A   AS  MONTH_01_A  ,
AA.MONTH_07_A   AS  MONTH_02_A  ,
AA.MONTH_08_A   AS  MONTH_03_A  ,
AA.MONTH_09_A   AS  MONTH_04_A  ,
AA.MONTH_10_A   AS  MONTH_05_A  ,
AA.MONTH_11_A   AS  MONTH_06_A  ,
AA.MONTH_12_A   AS  MONTH_07_A  ,
1   AS  MONTH_08_A  ,
1   AS  MONTH_09_A  ,
1   AS  MONTH_10_A  ,
1   AS  MONTH_11_A  ,
1   AS  MONTH_12_A  ,
ROW_NUMBER () OVER (PARTITION BY METRIC_ID ORDER BY ANALYZED_DATE DESC) AS SEQ
FROM ACCOUNT_ATTAINMENT AA
) D
WHERE SEQ =1
AND TO_NUMBER(TO_CHAR(ADD_MONTHS(ANALYZED_DATE,0),'YYYYMM')) = TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-5),'YYYYMM'))
UNION
SELECT 
D.*
FROM 
(
SELECT
AA.CUSTOMER_ID,
AA.METRIC_ID,
AA.ANALYZED_DATE,
AA.MONTH_07_A   AS  MONTH_01_A  ,
AA.MONTH_08_A   AS  MONTH_02_A  ,
AA.MONTH_09_A   AS  MONTH_03_A  ,
AA.MONTH_10_A   AS  MONTH_04_A  ,
AA.MONTH_11_A   AS  MONTH_05_A  ,
AA.MONTH_12_A   AS  MONTH_06_A  ,
1   AS  MONTH_07_A  ,
1   AS  MONTH_08_A  ,
1   AS  MONTH_09_A  ,
1   AS  MONTH_10_A  ,
1   AS  MONTH_11_A  ,
1   AS  MONTH_12_A  ,
ROW_NUMBER () OVER (PARTITION BY METRIC_ID ORDER BY ANALYZED_DATE DESC) AS SEQ
FROM ACCOUNT_ATTAINMENT AA
) D
WHERE SEQ =1
AND TO_NUMBER(TO_CHAR(ADD_MONTHS(ANALYZED_DATE,0),'YYYYMM')) = TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-6),'YYYYMM'))
UNION
SELECT 
D.*
FROM 
(
SELECT
AA.CUSTOMER_ID,
AA.METRIC_ID,
AA.ANALYZED_DATE,
AA.MONTH_08_A   AS  MONTH_01_A  ,
AA.MONTH_09_A   AS  MONTH_02_A  ,
AA.MONTH_10_A   AS  MONTH_03_A  ,
AA.MONTH_11_A   AS  MONTH_04_A  ,
AA.MONTH_12_A   AS  MONTH_05_A  ,
1   AS  MONTH_06_A  ,
1   AS  MONTH_07_A  ,
1   AS  MONTH_08_A  ,
1   AS  MONTH_09_A  ,
1   AS  MONTH_10_A  ,
1   AS  MONTH_11_A  ,
1   AS  MONTH_12_A  ,
ROW_NUMBER () OVER (PARTITION BY METRIC_ID ORDER BY ANALYZED_DATE DESC) AS SEQ
FROM ACCOUNT_ATTAINMENT AA
) D
WHERE SEQ =1
AND TO_NUMBER(TO_CHAR(ADD_MONTHS(ANALYZED_DATE,0),'YYYYMM')) = TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-7),'YYYYMM'))
UNION
SELECT 
D.*
FROM 
(
SELECT
AA.CUSTOMER_ID,
AA.METRIC_ID,
AA.ANALYZED_DATE,
AA.MONTH_09_A   AS  MONTH_01_A  ,
AA.MONTH_10_A   AS  MONTH_02_A  ,
AA.MONTH_11_A   AS  MONTH_03_A  ,
AA.MONTH_12_A   AS  MONTH_04_A  ,
1   AS  MONTH_05_A  ,
1   AS  MONTH_06_A  ,
1   AS  MONTH_07_A  ,
1   AS  MONTH_08_A  ,
1   AS  MONTH_09_A  ,
1   AS  MONTH_10_A  ,
1   AS  MONTH_11_A  ,
1   AS  MONTH_12_A  ,
ROW_NUMBER () OVER (PARTITION BY METRIC_ID ORDER BY ANALYZED_DATE DESC) AS SEQ
FROM ACCOUNT_ATTAINMENT AA
) D
WHERE SEQ =1
AND TO_NUMBER(TO_CHAR(ADD_MONTHS(ANALYZED_DATE,0),'YYYYMM')) = TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-8),'YYYYMM'))
UNION
SELECT 
D.*
FROM 
(
SELECT
AA.CUSTOMER_ID,
AA.METRIC_ID,
AA.ANALYZED_DATE,
AA.MONTH_10_A   AS  MONTH_01_A  ,
AA.MONTH_11_A   AS  MONTH_02_A  ,
AA.MONTH_12_A   AS  MONTH_03_A  ,
1   AS  MONTH_04_A  ,
1   AS  MONTH_05_A  ,
1   AS  MONTH_06_A  ,
1   AS  MONTH_07_A  ,
1   AS  MONTH_08_A  ,
1   AS  MONTH_09_A  ,
1   AS  MONTH_10_A  ,
1   AS  MONTH_11_A  ,
1   AS  MONTH_12_A  ,
ROW_NUMBER () OVER (PARTITION BY METRIC_ID ORDER BY ANALYZED_DATE DESC) AS SEQ
FROM ACCOUNT_ATTAINMENT AA
) D
WHERE SEQ =1
AND TO_NUMBER(TO_CHAR(ADD_MONTHS(ANALYZED_DATE,0),'YYYYMM')) = TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-9),'YYYYMM'))
UNION
SELECT 
D.*
FROM 
(
SELECT
AA.CUSTOMER_ID,
AA.METRIC_ID,
AA.ANALYZED_DATE,
AA.MONTH_11_A   AS  MONTH_01_A  ,
AA.MONTH_12_A   AS  MONTH_02_A  ,
1   AS  MONTH_03_A  ,
1   AS  MONTH_04_A  ,
1   AS  MONTH_05_A  ,
1   AS  MONTH_06_A  ,
1   AS  MONTH_07_A  ,
1   AS  MONTH_08_A  ,
1   AS  MONTH_09_A  ,
1   AS  MONTH_10_A  ,
1   AS  MONTH_11_A  ,
1   AS  MONTH_12_A  ,
ROW_NUMBER () OVER (PARTITION BY METRIC_ID ORDER BY ANALYZED_DATE DESC) AS SEQ
FROM ACCOUNT_ATTAINMENT AA
) D
WHERE SEQ =1
AND TO_NUMBER(TO_CHAR(ADD_MONTHS(ANALYZED_DATE,0),'YYYYMM')) = TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-10),'YYYYMM'))
UNION
SELECT 
D.*
FROM 
(
SELECT
AA.CUSTOMER_ID,
AA.METRIC_ID,
AA.ANALYZED_DATE,
AA.MONTH_12_A   AS  MONTH_01_A  ,
1   AS  MONTH_02_A  ,
1   AS  MONTH_03_A  ,
1   AS  MONTH_04_A  ,
1   AS  MONTH_05_A  ,
1   AS  MONTH_06_A  ,
1   AS  MONTH_07_A  ,
1   AS  MONTH_08_A  ,
1   AS  MONTH_09_A  ,
1   AS  MONTH_10_A  ,
1   AS  MONTH_11_A  ,
1   AS  MONTH_12_A  ,
ROW_NUMBER () OVER (PARTITION BY METRIC_ID ORDER BY ANALYZED_DATE DESC) AS SEQ
FROM ACCOUNT_ATTAINMENT AA
) D
WHERE SEQ =1
AND TO_NUMBER(TO_CHAR(ADD_MONTHS(ANALYZED_DATE,0),'YYYYMM')) = TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-11),'YYYYMM'))
  • "_It seems like this query might not be efficient is kind of a difficult question to answer. Additionally, giant blocks of code are hard to read (partly why you're here). In order to make your question more answerable, have a look at these resources: minimal reproducible example & What's on topic?. Welcome to the network 🙂 – Peter Vandivier Oct 17 at 8:08
0

It's really impossible to suggest any real improvements without knowing the table definition and some representative data. But one thing pops out immediately. THere is no reason to convert a DATE to a VARCHAR to a NUMBER just to compare. You can compare dates directly -

Instead of

AND TO_NUMBER(TO_CHAR(ADD_MONTHS(ANALYZED_DATE,0),'YYYYMM')) = TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-11),'YYYYMM')

you should compare the dates directly:

AND trunc(ADD_MONTHS(ANALYZED_DATE,0)) = trunc(ADD_MONTHS(SYSDATE,-11))

And with that it more appears that those various queries separated by UNION vary only by what you are subracting from SYSDATE in the above comparison. That would suggest they could be collapsed into one SELECT, comparing a range of dates, perhaps with a GROUP BY.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.