Почему порядок меняется при использовании UNION ALL в PostgreSQL?
Я пытаюсь решить этот вопрос о Hackerrank SQL (https://www.hackerrank.com/challenges/the-pads/problem?isFullScreen=true). (мое решение может быть неэффективным, неважно)
У меня есть два SQL-запроса в PostgreSQL, которые по отдельности возвращают ожидаемые результаты, но при объединении с использованием UNION ALL создают неожиданный порядок.
Первый запрос объединяет имя и первую букву профессии, упорядоченные по имени:
SELECT *
FROM (
SELECT CONCAT(Name, '(', LEFT(Occupation, 1), ')')
FROM OCCUPATIONS
ORDER BY Name
) AS sub1;
Результат ожидаемый:
Aamina(D)
Ashley(P)
Belvet(P)
Britney(P)
Christeen(S)
.
.
. Второй запрос подсчитывает профессии и объединяет результат, упорядоченный по количеству профессий и первой букве профессии:
SELECT *
FROM (
SELECT CONCAT('There are a total of ', COUNT(Occupation), ' ', LOWER(Occupation), 's.')
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY COUNT(Occupation), LEFT(Occupation, 1)
) AS sub2;
Результат второго запроса:
There are a total of 3 doctors.
There are a total of 4 actors.
There are a total of 4 singers.
There are a total of 7 professors.
Однако когда я объединяю эти два запроса с помощью UNION ALL, порядок первой части неожиданно меняется:
select dest
from (
select * from (select 1 as source, concat(Name,'(',LEFT(Occupation,1),')') as dest
from OCCUPATIONS
order by Name) as a
UNION ALL
select * from (select 2 as source, concat('There are a total of ',count(Occupation),' ',lower(Occupation),'s.') as dest
from OCCUPATIONS
group by Occupation
order by count(Occupation), left(Occupation,1)) as b
) as comb
order by source;
Общий результат:
Ashley(P)
Samantha(A)
Julia(D)
Britney(P)
There are a total of 3 doctors.
There are a total of 4 actors.
There are a total of 4 singers.
There are a total of 7 professors.
Обратите внимание, что порядок первого запроса выбора изменен. Почему это происходит ? Обратите внимание, что порядок второго запроса сохраняется.
Я хочу, чтобы результаты первого запроса сохраняли исходный порядок при объединении со вторым запросом. Как я могу этого добиться?
Подзапрос ORDER BY ничего не гарантирует.
I am trying to solve this Hackerrank SQL question ... и, как написано, ваш вопрос не имеет проблемной постановки. Внешние ссылки могут и часто ломаются со временем.
@TimBiegeleisen Вопрос уже толстый и ждет редактирования модераторами, если я добавлю описание, оно будет выглядеть более уродливым. Предложите мне, что делать, или отредактируйте по своему усмотрению.
Общий ответ на ваш вопрос заключается в том, что ORDER BY внутри подзапроса бессмысленны. Порядок не «прилипает» за пределы подзапросов.
@TimBiegeleisen Я ошибочно вставил свой старый запрос, задавая вопрос. Я обновил свой комбинированный запрос. Используя только этот запрос, я столкнулся с проблемой. Можете ли вы сказать мне, как порядок влияет на этот запрос?
@DrinkandDerive Это только предотвратит смешивание этих двух, но каждый набор строк все равно можно будет свободно переупорядочивать. Вам нужно будет добавить row_number()over(order by...), а затем order by source,row_number во внешний запрос: демо
Ответы 1
Если вы не укажете order by во внешнем запросе, его порядок будет случайным, как уже отметил @Tim Biegeleisen. Вы можете упорядочить CTE и подзапросы внутри, но пока на последнем, внешнем уровне нет order by, база данных может свободно изменять порядок строк.
Ваша идея установить связь от source до order by позже имеет смысл, но это только предотвратит смешивание двух источников, что в настоящее время маловероятно, но технически разрешено. Их внутренний порядок также необходимо передать во внешний запрос, который вы можете получить с помощью row_number() оконной функции : демо в db<>fiddle
with a as
(select 1 as source
, row_number()over(order by name) as row_number
, concat(name,'(',left(occupation,1),')') as dest
from occupations)
,b as
(select 2 as source
, row_number()over(order by count(occupation), occupation) as row_number
, concat('There are a total of '
,count(occupation),' '
,lower(occupation),'s.') as dest
from occupations
group by occupation)
,a_union_b as (select * from a union all select * from b)
select dest
from a_union_b
order by source,row_number;
Нет необходимости делать left(Occupation,1) в order by count(Occupation), left(Occupation,1) — предполагается, что строки с одинаковым количеством будут упорядочиваться в алфавитном порядке, а не только по первой букве occupation.
Это работает как в PostgreSQL, так и в Microsoft SQL Server, только последний из которых вы можете отправить для этого упражнения.
Что произойдет, если вы вынесете
ORDER BYнаружу?SELECT * FROM (...) AS sub1 ORDER BY Name UNION ALL ....