"ORDER BY... USING" в PostgreSQL

Предложение ORDER BY описывается в документации PostgreSQL как:

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

Может ли кто-нибудь дать мне несколько примеров использования USING operator? Можно ли получить чередующийся порядок результатов?

Ответ 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)... где somefunc отображает значения соответствующим образом. Поскольку это работает с каждой базой данных, это также самый распространенный способ. Для простых вещей вы даже можете написать выражение вместо пользовательской функции.

Переключение передач вверх

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)

Вуаля - отсортировано по y.

Подводя итог, можно сказать: ORDER BY... USING - интересный взгляд под капотом PostgreSQL. Но ничего, что вам потребуется в ближайшее время, если вы не работаете в очень специфических областях технологий баз данных.

Другой пример можно найти в документации Postgres. с исходным кодом для примера здесь и здесь. В этом примере также показано, как создавать операторы.

Ответ 2

Примеры:

CREATE TABLE test
(
  id serial NOT NULL,
  "number" integer,
  CONSTRAINT test_pkey PRIMARY KEY (id)
)

insert into test("number") values (1),(2),(3),(0),(-1);

select * from test order by number USING > //gives 3=>2=>1=>0=>-1

select * from test order by number USING < //gives -1=>0=>1=>2=>3

Итак, это эквивалентно desc и asc. Но вы можете использовать собственный оператор, что существенная особенность USING

Ответ 3

Хорошие ответы, но они не упоминали об одном реальном ценном случае для ИСПОЛЬЗОВАНИЯ.

Когда вы создаете индекс с семейством операторов не по умолчанию, например varchar_pattern_ops (~ > ~, ~ < ~, ~ >= ~...) вместо <, > , > =... тогда, если вы выполняете поиск основанный на индексе, и вы хотите использовать индекс по порядку по условию, вам нужно указать USING с соответствующим оператором.

Это можно проиллюстрировать таким примером:

CREATE INDEX index_words_word ON words(word text_pattern_ops);

Давайте сравним два запроса:

SELECT * FROM words WHERE word LIKE 'o%' LIMIT 10;

SELECT * FROM words WHERE word LIKE 'o%' ORDER BY word LIMIT 10;

Разница между их исполнением почти 100 раз при 500K словах DB! А также результаты могут быть неверными в пределах не-C.

Как это могло произойти?

При выполнении поиска с предложениями LIKE и ORDER BY вы фактически совершаете этот вызов:

SELECT * FROM words WHERE word ~>=~ 'o' AND word ~<~'p' ORDER BY word USING < LIMIT 10;

Ваш индекс создан с использованием оператора ~ < ~, поэтому PG не может использовать данный индекс в заданном предложении ORDER BY. Чтобы все было сделано, правильный запрос должен быть переписан в эту форму:

SELECT * FROM words WHERE word ~>=~ 'o' AND word ~<~'p' ORDER BY word USING ~<~ LIMIT 10;

или

SELECT * FROM words WHERE word LIKE 'o%' ORDER BY word USING ~<~ LIMIT 10;

Ответ 4

Дополнительно можно добавить ключевое слово ASC (по возрастанию) или DESC (убывающей) после любого выражения в предложении ORDER BY. Если не указанный, ASC принимается по умолчанию. В качестве альтернативы, имя оператора заказа может быть указано в предложении USING. оператор заказа должен быть не более или более чем членом некоторых Семейство операторов B-дерева. ASC обычно эквивалентна ИСПОЛЬЗОВАНИЮ < и DESC обычно эквивалентен USING > .

PostgreSQL 9.0

Может показаться что-то вроде этого, я думаю (у меня нет postgres, чтобы проверить это прямо сейчас, но проверим позже)

SELECT Name FROM Person
ORDER BY NameId USING >