На носу выборы, а это значит, что сегодня мы будем выбирать губернатора. А точнее назначать барьер прохождения кандидатов в следующий тур. Но сначала надо определиться с терминами.
Что такое гарантированный выбор в 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 можно посмотреть здесь и здесь.
До новых встреч.
Комментарии (21)
zip_zero
22.10.2015 10:14+1Кто может подсказать реальные business cases для использования такой логики?
romy4
22.10.2015 16:56Мне кажется, Полковника Кота должен назначать back-end, а не БД. Исключительными ситуациями занимается бизнес-логика. И если б.л. перенесена в базу, то это всё равно должно обрабатываться отдельной процедурой, а не в запросе на выборку из 10000.
Alhymik
22.10.2015 18:59Как-то надо было реализовать отправку платежей от посредника (процессинговой конторы) сторонней организации, скажем мобильному провайдеру. В платежах (упрощенно) было два поля — номер телефона и поступившая на него сумма.
Отправляемых платежей могло быть сотни тысяч, а могло и не быть вовсе (временное прекращение обслуживания и т.п.).
Если платежей не было, то в отправляемом файле должна была быть хоть одна строка. Например с теми же двумя полями; «Платежей нет», 0. На стороне провайдера ПО обрабатывало такие ситуации. Смысл в том, что таким образом провайдер убеждался, что платежей действительно не было. Ведь если бы от процессинговой конторы приходил просто пустой файл — это могла бы быть ошибка его ПО, или потеря данных в ходе отправки.
CertainMan
25.10.2015 02:48Решения на PostgreSQL в принципе те же, только в запросах нет возможности использовать переменные. И конструкцию с model увидел впервые только сегодня (нет, уже вчера), поэтому вряд ли в postgres есть что-то похожее.
Способы 1 и 2 работают с минимальными изменениями — :bound нужно заменить на конкретное значение, а во втором способе плюс к этому просто удалить «from dual».
Аналог способа 3:
select coalesce(e.id, d.id), coalesce(e.name, d.name), coalesce(e.profession, d.profession), coalesce(e.votes, d.votes) from (values (0, 'Полковник', 'кот', null::integer)) d (id, name, profession, votes) left join election e on e.votes > 10000 order by e.votes desc;
или для удобочитаемости можно вынести виртуальную таблицу d из подзапроса в with:
with d (id, name, profession, votes) as ( values (0, 'Полковник', 'кот', null::integer) ) select coalesce(e.id, d.id), coalesce(e.name, d.name), coalesce(e.profession, d.profession), coalesce(e.votes, d.votes) from d left join election e on e.votes > 8000 order by e.votes desc;
«values (0, 'Полковник', 'кот', null::integer)» везде можно заменить на аналогичный select, только придется явно указать тип для текстовых полей, зато имена колонкам можно задать на месте через as. Пример с with:
with d as ( select 0 as id, 'Полковник'::text as name, 'кот'::text as profession, null::integer as votes ) select coalesce(e.id, d.id), coalesce(e.name, d.name), coalesce(e.profession, d.profession), coalesce(e.votes, d.votes) from d left join election e on e.votes > 8000 order by e.votes desc;
CertainMan
25.10.2015 04:51Везде забыл добавить алиасы столбцов:
select coalesce(e.id, d.id) as id, coalesce(e.name, d.name) as name, coalesce(e.profession, d.profession) as profession, coalesce(e.votes, d.votes) as votes ...Alhymik
25.10.2015 08:46Интересно, а в PostgreSQL нет аналога nvl2? Это вроде SQL-ный стандарт. Coalesce, я так понял, как и в Oracle, выбирает первое встретившееся не NULL значение — подойдет, если все поля таблицы NOT NULL. Если в таблице у Неподкупного затереть профессию, то вместо прокурора мы получим кота. И еще интересно, может знаете, есть ли в постгрессе аналог ораклового rowid — глобальный уникальный системный идентификатор строки (как в 3-м способе для таблиц без поля с уникальным айдишником)?
CertainMan
25.10.2015 15:00В PostgreSQL NVL2 нет — это расширение Oracle, как и NVL. Их и в стандарте нет. NVL-то ладно, она заменяется стандартной COALESCE, а вот NLV2, кроме громоздого CASE, по-моему, ничем не заменить. Кстати, про NVL2 до этого вообще не слышал. По тексту не догадался, как она работает, а в документации прочитал только что. Очень удобная функция, хотелось бы видеть подобную в стандарте. Или, может быть, я не вкурсе, и аналог в стандарте все-же есть. В любом случае в PostgreSQL аналога не нашел.
А с COALESCE да, про NULL в отдельных полях не подумал, надо менять на CASE.
Насчет rowid. В таблицах postgres семь штук системных столбцов, один из которых, oid, как раз аналог rowid, и до версии 8.1 oid в пользовательских таблицах создавался по умолчанию. Но так как это 32-битное целое, в больших и долгоживущих бд он уже не гарантирует уникальность, поэтому с версии 8.1 oid-ы есть только в системных таблицах, а в пользовательских по умолчанию не создаются. Хотя можно при создании таблицы указать WITH OIDS или вообще вернуть oid-ы по умолчанию в настройках сервера.
Но для проверки существования строки можно взять и другой системный столбец, например tableoid:
select case when e.tableoid is null then d.id else e.id end as id, case when e.tableoid is null then d.name else e.name end as name, case when e.tableoid is null then d.profession else e.profession end as profession, case when e.tableoid is null then d.votes else e.votes end as votes ...
Только теперь стало громоздко и лично я выбирал бы первый вариант :)
CertainMan
25.10.2015 15:44Экзотический вариант с применением JSON для PostgreSQL, начиная с 9.3:
select j.* from (select '{"id":0,"name":"Полковник","profession":"кот","votes":null}'::json as j) d left join election e on e.votes > 10000, json_populate_record(null::election, case when e.tableoid is null then d.j else row_to_json(e.*) end) j order by e.votes desc;CertainMan
25.10.2015 16:44Пояснения к решению.
Идея в том, чтобы сначала свернуть запись в один столбец, к которыму можно применить CASE только один раз, а после получения нужной записи развернуть её обратно по столбцам. Функция row_to_json сворачивает запись, функция json_populate_record возвращает значение композитного типа, а если функция, возвращающая композитный тип, вызвана в секции FROM, то postgres разворачивает результат по отдельным столбцам. Но так получается только с функциями. Если обернуть функцию в подзапрос, то есть, вместо json_populate_record(...) написать LATERAL (SELECT json_populate_record(...)), запись не развернётся, а будет выведена как композитный столбец.
Запись можно было свернуть в столбец и без привлечения json, например, «SELECT ROW(election.*) AS e_record FROM election» выведет записи из election в один композитный столбец e_record. То есть, для того, чтобы свернуть запись можно использовать выражение ROW на месте функции row_to_json. Но вот функции, которая просто возвращала бы запись и которую можно было бы вызвать в секции FROM, чтобы запись развернулась, я не нашел.
Не уверен, что изложил понятно. Сам, наверное, не понял бы, если бы не был в теме :)
CertainMan
25.10.2015 19:52Теперь без json.
Дошло, что для разворачивания записи прекрасно подойдет та же coalesce — она может принять аргумент любого типа (в данном случае типа record) и вернуть его без изменений, а это именно то, что нужно:
select r.* from (values (0, 'Полковник', 'кот', null::integer)) d left join election e on e.votes > 10000, coalesce(case when e.tableoid is null then row(d.*) else row(e.*) end) r (id integer, name text, profession text, votes integer) order by e.votes desc;
Alhymik
26.10.2015 10:29CertainMan, возможно придется работать с постгресом, все это интересно, наверняка пригодится, спасибо.
whitepen
Увлечение трех-этажными конструкциями oracle, равно как и увлечение oracle формами имеет разумные пределы. Чаще просто пишем на plsql простой if и все. Если конструкция слишком сложна — от нее следует отказаться вовсе.
romy4
Это совет никогда не пользоваться сложными конструкциями?
whitepen
Что одному сложно, то другому просто. Каждый сам решает. Революционеры говорят, что мы вообще не будет структуризировать данные в базе, а будем кидать в кучу входящие сообщения — дата, источник, текстовое поле. Тупизна побеждает разум, берет не уменьем, а числом процессоров. А данные в куче на C++ обрабатываются на порядки быстрее, чем в оракле. Десяток мелких таблиц каждый с каждым дают астрономические итерации, а в куче вообще в оперативную память помещаются.
romy4
Не могу вам плюсик поставить, но совершенно согласен!
Alhymik
В эту минуту где-то заплакал один маленький Том Кайт.
В Oracle для профессионалов на эту тему расписано, что к чему.
Том Кайт авторитет, но отчасти я с Вами согласен. Иногда приходится грешить plsql-ем в угоду удобочитаемости.
whitepen
Один оператор — одна транзакция, как хорошо. Транзакция — дело темное, изучить ее невозможно, а предвидеть ваще нельзя. Вот напишет оракл новую версию, вот достанет тестовые скрипты от 1975 — вот и оттестировали во всех режимах. Как оно работает — никто не знает.
Мы люди простые, у нас вечером база копируется в «заднюю», а на задней жужжит аналитика без транзакций и изменения базы. Дешево и сердито.