Postgres:如何在 Have 子句中进行 NULL 友好的 MIN 过滤以仅选择一行?

本文介绍了Postgres:如何在 Have 子句中进行 NULL 友好的 MIN 过滤以仅选择一行?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

根据 Gordon Linoff 的建议，我为 Postgres:如何在 Have 子句中进行对 NULL 友好的 MIN 过滤?

According Gordon Linoff advice I've created a new followup question for Postgres: How to make NULL friendly MIN filtering in Having clause?

SELECT userId FROM audit_table GROUP BY userId HAVING MIN(updatedDate) > ? OR MIN(updatedDate) IS NULL; ORDER BY userId LIMIT 1

有没有办法让这个查询在 POSTGRES 中更高效?

Is there way to make this quesry more performant in POSTGRES ?

推荐答案

如果您担心性能，您可以:

If you are concerned about performance you can:

• 将 OR 转换为 UNION ALL.
• 按 updatedDate 索引.
• 限制每个子查询，并且
• 限制 UNION ALL.

例如你可以添加索引(和一些数据):

For example you can add the index (and some data):

create table audit_table ( userId int, updatedDate date ); insert into audit_table (userId, updatedDate) values (123, '2021-02-03'), (456, '2021-04-12'), (789, '2021-01-22'), (477, null); create index ix1 on audit_table (updatedDate nulls last, userId);

然后查询将使用索引:

select * from ( ( select userId, updatedDate from audit_table order by updatedDate nulls last limit 1 ) union all ( select userId, updatedDate from audit_table where updatedDate is null limit 1 ) ) x order by updatedDate nulls last limit 1

结果:

userid updateddate ------- ----------- 789 2021-01-22

当有大量行时，上面的查询将使用索引.如果表只有几百行，引擎最终可能会忽略索引并读取整个表.

The query above will use indexes when there is a significant number of rows. If the table only has a few hundred rows, the engine may end up ignoring the index and will read the whole table instead.

注意:如果同时找到空行和非空行，您可以选择优先考虑哪一个.在前一行到最后一行中，nulls first 将优先处理空行，而 nulls last 将优先处理非空行.

NOTE: If both a null and not null rows are found you can choose which one to prioritize. In the previous to last line nulls first will prioritize the null row, while nulls last will prioritize the non-null one.

请参阅 DB Fiddle 上的运行示例.

See running example at DB Fiddle.