Untitled
unknown
plain_text
a year ago
4.3 kB
3
Indexable
Never
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;