Skip to main content
Question

Any better way of writing the below query

  • June 18, 2024
  • 4 replies
  • 6 views

vinutauro

SELECT TRANSACTION_DATE,
ROWS_MET_SLO_PERC,
FLOOR(SCORE) AVERAGE_SCORE,
CASE
WHEN AVERAGE_SCORE = 4 THEN
'A'
WHEN AVERAGE_SCORE = 3 THEN
'B'
WHEN AVERAGE_SCORE = 2 THEN
'C'
WHEN AVERAGE_SCORE = 1 THEN
'D'
ELSE
'F'
END AS GRADE
FROM
(
SELECT TRANSACTION_DATE,
SUM(ROWS_MET_SLO_COUNT) / SUM(TOTAL_ROWS) * 100 ROWS_MET_SLO_PERC
FROM mONTHLY_DATA B
JOIN PRODUCT_DATA A
ON B.id = A.id
WHERE TRANSACTION_DATE IN ( ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -2), ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1))
GROUP BY TRANSACTION_DATE
) SLO_TABLE
JOIN
(
SELECT *
FROM GRADE_TABLE
WHERE ACTION_IND = 1
) G
ON SLO_TABLE.ROWS_MET_SLO_PERC >= G.LOWER_RANGE
AND SLO_TABLE.ROWS_MET_SLO_PERC < G.UPPER_RANGE

4 replies

SruthiA
Forum|alt.badge.img+1
  • Participating Frequently
  • June 18, 2024

could you please create a support case and provide table DDL's, explain plan of the query? is it performing slower?


vinutauro
  • Author
  • New Participant
  • June 20, 2024

Below is the DDL. Performance is not slow but just want to know if this query can be rewritten in a simple way without nesting it
Thanks in advance

CREATE TABLE GRADE_TABLE
(
LOWER_RANGE numeric(7,2),
UPPER_RANGE numeric(7,2),
ACTION_IND int,
SCORE int,
UPDT_DT_TM timestamptz NOT NULL DEFAULT statement_timestamp()
)
ORDER BY ACTION_IND,LOWER_RANGE,UPPER_RANGE;

CREATE TABLE MONTHLY_DATA
(
ID integer,
TRANSACTION_DATE date,
TOTAL_ROWS int,
ROWS_MET_SLO_COUNT int,
UPDT_DT_TM timestamptz NOT NULL default STATEMENT_TIMESTAMP() ENCODING COMMONDELTA_COMP
)
ORDER BY TRANSACTION_DATE,ID;

CREATE TABLE PRODUCT_DATA
(
OWNER VARCHAR(100),
ID NUMERIC(10,0),
NAME VARCHAR(255),
UPDT_DT_TM TIMESTAMPTZ NOT NULL DEFAULT STATEMENT_TIMESTAMP() ENCODING COMMONDELTA_COMP
)
ORDER BY ID;


SruthiA
Forum|alt.badge.img+1
  • Participating Frequently
  • June 21, 2024

could you please share explain plan and projection design of the tables involved in the query?


koumaran
  • New Participant
  • June 27, 2024

Maybe try with this. Would be better if I could have the explain plan and projection designs for sure.

WITH SLO_TABLE as (
SELECT TRANSACTION_DATE,
SUM(ROWS_MET_SLO_COUNT) / SUM(TOTAL_ROWS) * 100 ROWS_MET_SLO_PERC
FROM mONTHLY_DATA B
WHERE TRANSACTION_DATE BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -2) AND ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
AND ID in (select ID from PRODUCT_DATA)
GROUP BY TRANSACTION_DATE
),
RESULT as (SELECT * FROM GRADE_TABLE G join SLO_TABLE ON ACTION_IND = 1 AND SLO_TABLE.ROWS_MET_SLO_PERC >= G.LOWER_RANGE AND SLO_TABLE.ROWS_MET_SLO_PERC < G.UPPER_RANGE)
SELECT TRANSACTION_DATE,
ROWS_MET_SLO_PERC,
FLOOR(SCORE) AVERAGE_SCORE,
DECODE(AVERAGE_SCORE,
4,'A',
3,'B',
2,'C',
1,'D',
'F') GRADE
FROM RESULT;