История вопроса.
Это требование возникает в процессах загрузки данных, мигрирующих из устаревших систем таблиц или экспорта из плоских файлов, где временные метрики или категориальные атрибуты денормализованы в широкие заголовки столбцов (например, Jan_Sales, Feb_Sales), а не в нормализованные строки. Такие схемы распространены в бизнес-процессах, основанных на Excel, до ETL в реляционные хранилища данных, требуя преобразования в узкие таблицы фактов для обеспечения временного анализа и размерных JOIN операций. Задача заключается в том, чтобы транспонировать эти статические проекции столбцов в динамические потоки кортежей без обращения к императивной обработке построчно.
Проблема.
Стандартные SELECT выражения фиксируют проекцию столбцов на этапе разбора, предотвращая одновременное извлечение разных столбцов источника на разных выходных строках без LATERAL корреляции или итераций процедуры. Цель состоит в том, чтобы создать декартово произведение между каждой строкой источника и виртуальной таблицей размерностей, перечисляющей имена атрибутов, а затем мультиплексировать правильное значение источника в общий результирующий столбец с помощью условной логики. Это должно быть выполнено, используя только стандартный синтаксис соединения и скалярные выражения, доступные в ANSI SQL:1999 и более поздних версиях.
Решение.
Используйте CROSS JOIN с производной таблицей, выраженной через конструктор строк VALUES, который перечисляет категориальные ключи (например, имена месяцев) в виде строк. В списке SELECT используйте искомое выражение CASE, которое сопоставляет каждый ключ с его соответствующим столбцом источника, эффективно проецируя денормализованное значение в нормализованную строковую структуру. Отфильтруйте результат, чтобы исключить NULL значения, возникающие, когда определённый атрибут источника отсутствует для данного ключа, обеспечивая, чтобы итоговый вывод содержал только действительные измерения.
SELECT s.cost_center_id, m.fiscal_month, CASE m.fiscal_month WHEN 'M01' THEN s.m01_amt WHEN 'M02' THEN s.m02_amt WHEN 'M03' THEN s.m03_amt -- ... дополнительные месяцы WHEN 'M12' THEN s.m12_amt END AS amount FROM budget_wide s CROSS JOIN ( VALUES ('M01'), ('M02'), ('M03'), ('M04'), ('M05'), ('M06'), ('M07'), ('M08'), ('M09'), ('M10'), ('M11'), ('M12') ) AS m(fiscal_month) WHERE CASE m.fiscal_month WHEN 'M01' THEN s.m01_amt -- ... повторяем для всех, чтобы избежать NULL END IS NOT NULL;
Финансовый отдел экспортировал бюджетные allocations на финансовый год из корпоративной модели Excel в промежуточную таблицу, где двенадцать месячных столбцов (M01_Amt до M12_Amt) представляли денормализованные временные периоды для каждого центра затрат. Целевое хранилище данных SAP требовало узкую схему таблицы фактов (CostCenter_ID, Fiscal_Month, Amount), что потребовало преобразования UNPIVOT в загрузочном скрипте ANSI SQL, чтобы избежать промежуточной обработки на Python. Объем в пятьдесят миллионов записей исключал ручное преобразование или стратегии загрузки в несколько проходов.
Решение 1: Union All для каждого столбца.
Первоначальный подход использовал двенадцать отдельных запросов SELECT, каждый из которых проецировал разные месячные столбцы, жестко закодированные в общий столбец Amount и Month_Name, комбинируя через UNION ALL. Плюсы: Этот метод поддерживает универсальную совместимость, работая на устаревших мэйнфреймных базах данных и древних SQL движках, которые не обладают современным синтаксисом соединения. Минусы: Он выполняет двенадцать полных сканирований таблицы исходных данных, что приводит к линейному ухудшению ввода-вывода; план запроса становится объемным и трудным для кеширования, а любое изменение схемы (добавление тринадцатого периода) требует изменения двенадцати отдельных списков проекции.
Решение 2: Генерация динамического SQL.
Альтернативный вариант заключался в построении текста запроса динамически на уровне приложения, перебирая таблицы метаданных для генерации необходимых веток CASE или ветвей UNION во время выполнения. Плюсы: Это обеспечивает гибкость перед изменяющимися схемами и уменьшает ручной труд по созданию SQL, когда обрабатываются сотни столбцов. Минусы: Это нарушает запрет на процедурную логику; это вводит векторы атак с помощью SQL-инъекций и накладные расходы на компиляцию, а полученное выражение не может быть заключено в статический представление базы данных или определение процедуре.
Решение 3: Cross Join с Values.
Принятая реализация использовала CROSS JOIN с конструктором VALUES, определяющим двенадцать финансовых периодов, мультиплексируя правильную сумму через выражение CASE с ключом на идентификаторе виртуального периода. Плюсы: Это выполняется за один проход по исходной таблице, использует эффективные алгоритмы соединения и является полностью декларативным и портируемым на Oracle, SQL Server, PostgreSQL и Db2 без подсказок поставщика. Минусы: Требуется поддержка SQL:1999 для конструкторов строк, недоступных в устаревших системах, и множественная verbosity выражения CASE увеличивает накладные расходы на обслуживание, если не генерируется через шаблоны.
Результат.
Задержка преобразования сократилась с двадцати пяти минут до менее девяноста секунд за счёт устранения избыточных сканирований таблиц, присущих шаблону UNION ALL. Процесс загрузки стал устойчивым к расширениям схемы, требуя только добавления строки в конструктор VALUES при введении новых финансовых периодов. Более того, логика была инкапсулирована в стандартное представление, позволяющее прямое ад-хок запросы пользователями Tableau без промежуточных шагов ETL.
Как вы предотвращаете появление NULL значений в столбцах источника в виде строк в результатах преобразования, не вызывая дважды вычисления выражения CASE в плане выполнения?
Кандидаты часто вставляют выражение CASE внутрь предиката WHERE, например WHERE CASE ... END IS NOT NULL, что заставляет оптимизатор вычислять проекцию дважды — один раз для фильтрации и один раз для вывода. Эффективный шаблон ANSI SQL материализует результат внутри производной таблицы или Общего выражения таблицы (CTE): SELECT * FROM (SELECT ..., CASE ... END AS val FROM ... CROSS JOIN ...) sub WHERE val IS NOT NULL. Это вычисляет CASE один раз, фильтрует строки и поддерживает четкое разделение задач для оптимизатора запроса.
Когда вы преобразуете столбцы с гетерогенными типами данных (например, столбец комментария VARCHAR рядом со столбцом суммы DECIMAL), какая конкретная стратегия приведения типов ANSI SQL гарантирует согласованность типов в единственном результирующем столбце без потери данных?
Многие кандидаты неправильно полагаются на неявное преобразование типов, что может сократить строки или потерять десятичную точность, или они пытаются использовать UNION ALL, не осознавая, что правила приведения типов различаются в зависимости от платформы. Надежное решение явно приводит каждый столбец источника к общему супертипу — обычно VARCHAR — в каждом ветвлении WHEN выражения CASE: CASE WHEN key='Comment' THEN CAST(text_col AS VARCHAR(500)) ELSE CAST(num_col AS VARCHAR(500)) END. Это гарантирует, что все возвращаемые значения имеют единый тип данных, совместимый с определением результирующего столбца, сохраняя текстовое представление числовых данных, где это необходимо.
Почему подход CROSS JOIN с VALUES внешне кажется создавать взрыв декартового произведения, и как оптимизатор обычно смягчает это по сравнению с поведением удаления NULL нативным оператором UNPIVOT?
CROSS JOIN логически генерирует M×N строк (строки источника умноженные на количество атрибутов) до фильтрации, что кандидаты боятся ухудшит производительность на больших наборах данных. Однако современные оптимизаторы на основе стоимости распознают зависимость данных выражения CASE от небольшой постоянной таблицы и часто преобразуют план в простую проекцию или физический оператор UNPIVOT внутренне, избегая фактического умножения строк. В отличие от нативного UNPIVOT, который обычно автоматически удаляет NULL результаты, этот метод требует явного WHERE условия, чтобы выбрасывать строки, где атрибут источника был NULL, иначе результирующий набор будет содержать поддельные пустые факты, которые искажают агрегированные вычисления в дальнейшем.