Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE MATERIALIZED VIEW clrtap_nec_unfccc.running_total_and_target (
- year,
- country_code,
- pollutant_name,
- sector_code,
- emissions,
- target,
- running_total)
- AS
- SELECT source.year,
- source.country_code,
- source.pollutant_name,
- source.sector_code,
- source.emissions,
- target.target,
- source.running_total
- FROM (
- SELECT clrtap_nfr09_gf.year,
- clrtap_nfr09_gf.country_code,
- clrtap_nfr09_gf.pollutant_name,
- clrtap_nfr09_gf.emissions,
- clrtap_nfr09_gf.sector_code,
- sum(clrtap_nfr09_gf.emissions) OVER (PARTITION BY
- clrtap_nfr09_gf.year, clrtap_nfr09_gf.country_code, clrtap_nfr09_gf.pollutant_name
- ORDER BY clrtap_nfr09_gf.emissions DESC) AS running_total
- FROM clrtap_nec_unfccc.clrtap_nfr09_gf
- WHERE clrtap_nfr09_gf.sector_code::text !~~ 'NATIONAL%'::text AND
- clrtap_nfr09_gf.sector_code::text !~~ 'ADJ%'::text AND clrtap_nfr09_gf.emissions > 0::double precision
- ORDER BY clrtap_nfr09_gf.emissions DESC
- ) source,
- (
- SELECT clrtap_nfr09_gf.year,
- clrtap_nfr09_gf.country_code,
- clrtap_nfr09_gf.pollutant_name,
- sum(clrtap_nfr09_gf.emissions) * 0.8::double precision AS target,
- sum(clrtap_nfr09_gf.emissions) AS total
- FROM clrtap_nec_unfccc.clrtap_nfr09_gf
- WHERE clrtap_nfr09_gf.sector_code::text !~~ 'NATIONAL%'::text AND
- clrtap_nfr09_gf.sector_code::text !~~ 'ADJ%'::text AND clrtap_nfr09_gf.emissions > 0::double precision
- GROUP BY clrtap_nfr09_gf.year, clrtap_nfr09_gf.country_code,
- clrtap_nfr09_gf.pollutant_name
- ORDER BY clrtap_nfr09_gf.year, clrtap_nfr09_gf.country_code,
- clrtap_nfr09_gf.pollutant_name
- ) target
- WHERE source.year::text = target.year::text AND source.country_code::text =
- target.country_code::text AND source.pollutant_name::text = target.pollutant_name::text
- ORDER BY source.year, source.country_code, source.pollutant_name, source.emissions DESC
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement