Query Review - Snowflake-CodePudding

I have a query in snowflake that works as expected but I feel like there's gotta be a better way of doing this, so I'm checking if anyone has a better and more efficient solution to this.

I want to count how many users have SA4 and SA5. Then check if they are multi_unit or not. For the ones that are multi_unit, count how many of each of the other ST products they have.

Original Table:

https://imgur.com/CtSSXcD

Output table wanted with query:

https://imgur.com/a/gxIGcT6

Here's the query that works, but I feel like there's gotta be a better way of doing this. Please let me know your thoughts :) Appreciated

SELECT
'AB4' AS Product,
COUNT(*) AS CNT,
SUM(CASE WHEN MULTI_UNIT = 1 THEN 1 ELSE 0 END) AS MULTI,
SUM(AB300_IND) AS AB300,
SUM(AB10_IND) AS AB10,
SUM(AB20_IND) AS AB20,
SUM(AB30_IND) AS AB30,
FROM TABLE.VIEW.MAW
WHERE AB4_IND = 1
GROUP BY 1
UNION
SELECT
'AB5' AS Product,
COUNT(*) AS CNT,
SUM(CASE WHEN MULTI_UNIT = 1 THEN 1 ELSE 0 END) AS MULTI,
SUM(AB300_IND) AS AB300,
SUM(AB10_IND) AS AB10,
SUM(AB20_IND) AS AB20,
SUM(AB30_IND) AS AB30,
FROM TABLE.VIEW.MAW
WHERE AB5_IND = 1
GROUP BY 1

CodePudding user response：

UNION is an overkill because rows are already unique after aggregation. UNION ALL will work faster, UNION does additional DISTINCT aggregation.

SELECT
'AB4' AS Product,
COUNT(*) AS CNT,
SUM(CASE WHEN MULTI_UNIT = 1 THEN 1 ELSE 0 END) AS MULTI,
SUM(AB300_IND) AS AB300,
SUM(AB10_IND) AS AB10,
SUM(AB20_IND) AS AB20,
SUM(AB30_IND) AS AB30
FROM TABLE.VIEW.MAW
WHERE AB4_IND = 1
GROUP BY 1
UNION ALL                ----use UNION ALL instead of UNION
SELECT
'AB5' AS Product,
COUNT(*) AS CNT,
SUM(CASE WHEN MULTI_UNIT = 1 THEN 1 ELSE 0 END) AS MULTI,
SUM(AB300_IND) AS AB300,
SUM(AB10_IND) AS AB10,
SUM(AB20_IND) AS AB20,
SUM(AB30_IND) AS AB30
FROM TABLE.VIEW.MAW
WHERE AB5_IND = 1
GROUP BY 1

Further optimization is to get rid of UNION ALL completely... You can not use single query without union all or joins with condition like this

CASE WHEN AB4_IND = 1 THEN 'AB4'
            WHEN AB5_IND = 1 THEN 'AB5' END AS Product

and using it in groupby because in case the same row has AB4_IND and AB5_IND both equal 1, it will count only first condition in CASE (AB4).

You can still get rid of second query if you will join with constant two rows set containing Products required ('AB4'), ('AB5'), this will look shorter and may perform better:

SELECT p.Product,
       COUNT(*) AS CNT,
       SUM(CASE WHEN m.MULTI_UNIT = 1 THEN 1 ELSE 0 END) AS MULTI,
       SUM(m.AB300_IND) AS AB300,
       SUM(m.AB10_IND) AS AB10,
       SUM(m.AB20_IND) AS AB20,
       SUM(m.AB30_IND) AS AB30
  FROM (VALUES ('AB4'), ('AB5')) AS p (Product)
       INNER JOIN TABLE.VIEW.MAW m 
        ON (p.Product='AB4' and m.AB4_IND = 1) OR (p.Product='AB5' and m.AB5_IND = 1)
  WHERE (m.AB4_IND = 1) OR (m.AB5_IND = 1)
  GROUP BY p.Product;

CodePudding user response：

You can also try the UNPIVOT version of the solution:

SELECT
    PRODUCT,
    COUNT(1) AS CNT,
    SUM(CASE WHEN MULTI_UNIT = 1 THEN 1 ELSE 0 END) AS MULTI,
    SUM(AB300_IND) AS AB300,
    SUM(AB10_IND) AS AB10,
    SUM(AB20_IND) AS AB20,
    SUM(AB30_IND) AS AB30
FROM TABLE.VIEW.MAW
    UNPIVOT(PRODUCT_SELECTED FOR PRODUCT IN (AB4_IND, AB5_IND))
WHERE PRODUCT_SELECTED = 1
GROUP BY 1
;