Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION f_agregate_percentage(character varying, character varying, character varying, integer, integer, integer)
- RETURNS void AS
- $BODY$
- DECLARE
- pSessionId ALIAS FOR $1;
- pIdData ALIAS FOR $2;
- pDelimeter ALIAS FOR $3;
- pChild ALIAS FOR $4;
- pParent ALIAS FOR $5;
- pDecimalPoint ALIAS FOR $6;
- BEGIN
- DELETE FROM tt_dashboard_data WHERE session_id = pSessionId AND id_data = pIdData;
- DELETE FROM tt_dashboard_data_precision WHERE session_id = pSessionId AND id_data = pIdData;
- DELETE FROM tt_dashboard_measure_precision WHERE session_id = pSessionId AND id_data = pIdData;
- IF pChild = 1 THEN
- UPDATE tt_input_data SET child_group = data_group_1 WHERE session_id = pSessionId AND id_data = pIdData;
- ELSE
- IF pChild = 2 THEN
- UPDATE tt_input_data SET child_group = data_group_1||pDelimeter||data_group_2 WHERE session_id = pSessionId AND id_data = pIdData;
- ELSE
- IF pChild = 3 THEN
- UPDATE tt_input_data SET child_group = data_group_1||pDelimeter||data_group_2||pDelimeter||data_group_3 WHERE session_id = pSessionId AND id_data = pIdData;
- ELSE
- IF pChild = 4 THEN
- UPDATE tt_input_data SET child_group = data_group_1||pDelimeter||data_group_2||pDelimeter||data_group_3||pDelimeter||data_group_4 WHERE session_id = pSessionId AND id_data = pIdData;
- ELSE
- UPDATE tt_input_data SET child_group = data_group_1||pDelimeter||data_group_2||pDelimeter||data_group_3||pDelimeter||data_group_4||pDelimeter||data_group_5 WHERE session_id = pSessionId AND id_data = pIdData;
- END IF;
- END IF;
- END IF;
- END IF;
- IF pParent = 1 THEN
- UPDATE tt_input_data SET parent_group = data_group_1 WHERE session_id = pSessionId AND id_data = pIdData;
- ELSE
- IF pParent = 2 THEN
- UPDATE tt_input_data SET parent_group = data_group_1||pDelimeter||data_group_2 WHERE session_id = pSessionId AND id_data = pIdData;
- ELSE
- IF pParent = 3 THEN
- UPDATE tt_input_data SET parent_group = data_group_1||pDelimeter||data_group_2||pDelimeter||data_group_3 WHERE session_id = pSessionId AND id_data = pIdData;
- ELSE
- IF pParent = 4 THEN
- UPDATE tt_input_data SET parent_group = data_group_1||pDelimeter||data_group_2||pDelimeter||data_group_3||pDelimeter||data_group_4 WHERE session_id = pSessionId AND id_data = pIdData;
- ELSE
- UPDATE tt_input_data SET parent_group = data_group_1||pDelimeter||data_group_2||pDelimeter||data_group_3||pDelimeter||data_group_4||pDelimeter||data_group_5 WHERE session_id = pSessionId AND id_data = pIdData;
- END IF;
- END IF;
- END IF;
- END IF;
- INSERT INTO tt_group_data
- (session_id,id_data,data_group_1,data_group_2,data_group_3,data_group_4,data_group_5,
- child_group,parent_group)
- SELECT session_id, id_data, data_group_1, data_group_2,
- data_group_3, data_group_4, data_group_5,
- child_group, parent_group
- FROM tt_input_data
- WHERE session_id = pSessionId AND
- id_data = pIdData
- GROUP BY session_id, id_data, data_group_1, data_group_2, data_group_3, data_group_4, data_group_5,
- child_group,parent_group;
- INSERT INTO tt_dashboard_data
- (session_id,id_data,child_group,parent_group,
- sum_measure, min_measure,max_measure,avg_measure,
- sub_total, total, percentage,
- total_measure, percentage_measure)
- SELECT DISTINCT session_id, id_data, child_group, parent_group,
- SUM(data_measure) OVER (PARTITION BY child_group),
- MIN(data_measure) OVER (PARTITION BY child_group),
- MAX(data_measure) OVER (PARTITION BY child_group),
- AVG(data_measure) OVER (PARTITION BY child_group),
- SUM(data_count) OVER (PARTITION BY child_group),
- SUM(data_count) OVER (PARTITION BY parent_group),
- 100,
- SUM(data_measure) OVER (PARTITION BY parent_group),
- 100
- FROM tt_input_data
- WHERE session_id = pSessionId AND id_data = pIdData
- ORDER BY child_group;
- UPDATE tt_dashboard_data SET percentage = ROUND(100*sub_total/total,pDecimalPoint)::numeric WHERE session_id = pSessionId AND id_data = pIdData;
- UPDATE tt_dashboard_data SET percentage_measure = ROUND(100*sum_measure/total_measure,pDecimalPoint)::numeric WHERE session_id = pSessionId AND id_data = pIdData;
- INSERT INTO tt_dashboard_data_precision
- (session_id, id_data, rank_id,
- child_group, percentage,
- total_percentage, sum_percentage)
- SELECT session_id, id_data, RANK() OVER (PARTITION BY parent_group ORDER BY parent_group, sub_total DESC, id DESC),
- child_group, percentage,
- 100, SUM(percentage) OVER (PARTITION BY parent_group)
- FROM tt_dashboard_data
- WHERE session_id = pSessionId AND id_data = pIdData;
- UPDATE tt_dashboard_data_precision
- SET percentage = percentage - (sum_percentage - total_percentage)
- WHERE session_id = pSessionId AND
- rank_id = 1 AND
- id_data = pIdData;
- INSERT INTO tt_dashboard_measure_precision
- (session_id, id_data, rank_id,
- child_group, percentage,
- total_percentage, sum_percentage)
- SELECT session_id, id_data, RANK() OVER (PARTITION BY parent_group ORDER BY parent_group, sum_measure DESC, id DESC),
- child_group, percentage_measure,
- 100, SUM(percentage_measure) OVER (PARTITION BY parent_group)
- FROM tt_dashboard_data
- WHERE session_id = pSessionId AND id_data = pIdData;
- UPDATE tt_dashboard_measure_precision
- SET percentage = percentage - (sum_percentage - total_percentage)
- WHERE session_id = pSessionId AND
- rank_id = 1 AND
- id_data = pIdData;
- INSERT INTO tt_output_data
- (session_id, id_data,data_group_1,data_group_2,
- data_group_3,data_group_4,data_group_5,
- child_group,parent_group,
- sum_measure,min_measure,max_measure,avg_measure,
- sub_total,total,percentage,
- total_measure, percentage_measure)
- SELECT a.session_id, a.id_data, c.data_group_1, c.data_group_2,
- c.data_group_3, c.data_group_4, c.data_group_5,
- a.child_group, a.parent_group,
- ROUND(a.sum_measure,pDecimalPoint) AS sum_measure,
- ROUND(a.min_measure,pDecimalPoint) AS min_measure,
- ROUND(a.max_measure,pDecimalPoint) AS max_measure,
- ROUND(a.avg_measure,pDecimalPoint) AS avg_measure,
- a.sub_total, a.total,
- b.percentage,
- a.total_measure, d.percentage
- FROM tt_dashboard_data a,tt_dashboard_data_precision b,tt_group_data c, tt_dashboard_measure_precision d
- WHERE a.session_id = b.session_id AND
- a.child_group = b.child_group AND
- a.id_data = b.id_data AND
- a.session_id = pSessionId AND
- a.id_data = pIdData AND
- a.session_id = c.session_id AND
- a.id_data = c.id_data AND
- a.child_group = c.child_group AND
- a.session_id = d.session_id AND
- a.child_group = d.child_group AND
- a.id_data = d.id_data
- ORDER BY a.child_group;
- DELETE FROM tt_dashboard_data WHERE session_id = pSessionId AND id_data = pIdData;
- DELETE FROM tt_dashboard_data_precision WHERE session_id = pSessionId AND id_data = pIdData;
- DELETE FROM tt_dashboard_measure_precision WHERE session_id = pSessionId AND id_data = pIdData;
- DELETE FROM tt_input_data WHERE session_id = pSessionId AND id_data=pIdData;
- DELETE FROM tt_group_data WHERE session_id = pSessionId AND id_data=pIdData;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement