lookup_statistics_counter_by_interval.sql (4766B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2025 Taler Systems SA 4 -- 5 -- TALER is free software; you can redistribute it and/or modify it under the 6 -- terms of the GNU General Public License as published by the Free Software 7 -- Foundation; either version 3, or (at your option) any later version. 8 -- 9 -- TALER is distributed in the hope that it will be useful, but WITHOUT ANY 10 -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 11 -- A PARTICULAR PURPOSE. See the GNU General Public License for more details. 12 -- 13 -- You should have received a copy of the GNU General Public License along with 14 -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 -- 16 17 18 DROP FUNCTION IF EXISTS merchant_statistic_interval_number_get; 19 CREATE FUNCTION merchant_statistic_interval_number_get ( 20 IN in_slug TEXT 21 ) 22 RETURNS SETOF merchant.merchant_statistic_interval_number_get_return_value 23 LANGUAGE plpgsql 24 AS $$ 25 DECLARE 26 my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000; 27 my_ranges INT8[]; 28 my_range INT8; 29 my_delta INT8; 30 my_meta INT8; 31 my_next_max_serial INT8; 32 my_rec RECORD; 33 my_irec RECORD; 34 my_i INT; 35 my_min_serial INT8 DEFAULT NULL; 36 my_rval merchant.merchant_statistic_interval_number_get_return_value; 37 BEGIN 38 SELECT imeta_serial_id 39 ,ranges 40 ,precisions 41 INTO my_rec 42 FROM merchant_statistic_interval_meta 43 WHERE slug=in_slug; 44 IF NOT FOUND 45 THEN 46 RETURN; 47 END IF; 48 my_rval.rvalue = 0; 49 my_ranges = my_rec.ranges; 50 my_meta = my_rec.imeta_serial_id; 51 52 FOR my_i IN 1..COALESCE(array_length(my_ranges,1),0) 53 LOOP 54 my_range = my_ranges[my_i]; 55 SELECT event_delimiter 56 ,cumulative_number 57 INTO my_irec 58 FROM merchant_statistic_interval_counter 59 WHERE imeta_serial_id = my_meta 60 AND range = my_range; 61 IF FOUND 62 THEN 63 my_min_serial = my_irec.event_delimiter; 64 my_rval.rvalue = my_rval.rvalue + my_irec.cumulative_number; 65 66 -- Check if we have events that left the applicable range 67 SELECT SUM(delta) AS delta_sum 68 INTO my_irec 69 FROM merchant_statistic_counter_event 70 WHERE imeta_serial_id = my_meta 71 AND slot < my_time - my_range 72 AND nevent_serial_id >= my_min_serial; 73 74 IF FOUND AND my_irec.delta_sum IS NOT NULL 75 THEN 76 my_delta = my_irec.delta_sum; 77 my_rval.rvalue = my_rval.rvalue - my_delta; 78 79 -- First find out the next event delimiter value 80 SELECT nevent_serial_id 81 INTO my_next_max_serial 82 FROM merchant_statistic_counter_event 83 WHERE imeta_serial_id = my_meta 84 AND slot >= my_time - my_range 85 AND nevent_serial_id >= my_min_serial 86 ORDER BY slot ASC 87 LIMIT 1; 88 89 IF FOUND 90 THEN 91 -- remove expired events from the sum of the current slot 92 93 UPDATE merchant_statistic_interval_counter 94 SET cumulative_number = cumulative_number - my_delta, 95 event_delimiter = my_next_max_serial 96 WHERE imeta_serial_id = my_meta 97 AND range = my_range; 98 ELSE 99 -- actually, slot is now empty, remove it entirely 100 DELETE FROM merchant_statistic_interval_counter 101 WHERE imeta_serial_id = my_meta 102 AND range = my_range; 103 END IF; 104 IF (my_i < COALESCE(array_length(my_ranges,1),0)) 105 THEN 106 -- carry over all events into the next slot 107 UPDATE merchant_statistic_interval_counter AS usic SET 108 cumulative_number = cumulative_number + my_delta, 109 event_delimiter = LEAST(usic.event_delimiter,my_min_serial) 110 WHERE imeta_serial_id = my_meta 111 AND range=my_ranges[my_i+1]; 112 IF NOT FOUND 113 THEN 114 INSERT INTO merchant_statistic_interval_counter 115 (imeta_serial_id 116 ,range 117 ,event_delimiter 118 ,cumulative_number 119 ) VALUES ( 120 my_meta 121 ,my_ranges[my_i+1] 122 ,my_min_serial 123 ,my_delta); 124 END IF; 125 ELSE 126 -- events are obsolete, delete them 127 DELETE FROM merchant_statistic_counter_event 128 WHERE imeta_serial_id = my_meta 129 AND slot < my_time - my_range; 130 END IF; 131 END IF; 132 133 my_rval.range = my_range; 134 RETURN NEXT my_rval; 135 END IF; 136 END LOOP; 137 END $$; 138 139 COMMENT ON FUNCTION merchant_statistic_interval_number_get 140 IS 'Returns deposit statistic tracking deposited amounts over certain time intervals; we first trim the stored data to only track what is still in-range, and then return the remaining value for each range';