loading..
Русский    English
03:42
листать

Нумерация строк при наличии дубликатов в результирующем столбце

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

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

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

Консоль
Выполнить
  1. SELECT id_psg
  2. FROM pass_in_trip;
который вернет номера пассажиров, совершавших полеты, зафиксированные в базе данных. Поскольку один и тот же пассажир может совершить несколько рейсов, мы получаем здесь дубликаты. Однако ни один пассажир не может в один и тот же день более одного раза полететь одним и тем же рейсом, что регламентируется соответствующим первичным ключом — {trip_no, date, id_psg}.

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

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

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

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

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

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

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

Консоль
Выполнить
  1. SELECT COUNT(*) num, P2.id_psg
  2. FROM (SELECT *, CAST(date AS CHAR(11)) +
  3.                 RIGHT('00' + CAST(id_psg AS VARCHAR(2)), 2)+
  4.                 CAST(trip_no AS CHAR(4)) dit
  5.        FROM Pass_in_trip
  6.       ) P1 JOIN
  7.       (SELECT *, CAST(date AS CHAR(11)) +
  8.                  RIGHT('00' + CAST(id_psg AS VARCHAR(2)), 2)+
  9.                  CAST(trip_no AS CHAR(4)) dit
  10.        FROM pass_in_trip
  11.        ) P2 ON P1.dit <= P2.dit
  12. GROUP BY P2.dit, P2.id_psg
  13. ORDER BY 1;

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

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

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

Консоль
Выполнить
  1. SELECT ROW_NUMBER() OVER(ORDER BY date, id_psg, trip_no) num, id_psg
  2. FROM Pass_in_trip
  3. ORDER BY num;

Рекомендуемые упражнения: 47


Bookmark and Share
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE Convert COUNT CROSS APPLY CTE DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INNER JOIN insert INTERSECT IS NOT NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker MAX MIN MONTH MySQL NOT Больше тэгов
Учебник обновлялся
месяц назад
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100