OR子句需要更长的时间

由景长星发布于 2020-05-18 11:17:08

我有一个查询作为下面的示例，但它永远运行..如果执行联合，则我复制了整个大型查询..此修复程序的任何帮助

select columns 
 from table a
 join (select *
         from ( select *, row_number() over( partition BY id ORDER BY date ) row_seq
                  from table b
              ) where row_seq = 1
       ) o ON  a.id = o.id OR  a.id = o.src_id 
 left join table p  on a.partner_c = p.id
 left join table pp on a.parnt_id = pp.id

凹凸萌

2020-05-18 11:17:08

内部

select *
from ( 
    select *,
        row_number() over( partition BY id ORDER BY date ) row_seq
    from table b
) where row_seq = 1

can be written using the QUALIFY clause like so:

select *,
from table b
qualify row_number() over( partition BY id ORDER BY date ) = 1

可以将其推送到CTE中，例如：

WITH raw_rows AS (
    select *,
    from table b
    qualify row_number() over( partition BY id ORDER BY date ) = 1
) 
select columns 
from table a
join raw_rows o ON (a.id = o.id OR  a.id = o.src_id)
left join table p  on a.partner_c = p.id
left join table pp on a.parnt_id = pp.id

which means you can union that whole thing or just the table a and raw_rows o

WITH raw_rows AS (
    select *,
    from table b
    qualify row_number() over( partition BY id ORDER BY date ) = 1
), a_and_o AS (
    select a.columns
        ,o.columns 
    from table a
    join raw_rows o ON a.id = o.id 

    UNION ALL

    select a.columns
        ,o.columns 
    from table a
    join raw_rows o ON a.id = o.src_id
)    
select columns 
from a_and_o a
left join table p  on a.partner_c = p.id
left join table pp on a.parnt_id = pp.id

but this will give you a slightly different result if o.src_id == o.id for any row, as it will match both verse the OR vesion will have those lines once. You can pay the GROUP BY cost by swapping to UNION to de-dup, but then if you have many lines you want to keep those will be lost. Or you can change second half of the union to not included those line with

join raw_rows o ON a.id = o.src_id AND o.src_id != o.id

取决于您的数据和需求。

景长星

这家伙很懒，什么都没留下

积分
0
话题
0
评论
3193
注册排名
2141