how to get monthname without using in group by in

问题:

query is taking so much time for execution and i want to decrease the execution time.this query is working but it is taking more time

*since i am not too good for writting good query so anyone let me know what else i can do with this query but the priority is to remove monthname from group by because it is taking much time while execution.But i need the monthname so is there is any chances to get month name without using in group by *

SELECT          set2.prod_nm, 
                set2.therapeuticclass, 
                set2.total, 
                set2.qty                               AS quantity, 
                set2.mfg                               AS mfg, 
                set2.monthname                         AS monthname, 
                set2.year                              AS year, 
                Round(((set2.total/set3.total)*100),2) AS share 
FROM            ( 
                         SELECT   set1.prod_nm                                                                                                                              AS prod_nm,
                                  set1.mfg                                                                                                                                  AS mfg,
                                  set1.monthname                                                                                                                            AS monthname,
                                  set1.year                                                                                                                                 AS year,
                                  Sum(set1.total)                                                                                                                           AS total,
                                  Sum(set1.qty)                                                                                                                             AS qty,
                                  set1.th_class_1                                                                                                                           AS th_1,
                                  set1.th_class_2                                                                                                                           AS th_2,
                                  set1.th_class_3                                                                                                                           AS th_3,
                                  set1.th_class_4                                                                                                                           AS th_4,
                                           Concat(Concat(Concat(Concat( set1.th_class_1, ','),set1.th_class_2),','),Concat(Concat( set1.th_class_3, ','),set1.th_class_4) ) AS therapeuticclass
                         FROM     ( 
                                           SELECT   retail_store_prod.th_class_4 AS th_class_4, 
                                                    retail_store_prod.th_class_3 AS th_class_3, 
                                                    retail_store_prod.mfg        AS mfg, 
                                                    retail_store_prod.th_class_2 AS th_class_2, 
                                                    retail_store_prod.th_class_1 AS th_class_1, 
                                                    retail_store_prod.store_id   AS store_id , 
                                                    retail_store.str_nm, 
                                                    Sum(retail_str_sales_detail.qty)             AS qty,
                                                    retail_ str_sales_detail.prod_nm             as prod_nm,
                                                    monthname(retail_str_sales_detail.sale_date) AS monthname,
                                                    Year(retail_str_sales_detail.sale_date)      AS year,
                                                    Round(Sum (retail_str_sales_detail.total),2) AS total
                                           from     retail_str_sales_detail , 
                                                    retail_store_prod, 
                                                    retail_store 
                                           WHERE    retail_store_prod.prod_nm = retail_str_sales_detail.prod_nm
                                           AND      retail_store_prod.store_id=retail_str_sales_detail.store_id
                                           AND      retail_store.store_id = retail_store_prod.store_id
                                           AND      retail_store_prod.th_class_4 != 'NULL' 
                                           AND      retail_store_prod.th_class_3 != 'NULL' 
                                           AND      retail_store_prod.th_class_2 != 'NULL' 
                                           AND      retail_store_prod.th_class_1 != 'NULL' 
                                           AND      retail_store_prod.th_class_4 != '' 
                                           AND      retail_store_prod.th_class_3 != '' 
                                           AND      retail_store_prod.th_class_2 != '' 
                                           AND      retail_store_prod.th_class_1 != '' 
                                           GROUP BY retail_store_prod.th_class_4 , 
                                                    retail_store_prod.th_class_3 , 
                                                    retail_store_prod.mfg, 
                                                    retail_str_sales_detail.sale_date, 
                                                    retail_store_prod.th_class_2 , 
                                                    retail_store_prod.th_class_1, 
                                                    retail_str_sales_detail.prod_nm , 
                                                    retail_store.str_nm, 
                                                    retail_store_prod.store_id 
                                           ORDER BY retail_str_sales_detail.prod_nm, 
                                                    retail_store_prod.th_class_4 , 
                                                    retail_store_prod.th_class_3 , 
                                                    retail_store_prod.th_class_2 , 
                                                    retail_store_prod.th_class_1 , 
                                                    retail_store.str_nm, 
                                                    round(sum (retail_str_sales_detail.total),2) DESC) AS set1
                         GROUP BY set1.prod_nm, 
                                  set1.th_class_1, 
                                  set1.th_class_2, 
                                  set1.th_class_3, 
                                  set1.th_class_4, 
                                  set1.year, 
                                  set1.monthname, 
                                  set1.mfg, 
                                           concat(concat(concat(concat( set1.th_class_1, ','),set1.th_class_2),','),concat(concat( set1.th_class_3, ','),set1.th_class_4) )
                         ORDER BY set1.prod_nm) AS set2 
FULL OUTER JOIN 
                ( 
                         SELECT   sum(set1.total)                                                                                                                           AS total,
                                  sum(set1.qty)                                                                                                                             AS qty,
                                  set1.th_class_1                                                                                                                           AS th_1,
                                  set1.th_class_2                                                                                                                           AS th_2,
                                  set1.th_class_3                                                                                                                           AS th_3,
                                  set1.th_class_4                                                                                                                           AS th_4,
                                           concat(concat(concat(concat( set1.th_class_1, ','),set1.th_class_2),','),concat(concat( set1.th_class_3, ','),set1.th_class_4) ) AS therapeuticclass
                         FROM     ( 
                                           SELECT   retail_store_prod.th_class_4 AS th_class_4, 
                                                    retail_store_prod.th_class_3 AS th_class_3, 
                                                    retail_store_prod.th_class_2 AS th_class_2, 
                                                    retail_store_prod.th_class_1 AS th_class_1, 
                                                    retail_store_prod.store_id   AS store_id , 
                                                    retail_store.str_nm, 
                                                    sum(retail_str_sales_detail.qty)             AS qty,
                                                    retail_str_sales_detail.prod_nm              AS prod_nm,
                                                    round(sum (retail_str_sales_detail.total),2) AS total
                                           FROM     retail_str_sales_detail , 
                                                    retail_store_prod, 
                                                    retail_store 
                                           WHERE    retail_store_prod.prod_nm = retail_str_sales_detail.prod_nm
                                           AND      retail_store_prod.store_id=retail_str_sales_detail.store_id
                                           AND      retail_store.store_id = retail_store_prod.store_id
                                           AND      retail_store_prod.th_class_4 != 'NULL' 
                                           AND      retail_store_prod.th_class_3 != 'NULL' 
                                           AND      retail_store_prod.th_class_2 != 'NULL' 
                                           AND      retail_store_prod.th_class_1 != 'NULL' 
                                           AND      retail_store_prod.th_class_4 != '' 
                                           AND      retail_store_prod.th_class_3 != '' 
                                           AND      retail_store_prod.th_class_2 != '' 
                                           AND      retail_store_prod.th_class_1 != '' 
                                           GROUP BY retail_store_prod.th_class_4 , 
                                                    retail_store_prod.th_class_3 , 
                                                    retail_store_prod.th_class_2 , 
                                                    retail_store_prod.th_class_1 , 
                                                    retail_str_sales_detail.prod_nm , 
                                                    retail_store.str_nm, 
                                                    retail_store_prod.store_id 
                                           ORDER BY retail_str_sales_detail.prod_nm, 
                                                    retail_store_prod.th_class_4 , 
                                                    retail_store_prod.th_class_3 , 
                                                    retail_store_prod.th_class_2 , 
                                                    retail_store_prod.th_class_1 , 
                                                    retail_store.str_nm, 
                                                    round(sum (retail_str_sales_detail.total),2) DESC) AS set1
                         GROUP BY set1.th_class_1, 
                                  set1.th_class_2, 
                                  set1.th_class_3, 
                                  set1.th_class_4, 
                                           concat(concat(concat(concat( set1.th_class_1, ','),set1.th_class_2),','),concat(concat( set1.th_class_3, ','),set1.th_class_4) ) ) AS set3
ON              set3.th_1= set2.th_1 
AND             set3.th_2 = set2.th_2 
AND             set3.th_3= set2.th_3 
AND             set3.th_4 = set2.th_4

回答1:

There are a number of things that you could improve in this query.

• You are using a FULL OUTER JOIN when you know that you will get rows on both sides. An INNER JOIN will get the same result (and do so faster)
• You GROUP BY sale_date, but only have monthname and year in the select list. Unless ROUNDing the total at the sale_date level is important, you can aggregate at year/month level
• The two halves of the query are essentially the same apart from the aggregation level. You could use a GROUPING SET, an OLAP function, or a WITH (CTE) to remove the redundancy in your code.
• You group by derivations of columns you are grouping by. This is not needed.
• You have ORDER BY is your sub-selects. These are not needed.

Also, you have a rouge space in the query on this line

retail_ str_sales_detail.prod_nm             as prod_nm,

so you query won't run as pasted above

Still, here is a re-write. I've not done a ROUND() at the same level of aggregation as your query, so even if I have understood your code correctly, my version might not get exactly the same result

Enjoy! I hope it inspires you to learn more about SQL and improve your query writing skills. (P.S. Next time please send your table DDL and note the Db2 version you are using)

SELECT prod_nm
,      th_class_1 || ',' || th_class_2 || ',' ||
       th_class_3 || ',' || th_class_4              AS therapeuticclass
,      total
,      qty
,      mfg
,      yearmonth/100 as year
,      MONTHNAME(TO_DATE(yearmonth*100+1,'YYYYMMDD'))      as monthname 
,      Round(((
        total / SUM(total) OVER(PARTITION BY 
                       th_class_4, th_class_3, th_class_2, th_class_1 )
       )*100),2) AS share 
FROM
(   SELECT
           sd.prod_nm
    ,      sp.mfg    
    ,      sp.th_class_4
    ,      sp.th_class_3
    ,      sp.th_class_2
    ,      sp.th_class_1 
--  ,      sp.store_id      
--  ,      rs.str_nm 
    ,      INTEGER(sd.sale_date)/100  AS yearmonth
    ,      SUM(sd.qty)                AS qty
    ,      ROUND(SUM(sd.total),2)      AS total
    FROM        
                retail_str_sales_detail sd 
    INNER JOIN  retail_store_prod       sp  ON  sd.prod_nm  = sp.prod_nm 
                                            AND sd.store_id = sp.store_id
    INNER JOIN  retail_store            rs  ON  rs.store_id = sp.store_id
    WHERE
            sp.th_class_4 NOT IN ('NULL','') 
    AND     sp.th_class_3 NOT IN ('NULL','') 
    AND     sp.th_class_2 NOT IN ('NULL','') 
    AND     sp.th_class_1 NOT IN ('NULL','') 
    GROUP BY
            sd.prod_nm 
    ,       sp.th_class_4
    ,       sp.th_class_3
    ,       sp.th_class_2
    ,       sp.th_class_1 
    ,       sp.mfg
--  ,       sp.store_id 
--  ,       rs.str_nm 
    ,       INTEGER(sd.sale_date)/100 
) s