Untitled

CREATE TEMP TABLE PurchaseStats AS
SELECT
    "region",
    "purchaseNumber",
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE parsing_status = 'success') AS completed
FROM xml_files
WHERE section_name IN ('notifications', 'protocols')
  AND xml_date >= DATE '2015-01-01' AND xml_date <= DATE '2022-12-31'
GROUP BY "region", "purchaseNumber";

-- Создаем временную таблицу для статистики по годам и секциям
CREATE TEMP TABLE SectionStats AS
SELECT
    t1."region",
    t1.section_name AS section,
    EXTRACT(YEAR FROM t1.xml_date)::TEXT AS year,
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE t1.parsing_status = 'success') AS completed
FROM xml_files t1
WHERE NOT EXISTS (
    SELECT 1
    FROM xml_files t2
    WHERE t1."purchaseNumber" = t2."purchaseNumber"
      AND t1."docPublishDate" < t2."docPublishDate"
)
AND t1.section_name IN ('notifications', 'protocols')
AND t1.xml_date >= DATE '2015-01-01' AND t1.xml_date <= DATE '2022-12-31'
GROUP BY t1."region", t1.section_name, year;

-- Получаем JSON для регионов и их статистики
-- ниже создаем вьюшку. Выше подготовка. А ниже одна строка, если вьюшка не нужна ее можно удалить удалив эту одну строку

CREATE MATERIALIZED VIEW xml_files_processing_stat_sections_and_regions AS

SELECT
    json_build_object(
        'total', (
            SELECT COUNT(*) FROM PurchaseStats
        ),
        'regions', (
            SELECT json_object_agg(
                outer_data."region",
                json_build_object(
                    'notifications', (
                        SELECT json_build_object(
                            'total', ss.total,
                            'completed', ss.completed,
                            'years', (
                                SELECT json_object_agg(year, json_build_object(
                                    'total', year_total.total,
                                    'completed', year_total.completed
                                ))
                                FROM (
                                    SELECT year, SUM(total) AS total, SUM(completed) AS completed
                                    FROM SectionStats AS ss2
                                    WHERE ss2.section = 'notifications'
                                      AND ss2."region" = outer_data."region"
                                    GROUP BY year
                                ) AS year_total
                            )
                        )
                        FROM SectionStats AS ss
                        WHERE ss.section = 'notifications'
                          AND ss."region" = outer_data."region"
                    ),
                    'protocols', (
                        SELECT json_build_object(
                            'total', sp.total,
                            'completed', sp.completed,
                            'years', (
                                SELECT json_object_agg(year, json_build_object(
                                    'total', year_total.total,
                                    'completed', year_total.completed
                                ))
                                FROM (
                                    SELECT year, SUM(total) AS total, SUM(completed) AS completed
                                    FROM SectionStats AS sp2
                                    WHERE sp2.section = 'protocols'
                                      AND sp2."region" = outer_data."region"
                                    GROUP BY year
                                ) AS year_total
                            )
                        )
                        FROM SectionStats AS sp
                        WHERE sp.section = 'protocols'
                          AND sp."region" = outer_data."region"
                    )
                )
            )
            FROM (
                SELECT DISTINCT "region" FROM xml_files
                WHERE section_name IN ('notifications', 'protocols')
                  AND xml_date >= DATE '2015-01-01' AND xml_date <= DATE '2022-12-31'
            ) AS outer_data
        )
    ) AS result;