你能否在ORDER BY子句中使用自定义的Postgres比较函数？

Question

在Python中，我可以编写一个排序比较函数，该函数返回集合{-1, 0, 1}中的一个元素，并将其传递给排序函数，例如：

sorted(["some","data","with","a","nonconventional","sort"], custom_function)

这段代码将根据我在函数中定义的排序规则对序列进行排序。

在Postgres中我能做到同样的操作吗？

例如：

SELECT widget FROM items ORDER BY custom_function(widget)

编辑：欢迎提供示例和/或文档指针。

- Sean Woods

---

可以，但不是很直接：https://dev59.com/42w05IYBdhLWcg3wfx40 - Nick Barnes

这更多是一个适合dba.stackexchange的问题。 - Nico Van Belle

使用Nico的提示（在下面链接中），以及其他解释。 - Peter Krauss

Answer 1

是的，你可以这样做，甚至可以创建一个功能索引来加速排序。

编辑：简单例子：

CREATE TABLE foo(
    id serial primary key,
    bar int
);
-- create some data
INSERT INTO foo(bar) SELECT i FROM generate_series(50,70) i;
-- show the result
SELECT * FROM foo;

CREATE OR REPLACE FUNCTION my_sort(int) RETURNS int 
LANGUAGE sql 
AS
$$
    SELECT $1 % 5; -- get the modulo (remainder)
$$;
-- lets sort!
SELECT *, my_sort(bar) FROM foo ORDER BY my_sort(bar) ASC;

-- make an index as well:
CREATE INDEX idx_my_sort ON foo ((my_sort(bar)));

这份手册充满了如何使用您自己的函数的示例，只需开始尝试。

• SQL：http://www.postgresql.org/docs/current/static/xfunc-sql.html
• PL/pgSQL：http://www.postgresql.org/docs/current/static/plpgsql.html

- Frank Heikens

---

我也这么想，很抱歉问了一个相当“显然”的问题，但是……我该怎么做呢？文档和谷歌似乎没有给我明显的答案。能提供更多细节/示例将不胜感激。谢谢！ - Sean Woods

谢谢你提供额外的例子。不过，你的函数只接受一个变量。在Python中，sort函数需要两个变量。如果变量1应该排在变量2之前，它返回-1。如果它们相等，则返回0；如果变量2应该排在变量1之前，则返回1。我仍然没有看到这里的等价物。 - Sean Woods

就像我所说的，这完全取决于你的想象力，PostgreSQL 没有（很多）限制。如果您需要一个拥有 2 个参数的函数，只需创建一个即可。如果您需要 10 个参数，那就去做吧。开始使用 pl/pgsql 并为您的比较构建 if-else 结构。 - Frank Heikens

我觉得你没有理解要点。肖恩•伍兹说，Python中的函数接受两个元素并进行比较。在PostgreSQL中，则需要输入两行数据进行比较并返回哪个更大。你是正确的，可以创建一个函数用于排序，它接受两个参数，但是我认为这两个参数来自同一行，而不是两行不同的数据。 - Rauni Lillemets

Rauni是正确的，@FrankHeikens的解决方案并没有完全回答问题。换句话说，这个答案中的函数不是一个比较函数，而是一个将值转换为在使用内置比较器进行排序时给出所需顺序的函数。 - EricS

Answer 2

我们可以通过使用名称来避免关于排序方法的混淆：

- 标准 SQL `select * from t order by f(x)` 语句的“分数函数”。 - Python 的排序数组方法中的“比较函数”（问题文本中称为“排序函数”）。

PostgreSQL 的 `ORDER BY` 子句有三种机制进行排序：

- 标准排序，使用“分数函数”，也可以与索引一起使用。 - 特殊“标准字符串比较替代方案”，根据配置的排序规则进行排序（仅适用于 `text`、`varchar` 等数据类型）。 - `ORDER BY ... USING` 子句。参见此问题或官方文档示例。例如：`SELECT * FROM mytable ORDER BY somecol USING ~<~`，其中 `~<~` 是一个运算符，它嵌入了一个“比较函数”。

在关系型数据库管理系统中，“标准方式”可能不像 Python 的标准方式那样，因为索引是关系型数据库管理系统的目的，而使用分数函数进行索引更加容易。

回答该问题：

- 直接解决方案。没有直接的方法使用用户定义函数作为“比较函数”，就像 Python 或 JavaScript 等语言中的“sort 方法”一样。 - 间接解决方案。您可以在一个“用户定义的操作符”中使用一个“用户定义的比较函数”，并使用一个“用户定义的操作符类”对其进行索引。参见 PostgreSQL 文档：

- 使用“比较函数”的 `CREATE OPERATOR`； - 可以进行索引的 `CREATE OPERATOR CLASS`。

解释“比较函数”：

在 Python 中，比较函数如下所示：

def compare(a, b):
    return 1 if a > b else 0 if a == b else -1

比较函数比分数函数使用更少的CPU。同时，当分数函数未知时，它也有助于表达顺序。

详见：

• C语言请参考https://www.gnu.org/software/libc/manual/html_node/Comparison-Functions.html
• Javascript请参考https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/sort#Description

其他典型的比较函数

维基百科的例子用于比较元组：

function tupleCompare((lefta, leftb, leftc), (righta, rightb, rightc))
    if lefta ≠ righta
        return compare(lefta, righta)
    else if leftb ≠ rightb
        return compare(leftb, rightb)
    else
        return compare(leftc, rightc)

在 JavaScript 中：

function compare(a, b) {
  if (a is less than b by some ordering criterion) {
    return -1;
  }
  if (a is greater than b by the ordering criterion) {
    return 1;
  }
  // a must be equal to b
  return 0;
}

以下是PostgreSQL文档中的C++示例：

complex_abs_cmp_internal(Complex *a, Complex *b)
{
    double      amag = Mag(a),
                bmag = Mag(b);

    if (amag < bmag)
        return -1;
    if (amag > bmag)
        return 1;
    return 0;
}

- Peter Krauss

Answer 3

你可以像这样做：

你可以这样做

。

SELECT DISTINCT ON (interval_alias) *,
  to_timestamp(floor((extract('epoch' FROM index.created_at) / 10)) * 10) AT
  TIME ZONE 'UTC' AS interval_alias
  FROM index
  WHERE index.created_at >= '{start_date}'
  AND index.created_at <= '{end_date}'
  AND product = '{product_id}'
  GROUP BY id, interval_alias
ORDER BY interval_alias;

首先，您需要使用AS定义参数作为您的排序列。它可以是函数或任何SQL表达式。然后将其设置为ORDER BY表达式即可完成！在我看来，这是执行此类排序的最流畅方法。

- Number16BusShelter