<commit_before>


DROP TABLE IF EXISTS 	calc_ego_loads.ego_deu_consumption CASCADE;
CREATE TABLE 		calc_ego_loads.ego_deu_consumption
(
	id integer NOT NULL,
	subst_id integer,
 	sector_area_residential numeric,
	sector_area_retail numeric,
	sector_area_industrial numeric,
	sector_area_agricultural numeric,
	sector_consumption_residential numeric,
	sector_consumption_retail numeric,
	sector_consumption_industrial numeric,
	sector_consumption_agricultural numeric,
	CONSTRAINT ego_deu_consumption_pkey PRIMARY KEY (id)
);

-- Set ID   (OK!) -> 100ms =206.846
INSERT INTO 	calc_ego_loads.ego_deu_consumption (id,subst_id,
													sector_area_residential,sector_area_retail,
													sector_area_industrial,sector_area_agricultural)
	SELECT 	id,
			subst_id,
			sector_area_residential,
			sector_area_retail,
			sector_area_industrial,
			sector_area_agricultural
	FROM 	calc_ego_loads.ego_deu_load_area;
	

-- Calculate the landuse area per district 

-- ALTER TABLE orig_ego_consumption.lak_consumption_per_district
-- 	ADD COLUMN area_retail numeric,
-- 	ADD COLUMN area_agriculture numeric,
-- 	ADD COLUMN area_tertiary_sector numeric;


-- Calculate the retail area per district

UPDATE orig_ego_consumption.lak_consumption_per_district a
SET area_retail = result.sum
FROM
( 
	SELECT 
	sum(sector_area_retail),
 	sum(coalesce(sector_area_retail,0)), 
	substr(nuts,1,5) 
	FROM calc_ego_loads.ego_deu_load_area
	GROUP BY substr(nuts,1,5)
) as result

WHERE result.substr = substr(a.eu_code,1,5);


-- Calculate the agricultural area per district

UPDATE orig_ego_consumption.lak_consumption_per_district a
SET area_agriculture = result.sum
FROM
( 
	SELECT 
	sum(sector_area_agricultural), 
	sum(coalesce(sector_area_agricultural,0)), 
	substr(nuts,1,5) 
	FROM calc_ego_loads.ego_deu_load_area
	GROUP BY substr(nuts,1,5)
) as result

WHERE result.substr = substr(a.eu_code,1,5);


-- Calculate area of tertiary sector by adding agricultural and retail area up


UPDATE orig_ego_consumption.lak_consumption_per_district 
	SET area_tertiary_sector = coalesce(area_retail,0) + coalesce(area_agriculture,0);


-- Calculate sector consumption of industry per loadarea

UPDATE calc_ego_loads.ego_deu_consumption a
SET   sector_consumption_industrial = sub.result 
FROM
(
	SELECT
	c.id,
	b.elec_consumption_industry/nullif(b.area_industry,0) * c.sector_area_industrial as result
	FROM
	orig_ego_consumption.lak_consumption_per_district b,
	calc_ego_loads.ego_deu_load_area c
	WHERE
	c.nuts = b.eu_code
) AS sub
WHERE
sub.id = a.id;

-- Calculate sector consumption of tertiary sector per loadarea

UPDATE calc_ego_loads.ego_deu_consumption a
SET   sector_consumption_retail = sub.result 
FROM
(
	SELECT
	c.id,
	b.elec_consumption_tertiary_sector/nullif(b.area_tertiary_sector,0) * c.sector_area_retail as result
	FROM
	orig_ego_consumption.lak_consumption_per_district b,
	calc_ego_loads.ego_deu_load_area c
	WHERE
	c.nuts = b.eu_code
) AS sub
WHERE
sub.id = a.id;

-- Calculate sector consumption of agriculture per loadarea

UPDATE calc_ego_loads.ego_deu_consumption a
SET   sector_consumption_agricultural = sub.result 
FROM
(
	SELECT
	c.id,
	b.elec_consumption_tertiary_sector/nullif(b.area_tertiary_sector,0) * c.sector_area_agricultural as result
	FROM
	orig_ego_consumption.lak_consumption_per_district b,
	calc_ego_loads.ego_deu_load_area c
	WHERE
	c.nuts = b.eu_code
) AS sub
WHERE
sub.id = a.id;


-- Calculate sector consumption of households per loadarea

UPDATE calc_ego_loads.ego_deu_consumption a
SET   sector_consumption_residential = sub.result 
FROM
(
	SELECT
	c.id,
	b.elec_consumption_households_per_person * c.zensus_sum as result
	FROM 
	orig_ego_consumption.lak_consumption_per_federalstate b,
	calc_ego_loads.ego_deu_load_area c
	WHERE
	substring(c.nuts,1,3) = b.eu_code

) AS sub
WHERE
sub.id = a.id;

-- Add Geometry information to consumption table

ALTER TABLE calc_ego_loads.ego_deu_consumption
ADD COLUMN geom geometry (Polygon,3035); 

UPDATE calc_ego_loads.ego_deu_consumption a
SET geom = b.geom
FROM calc_ego_loads.ego_deu_load_area b
WHERE a.id = b.id;

-- Grant oeuser   (OK!) -> 100ms =0
GRANT ALL ON TABLE 	calc_ego_loads.ego_deu_consumption TO oeuser WITH GRANT OPTION;
ALTER TABLE		calc_ego_loads.ego_deu_consumption OWNER TO oeuser;

-- Scenario eGo data processing
INSERT INTO	scenario.eGo_data_processing_clean_run (version,schema_name,table_name,script_name,entries,status,timestamp)
	SELECT	'0.1' AS version,
		'calc_ego_loads' AS schema_name,
		'ego_deu_consumption' AS table_name,
		'process_eGo_consumption.sql' AS script_name,
		COUNT(id)AS entries,
		'OK' AS status,
		NOW() AT TIME ZONE 'Europe/Berlin' AS timestamp
	FROM	calc_ego_loads.ego_deu_consumption;

---------- ---------- ----------

-- -- Consumption Join   (OK!) 10.000ms =206.846
-- DROP TABLE IF EXISTS 	calc_ego_loads.ego_deu_consumption_area CASCADE;
-- CREATE TABLE 		calc_ego_loads.ego_deu_consumption_area AS
-- 	SELECT	a.id,
-- 		a.subst_id,<commit_msg>Add assignment of otg_id to process_eGo_consumption<commit_after>


DROP TABLE IF EXISTS 	calc_ego_loads.ego_deu_consumption CASCADE;
CREATE TABLE 		calc_ego_loads.ego_deu_consumption
(
	id integer NOT NULL,
	subst_id integer,
	otg_id integer,
	un_id integer,
 	sector_area_residential numeric,
	sector_area_retail numeric,
	sector_area_industrial numeric,
	sector_area_agricultural numeric,
	sector_consumption_residential numeric,
	sector_consumption_retail numeric,
	sector_consumption_industrial numeric,
	sector_consumption_agricultural numeric,
	geom geometry (Polygon,3035), 
	CONSTRAINT ego_deu_consumption_pkey PRIMARY KEY (id)
);

-- Set ID   (OK!) -> 100ms =206.846
INSERT INTO 	calc_ego_loads.ego_deu_consumption (id,subst_id,
		sector_area_residential,sector_area_retail,
		sector_area_industrial,sector_area_agricultural)
	SELECT 		id,
			subst_id,
			sector_area_residential,
			sector_area_retail,
			sector_area_industrial,
			sector_area_agricultural
	FROM 	calc_ego_loads.ego_deu_load_area;
	

-- Calculate the landuse area per district 

-- ALTER TABLE orig_ego_consumption.lak_consumption_per_district
-- 	ADD COLUMN area_retail numeric,
-- 	ADD COLUMN area_agriculture numeric,
-- 	ADD COLUMN area_tertiary_sector numeric;


-- Calculate the retail area per district

UPDATE orig_ego_consumption.lak_consumption_per_district a
SET area_retail = result.sum
FROM
( 
	SELECT 
	sum(sector_area_retail),
 	sum(coalesce(sector_area_retail,0)), 
	substr(nuts,1,5) 
	FROM calc_ego_loads.ego_deu_load_area
	GROUP BY substr(nuts,1,5)
) as result

WHERE result.substr = substr(a.eu_code,1,5);


-- Calculate the agricultural area per district

UPDATE orig_ego_consumption.lak_consumption_per_district a
SET area_agriculture = result.sum
FROM
( 
	SELECT 
	sum(sector_area_agricultural), 
	sum(coalesce(sector_area_agricultural,0)), 
	substr(nuts,1,5) 
	FROM calc_ego_loads.ego_deu_load_area
	GROUP BY substr(nuts,1,5)
) as result

WHERE result.substr = substr(a.eu_code,1,5);


-- Calculate area of tertiary sector by adding agricultural and retail area up


UPDATE orig_ego_consumption.lak_consumption_per_district 
	SET area_tertiary_sector = coalesce(area_retail,0) + coalesce(area_agriculture,0);


-- Calculate sector consumption of industry per loadarea

UPDATE calc_ego_loads.ego_deu_consumption a
SET   sector_consumption_industrial = sub.result 
FROM
(
	SELECT
	c.id,
	b.elec_consumption_industry/nullif(b.area_industry,0) * c.sector_area_industrial as result
	FROM
	orig_ego_consumption.lak_consumption_per_district b,
	calc_ego_loads.ego_deu_load_area c
	WHERE
	c.nuts = b.eu_code
) AS sub
WHERE
sub.id = a.id;

-- Calculate sector consumption of tertiary sector per loadarea

UPDATE calc_ego_loads.ego_deu_consumption a
SET   sector_consumption_retail = sub.result 
FROM
(
	SELECT
	c.id,
	b.elec_consumption_tertiary_sector/nullif(b.area_tertiary_sector,0) * c.sector_area_retail as result
	FROM
	orig_ego_consumption.lak_consumption_per_district b,
	calc_ego_loads.ego_deu_load_area c
	WHERE
	c.nuts = b.eu_code
) AS sub
WHERE
sub.id = a.id;

-- Calculate sector consumption of agriculture per loadarea

UPDATE calc_ego_loads.ego_deu_consumption a
SET   sector_consumption_agricultural = sub.result 
FROM
(
	SELECT
	c.id,
	b.elec_consumption_tertiary_sector/nullif(b.area_tertiary_sector,0) * c.sector_area_agricultural as result
	FROM
	orig_ego_consumption.lak_consumption_per_district b,
	calc_ego_loads.ego_deu_load_area c
	WHERE
	c.nuts = b.eu_code
) AS sub
WHERE
sub.id = a.id;


-- Calculate sector consumption of households per loadarea

UPDATE calc_ego_loads.ego_deu_consumption a
SET   sector_consumption_residential = sub.result 
FROM
(
	SELECT
	c.id,
	b.elec_consumption_households_per_person * c.zensus_sum as result
	FROM 
	orig_ego_consumption.lak_consumption_per_federalstate b,
	calc_ego_loads.ego_deu_load_area c
	WHERE
	substring(c.nuts,1,3) = b.eu_code

) AS sub
WHERE
sub.id = a.id;

-- Add Geometry information in consumption table

UPDATE calc_ego_loads.ego_deu_consumption a
SET geom = b.geom
FROM calc_ego_loads.ego_deu_load_area b
WHERE a.id = b.id;


-- Identify corresponding otg_id

UPDATE calc_ego_loads.ego_deu_consumption a
	SET otg_id = b.otg_id
	FROM calc_ego_substation.ego_deu_substations b
WHERE a.subst_id = b.id; 


-- Grant oeuser   (OK!) -> 100ms =0
GRANT ALL ON TABLE 	calc_ego_loads.ego_deu_consumption TO oeuser WITH GRANT OPTION;
ALTER TABLE		calc_ego_loads.ego_deu_consumption OWNER TO oeuser;

-- Scenario eGo data processing
INSERT INTO	scenario.eGo_data_processing_clean_run (version,schema_name,table_name,script_name,entries,status,timestamp)
	SELECT	'0.1' AS version,
		'calc_ego_loads' AS schema_name,
		'ego_deu_consumption' AS table_name,
		'process_eGo_consumption.sql' AS script_name,
		COUNT(id)AS entries,
		'OK' AS status,
		NOW() AT TIME ZONE 'Europe/Berlin' AS timestamp
	FROM	calc_ego_loads.ego_deu_consumption;

---------- ---------- ----------

-- -- Consumption Join   (OK!) 10.000ms =206.846
-- DROP TABLE IF EXISTS 	calc_ego_loads.ego_deu_consumption_area CASCADE;
-- CREATE TABLE 		calc_ego_loads.ego_deu_consumption_area AS
-- 	SELECT	a.id,
-- 		a.subst_id,
{'fraction_non_alphanumeric': 0.07296603475513429, 'fraction_numerical': 0.010071090047393365, 'mean_word_length': 5.195107033639144, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 7, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4551815', 'n_tokens_mistral': 4076, 'n_tokens_neox': 3760, 'n_words': 784}