sql - 根据两个条件过滤列时如何编写Postgres查询？

Question

我在 Postgres 中有以下 widgets 表:

(该屏幕截图是它的粗略 Excel 表示。)我正在尝试编写一个将返回我的 SQL 查询:

1. widgets记录代表今天最早加载时间的widget；或
2. 如果今天没有加载小部件，则加载时间最早的那个(对于整个表)

因此，使用上图:

• 查询将首先尝试返回今天最先加载的小部件(如果存在这样的小部件)。在这种情况下，今天仅加载了 id 为 3 和 5003094(分别)的小部件。在这两个中，widget_id = 3 比另一个加载得早，因此这是查询将返回的记录。
• 但是，如果我们假设这两个小部件不在表中，而且今天没有加载任何小部件，那么查询将返回 widget_id = 1，因为它是在2010.

这是我对查询的初步尝试:

SELECT
    MIN(w.loaded_date_time)
FROM
    widgets w
WHERE
    w.loaded_date_time >= now()
    OR
    1=1

但是，我马上就知道这在语法上是不正确的。有任何想法吗？提前致谢!

Answer 1

一行总计

SELECT *
FROM   widgets
ORDER  BY loaded_date_time < now()::date, loaded_date_time
LIMIT  1;

.. 首先有效地对今天(+不存在的 future )的时间戳进行排序，因为 boolean 表达式被排序为 FALSE -> TRUE -> NULL。

如果 future 的日期是可能的:

ORDER  BY
       (loaded_date_time::date = now()::date) DESC NULLS LAST
      ,loaded_date_time

NULLS LAST 仅在 loaded_date_time 可以为 NULL 时才相关，这应该是不允许的。在这种情况下删除该子句。

每个小部件一行

SELECT DISTINCT ON (widget_id)
       widget_id, loaded_date_time
FROM   widgets
ORDER  BY
       widget_id
      ,(loaded_date_time::date = now()::date) DESC
      ,loaded_date_time;

为什么以及如何运作？

• 关于 DISTINCT ON:
  • Select first row in each GROUP BY group?

• 在ORDER BY子句中...
  ... widget_id 首先出现，显然 - 必须匹配 DISTINCT 子句。
  ...然后首先对“今天”的记录进行排序。如果没有，其他记录自动上移。
  ... 最后，先排序较早的记录。

  因此，所需的行排在第一位，并由 DISTINCT 选取。一口气搞定。

• 如果将表达式 (loaded_date_time::date = now()::date) 重写为

  (loaded_date_time >= now()::date AND
   loaded_date_time < (now()::date + 1))   -- note: < not: <=
  

.. 在 loaded_date_time 上使用普通索引可能会更快。因为，如果你在等号两边都有一个表达式，你肯定根本不能使用普通的索引。您应该在 widget_id 上有一个索引(很明显)，并且可能在 loaded_date_time 上有另一个索引:

CREATE INDEX foo_idx ON widgets (loaded_date_time)

多列索引可能会快一点:

CREATE INDEX foo_idx ON widgets (widget_id, loaded_date_time);

使用 EXPLAIN ANLYZE 进行测试, 是否被使用。应该可以，我没有测试。如果没有，就没有意义。