在此查询中，您可以改进很多内容。

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

• 当您知道将在两边都得到行时，您正在使用FULL OUTER JOIN。内联接将获得相同的结果（并且执行速度更快）


• 您通过sale_date进行分组，但选择列表中仅包含月份名称和年份。除非重要的是在sale_date级别上对总数进行ROUND，否则可以在年/月级别上进行汇总。


• 除了聚合级别外，查询的两半基本上是相同的。您可以使用GROUPING SET，OLAP函数或WITH（CTE）删除代码中的冗余。


• 您可以按要分组的列的派生进行分组。


• 您拥有ORDER BY是您的子选择。

• 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

仍然，这是一个重写。我没有在与查询相同的聚合级别上完成ROUND（），因此，即使我正确理解了您的代码，我的版本也可能不会完全获得相同的结果

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

享受！我希望它能激发您学习更多有关SQL的知识并提高您的查询编写技能。 （PS下次请发送表DDL并注意您使用的Db2版本）

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