Generate renewable summary statistics in the format used by GermanZero-de/localzero-data-public

localzero.sql

with Stichtag as (select "2018-12-31" as Stichtag),
ags as (
  select Gemeindeschluessel as ags from EinheitSolar
  union select Gemeindeschluessel as ags from EinheitWind
  union select Gemeindeschluessel as ags from EinheitBiomasse
  union select Gemeindeschluessel as ags from EinheitGeoSolarthermieGrubenKlaerschlammDruckentspannung
  union select Gemeindeschluessel as ags from EinheitWasser
),
pv as (
  select
    Gemeindeschluessel as ags,
    round(sum(Nettonennleistung), 3) as value
  from
    EinheitSolar,
    Stichtag
  where
    Inbetriebnahmedatum <= Stichtag -- Vor/am Stichtag in Betrieb genommen ...
    and (
      -- ... und nie oder nach Stichtag stillgelegt ...
      DatumEndgueltigeStilllegung is null
      or DatumEndgueltigeStilllegung > Stichtag
    )
    and (
      -- ... und nie voruebergehend stillgelegt oder vor/am Stichtag wieder in Betrieb genommen.
      DatumBeginnVoruebergehendeStilllegung is null
      or DatumWiederaufnahmeBetrieb <= Stichtag
    )
  group by
    Gemeindeschluessel
),
wind_on as (
  select
    Gemeindeschluessel as ags,
    round(sum(Nettonennleistung), 3) as value
  from
    EinheitWind,
    Stichtag
  where
    Lage = 888 -- select Id from Katalogwert where Wert = "Windkraft an Land"
    and Inbetriebnahmedatum <= Stichtag
    and (
      DatumEndgueltigeStilllegung is null
      or DatumEndgueltigeStilllegung > Stichtag
    )
    and (
      DatumBeginnVoruebergehendeStilllegung is null
      or DatumWiederaufnahmeBetrieb <= Stichtag
    )
  group by
    Gemeindeschluessel
),
biomass as (
  select
    Gemeindeschluessel as ags,
    round(sum(Nettonennleistung), 3) as value
  from
    EinheitBiomasse,
    Stichtag
  where
    Inbetriebnahmedatum <= Stichtag
    and (
      DatumEndgueltigeStilllegung is null
      or DatumEndgueltigeStilllegung > Stichtag
    )
    and (
      DatumBeginnVoruebergehendeStilllegung is null
      or DatumWiederaufnahmeBetrieb <= Stichtag
    )
  group by
    Gemeindeschluessel
),
geothermal as (
  select
    Gemeindeschluessel as ags,
    round(sum(Nettonennleistung), 3) as value
  from
    EinheitGeoSolarthermieGrubenKlaerschlammDruckentspannung,
    Stichtag
  where
    Energietraeger = 2403 -- select Id from Katalogwert where Wert = "Geothermie"
    and Inbetriebnahmedatum <= Stichtag
    and (
      DatumEndgueltigeStilllegung is null
      or DatumEndgueltigeStilllegung > Stichtag
    )
    and (
      DatumBeginnVoruebergehendeStilllegung is null
      or DatumWiederaufnahmeBetrieb <= Stichtag
    )
  group by
    Gemeindeschluessel
),
water as (
  select
    Gemeindeschluessel as ags,
    round(sum(Nettonennleistung), 3) as value
  from
    EinheitWasser,
    Stichtag
  where
    Inbetriebnahmedatum <= Stichtag
    and (
      DatumEndgueltigeStilllegung is null
      or DatumEndgueltigeStilllegung > Stichtag
    )
    and (
      DatumBeginnVoruebergehendeStilllegung is null
      or DatumWiederaufnahmeBetrieb <= Stichtag
    )
  group by
    Gemeindeschluessel
),
combined as (
  select
    ags.ags,
    ifnull(pv.value, 0.0) as pv,
    ifnull(wind_on.value, 0.0) as wind_on,
    ifnull(biomass.value, 0.0) as biomass,
    ifnull(geothermal.value, 0.0) as geothermal,
    ifnull(water.value, 0.0) as water
  from
    ags
    left join pv on ags.ags = pv.ags
    left join wind_on on ags.ags = wind_on.ags
    left join biomass on ags.ags = biomass.ags
    left join geothermal on ags.ags = geothermal.ags
    left join water on ags.ags = water.ags
  where
    ags.ags is not null
)
select
  ags, pv, wind_on, biomass, geothermal, water
from
  combined
union all
select
  "DG000000" as ags,
  round(sum(pv), 3) as pv,
  round(sum(wind_on), 3) as wind_on,
  round(sum(biomass), 3) as biomass,
  round(sum(geothermal), 3) as geothermal,
  round(sum(water), 3) as water
from
  combined