将子查询转换为连接

Question

我正在努力提高我的查询性能，因为我正在处理大数据。(许多TB的数据)我正在研究Impala。

我使用的查询如下所示。这是子查询的形式。我不确定如何优化它。可能是JOINS。如果是，我不能将此子查询转换为连接。

请建议如何将此子查询转换为连接。我需要提高查询性能

select mytable.id,group_concat(mytable.acqid) as 'acqid',group_concat(mytable.address) as 'address'
from (select myinnertable.id, case when myinnertable.mykey = 'AcqId' then myinnertable.myvalue end as 'acqid',
             case when myinnertable.mykey = 'Address' then myinnertable.myvalue end as 'address'
      from (select id,mykey,myvalue
            from table1
            where table1.date_col BETWEEN '2021-05-02' and '2021-05-19' and mykey!='velList'
           ) myinnertable
     ) mytable
group by mytable.id;

Answer 1

您正在做的事情可以在没有嵌套上下文的情况下得到简化。正如戈登所指出的，您只使用了一个表，但在结果中进行了3次遍历。我会确保它在(date_col, id, mykey)上有一个索引，以帮助优化拉取数据。

select 
        t1.id, 
        group_concat( case when t1.mykey = 'AcqId' 
                        then t1.myvalue end ) acqid,
        group_concat( case when t1.mykey = 'Address' 
                        then t1.myvalue end ) address
    from 
        table1 t1
    where 
            t1.date_col BETWEEN '2021-05-02' and '2021-05-19' 
        and t1.mykey != 'velList' 
    group by 
        t1.id;