sql—需要帮助避免在查询中多次在(内部)联接中使用的视图吗

s3fp2yjn 于 2021-05-29 发布在 Hadoop

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

我们有一个使用多个内部连接的查询，现在我们在同一个视图上多次进行内部连接。它类似于层次结构类型join
这会增加查询的执行时间。
查询

Select field1, field2 ... few fields
    FROM 
    CUST_MART.v_CPM AS P 
    JOIN CUST_MART.V_CPM_MART_HIER AS C ON p.prod_cl = c.prod_cl 
    AND g.lvl = c.lvl 
    JOIN CUST_MART.V_CPM_MART_CLASS AS D0 ON c.prod_cl_0 = d0.prod_cl 
    AND c.lvl_0 = d0.lvl 
    JOIN CUST_MART.V_CPM_MART_CLASS AS D2 ON c.prod_cl_2 = d2.prod_cl 
    AND c.lvl_2 = d2.lvl 
    JOIN CUST_MART.V_CPM_MART_CLASS AS D3 ON c.prod_cl_3 = d3.prod_cl 
    AND c.lvl_3 = d3.lvl 
    JOIN CUST_MART.V_CPM_MART_CLASS AS D6 ON c.prod_cl_6 = d6.prod_cl 
    AND c.lvl_6 = d6.lvl 
    JOIN CUST_MART.v_CPM_upc AS V on p.skt = v.skt

这个查询是否有效-

Select field1, field2 ... few fields
    FROM 
    CUST_MART.v_CPM AS P 
    JOIN CUST_MART.V_CPM_MART_HIER AS C ON p.prod_cl = c.prod_cl 
    AND g.lvl = c.lvl 
    JOIN CUST_MART.V_CPM_MART_CLASS AS D0 ON c.prod_cl_0 = d0.prod_cl 
    AND c.lvl_0 = d0.lvl 
    AND c.prod_cl_2 = d0.prod_cl 
    AND c.lvl_2 = d0.lvl 
    AND c.prod_cl_3 = d0.prod_cl 
    AND c.lvl_3 = d0.lvl 
    AND c.prod_cl_6 = d0.prod_cl 
    AND c.lvl_6 = d0.lvl 
    JOIN CUST_MART.v_CPM_upc AS V on p.skt = v.skt

请求
我重写的查询可以工作吗？
我不能在prod上运行，所以不能用现有数据测试这个
此查询每天运行4小时，因此需要修复此查询
我还有别的办法可以考虑吗

sql hadoop Hive hiveql

来源：https://stackoverflow.com/questions/55530424/need-help-in-avoiding-the-view-which-is-used-in-inner-join-multiple-times-in-a

1条答案

按热度按时间

q9yhzks01#

不，重写的查询将不起作用。想想这个 JOIN :

JOIN CUST_MART.V_CPM_MART_CLASS D0
ON c.prod_cl_0 = d0.prod_cl AND
   c.lvl_0 = d0.lvl AND
   c.prod_cl_2 = d0.prod_cl AND
   c.lvl_2 = d0.lvl AND
   c.prod_cl_3 = d0.prod_cl AND
   c.lvl_3 = d0.lvl AND
   c.prod_cl_6 = d0.prod_cl AND
   c.lvl_6 = d0.lvl

仅在以下情况下匹配：

c.lvl_0 = c.lvl_1 = c.lvl_2 = c.lvl_3 = c.lvl_6

此条件不在原始查询中。我猜这在你的数据中从来都不是真的。

赞(0）回复(0）举报 2021-05-29

我来回答

sql—需要帮助避免在查询中多次在(内部)联接中使用的视图吗

1条答案

相关问题

热门标签

最新问答