Oracle, типичные задачи SQL. Гарантированный выбор

На носу выборы, а это значит, что сегодня мы будем выбирать губернатора. А точнее назначать барьер прохождения кандидатов в следующий тур. Но сначала надо определиться с терминами.

Что такое гарантированный выбор в SQL? Допустим, что в условии запроса к таблице выполняется сравнение какого-либо поля с какой-нибудь переменной. В зависимости от значения этой переменной запрос может вернуть строки из таблицы, а может и не вернуть их вовсе. Если выпадает такое значение переменной, что строки из таблицы не возвращаются, то для этого случая надо специально сгенерировать заранее определенный левый результат. То есть в любом случае общий запрос должен гарантированно что-нибудь да вернуть. Сам термин взят отсюда. Однако задача усложняется тем (а может и наоборот, упрощается), что вместо одной простой ячейки со значением, нам нужно гарантировано вернуть полноценную строку.

Привожу данные центризбиркома. Первый тур голосования закончился с такими результатами

ID	Имя кандидата	Профессия	Количество голосов
1	Неподкупный Аморал Чисторукович	прокурор	9867
2	Эффективный Бюджет Освоилович	бизнесмен	8650
3	Правдивый Чтодадут Написайлович	редактор газеты	745
4	Благообразная Люцифера Феоктистовна	настоятельница	234
5	Хренсгоры Ктотакой Никтонезнаевич	учитель сельской школы	3

create table election 
as  
with t (id, name, profession, votes) as (
select 1, 'Неподкупный Аморал Чисторукович', 'прокурор', 9867 from dual union all
select 2, 'Эффективный Бюджет Освоилович', 'бизнесмен', 8650 from dual union all
select 3, 'Правдивый Чтодадут Написайлович', 'редактор газеты', 745 from dual union all
select 4, 'Благообразная Люцифера Феоктистовна ', 'настоятельница', 234 from dual union all
select 5, 'Хренсгоры Ктотакой Никтонезнаевич', 'учитель сельской школы', 3 from dual
)
select * from t;

alter table election add primary key (id);

Начальный запрос, определяющий выход кандидатов в следующий тур, предельно прост:

select * from election where votes > :bound

Предположим, что проходной барьер равен 8000 голосов. Сбиндив это число с :bound, получаем

ID	NAME	PROFESSION	VOTES
1	Неподкупный Аморал Чисторукович	прокурор	9867
2	Эффективный Бюджет Освоилович	бизнесмен	8650

Но что если проходной барьер 10 000, то есть больше, чем максимально набранное количество голосов? Тогда очевидно, что из вышеозначенных кандидатов в следующий тур не проходит никто. В этом случае устанавливается диктатура и губернатором области автоматически становится кот Полковник. Вот некоторые из способов его назначения:

Способ 1. UNION ALL таблицы с агрегированной собой же

with t as (
  select *
  from election 
  where votes > :bound
)
select id, name, profession, votes from t 
union all
select 0, 'Полковник', 'кот', null from t having count(*) = 0
order by votes desc

Способ 2. UNION ALL таблицы с DUAL

with t as (
  select *
  from election 
  where votes > :bound
)
select id, name, profession, votes 
from t
union all
select 0, 'Полковник', 'кот', null 
from dual 
where not exists (select null from t)
order by votes desc

Способ 3. LEFT JOIN таблицы с DUAL

select nvl(e.id, 0) id,
       nvl2(e.id, e.name, 'Полковник') name,
       nvl2(e.id, e.profession, 'кот') profession,
       e.votes
from dual d 
left join election e on e.votes > :bound
order by e.votes desc

для случаев, когда в таблице отсутствует уникальное NOT NULL поле

select nvl2(e.rowid, e.id, 0) id,
       nvl2(e.rowid, e.name, 'Полковник') name,
       nvl2(e.rowid, e.profession, 'кот') profession,
       e.votes
from dual d 
left join election e on e.votes > :bound
order by e.votes desc

Способ 4. Моделька с котом.

select id, name, profession, votes
from election
where votes > :bound
model
dimension by (rownum rn)
measures (id, name, profession, votes)
rules (
  name[1] = nvl2(id[1], name[1], 'Полковник'),
  profession[1] = nvl2(id[1], profession[1], 'кот'),
  id[1] = nvl(id[1], 0)
)
order by votes desc

Ниже гарантированный результат при бинде со слишком большим барьером 10 000

ID	NAME	PROFESSION	VOTES
0	Полковник	кот

Ясно, что если задать начальную планку 8000, то эти запросы также отработают корректно.

На этом пока всё. Все совпадения имен персонажей с реальными людьми считать случайными.

Решение некоторых других типичных задач SQL можно посмотреть здесь и здесь.

До новых встреч.