Вопрос или проблема
У меня есть три таблицы.
Первая таблица называется REPORTS_MASTER_LIST, она содержит столбцы REPORT_NAME, REPORT_CATEGORY. В ней находятся все отчеты, которые могут быть выполнены пользователями, и их категории.
Вторая таблица называется USER_REPORT_EXECUTION, она содержит столбцы USERNAME, REPORT_NAME, REPORT_CATEGORY. Каждая строка в этой таблице представляет собой отчет, который был выполнен пользователем.
Третья таблица называется USER_DEPARTMENTS_LIST, она содержит столбцы USERNAME, USER_DEPARTMENT. В ней находятся все пользователи вместе с их отделами.
Я хочу создать матрицу, которая слева (в строках) покажет REPORT_TYPE, которую можно будет развернуть для отображения REPORT_NAME. В верхней части матрицы (в столбцах) должны быть указаны USER_DEPARTMENT. Значения, показанные в матрице, должны представлять собой количество, отражающее количество выполнений отчетов по USER_DEPARTMENT для каждого REPORT_NAME.
В настоящее время я могу сделать это, используя только таблицы USER_REPORT_EXECUTION и USER_DEPARTMENT. Проблема в том, что если в REPORTS_MASTER_LIST есть отчет, который никогда не выполнялся пользователем, он не появится в матрице, хотя мне нужно, чтобы он отображался и показывал ‘0’ для каждого USER_DEPARTMENT. Есть ли способ сделать это, интегрировав таблицу REPORTS_MASTER_LIST в матрицу каким-либо образом?
Ответ или решение
Создание матрицы счетов с использованием мастер-списка отчетов
Ваша задача заключается в создании матрицы, которая будет отображать количество выполненных отчетов пользователями, с разделением по категориям отчетов и отделам пользователей. Давайте разберем необходимую логику и SQL-запросы, чтобы правильно интегрировать таблицу REPORTS_MASTER_LIST и обеспечить отображение всех отчетов, включая те, которые не были выполнены ни разу.
Описание таблиц
-
REPORTS_MASTER_LIST: Данная таблица содержит два столбца — REPORT_NAME и REPORT_CATEGORY. Она включает все отчеты, доступные для выполнения пользователями.
-
USER_REPORT_EXECUTION: В этой таблице имеются поля USERNAME, REPORT_NAME и REPORT_CATEGORY, которые фиксируют факты выполнения отчетов пользователями.
-
USER_DEPARTMENTS_LIST: Здесь представлена информация о пользователях и их соответствующих отделах — USERNAME и USER_DEPARTMENT.
Построение матрицы
Для построения необходимой матрицы, где по строкам находятся REPORT_CATEGORY и REPORT_NAME, а по столбцам — USER_DEPARTMENT, необходимо создать SQL-запрос, который включает все отчеты из REPORTS_MASTER_LIST, даже те, которые не были выполнены. Для этого можно использовать структуру с объединением данных через таблицу пользователей и отчетов.
SQL-запрос
Ниже приведен пример SQL-запроса, который позволяет достичь поставленной цели. Используем конструкцию LEFT JOIN, чтобы заявить, что мы хотим видеть все элементы из REPORTS_MASTER_LIST и только те, которые соответствуют выполненным отчетам.
SELECT
rml.REPORT_CATEGORY,
rml.REPORT_NAME,
udl.USER_DEPARTMENT,
COUNT(ure.USERNAME) AS execution_count
FROM
REPORTS_MASTER_LIST rml
CROSS JOIN
(SELECT DISTINCT USER_DEPARTMENT FROM USER_DEPARTMENTS_LIST) udl
LEFT JOIN
USER_REPORT_EXECUTION ure ON rml.REPORT_NAME = ure.REPORT_NAME
AND ure.REPORT_CATEGORY = rml.REPORT_CATEGORY
AND ure.USERNAME IN (SELECT USERNAME FROM USER_DEPARTMENTS_LIST)
GROUP BY
rml.REPORT_CATEGORY,
rml.REPORT_NAME,
udl.USER_DEPARTMENT
ORDER BY
rml.REPORT_CATEGORY,
rml.REPORT_NAME,
udl.USER_DEPARTMENT;Пояснение запроса:
-
CROSS JOIN: Используется для получения всех сочетаний отчетов и отделов. Это необходимо для того, чтобы включить даже те отчеты, которые не были выполнены ни разу.
-
LEFT JOIN: Это соединение позволяет оставлять строки из REPORTS_MASTER_LIST даже в случае отсутствия выполнений в USER_REPORT_EXECUTION.
-
COUNT(ure.USERNAME): Счетчик, который возвращает количество выполнений каждого отчета. Если отчет не был выполнен, значение будет равно 0.
-
GROUP BY: Соединяет все данные по категориям и названиям отчетов и отделам, что позволяет получить нужную структуру.
-
ORDER BY: Упорядочивает результат по категории отчетов и названиям, чтобы результат был организованным и легким для восприятия.
Заключение
С помощью представленного запроса вы сможете создать необходимую матрицу, которая отобразит все отчеты, даже если ни один из пользователей не запускал их. Это обеспечит полный и четкий обзор выполнения отчетов в зависимости от отделов пользователей. Такое решение значительно улучшит аналитические способности вашей организации, позволяя принимать более обоснованные управленческие решения.