detailed-view-reporting
ARCHIVED: This plan was not delivered. Preserved for context.
CREATE TABLE emission_type_mapping ( id INT PRIMARY KEY, name TEXT NOT NULL );
INSERT INTO emission_type_mapping (id, name) VALUES -- food (10000, 'food'), (10001, 'foodvegetarian'), (10002, 'foodnon_vegetarian'),
-- waste (20000, 'waste'), (20001, 'wasteincineration'), (20002, 'wastecomposting'), (20003, 'wastebiogas'), (2000301, 'wastebiogasorganic_waste_food_leftovers'), (2000302, 'wastebiogascooking_vegetable_oil'), (20004, 'wasterecycling'), (2000401, 'wasterecyclingpaper'), (2000402, 'wasterecyclingcardboard'), (2000403, 'wasterecyclingplastics'),q (2000404, 'wasterecyclingglass'), (2000405, 'wasterecyclingferrous_metals'), (2000406, 'wasterecyclingnon_ferrous_metals'), (2000407, 'wasterecyclingelectronics'), (2000408, 'wasterecyclingwood'), (2000409, 'wasterecyclingpet'), (2000410, 'wasterecyclingaluminum'), (2000411, 'wasterecyclingtextile'), (2000412, 'wasterecyclingtoner_and_ink_cartridges'), (2000413, 'wasterecyclinginert_waste'),
-- commuting (30000, 'commuting'), (30001, 'commutingwalking'), (30002, 'commutingcycling'), (30003, 'commutingpowered_two_wheeler'), (30004, 'commutingpublic_transport'), (30005, 'commuting__car'),
-- professional travel (50000, 'professional_travel'), (50100, 'professional_traveltrain'), (50101, 'professional_traveltrainclass_1'), (50102, 'professional_traveltrainclass_2'), (50200, 'professional_travelplane'), (50201, 'professional_travelplanefirst'), (50202, 'professional_travelplanebusiness'), (50203, 'professional_travelplaneeco'),
-- buildings (60000, 'buildings'), (60100, 'buildings__rooms'),
(60101, 'buildingsroomslighting'), (6010101, 'buildingsroomslightingoffice'), (6010102, 'buildingsroomslightinglaboratories'), (6010103, 'buildingsroomslightingarchives'), (6010104, 'buildingsroomslightinglibraries'), (6010105, 'buildingsroomslightingauditoriums'), (6010106, 'buildingsroomslightingmiscellaneous'),
(60102, 'buildingsroomscooling'), (6010201, 'buildingsroomscoolingoffice'), (6010202, 'buildingsroomscoolinglaboratories'), (6010203, 'buildingsroomscoolingarchives'), (6010204, 'buildingsroomscoolinglibraries'), (6010205, 'buildingsroomscoolingauditoriums'), (6010206, 'buildingsroomscoolingmiscellaneous'),
(60103, 'buildingsroomsventilation'), (6010301, 'buildingsroomsventilationoffice'), (6010302, 'buildingsroomsventilationlaboratories'), (6010303, 'buildingsroomsventilationarchives'), (6010304, 'buildingsroomsventilationlibraries'), (6010305, 'buildingsroomsventilationauditoriums'), (6010306, 'buildingsroomsventilationmiscellaneous'),
(60104, 'buildingsroomsheating_elec'), (6010401, 'buildingsroomsheating_elecoffice'), (6010402, 'buildingsroomsheating_eleclaboratories'), (6010403, 'buildingsroomsheating_elecarchives'), (6010404, 'buildingsroomsheating_eleclibraries'), (6010405, 'buildingsroomsheating_elecauditoriums'), (6010406, 'buildingsroomsheating_elecmiscellaneous'),
(60105, 'buildingsroomsheating_thermal'), (6010501, 'buildingsroomsheating_thermaloffice'), (6010502, 'buildingsroomsheating_thermallaboratories'), (6010503, 'buildingsroomsheating_thermalarchives'), (6010504, 'buildingsroomsheating_thermallibraries'), (6010505, 'buildingsroomsheating_thermalauditoriums'), (6010506, 'buildingsroomsheating_thermalmiscellaneous'),
(60200, 'buildingscombustion'), (60201, 'buildingscombustionnatural_gas'), (60202, 'buildingscombustionheating_oil'), (60203, 'buildingscombustionbiomethane'), (60204, 'buildingscombustionpellets'), (60205, 'buildingscombustionforest_chips'), (60206, 'buildingscombustionwood_logs'), (60300, 'buildingsembodied_energy'),
-- process emissions (70000, 'process_emissions'), (70100, 'process_emissionsch4'), (70200, 'process_emissionsco2'), (70300, 'process_emissionsn2o'), (70400, 'process_emissionsrefrigerants'),
-- equipment (80000, 'equipment'), (80100, 'equipmentscientific'), (80200, 'equipmentit'), (80300, 'equipment__other'),
-- purchases (90000, 'purchases'), (90100, 'purchasesgoods_and_services'), (90200, 'purchasesscientific_equipment'), (90300, 'purchasesit_equipment'), (90400, 'purchasesconsumable_accessories'), (90500, 'purchasesbiological_chemical_gaseous'), (90600, 'purchasesservices'), (90700, 'purchasesvehicles'), (90800, 'purchasesother'), (90900, 'purchasesadditional'), (90901, 'purchasesadditional__ln2'),
-- research facilities (100000, 'research_facilities'), (100100, 'research_facilitiesfacilities'), (100200, 'research_facilitiesanimal'),
-- external (110000, 'external'), (110100, 'externalclouds'), (110101, 'externalcloudsvirtualisation'), (110102, 'externalcloudscalcul'), (110103, 'externalcloudsstockage'), (110200, 'externalai'), (110201, 'externalaiprovider_google'), (110202, 'externalaiprovider_mistral_ai'), (110203, 'externalaiprovider_anthropic'), (110204, 'externalaiprovider_openai'), (110205, 'externalaiprovider_cohere'), (110206, 'externalaiprovider_others');
pivot that generate the sql
WITH keys AS ( SELECT DISTINCT (jsonb_object_keys(cr.stats::jsonb -> 'by_emission_type'))::int AS id FROM carbon_reports cr ), cols AS ( SELECT k.id, m.name FROM keys k LEFT JOIN emission_type_mapping m ON m.id = k.id ) SELECT 'SELECT cr.year, u.institutional_id,
(cr.stats->>''scope1'')::float AS scope1,
(cr.stats->>''scope2'')::float AS scope2,
(cr.stats->>''scope3'')::float AS scope3,
(cr.stats->>''total'')::float AS total,' ||
string_agg(
format(
'COALESCE((cr.stats->''by_emission_type''->>''%s'')::float, 0) AS "%s"',
id,
COALESCE(name, id::text)
),
',\n '
ORDER BY id
) ||
'
FROM carbon_reports cr
JOIN units u ON u.id = cr.unit_id
ORDER BY cr.year, u.institutional_id;
'
FROM cols;
--> here it is
SELECT cr.year, u.institutional_id,
(cr.stats->>'scope1')::float AS scope1,
(cr.stats->>'scope2')::float AS scope2,
(cr.stats->>'scope3')::float AS scope3,
(cr.stats->>'total')::float AS total,COALESCE((cr.stats->'by_emission_type'->>'10000')::float, 0) AS "food",
COALESCE((cr.stats->'by_emission_type'->>'20000')::float, 0) AS "waste",
COALESCE((cr.stats->'by_emission_type'->>'30000')::float, 0) AS "commuting"
FROM carbon_reports cr
JOIN units u ON u.id = cr.unit_id
ORDER BY cr.year, u.institutional_id;