Разбор денормализованных строк в SQL Server 2016

Эта статья появилась на свет в тот момент, когда я работал над другим материалом, посвященным строковым функциям. До той поры мне не доводилось сталкиваться с новой функцией STRING_SPLIT (), реализованной в версии SQL Server 2016; я познакомился с ней, когда работал над темой, которую в будущем надеюсь разобрать в нескольких статьях. Этой функцией можно воспользоваться в случае, когда целевая база данных находится в режиме COMPATIBILITY_MODE = 130.

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

Синтаксис функции STRING_SPLIT

С точки зрения синтаксиса конструкция, обеспечивающая возвращение результатов выполнения функции STRING_SPLIT, напоминает произвольный вызов функции, возвращающей табличное значение (листинг 1).

В качестве входного строкового параметра может выступать любое из следующих значений:

• строковое значение с разделителями;
• присваивание переменной типа varchar ();
• табличный столбец при использовании оператора CROSS APPLY.

Примеры возвращаемых результатов выполнения функции STRING_SPLIT ()

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

Первый пример — это передача строкового значения. Вне всякого сомнения, это самый простой метод решения задачи. Пользователь должен представить только входную строку и символ разделителя. Следующий пример позволит вам более ясно представить картину. Посмотрим, что произойдет, когда мы в демонстрационных целях введем в функцию список аэропортов, посещенных мною на протяжении прошлого года (листинг 2). Результаты показаны на рисунке 1.

Рисунок 1. Результаты выполнения листинга 2

Разумеется, эти результаты можно пропустить через фильтр с предикатами в предложении WHERE, чтобы отсортировать их с помощью предложения ORDER BY (листинг 3). Результаты приведены на рисунке 2.

Рисунок 2. Результаты выполнения листинга 3

Второй пример — нормализация данных с помощью переменной для строки с разделителями и курсора. Ведь, кроме всего прочего, мы можем объявить переменную типа varchar (n) или varchar (max) и передать ее в качестве первого параметра; результаты будут аналогичными. В этом примере мы имеем дело с очисткой денормализованных данных из одной таблицы посредством парсинга и вставкой их в нормализованную таблицу. Я воспользуюсь тремя таблицами (листинг 4):

• [events] — таблица, где хранятся связанные с конференциями SQL Cruise данные, характеризующие предстоящие события до конца 2018 года;
• [event_ports_denormalized] — таб­ли­ца, где хранится столбец с идентификаторами со ссылками на столбец идентификаторов в таблице [events] и столбец ports_list_csv, содержащий список всех портов для данного события с разделителями-запятыми;
• [event_ports] — таблица, которая будет представлять собой нормализованную версию таблицы [event_ports_denormalized].

Чтобы придать осмысленность полученным результатам, нам нужно заполнить эти таблицы (листинг 5).

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

Рисунок 3. Содержимое демонстрационных таблиц

Если бы мы захотели взглянуть на результирующий набор портов и связанных с ними событий, то убедились бы, что результирующие данные слабо поддаются дальнейшей обработке и объединяются с другими таблицами в данном состоянии (листинг 6). Результаты показаны на рисунке 4.

Рисунок 4. Результаты выполнения листинга 6

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

Наш подход будет состоять в следующем. С помощью курсора мы будем назначать переменным каждое значение формата csv и ассоциированный с ним идентификатор, с тем чтобы далее с использованием функции STRING_SPLIT () проанализировать эти значения и ввести их в таблицу [event_ports] (листинг 7).

Теперь с помощью запроса, приведенного в листинге 8, мы можем просмотреть нормализованный листинг событий и связанных с ними портов. Результаты представлены на рисунке 5.

Рисунок 5. Результаты запроса листинга 8

Третий пример — использование табличных значений на протяжении действия оператора CROSS APPLY (курсор в этом случае не применяется). Последний процесс я продемонстрировал вам для того, чтобы показать, насколько проще он выглядит при использовании оператора CROSS APPLY. Возьмем финальный этап использования функции STRING_SPLIT () с целью нормализации данных с разделителями, где напрямую применяется оператор CROSS APPLY, а курсор не используется вообще. Я очистил таблицу [event_ports] и теперь готов использовать процесс, показанный в листинге 9, для перезагрузки этой таблицы.

Результаты получаются те же самые, причем состоят они всего лишь из четырех строк кода (листинг 10). Результаты представлены на рисунке 6.

Рисунок 6. Результаты запроса листинга 10

Значение NULL или пустая строка?

Последнее обстоятельство, которое мне хотелось бы рассмотреть: что происходит, когда мы сталкиваемся с двумя символами-разделителями, расположенными последовательно во входной строке функции. Возьмем для примера параметр (обращая внимание на стоящие друг за другом запятые между аэропортами Ketchikan и Juneau), приведенный в листинге 11.

Значение строки, возвращенной для стоящих друг за другом разделителей-запятых, воспринимается как пустая строка, но не как NULL (рисунок 7).

Рисунок 7. Результаты запроса листинга 11

Возможно, это связано с тем, что код составлялся давно, а может быть, все дело в недостаточной квалификации разработчика, но так или иначе во многих приложениях и решениях, не использующих язык SQL, значения с разделителями-запятыми (CSV) часто хранятся в одном столбце или поле. Во многих случаях SQL Server подключается к работе с этими денормализованными данными либо в ходе миграции, при выполнении операций по извлечению, преобразованию и загрузке, либо при осуществлении координированного слияния системных данных, которое выполнялось бы более эффективно с помощью функции STRING_SPLIT (). В сущности, это функция синтаксического разбора, которая нормализует данные, не являющиеся нормализованными. В результате открываются новые возможности простой нормализации данных — внутренних в контексте базы данных SQL Server или получаемых в процессе взаимодействия с внешним источником данных.

SELECT value
FROM STRING_SPLIT( input string varchar(max), separator character varchar(1));

SELECT value
FROM STRING_SPLIT ('AZO,YYZ,SEA,PDX,SFO,RKV,AMS', ',');

SELECT value
FROM STRING_SPLIT ('AZO,YYZ,SEA,PDX,SFO,RKV,AMS', ',')
WHERE value LIKE 'A%'
ORDER BY value;

CREATE TABLE [events]
        (
                id INT IDENTITY(1,1),
                event_name VARCHAR(100)
        );

CREATE TABLE [event_ports_denormalized]
        (
                id INT NOT NULL,
                ports_list_csv VARCHAR(500) NOT NULL
        );

CREATE TABLE [event_ports]
        (
                id INT NOT NULL,
                port_name VARCHAR(50) NOT NULL
        );

INSERT INTO [events](event_name)
VALUES ('SQL Cruise Alaska 2017')
        , ('SQL Cruise Caribbean 2018')
        , ('SQL Cruise Alaska 2018')

INSERT INTO [event_ports_denormalized](id, ports_list_csv)
VALUES (1,'Seattle,Ketchikan,Juneau,Skagway,Victoria')
        , (2,'Miami,St. Thomas,Tortola,Nassau')
        , (3,'Seattle,Ketchikan,Juneau,Skagway,Victoria');

SELECT E.event_name, EPD.ports_list_csv
FROM [events] AS E
        INNER JOIN [event_ports_denormalized] AS EPD
                ON E.id = EPD.id
ORDER BY E.id;

DECLARE @id INT
DECLARE @csv VARCHAR(500)

DECLARE splitting_cursor  FOR
        SELECT id, ports_list_csv
        FROM [event_ports_denormalized];

OPEN splitting_cursor

FETCH NEXT FROM splitting_cursor
INTO @id, @csv
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO [event_ports] (id, port_name)
        SELECT @id, SS.value
        FROM STRING_SPLIT(@csv,',') AS SS

        FETCH NEXT FROM splitting_cursor
        INTO @id, @csv
END

CLOSE splitting_cursor;
DEALLOCATE splitting_cursor;

SELECT E.event_name, EP.port_name
FROM [events] AS E
        INNER JOIN [event_ports] AS EP
                ON E.id = EP.id
ORDER BY E.id;

INSERT INTO [event_ports] (id, port_name)
SELECT id, SS.value
FROM [event_ports_denormalized] AS EPD
        CROSS APPLY STRING_SPLIT(EPD.ports_list_csv,',') AS SS;

SELECT E.event_name, EP.port_name
FROM [events] AS E
INNER JOIN [event_ports] AS EP
  ON E.id = EP.id
ORDER BY E.id;

SELECT value
FROM STRING_SPLIT('Ketchikan,,Juneau,Skagway,Victoria',',');