Нужен запрос postgresql, который возвращает нулевые значения после ненулевого значения

#ruby-on-rails #postgresql #sql-order-by

Вопрос:

Нужен запрос postgresql, который возвращает нулевое значение в конце ненулевого значения независимо от порядка возрастания / убывания.

Например: — У меня есть столбец с надписью purchase_price, имеющий значения 1, 5, 3, 0, null, null

Затем для ORDER BY purchase_price ASC он должен вернуть

 0
1
3
5
null
null

и для ORDER BY purchase_price DESC он должен возвращать

 5
3
1
0
null
null

Мне нужно обобщающее решение, которое я могу применить для 'boolean' , 'float' , 'string' 'integer' , 'date' типа данных

Ответ №1:

order by поддерживает nulls first/last . По умолчанию asc получает nulls last и desc получает nulls first . Вы можете переопределить их для каждого столбца:

 order by col1 desc nulls last, col2 desc nulls first, etc

Дополнительное примечание по этому поводу: основная причина заключается в том, что null помещаются в конец индекса btree. В PostgreSQL 9.0 order by col desc nulls last оптимизирован для разделения проверки индекса на две части (обратная проверка по ненулевым строкам, за которыми следуют нулевые строки). Это было не так в исходной версии, где это было добавлено (8.4, если не изменяет память); последняя продолжает выполнять последовательное сканирование всей таблицы с последующей быстрой сортировкой). А более старые версии вообще не поддерживают эту функцию. Так что будьте осторожны при использовании его во всем, кроме последних версий PostgreSQL.

Ответ №2:

 select * from (select * from foo where x is not null order by x desc) a union all select  * from foo where x is null;

Дешевый взлом, но вы можете выбрать ненулевые случаи в любом порядке, который вы хотите, а затем выполнить ОБЪЕДИНЕНИЕ ALL (здесь важно all) с нулевыми случаями, которое будет строго следовать впоследствии.

Ответ №3:

Я всегда делал это с помощью скрытого объединения в следующих строках:

 select *, '1' as orderCol
from table
where nullableColumn is not null
union
select *, '2' as orderCol
from table
where nullableColumn is null
order by orderCol, nullableColumn

Вам явно нужно изменить его для упорядочивания desc — например, установив значения наоборот…

Довольно общий, но не очень элегантный.