Postgres:如何在 Have 子句中进行 NULL 友好的 MIN 过滤以仅选择一行? [英] Postgres: How to make NULL friendly MIN filtering in Having clause to select exactly one row?

问题描述

根据 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.

这篇关于Postgres:如何在 Have 子句中进行 NULL 友好的 MIN 过滤以仅选择一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！