sql - 左外连接与嵌套聚合选择查找表中最新行的好处是什么？

Question

我在做:

select * from mytable y
where y.year = (select max(yi.year) 
                from mytable yi
                where yi.person = y.person)

从性能方面来看，它比以下情况更好还是更差:

select y.* from mytable y
left outer join mytable y2
  on y.year < y2.year
  and y.person = y2.person
where y2.year is null

解释计划/轶事证据尚无定论，所以我想知道总的来说一个是否比另一个更好。

Answer 1

“一般来说”，两种查询都可以根据数据分布产生不同的执行计划。

但是，假设您的第二个查询实际上是这样的:

SELECT  y.*
FROM    mytable y
LEFT JOIN
        mytable y2
ON      y2.person = y.person
        AND y2.year > y.year
WHERE   y2.year IS NULL

，LEFT JOIN 版本很可能会更快，因为它将优化为 HASH ANTI JOIN 或 HASH JOIN带过滤器，取决于您是否在 mytable (person, year) 上有索引以及其他一些条件。子查询版本不可针对反连接进行优化。

您很可能会发现这些查询更有效率:

SELECT  *
FROM    mytable y
WHERE   (y.person , y.year) IN
        (
        SELECT  person, MAX(year)
        FROM    mytable
        GROUP BY
                person
        )

或

SELECT  *
FROM    (
        SELECT  y.*,
                DENSE_RANK() OVER (PARTITION BY person ORDER BY year DESC) dr
        FROM    mytable y
        )
WHERE   dr = 1

，第一个在多人和每个人几年的情况下更有效率，第二个在相反的情况下更有效率。

您可以将 DENSE_RANK 替换为 ROW_NUMBER，这样您就可以根据需要去除 person, MAX(year) 上的重复项到。