<commit_before>DROP FUNCTION IF EXISTS fn_calc_sum_of_parts(cache_job_id int);
CREATE FUNCTION fn_calc_sum_of_parts(cache_job_id int)
RETURNS TABLE(id int) AS
$func$
BEGIN

  ---- SUM OF PARTS ------

    WITH RECURSIVE ind_graph AS
    (
    -- non-recursive term ( rows where the components aren't
    -- master_indicators in another calculation )

  	SELECT
    		 cic.id
  		,cic.indicator_id
  		,cic.indicator_component_id
  		--,0 as lvl
  	FROM calculated_indicator_component cic
  	WHERE NOT EXISTS (
  		SELECT 1 FROM calculated_indicator_component cic_leaf
  		WHERE cic.indicator_component_id = cic_leaf.indicator_id<commit_msg>prefer data for indicators higher up in the indicator_tree<commit_after>DROP FUNCTION IF EXISTS fn_calc_sum_of_parts(cache_job_id int);
CREATE FUNCTION fn_calc_sum_of_parts(cache_job_id int)
RETURNS TABLE(id int) AS
$func$
BEGIN


    -- THIS QUERY ENSURES THAT IF DATA IS STORED AT A HIGHER lvl
    -- THEN IT's SUB COMPONENTS, DATA AT THE HIGHER LEVEL WINS
        --> for instance, if i have data stored at indicator_id 251
        --> as well as the component indicators of 251 ( 24,251,267,268,264 )
        --> we will take the data for 251 over it's sub-components

    INSERT INTO _tmp_calc_datapoint
    (indicator_id,location_id,campaign_id,value)

    SELECT
    cic.indicator_id
      , dwc.location_id
      , dwc.campaign_id
      , SUM(COALESCE(dwc.value,0.00)) as agg_value
    FROM calculated_indicator_component cic
    INNER JOIN _tmp_calc_datapoint dwc
    ON 1 = 1
    AND cic.indicator_component_id = dwc.indicator_id
    AND calculation = 'PART_TO_BE_SUMMED'
    AND NOT EXISTS (
      SELECT 1 FROM _tmp_calc_datapoint tcd
      WHERE dwc.location_id = tcd.location_id
      AND dwc.campaign_id = tcd.campaign_id
      AND cic.indicator_id = tcd.indicator_id
    )

    GROUP BY cic.indicator_id, dwc.location_id, dwc.campaign_id;

    -- THIS HANDLES INDICATORS WHERE THE SUM IS MULTI LAYERED see:
    -- http://rhizome.work/ufadmin/manage/indicator/21
    -- http://rhizome.work/ufadmin/manage/indicator/251

    WITH RECURSIVE ind_graph AS
    (
    -- non-recursive term ( rows where the components aren't
    -- master_indicators in another calculation )

  	SELECT
    		 cic.id
  		,cic.indicator_id
  		,cic.indicator_component_id
  		--,0 as lvl
  	FROM calculated_indicator_component cic
  	WHERE NOT EXISTS (
  		SELECT 1 FROM calculated_indicator_component cic_leaf
  		WHERE cic.indicator_component_id = cic_leaf.indicator_id
{'fraction_non_alphanumeric': 0.06594724220623502, 'fraction_numerical': 0.015187849720223821, 'mean_word_length': 3.2067226890756304, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 6, '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': '3948093', 'n_tokens_mistral': 955, 'n_tokens_neox': 868, 'n_words': 226}