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;