Options

Any better way of writing the below query

vinutaurovinutauro Community Edition User

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

Answers

  • Options
    SruthiASruthiA Vertica Employee Administrator

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

  • Options
    vinutaurovinutauro Community Edition User

    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;

  • Options
    SruthiASruthiA Vertica Employee Administrator

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

  • Options
    koumarankoumaran Vertica Employee Employee

    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;

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file