Типичные проблемы

Нумерация

Согласно реляционной теории в таблице не может быть одинаковых строк. И хотя реализации допускают построение таблиц, не имеющих первичного ключа, и, как следствие, допускающих наличие одинаковых строк, следует, на наш взгляд, отнести эту ситуацию к ошибкам в проектировании. Кроме того, таблица, не имеющая первичного ключа или уникального индекса, не является обновляемой. Последнее заключение вполне естественно, так как система не имеет информации о том, какой из дубликатов предпочесть.

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

Чтобы пояснить сказанное, рассмотрим следующий запрос

Консоль

Выполнить

SELECT id_psg 
FROM pass_in_trip;

SELECT id_psg FROM pass_in_trip;

    Схема

Нет схемы

Итак, нам нужно перенумеровать пассажиров, которые могут повторяться. Зададимся для начала порядком, в котором их нужно перенумеровать. Пусть этот порядок соответствует сортировке по трем полям — дате полета, идентификатору пассажира и номеру рейса (по возрастанию).

Чтобы свести задачу к ранее рассмотренной (а это возможно, так как три перечисленных поля представляют собой первичный ключ), сконструируем столбец, который объединял бы информацию из перечисленных полей. Поскольку поля имеют разные типы данных, приведем их к единому символьному представлению и выполним конкатенацию.

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

Однако для правильности сортировки нужно «односимвольных» пассажиров записывать с лидирующим нулем — 01, 09 и т. д. Иначе пассажир с номером 10 будет предшествовать, скажем, пассажиру с идентификационным номером 2. Выполним соответствующие преобразования:

Преобразования 8.2.2

Дата: CAST(date AS CHAR(11))
Номер рейса: CAST(trip_no AS CHAR(4))
Идентификатор пассажира: RIGHT('00'+CAST(id_psg AS VARCHAR(2)),2).

В последнем преобразовании идентификатора пассажира мы использовали нестандартную функцию RIGHT (SQL Server), которая извлекает из строки указанное количество символов справа. Можно было бы применить функцию SUBSTRING, однако так получается короче и, кроме того, наверняка в других коммерческих продуктах имеются аналогичные «расширения» стандарта. Соединяя эти выражения в указанном порядке, мы получим уникальный столбец, который и будет служить для нумерации пассажиров в соответствии с возрастанием (убыванием) значений в этом столбце. Используя этот столбец, мы можем воспользоваться решением 8.2.1. Окончательно получим:

Консоль

Выполнить

SELECT COUNT(*) num, P2.id_psg 
FROM (SELECT *, CAST(date AS CHAR(11)) + 
                RIGHT('00' + CAST(id_psg AS VARCHAR(2)), 2)+
                CAST(trip_no AS CHAR(4)) dit 
       FROM Pass_in_trip
      ) P1 JOIN 
      (SELECT *, CAST(date AS CHAR(11)) + 
                 RIGHT('00' + CAST(id_psg AS VARCHAR(2)), 2)+
                 CAST(trip_no AS CHAR(4)) dit 
       FROM pass_in_trip
       ) P2 ON P1.dit <= P2.dit
GROUP BY P2.dit, P2.id_psg
ORDER BY 1;

SELECT COUNT(*) num, P2.id_psg FROM (SELECT *, CAST(date AS CHAR(11)) +                 RIGHT('00' + CAST(id_psg AS VARCHAR(2)), 2)+                 CAST(trip_no AS CHAR(4)) dit        FROM Pass_in_trip       ) P1 JOIN       (SELECT *, CAST(date AS CHAR(11)) +                  RIGHT('00' + CAST(id_psg AS VARCHAR(2)), 2)+                  CAST(trip_no AS CHAR(4)) dit        FROM pass_in_trip        ) P2 ON P1.dit <= P2.dit GROUP BY P2.dit, P2.id_psg ORDER BY 1;

    Схема

Нет схемы

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

RIGHT('00' + CAST(id_psg AS VARCHAR(2)), 2).

В этом примере еще более наглядно, чем для решения 8.2.1, проявляются достоинства ранжирующих функций. Вот как просто и понятно можно переписать решение нашей задачи с их использованием:

Консоль

Выполнить

SELECT ROW_NUMBER() OVER(ORDER BY date, id_psg, trip_no) num, id_psg
FROM Pass_in_trip
ORDER BY num;

SELECT ROW_NUMBER() OVER(order by date, id_psg, trip_no) num, id_psg FROM Pass_in_trip ORDER BY num;

    Схема

Нет схемы