如何在不使用分组依据的情况下获取月名称，更有效 [英] how to get monthname without using in group by in query and more efficient
本文介绍了如何在不使用分组依据的情况下获取月名称，更有效的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
查询执行时间很长，我想减少
执行时间。此查询有效，但需要更多时间
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
*因为我不太擅长编写良好的查询，所以有人让我知道我还能对此查询做什么，但是优先事项是从group by中删除monthname，因为它在执行过程中需要花费很多时间。但是我需要monthname，所以有没有机会在不按分组的情况下使用*
*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
推荐答案
在此查询中，您可以改进很多内容。
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
这篇关于如何在不使用分组依据的情况下获取月名称，更有效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
