需要帮助重新编写此查询，它根据解释计划多次使用同一数据集

des4xlb0 于 2021-06-27 发布在 Hive

关注(0)|答案(1)|浏览(379)

我们的开发团队运行了一个查询，这个查询占用了大量的资源，查看explain计划，它似乎多次使用同一个数据集。我们是否可以重新编写这个查询。
现在，我尝试用直接连接来替换相关联的查询，但是多个相关联的查询除了一个小的区别外看起来还是一样的。

select tb2.mktg_id, mktg_cd , count(distinct tb2.conf_id) 
  from
(select conf_id, count(distinct c.mktg_id) as num_cpg 
   from acc_latst c, off_latst ot 
  where c.mktg_id = ot.mktg_id and c.bus_eff_dt > '2019-01-01' and to_date(strt_tms) = '2019-01-10'  
  group by conf_id 
 having count(distinct c.mktg_id) >1 
)tb1,
(select distinct conf_id, c.mktg_id, mktg_cd 
   from acc_latst c, off_latst ot 
  where c.mktg_id = ot.mktg_id and c.bus_eff_dt > '2019-01-01' and to_date(strt_tms) = '2019-01-10'
)tb2
  where tb1.conf_id = tb2.conf_id group by tb2.mktg_id, mktg_cd

Hive yarn performance apache-tez query-tuning

来源：https://stackoverflow.com/questions/54625648/need-help-in-re-writing-this-query-which-uses-same-data-set-multiple-times-as

1条答案

按热度按时间

uelo1irk1#

一种方法是使用cte-

with res1 as 
(
select distinct conf_id, c.mktg_id, mktg_cd 
   from acc_latst c, off_latst ot 
  where c.mktg_id = ot.mktg_id and c.bus_eff_dt > '2019-01-01' and to_date(strt_tms) = '2019-01-10'
)
,res2 as
(
select conf_id, count(distinct c.mktg_id) as num_cpg
from res1 group by conf_id having count(distinct c.mktg_id) > 1
)
select res1.mktg_id, mktg_cd, count(distinct res1.conf_id)  from res1 t1 inner join res2 t2 on t1.conf_id=t2.conf_id group by res1.mktg_id, mktg_cd;

如果查询仍然很慢，请提供表和分区的详细信息。

赞(0）回复(0）举报 2021-06-27

我来回答

需要帮助重新编写此查询，它根据解释计划多次使用同一数据集

1条答案

相关问题

热门标签

最新问答