“通过...使用...订购”； PostgreSQL中的子句 [英] "ORDER BY ... USING" clause in PostgreSQL

问题描述

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

The ORDER BY clause is decribed in the PostgreSQLdocumentation as:

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

有人可以给我一些使用使用运算符？

Can someone give me some examples how to use the USING operator? Is it possible to get an alternating order of the resultset?

推荐答案

一个简单的例子是：

> SELECT * FROM tab ORDER BY col USING <

但这很无聊，因为传统的<$ c $是您无法获得的c> ORDER BY col ASC 。

But this is boring, because this is nothing you can't get with the traditional ORDER BY col ASC.

标准目录中也没有提到奇怪的比较功能/运算符。您可以获得它们的列表：

Also the standard catalog doesn't mention anything exciting about strange comparison functions/operators. You can get a list of them:

    > 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 等，还有一些用于数组和向量的等等。这些运算符都无法帮助您获得自定义订购。

You will notice, that there are mostly < and > functions for primitive types like integer, date etc and some more for arrays and vectors and so on. None of these operators will help you to get a custom ordering.

在大多数需要自定义订购的情况下，您可以使用类似的方法 ...按somefunc（tablecolumn）排序... 其中 somefunc 会适当地映射值。因为这适用于每个数据库，所以这也是最常见的方法。对于简单的事情，您甚至可以编写表达式而不是自定义函数。

In most cases where custom ordering is required you can get away using something like ... ORDER BY somefunc(tablecolumn) ... where somefunc maps the values appropriately. Because that works with every database this is also the most common way. For simple things you can even write an expression instead of a custom function.

切换齿轮

订购...使用在几种情况下很有意义：

ORDER BY ... USING makes sense in several cases:

• 排序非常罕见，以至于 somefunc 技巧不起作用。


• 您使用非原始类型（例如点，圆圈或虚数），并且您不想在查询中重复使用奇怪的计算方法。


• 要排序的数据集太大，因此甚至需要索引支持。

• The ordering is so uncommon, that the somefunc trick doesn't work.
• You work with a non-primitive type (like point, circle or imaginary numbers) and you don't want to repeat yourself in your queries with strange calculations.
• The dataset you want to sort is so large, that support by an index is desired or even required.

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

I will focus on the complex datatypes: often there is more than one way to sort them in a reasonable way. A good example is point: You can "order" them by the distance to (0,0), or by x first, then by y or just by y or anything else you want.

当然，PostgreSQL 有 点的预定义运算符：

Of course, PostgreSQL has predefined operators for point:

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

但其中的 none 都可用于默认情况下为ORDER BY （见上文）：

But none of them is declared usable for ORDER BY by default (see above):

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

点的简单运算符是和上方运算符< ^ 和> ^ 。他们只是比较了点的 y 部分。但是：

Simple operators for point are the "below" and "above" operators <^ and >^. They compare simply the y part of the point. But:

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

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

ORDER BY USING requires an operator with defined semantics: Obviously it must be a binary operator, it must accept the same type as arguments and it must return boolean. I think it must also be transitive (if a < b and b < c then a < c). There may be more requirements. But all these requirements are also necessary for proper btree-index ordering. This explains the strange error messages containing the reference to btree.

按使用顺序不仅需要定义一个运算符，还需要 operator class 和一个 operator family 。尽管一个可以仅使用一个运算符来实现排序，但PostgreSQL会尝试有效地进行排序并最小化比较。因此，即使您只指定一个运算符，其他运算符也会被使用-其他运算符必须遵守某些数学约束-我已经提到过传递性，但是还有更多。

ORDER BY USING also requires not just one operator to be defined but an operator class and an operator family. While one could implement sorting with only one operator, PostgreSQL tries to sort efficiently and minimize comparisons. Therefore, several operators are used even when you specify only one - the others must adhere to certain mathematical constraints - I've already mentioned transitivity, but there are more.

Switching Gears up

让我们定义一些合适的东西：点的运算符，仅比较 y 部分。

Let's define something suitable: An operator for points which compares only the y part.

第一步是创建一个可用于 btree 索引访问方法的自定义运算符系列。 参见

The first step is to create a custom operator family which can be used by the btree index access method. see

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

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

Next we must provide a comparator function which returns -1, 0, +1 when comparing two points. This function WILL be called internally!

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

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

Next we define the operator class for the family. See the manual for an explanation of the numbers.

    > 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

此步骤结合了多个运算符和功能，并定义了它们的关系和含义。例如，运算符1 的意思是：这是小于测试的运算符。

This step combines several operators and functions and also defines their relationship and meaning. For example OPERATOR 1 means: This is the operator for less-than tests.

现在可以在<$ c中使用运算符< ^ 和> ^ $ c>使用顺序：

Now the operators <^ and >^ can be used in 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 。

总结一下： 订购...使用是PostgreSQL幕后的有趣外观。但是除非您从事非常数据库技术的特定领域的工作，否则您很快就不需要任何东西。

To sum it up: ORDER BY ... USING is an interesting look under the hood of PostgreSQL. But nothing you will require anytime soon unless you work in very specific areas of database technology.

可以找到另一个示例。，其中包含示例此处和此处。此示例还显示了如何创建运算符。

Another example can be found in the Postgres docs. with source code for the example here and here. This example also shows how to create the operators.

这篇关于“通过...使用...订购”； PostgreSQL中的子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！