<commit_before>

DROP TABLE IF EXISTS datapoint_with_computed;
CREATE TABLE datapoint_with_computed AS

SELECT
ID
,indicator_id
,region_id
,campaign_id
,value
,CAST(0 as BOOLEAN) as is_calc
FROM datapoint

UNION ALL

SELECT
x.max_dp_id + row_number() OVER (ORDER BY d_part.campaign_id,part.indicator_id,d_part.region_id)  AS id
,part.indicator_id as master_indicator_id
,d_part.region_id
,d_part.campaign_id
,d_part.value / NULLIF(d_whole.value,0) as value
,CAST(1 as BOOLEAN) as is_calc
FROM
(
	SELECT max(id) as max_dp_id FROM datapoint
) x
INNER JOIN calculated_indicator_component part
ON 1=1
INNER JOIN calculated_indicator_component whole
ON part.indicator_id = whole.indicator_id
AND whole.calculation = 'WHOLE'
AND part.calculation = 'PART'
INNER JOIN datapoint d_part
ON part.indicator_component_id = d_part.indicator_id
INNER JOIN datapoint d_whole
ON whole.indicator_component_id = d_whole.indicator_id
AND d_part.campaign_id = d_whole.campaign_id
AND d_part.region_id = d_whole.region_id;

GRANT SELECT ON datapoint_with_computed TO djangoapp;<commit_msg>add part to be summed computed indicator ref and logic<commit_after>
DROP TABLE IF EXISTS datapoint_with_computed;
CREATE TABLE datapoint_with_computed AS

SELECT
ID
,indicator_id
,region_id
,campaign_id
,value
,CAST(0 as BOOLEAN) as is_calc
FROM datapoint;

-- make ID column auto increment
CREATE SEQUENCE dwc_seq;
ALTER TABLE datapoint_with_computed ALTER COLUMN id SET DEFAULT nextval('dwc_seq');
ALTER TABLE datapoint_with_computed ALTER COLUMN id SET NOT NULL;
ALTER SEQUENCE dwc_seq OWNED BY datapoint_with_computed.id;

----- PART / WHOLE ------
INSERT INTO datapoint_with_computed
(indicator_id,region_id,campaign_id,value,is_calc)

SELECT
part.indicator_id as master_indicator_id
,d_part.region_id
,d_part.campaign_id
,d_part.value / NULLIF(d_whole.value,0) as value
,CAST(1 as BOOLEAN) as is_calc
FROM(
	SELECT max(id) as max_dp_id FROM datapoint_with_computed
) x
INNER JOIN calculated_indicator_component part
ON 1=1
INNER JOIN calculated_indicator_component whole
ON part.indicator_id = whole.indicator_id
AND whole.calculation = 'WHOLE'
AND part.calculation = 'PART'
INNER JOIN datapoint d_part
ON part.indicator_component_id = d_part.indicator_id
INNER JOIN datapoint d_whole
ON whole.indicator_component_id = d_whole.indicator_id
AND d_part.campaign_id = d_whole.campaign_id
AND d_part.region_id = d_whole.region_id;


----- SUM OF PARTS ------
INSERT INTO datapoint_with_computed
(indicator_id,region_id,campaign_id,value,is_calc)

SELECT
DISTINCT
i_part.indicator_id
,region_id
,campaign_id
,SUM(d.value) as value
,CAST(1 as BOOLEAN) as is_calc
FROM datapoint d
INNER JOIN calculated_indicator_component i_part
ON i_part.indicator_component_id = d.indicator_id
AND i_part.calculation = 'PART_TO_BE_SUMMED'
GROUP BY i_part.indicator_id,region_id,campaign_id;

GRANT SELECT ON datapoint_with_computed TO djangoapp;
{'fraction_non_alphanumeric': 0.06625258799171843, 'fraction_numerical': 0.003795721187025535, 'mean_word_length': 6.433333333333334, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, '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': '3830435', 'n_tokens_mistral': 1118, 'n_tokens_neox': 1062, 'n_words': 274}