Логическая обработка запросов: альтернативное решение с использованием APPLY
Содержание:
1. Введение;
2. 1-U1, 1-U2, 1-U3;
3. Альтернативное решение с использованием APPLY (Вы читаете данный раздел);
4. Сочетание операторов.
Оператор UNPIVOT построен очень изящно и отличается лаконичностью, но у него есть недостатки. Один из них — невозможность (по крайней мере, простым способом) обратного транспонирования нескольких наборов исходных столбцов в несколько целевых столбцов значений. Представьте, например, что у вас есть один набор столбцов, представляющих значения заказов, и другой, представляющий количества заказов. С другой стороны, вы можете быть не заинтересованы в удалении строк со значениями NULL в столбце значений. В коде ниже приводится альтернативное решение обратного транспонирования с использованием оператора APPLY для нашей тестовой задачи.
Альтернативное решение обратного транспонирования с использованием оператора APPLY
В решении используется оператор CROSS APPLY с конструктором табличного значения для определения трех строк для трех лет заказов, чтобы формировать копии (шаг 1). Во второй столбец в каждой из трех строк извлекается элемент из исходной строки года, соответствующий текущему году заказа (шаг 2). Вы можете выполнить первый шаг с помощью CROSS JOIN, но не второй. Помните, что объединение воспринимает свои два списка входных данных как набор, поэтому нельзя ссылаться с одной стороны на элементы другой. И наоборот, CROSS APPLY оценивает входные данные слева направо и потому позволяет входным данным справа ссылаться на данные слева. Чтобы обеспечить поддержку нескольких величин (например, val и city), просто укажите больше столбцов в каждой строке.
Наконец, поскольку APPLY создает псевдоним столбца значений (в нашем случае val) в предложении FROM, можно ссылаться на этот псевдоним в предложении WHERE, если вы хотите удалить строки со значениями NULL. Очевидно, что удалять значения NULL не обязательно.
Обратное транспонирование нескольких наборов столбцов
Чтобы показать, как этот метод работает при обратном транспонировании нескольких наборов столбцов, я воспользуюсь таблицей с именем MyPivotedOrders2, которую можно создать и заполнить данными с помощью программного кода, представленного ниже.
Содержимое таблицы Sales.MyPivotedOrders2
В таблице выше показано содержимое MyPivotedOrders2.
Требуется создать два столбца значений: из val2014, val20l5, val2016 необходимо создать целевой столбец с именем val, а из qty2014, qty2015, qty2016 — целевой столбец с именем qty. Как отмечалось, для каждой целевой величины просто добавьте в каждую строку в конструкторе табличных значений столбец, соответствующий этой величине, например, как в коде ниже.
Добавление столбца
Выходные данные, формируемые этим запросом, показаны в таблице ниже.
Результат обратного транспонирования с несколькими показателями
1. Введение;
2. 1-U1, 1-U2, 1-U3;
3.
4. Сочетание операторов.
Оператор UNPIVOT построен очень изящно и отличается лаконичностью, но у него есть недостатки. Один из них — невозможность (по крайней мере, простым способом) обратного транспонирования нескольких наборов исходных столбцов в несколько целевых столбцов значений. Представьте, например, что у вас есть один набор столбцов, представляющих значения заказов, и другой, представляющий количества заказов. С другой стороны, вы можете быть не заинтересованы в удалении строк со значениями NULL в столбце значений. В коде ниже приводится альтернативное решение обратного транспонирования с использованием оператора APPLY для нашей тестовой задачи.
Альтернативное решение обратного транспонирования с использованием оператора APPLY
В решении используется оператор CROSS APPLY с конструктором табличного значения для определения трех строк для трех лет заказов, чтобы формировать копии (шаг 1). Во второй столбец в каждой из трех строк извлекается элемент из исходной строки года, соответствующий текущему году заказа (шаг 2). Вы можете выполнить первый шаг с помощью CROSS JOIN, но не второй. Помните, что объединение воспринимает свои два списка входных данных как набор, поэтому нельзя ссылаться с одной стороны на элементы другой. И наоборот, CROSS APPLY оценивает входные данные слева направо и потому позволяет входным данным справа ссылаться на данные слева. Чтобы обеспечить поддержку нескольких величин (например, val и city), просто укажите больше столбцов в каждой строке.
Наконец, поскольку APPLY создает псевдоним столбца значений (в нашем случае val) в предложении FROM, можно ссылаться на этот псевдоним в предложении WHERE, если вы хотите удалить строки со значениями NULL. Очевидно, что удалять значения NULL не обязательно.
Обратное транспонирование нескольких наборов столбцов
Чтобы показать, как этот метод работает при обратном транспонировании нескольких наборов столбцов, я воспользуюсь таблицей с именем MyPivotedOrders2, которую можно создать и заполнить данными с помощью программного кода, представленного ниже.
Содержимое таблицы Sales.MyPivotedOrders2
В таблице выше показано содержимое MyPivotedOrders2.
Требуется создать два столбца значений: из val2014, val20l5, val2016 необходимо создать целевой столбец с именем val, а из qty2014, qty2015, qty2016 — целевой столбец с именем qty. Как отмечалось, для каждой целевой величины просто добавьте в каждую строку в конструкторе табличных значений столбец, соответствующий этой величине, например, как в коде ниже.
Добавление столбца
Выходные данные, формируемые этим запросом, показаны в таблице ниже.
Результат обратного транспонирования с несколькими показателями
