主页 / dba / 问题 / 310897
Accepted
Henrik N
Asked: 2022-04-14 02:31:49 +0800 CST

进行 > 或 < 比较的好方法是什么，认为 NULL 首先或最后排序？

  • 772

我在 Postgres 13.5 上。

记录总是有一个number(type: integer) 和可选的单个letterAZ (type: varchar(1)– 不是char出于框架原因)。

记录通常是排序的number ASC, letter ASC NULLS FIRST。序列可能有间隙。缺少的字母表示为 NULL。

例如，您可能会得到以下顺序：1、1A、1B、2、10、10A、10C

现在，我想做一些事情，比如在给定记录的“右侧”找到两条记录。所以如果给定的记录是 2 号，我想在上面的例子中找到 10 和 10A。

如果我可以查询（伪代码）之类的条件会很方便：number > $given_number OR (number = $given_number AND letter > $given_letter NULLS FIRST)

当然，这不是书面的。我有什么方法可以做到这一点？

我不想合并列或添加新列。

我能想到的解决方案：

  • 选择 SQL 中的记录 ID 列表，使用数据库外部的应用程序逻辑查找下两条记录的 ID，然后进行第二次查询以仅查找那些记录。
  • 明确说明 NULL 的更长条件，例如WHERE number > $given_number OR (number = $given_number AND (($given_letter IS NULL AND letter IS NOT NULL) OR letter > $given_letter)) ORDER BY number ASC, letter ASC NULLS FIRST LIMIT 2
  • 合并 NULL，类似于WHERE number > $given_number OR (number = $given_number AND letter > COALESCE($given_letter, '')) ORDER BY number ASC, letter ASC NULLS FIRST LIMIT 2

有更好的想法吗？或者对这些有什么想法？

postgresql

2 个回答

  1. Best Answer

    如果您可以将 NULL 值转换letter为空字符串 ( '')，那么一切都会到位。空字符串按默认排序顺序在任何其他值之前排序。

    那是假设您没有空字符串和NULL 值（这将更不幸）。

    UPDATE tbl SET letter = '' WHERE letter IS NULL;

ALTER TABLE tbl
  ALTER COLUMN letter SET NOT NULL
, ALTER COLUMN letter SET DEFAULT ''
, ADD PRIMARY KEY (number, letter)  -- optional
;

    您的排序顺序：

    number ASC, letter ASC NULLS FIRST

    变成了：

    number, letter

    ...找到给定记录“右侧”的两条记录。

    SELECT *
FROM   tbl
WHERE  (number, letter) > (10, '')
ORDER  BY number, letter
LIMIT  2;

    db<>在这里摆弄

    注意行值比较的使用。看：

    UNIQUE用索引或 PK on备份它，(number, letter)你就很成功了。

    如果您无法清理表定义，仍有一种解决方法：

    SELECT *
FROM   tbl
WHERE  (number, COALESCE(letter, '')) > (10, COALESCE(NULL, ''))
ORDER  BY number, COALESCE(letter, '')
LIMIT  2;

    甚至可以通过匹配的多列索引来支持。而是保持简单并转换您的 NULL 值。

    顺便说一句：
    对于来自 AZ 的字母，该类型"char"甚至会更有效一些 - 与 绝对不同char，这永远不会有用。（但您的“框架原因”可能反对它。）请参阅：

    • 4

  2. 例如，您可能会得到以下顺序：1、1A、1B、2、10、10A、10C 现在，我想做一些事情，比如查找给定记录“右侧”的两条记录。所以如果给定的记录是 2 号，我想在上面的例子中找到 10 和 10A。

    LEAD(column_name,1) OVER (ORDER BY ...)如果出现以下情况，则窗口函数LEAD(column_name,2) OVER (ORDER BY ...)可能会执行您想要的操作：

    • 根据给定的排序顺序，值是一个和两个记录的事实可以在查询中硬连线。
    • 过滤给定记录的条件可以添加到外部查询中，从而导致计划的效率可能低于您提出的带有 LIMIT 子句的解决方案。

    查询的形式是

    SELECT * FROM (
   SELECT *,
   LEAD(number,1) OVER (ORDER BY number,letter nulls first) AS next_number_1,
   LEAD(letter,1) OVER (ORDER BY number,letter nulls first) AS next_letter_1,
   LEAD(number,2) OVER (ORDER BY number,letter nulls first) AS next_number_2,
   LEAD(letter,2) OVER (ORDER BY number,letter nulls first) AS next_letter_2
   FROM table
) s
WHERE number=$given_number AND letter is not distinct from $given_letter
    • 1

相关问题