avshukan15 июн 2016 в 13:25

Как sql-запросом извлечь из базы данных информацию, которой там нет

Простой

3 мин

37K

Microsoft SQL Server * MySQL * Oracle * SQL *

Кейс

+10

Комментарии 37

maxru 15 июн 2016 в 13:51

unnest

Igogo2012 15 июн 2016 в 13:52

SELECT '1-2, 4, 6, 10, 13-15, 18...';

impetus 15 июн 2016 в 13:57

Вот ровно этим самым (получение данных, которых там нет) — игрался в конце 90-х в появившейся тогда базе (ещё на клиппере) городских телефонных номеров (квартирных) — открытие было любопытным — оказалось что в базе отсуствовали все «красивые» номера — типа 2-34-56, 3-00-00, 5-55-55
Исходником базы был «городской телефонный справочник» — да, были времена когда фамилия-имя-адрес-телефон всех жителей города публиковались. Как оказалось — публиковались не все — позвонив наугад по некоторым — выяснил, что эти телефоны живые, т.е это не горсвязь придерживала их, а как-то кому-то они доставались. (бегло просмотрел потом справочник — телефонов с последними -00-00 в нём действительно не было).
С тех пор кейс «как оценить объём и состав данных, которые в базе быть должны, но их там нет» попадается достаточно регулярно.

impetus 15 июн 2016 в 14:06

Сорри, это было лето 93-го, ещё не «конец 90-х».

avshukan 15 июн 2016 в 14:44

Собственно,
основная цель поста — как раз узнать, насколько это частный случай (или, может, с аналогичными задачами каждый второй сталкивается).
Про телефонные номера интересно, а можете привести ещё пару примеров, если уж кейсы регулярные?

impetus 15 июн 2016 в 19:17

Ну это общий кейс, а не sql… Самое простое — найти билет (на поезд), если билетов нет. Особенность РЖД и подобных систем — долго держат бронь с первого к большому городу полустанка, напр для Киева это может быть Дарница, Бровары, Бахмач, Конотоп — и если с самого Киева в Москву билетов наглухо нет — то с указанных пунктов они вполне могут быть, и, что самое удивительное, будут в системе и билеты ДО них — и вполне можно в той же кассе купить на один и тот же поезд-вагон-место — цепочку Киев-Бахмач + Бахмач-Москва. (а если разбивку сделать ещё раз в Белгороде — то выйдет ещё и дешевле).
Тут главное в голове иметь зарубку, что «если в (какой-либо) системе чего-то нет — это не значит, что этого нет вообще», типа «в каждой пустныне есть оазис… Но не каждый верблюд может его найти»
А уж как в одной нефтелавке несколько скважин в системе потеряли… (о чём очень долго и не догадывались)…
Ещё народ регулярно камеры наблюдения теряет — и тоже с обоих концов поиски забавные — что те, у кого камер живых меньше. чем вроде инсталлированных не сразу спохватываются, что наоборот, на местности — висит на верхотуре камера, питается чуть ли не от освещения, поток гонит по вай-фаю, чья, когда, кем поставлена — неведомо, арендаторы открещиваются — не наша мол…

avshukan 16 июн 2016 в 09:18

Спасибо, познавательно

ySky 15 июн 2016 в 19:39

На предыдущем месте работы (музей) часто возникала необходимость узнать, какие инвентарные номера (на которых строилась логика всей работы) пропущены. Решение было практически таким же, как вы его представили (единственное различие в том, что номера могли иметь вид [some_letters] 12345 [[start_number][letter]][-][[end_number][letter]], в квадратных скобках — опциональные блоки).
К счастью, существовала база, в которой были перечислены промежутки этих номеров (в виде [some_letters] 12345 [start-end]).
Запрос, правда, в итоге был сложнее, потому что появилось ещё множество кейсов для проверки (пользователи вводили крайне разнящиеся данные, да и, в конце концов, выяснилось, что релевантнее искать не пропущенные номера, а номера, для которых сделали сразу несколько записей).

avshukan 19 июн 2016 в 12:58

Да, я рассматривал простейший случай (-:

michael_v89 15 июн 2016 в 17:06

С использованием переменных можно попроще сделать. В принципе, можно и без 3-го уровня вложенности обойтись, но так понятнее выглядит.

SELECT GROUP_CONCAT(__range)
FROM (
    SELECT IF(__from = __to, __from, CONCAT(__from, '-', __to)) AS __range
    FROM (
        SELECT  (@n + 1) AS __from,  (id - 1) AS __to,  @n := id AS __change
        FROM (
            SELECT id, @n := 0 FROM test ORDER BY id
        ) t
    ) t2
    WHERE __to >= __from
    
    UNION
    
    SELECT CONCAT((@n + 1), '...') AS __range
) t3

m8rge 16 июн 2016 в 18:04

В чем смысл двух знаков подчеркивания в начале переменных?

michael_v89 16 июн 2016 в 18:10

'from' и 'to' — ключевые слова

m8rge 17 июн 2016 в 04:05

А __range, __change?

michael_v89 17 июн 2016 в 04:17

И они тоже)

avshukan 22 июн 2016 в 12:17

Спасибо, надо действительно почитать про переменные.

Кстати на пустой таблице скрипт неверно отрабатывает.

michael_v89 22 июн 2016 в 15:25

Да, не подумал. Раз уж тут коллекция вариантов, то вот исправленный и более короткий (для MySQL):

SELECT CONCAT_WS(',',
    GROUP_CONCAT( IF(f = t, f, CONCAT(f, '-', t)) ),
    CONCAT((@n + 1), '...')
) AS result
FROM (
    SELECT  (@n + 1) AS f,  (id - 1) AS t,  @n := id AS c
    FROM (SELECT @n := 0) t, test
    ORDER BY id
) t2
WHERE t >= f

avshukan 26 июн 2016 в 10:41

Хороший запрос получился.

Переменные — это здорово! Столько возможностей открывается!

Чувствую себя диким человеком (-:

VolCh 26 июн 2016 в 11:24

Недавно пришёл к выводу (достаточно дорогой ценой), что лучше user-defined variables MySQL использовать исключительно для эмуляции оконных функций. Чрезмерное их употребление очень снижает переносимость на другие SQL.

avshukan 23 июл 2016 в 01:01

Мне кажется, это классическая дилемма производительности и универсальности. (-:
Либо пишешь запрос универсальным — и тогда он легко переносим, но не является оптимальным для каждой СУБД, на которую переносится.
Либо пишешь под каждую СУБД свою версию запроса, используя те специфические возможности по оптимизации, которые отличают из друг от друга (-:
Это, наверно, не касается, только самых простых запросов (-:

VolCh 15 июн 2016 в 17:10

Чаще задача попадается получения первого свободного номера, а не всех диапазонов

avshukan 16 июн 2016 в 09:21

Согласен, но про первый свободный номер было неинтересно писать

burrdarr 16 июн 2016 в 09:15

А что мешает организовать проверку наличия всех необходимых данных при их вводе, чтобы предупредить проблему?

avshukan 16 июн 2016 в 09:23

Особенности бумажного документооборота.
Если бы номер формировался при вводе в БД, тогда вы правы. Но, увы, это не всегда зависит от вас.

VolCh 16 июн 2016 в 16:08

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

KonstantinSoloviov 16 июн 2016 в 15:04

Для тех у кого, как и у меня, не получилось с Oracle:

Функцию LISTAGG можно использовать в следующих версиях Oracle / PLSQL:
Oracle 12c, Oracle 11g Release 2

Насколько я знаю задача объединения полей выборки в одну строку не может быть решена в стандартном SQL без привлечения специальных функций агрегирования типа LISTAGG.

«В столбик» же задачу можно решить как-то так:

DROP TABLE TEST;
CREATE TABLE Test ( Id NUMBER NOT NULL );

INSERT INTO Test(id) VALUES (3);
INSERT INTO Test(id) VALUES (5);
INSERT INTO Test(id) VALUES (7);
INSERT INTO Test(id) VALUES (8);
INSERT INTO Test(id) VALUES (9);
INSERT INTO Test(id) VALUES (11);
INSERT INTO Test(id) VALUES (12);
INSERT INTO Test(id) VALUES (16);
INSERT INTO Test(id) VALUES (17);

select nid from
(select ROWNUM nid from Test, Test where ROWNUM <= (select max(id) from Test)+1)
left join Test T on T.id = nid
where T.id is null
order by nid

Отмазка: способ не работает для сильноразреженных таблиц в которых количество пропусков больше чем количество строк в квадрате. Но в базе ведь всегда найдется таблица в которой много строк, тогда ее можно использовать как 'опорную' во внутреннем запросе. )

Vapaamies 16 июн 2016 в 21:59

В предыдущих версиях Oracle listagg можно реализовать как собственную агрегатную функцию, на сайте Тома Кайта даже была примерная реализация. Для коротких данных, вроде как тут, можно попробовать обойтись генерацией вложенного дерева и sys_connect_by_path.

avshukan 17 июн 2016 в 08:55

http://apps-oracle.ru/stragg/

yizraor 16 июн 2016 в 20:55

заголовок громкий :)
но задача (нахождение пропусков в нумерации) не такая уж и малоизвестная.

кстати, насчет MySQL не знаю, а в ORACLE есть функции LAG() и LEAD(), с которыми можно бы избавиться от лишних подзапросов.

под рукой сейчас только MSSQL, так что на нём набросал вот такой вариант, который идет в одно чтение таблицы (и вообще без сортировки, если поле «id» — первичный ключ):

select rlist =
(
	select "text()" =
		iif(id1 < id2, convert(varchar(15), id1) +
		iif(id1 < (id2 - 1), '-' + convert(varchar(15), id2 - 1), ''), '') +
		iif(id3 is null, iif(id1 < id2, ', ', '') + convert(varchar(15), id2 + 1) + '...', ', ')
	from
	(
		select
			id1 = isnull(lag(id) over (order by id), 0) + 1,
			id2 = id,
			id3 = lead(id) over (order by id)
		from test
	) t
	where ( id1 < id2 ) or ( id3 is null )
	order by id2
	for xml path('')
)

avshukan 22 июн 2016 в 12:19

Спасибо, добавил скрипт в статью

asmm 17 июн 2016 в 08:41

Более компактный и менее ресурсоёмкий запрос для MySQL:
DROP TABLE IF EXISTS `Test`;
CREATE TABLE IF NOT EXISTS `Test` (`id` int(6) NOT NULL/* PRIMARY KEY*/);
INSERT INTO `Test` (`id`) VALUES (3), (5), (7), (8), (9), (11), (17), (12), (16);

SET @prev_id := 0;
SELECT CONCAT(GROUP_CONCAT(miss_num), ',', MAX(id) + 1, '...') miss_num
FROM (
SELECT t.*
, CASE
WHEN prev_id_plan = prev_id_fact THEN NULL
WHEN prev_id_plan = prev_id_fact + 1 THEN prev_id_plan
ELSE CONCAT(prev_id_fact + 1, '-', prev_id_plan)
END miss_num
FROM (
SELECT id — 1 prev_id_plan, @prev_id prev_id_fact, @prev_id := id id
FROM `Test` t
ORDER BY t.id
) t
) t
;

DROP TABLE `Test`;

asmm 17 июн 2016 в 09:23

Более лаконичная версия с расширенным функционалом, можно задавать начальный номер

SELECT CONCAT(IFNULL(CONCAT(GROUP_CONCAT(miss_num), ','), '')
, IFNULL(MAX(id) + 1, @start_num)
, '...'
) miss_num
FROM (
SELECT @prev_id prev_id
, CASE
    WHEN @prev_id + 1 = id THEN NULL
    WHEN @prev_id + 2 = id THEN @prev_id + 1
    ELSE CONCAT(@prev_id + 1, '-', id - 1)
  END miss_num
, @prev_id := id id
FROM (SELECT @start_num := 1 start_num, @prev_id := @start_num - 1 prev_id) p
, `Test` t
WHERE t.id >= p.start_num
ORDER BY t.id
) t

xtender 18 июн 2016 в 22:04

У меня так получилось:

   select
      concat(
         ifnull(group_concat(if(id-p=1,'',concat(p+1,if(id-p=2,'',concat('-',id-1)),',')) separator ''),'')
        ,max(id)+1,'...')
      s
   from (
         select @p p
               ,@p := id id
         from (
               select 0 id, @rn:=0 rn from dual
               union all
               select id, @rn:=@rn+1  from test
              ) v1
         ) v2

но я не спец по mysql

avshukan 22 июн 2016 в 12:19

Спасибо, добавил скрипт в статью

sergeypid 17 июн 2016 в 09:11

Заголовок наше все. Мой любимый заголовок на новостном сайте Американка расчесала голову до мозга

xtender 17 июн 2016 в 23:25

В Oracle все это можно сделать за один скан.
Объединение диапазонов вообще древняя и широкоизвестная задача и делается кучей разных способов, например так:

select
   listagg(decode(id1,id2,to_char(id1),to_char(id1)||'-'||to_char(id2)),',') 
      within group(order by id1)s -- просто форматирование
from (
     select min(id) id1,max(id) id2  -- это у нас и есть схлопнутые диапазоны, т.е. типа (1,3); (4,4); (7,10)
     from (select id,row_number()over(order by id) rn
           from test)
     group by id - rn -- группируем по дельте от ряда
     )

Соответственно, чтобы найти пропущенные/недостающие, достаточно внести небольшое изменение — после «схлопывания» диапазонов группировкой, вывести диапазоны между ними, используя lead или lag и +-1:

select
   listagg(decode(id2
                 ,id1 ,to_char(id1)
                 ,null,to_char(id1)||'...'
                 ,to_char(id1)||'-'||to_char(id2)
                 )
           ,',') 
      within group(order by id1)s -- просто требуемое форматирование
from (
      select
         id2+1 id1 -- конец предыдущего + 1
        ,lead(id1) over(order by id1)-1 id2 --начало следующего - 1
      from (
           select min(id) id1,max(id) id2 -- это у нас и есть схлопнутые диапазоны, т.е. типа (1,3); (4,4); (7,10)
           from (select 0 id, 0 rn from dual -- добавляем "начало"
                 union all
                 select id,row_number()over(order by id) rn
                 from test)
           group by id - rn
           )
)

xtender 17 июн 2016 в 23:36

Если так дотошно не показывать-комментировать, то получится очень кратко:

select listagg(id1||decode(id2
                            ,id1 ,null
                            ,null,'...'
                            ,'-'||id2)
              ,',') 
         within group(order by id1)s
from (select max(id)+1                            id1
            ,lead(min(id)) over(order by min(id)) id2
      from (select 0 id, 0 rn from dual
            union all
            select id,row_number()over(order by id) rn from test)
      group by id - rn)

avshukan 22 июн 2016 в 12:20

Спасибо за комментарий, добавил ваш скрипт в статью

Зарегистрируйтесь на Хабре, чтобы оставить комментарий