При работе с таблицами, имеющими зависимые столбцы, может быть полезно аудитировать значения одного столбца для заданного значения другого. Это позволяет ответить на вопросы, такие как, имеет ли каждое значение в одном столбце соответствующее значение в другом столбце, или есть ли дублирующиеся значения. В этой статье мы представим хранимую процедуру с именем sp_HorizontalTree, которая может использоваться для аудита таблиц с зависимыми столбцами.
Процедура sp_HorizontalTree работает путем построения древовидной структуры с наблюдаемыми значениями первого столбца в качестве верхних узлов и наблюдаемыми значениями второго столбца в качестве детей верхних узлов и так далее. Каждый узел в дереве представляет собой подмножество записей в таблице с определенными значениями для соответствующих столбцов. Изучая структуру дерева, вы можете легко определить любые несоответствия или закономерности в данных.
Например, предположим, у нас есть таблица бухгалтерии с колонками для финансового года, финансового квартала, бухгалтерского периода и даты. Мы можем использовать процедуру sp_HorizontalTree для построения дерева, которое представляет зависимости между этими столбцами. Анализируя дерево, мы можем определить, имеет ли каждый финансовый год четыре финансовых квартала, есть ли какие-либо бухгалтерские периоды, охватывающие несколько финансовых кварталов и так далее.
Вот пример структуры дерева для таблицы бухгалтерии:
NodeId Node ParentId 1 Top_Node NULL 2 2019/2020 1 3 2020/2021 1 4 2021/2022 1 5 2020 Q2 3 6 2021 Q3 4 7 2020 Q3 3 8 2021 Q1 4 9 2019 Q2 2 10 2020 Q4 3 11 2019 Q4 2 12 2021 Q2 4 13 2021 Q4 4 14 2020 Q1 3 15 2019 Q1 2 16 2019 Q3 2 17 2019-01 15 18 2019-02 15 19 2019-03 15 20 2019-04 9 21 2019-04 15 22 2019-05 9 23 2019-06 9 24 2019-07 9 25 2019-07 16 26 2019-08 16 27 2019-09 16 28 2019-10 11 29 2019-10 16 30 2019-11 11 31 2019-12 11 32 2019-13 11
Изучая это дерево, мы видим, что есть три различных финансовых года (2019/2020, 2020/2021, 2021/2022) и четыре различных финансовых квартала (2020 Q2, 2021 Q3, 2020 Q3, 2021 Q1). Мы также видим, что есть несколько бухгалтерских периодов (например, 2019-04), охватывающих несколько финансовых кварталов.
Процедура sp_HorizontalTree также предоставляет сводную таблицу, которая может использоваться для написания SQL-запросов для целей аудита. Эта сводная таблица не ссылается на имена столбцов исходной таблицы, поэтому SQL-запросы, написанные для одной таблицы, могут быть применены к любой другой таблице с аналогичным содержимым. Например, вы можете использовать следующий запрос для перечисления бухгалтерских периодов, охватывающих несколько финансовых кварталов:
SELECT Node FROM ##HorizTree WHERE Depth = 3 GROUP BY NODE HAVING COUNT(*) > 1
Этот запрос вернет бухгалтерские периоды, которые появляются более одного раза на глубине 3 в дереве. Запустив этот запрос на горизонтальном дереве любой таблицы, вы легко можете определить любые несоответствия или закономерности в данных.
В заключение, процедура sp_HorizontalTree является мощным инструментом для аудита таблиц с зависимыми столбцами. Построение древовидной структуры, представляющей зависимости между столбцами, позволяет легко определить любые несоответствия или закономерности в данных. Это может быть полезно для обеспечения целостности данных и выявления любых проблем, которые могут потребовать внимания. Благодаря возможности написания SQL-запросов на основе сводной таблицы, вы легко можете применить процесс аудита к любой таблице с аналогичным содержимым.
Ресурсы:
- Готовая к печати копия этой статьи
- Скрипт для установки процедуры sp_HorizontalTree
- Скрипт для создания таблицы D_DATE
- Скрипт для запуска демонстрации
Ссылки:
- “Проверка деревьев”, www.sqlservercentral.com, 20 января 2023 года