PostgreSQL中的“ RDER BY”（使用）子句

Question

PostgreSQL文档中将ORDER BY子句描述为：

ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...]

有人可以给我一些使用方法的例子USING operator吗？是否有可能获得结果集的交替顺序？

Answer 1

一个非常简单的示例是：

> SELECT * FROM tab ORDER BY col USING <

但这很无聊，因为这是传统方式无法做到的ORDER BY col ASC。

同样，标准目录中没有提及任何奇怪的比较功能/运算符。您可以获得它们的列表：

    > SELECT amoplefttype::regtype, amoprighttype::regtype, amopopr::regoper 
      FROM pg_am JOIN pg_amop ON pg_am.oid = pg_amop.amopmethod 
      WHERE amname = 'btree' AND amopstrategy IN (1,5);

您会注意到，主要有<和>函数用于基本类型，例如integer，date等等，还有更多函数用于数组和向量等等。这些操作员都无法帮助您获得自定义订购。

在 大多数 情况下，需要自定义排序，您可以使用诸如... ORDER BY somefunc(tablecolumn) ...wheresomefunc适当映射值之类的方法。因为这适用于每个数据库，所以这也是最常见的方法。对于简单的事情，您甚至可以编写表达式而不是自定义函数。

换档

ORDER BY ... USING 在几种情况下有意义：

• 排序非常罕见，因此somefunc技巧不起作用。
• 你与非基本类型（如工作point，circle或虚数），并且你不想重复自己在陌生的计算查询。
• 您要排序的数据集非常大，因此甚至需要索引的支持。

我将重点介绍复杂的数据类型：通常，有多种方法可以合理地对它们进行排序。一个很好的例子是point：您可以按到（0,0）的距离对它们进行“排序”，或者先按
x进行 排序，然后按 y 或仅按 y 或任何其他所需的值对它们进行“排序” 。

当然，PostgreSQL为以下对象 提供了 预定义的运算符point：

    > CREATE TABLE p ( p point );
    > SELECT p <-> point(0,0) FROM p;

但是默认情况下，它们都 没有 声明可使用ORDER BY（请参见上文）：

    > SELECT * FROM p ORDER BY p;
    ERROR:  could not identify an ordering operator for type point
    TIP:  Use an explicit ordering operator or modify the query.

对于简单的操作符point是“下面”和“上面”运营商<^和>^。他们只是简单地比较y了重点。但：

    >  SELECT * FROM p ORDER BY p USING >^;
    ERROR: operator > is not a valid ordering operator
    TIP: Ordering operators must be "<" or ">" members of __btree__ operator families.

ORDER BY USING需要具有定义的语义的运算符：显然，它必须是二进制运算符，它必须接受与参数相同的类型，并且必须返回布尔值。我认为它也必须是可传递的（如果a
<b和b <c则a <c）。可能还有更多要求。但是，所有这些要求对于正确的 btree
-index排序也是必需的。这解释了奇怪的错误消息，其中包含对 btree 的引用。

ORDER BY USING还不仅需要定义 一个运算符 ，而且还需要 一个运算 符类 和一个 运算符系列 。虽然一个人 只能
用一个运算符 来 实现排序，但是PostgreSQL尝试有效地排序并最小化比较。因此，即使仅指定一个运算符，也会使用多个运算符-
其他运算符必须遵守某些数学约束-我已经提到过传递性，但还有更多。

换档

让我们定义一个合适的东西：一个只比较y零件的点运算符。

第一步是创建可由 btree
索引访问方法使用的自定义运算符系列。看

    > CREATE OPERATOR FAMILY xyzfam USING btree;   -- superuser access required!
    CREATE OPERATOR FAMILY

接下来，我们必须提供一个比较器函数，当比较两个点时返回-1、0，+ 1。此函数 将 在内部调用！

    > CREATE FUNCTION xyz_v_cmp(p1 point, p2 point) RETURNS int 
      AS $$BEGIN RETURN btfloat8cmp(p1[1],p2[1]); END $$ LANGUAGE plpgsql;
    CREATE FUNCTION

接下来，我们为家庭定义操作员类别。有关数字的说明，请参见手册。

    > CREATE OPERATOR CLASS xyz_ops FOR TYPE point USING btree FAMILY xyzfam AS 
        OPERATOR 1 <^ ,
        OPERATOR 3 ?- ,
        OPERATOR 5 >^ ,
        FUNCTION 1 xyz_v_cmp(point, point) ;
    CREATE OPERATOR CLASS

此步骤结合了多个运算符和功能，并定义了它们的关系和含义。例如，OPERATOR 1表示：这是less-than测试的运算符。

现在，运算符<^和>^可以用于ORDER BY USING：

> INSERT INTO p SELECT point(floor(random()*100), floor(random()*100)) FROM generate_series(1, 5);
INSERT 0 5
> SELECT * FROM p ORDER BY p USING >^;
    p    
---------
 (17,8)
 (74,57)
 (59,65)
 (0,87)
 (58,91)

Voila-按 y 排序。

总结一下： ORDER BY ... USING在PostgreSQL的幕后， 这 是一个有趣的外观。但是除非您在数据库技术的 非常
特定的领域中工作，否则您很快将不需要任何东西。

可以在Postgres文档中找到另一个示例。在这里和这里的示例源代码。此示例还显示了如何创建运算符。