commit 85e355c7f56ce900a76ea182246f5454c1d4a3d4
parent 104613dcc3a364a1ae098f2081579abf4e9bc2f7
Author: Christian Grothoff <grothoff@gnunet.org>
Date: Sat, 22 Mar 2025 07:11:53 +0100
more work on statistics
Diffstat:
1 file changed, 191 insertions(+), 7 deletions(-)
diff --git a/src/backenddb/pg_statistics_helpers.sql b/src/backenddb/pg_statistics_helpers.sql
@@ -14,9 +14,6 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-BEGIN;
-SET search_path TO merchant;
-
DROP FUNCTION IF EXISTS interval_to_start;
CREATE OR REPLACE FUNCTION interval_to_start (
IN in_timestamp TIMESTAMP,
@@ -453,12 +450,10 @@ BEGIN
my_rval.rvalue = 0;
my_ranges = my_rec.ranges;
my_meta = my_rec.imeta_serial_id;
- -- my_precisions = my_rec.precisions;
FOR my_i IN 1..COALESCE(array_length(my_ranges,1),0)
LOOP
my_range = my_ranges[my_i];
- -- my_precision = my_precisions[my_i];
SELECT event_delimiter
,cumulative_number
INTO my_irec
@@ -496,6 +491,21 @@ BEGIN
WHERE imeta_serial_id = my_meta
AND merchant_serial = in_merchant_serial
AND range=my_ranges[my_i+1];
+ IF NOT FOUND
+ THEN
+ INSERT INTO merchant_statistic_interval_counter
+ (imeta_serial_id
+ ,merchant_serial
+ ,range
+ ,event_delimiter
+ ,cumulative_number
+ ) VALUES (
+ my_meta
+ ,in_merchant_serial
+ ,my_ranges[my_i+1]
+ ,my_max_serial
+ ,in_delta);
+ END IF;
ELSE
-- events are obsolete, delete them
DELETE FROM merchant_statistic_counter_event
@@ -523,8 +533,183 @@ CREATE OR REPLACE FUNCTION merchant_statistic_interval_amount_get (
RETURNS SETOF merchant_statistic_interval_amount_get_return_value
LANGUAGE plpgsql
AS $$
+DECLARE
+ my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM NOW()) * 1000000)::INT8;
+ my_ranges INT8[];
+ my_range INT8;
+ my_delta_value INT8;
+ my_delta_frac INT8;
+ my_meta INT8;
+ my_instance_id INT8;
+ my_currency TEXT;
+ my_curs CURSOR (meta_serial_id INT8,instid INT8,curr TEXT,range INT8,min_serial INT8) FOR
+ SELECT nevent_serial_id
+ ,delta_value
+ ,delta_frac
+ FROM merchant_statistic_amount_event
+ WHERE imeta_serial_id = meta_serial_id
+ AND instance_id = instid
+ AND delta_curr = curr
+ AND slot < my_time - range
+ AND imeta_serial_id >= min_serial
+ ORDER BY slot ASC;
+ my_rec RECORD;
+ my_irec RECORD;
+ my_i INT;
+ my_max_serial INT8 DEFAULT NULL;
+ my_rval merchant_statistic_interval_amount_get_return_value;
BEGIN
- -- FIXME: implement!
+ SELECT merchant_serial
+ INTO my_instance_id
+ FROM merchant_instances
+ WHERE merchant_id=in_instance_id;
+ IF NOT FOUND
+ THEN
+ RETURN;
+ END IF;
+
+ SELECT imeta_serial_id
+ ,ranges
+ ,precisions
+ INTO my_rec
+ FROM merchant_statistic_interval_meta
+ WHERE slug=in_slug;
+ IF NOT FOUND
+ THEN
+ RETURN;
+ END IF;
+
+ my_meta = my_rec.imeta_serial_id;
+ my_ranges = my_rec.ranges;
+
+ FOR my_currency IN
+ SELECT DISTINCT delta_curr
+ FROM merchant_statistic_amount_event
+ WHERE imeta_serial_id = my_meta
+ LOOP
+
+ my_rval.rvalue.val = 0;
+ my_rval.rvalue.frac = 0;
+ my_rval.rvalue.curr = my_currency;
+
+ FOR my_i IN 1..COALESCE(array_length(my_ranges,1),0)
+ LOOP
+ my_range = my_ranges[my_i];
+ SELECT event_delimiter
+ ,cumulative_value
+ ,cumulative_frac
+ INTO my_irec
+ FROM merchant_statistic_interval_amount
+ WHERE imeta_serial_id = my_meta
+ AND merchant_serial = in_merchant_serial
+ AND curr = my_currency
+ AND range = my_range;
+
+ my_max_serial = my_irec.event_delimiter;
+ my_rval.rvalue.val = my_rval.rvalue.val + my_irec.cumulative_value + my_irec.cummulative_frac / 100000000;
+ my_rval.rvalue.frac = my_rval.rvalue.frac + my_irec.cummulative_frac % 100000000;
+ IF (my_rval.rvalue).frac > 100000000
+ THEN
+ my_rval.rvalue.frac = my_rval.rvalue.frac - 100000000;
+ my_rval.rvalue.val = my_rval.rvalue.val + 1;
+ END IF;
+
+ my_delta_value = 0;
+ my_delta_frac = 0;
+ OPEN my_curs (meta_serial_id := my_meta, instid := my_instance_id, curr = my_currency, range := my_range, min_serial := my_max_serial);
+ LOOP
+ FETCH NEXT FROM my_curs INTO my_irec;
+ EXIT WHEN NOT FOUND;
+ my_delta_value = my_delta_value + (my_irec).delta_value;
+ my_delta_frac = my_delta_frac + (my_irec).delta_frac;
+ my_max_serial = my_irec.nevent_serial_id;
+ END LOOP;
+
+ IF (0 != my_delta_value + my_delta_frac)
+ THEN
+ -- Normalize my_delta first
+ my_delta_value = my_delta_value + my_delta_frac / 100000000;
+ my_delta_frac = my_delta_frac % 100000000;
+ -- remove expired events from the sum of the current slot
+ my_rval.rvalue.val = my_rval.rvalue.val - my_delta_value;
+ IF (my_rval.rvalue.frac > my_delta_frac)
+ THEN
+ my_rval.rvalue.frac = (my_rval.rvalue).frac - my_delta_frac;
+ ELSE
+ my_rval.rvalue.frac = 100000000 + my_rval.rvalue.frac - my_delta_frac;
+ my_rval.rvalue.val = my_rval.rvalue.val - 1;
+ END IF;
+ UPDATE merchant_statistic_interval_amount SET
+ cumulative_value = cumulative_value - my_delta_value
+ - CASE
+ WHEN cummulative_frac < my_delta_frac
+ THEN 1
+ ELSE 0
+ END,
+ cumulative_frac = cumulative_frac - my_delta_frac
+ + CASE
+ WHEN cummulative_frac < my_delta_frac
+ THEN 100000000
+ ELSE 0
+ END,
+ event_delimiter = my_max_serial + 1
+ WHERE imeta_serial_id = my_meta
+ AND merchant_serial = in_merchant_serial
+ AND curr = my_currency
+ AND range = my_range;
+ IF (my_i < array_length(my_ranges,1))
+ THEN
+ -- carry over events into the next (larger) slot
+ UPDATE merchant_statistic_interval_amount SET
+ cumulative_value = cumulative_value + my_delta_value
+ + CASE
+ WHEN cummulative_frac + my_delta_frac > 100000000
+ THEN 1
+ ELSE 0
+ END,
+ cumulative_frac = cumulative_frac + my_delta_value
+ - CASE
+ WHEN cummulative_frac + my_delta_frac > 100000000
+ THEN 100000000
+ ELSE 0
+ END
+ WHERE imeta_serial_id = my_meta
+ AND merchant_serial = in_merchant_serial
+ AND range=my_ranges[my_i+1];
+ IF NOT FOUND
+ THEN
+ INSERT INTO merchant_statistic_interval_amount
+ (imeta_serial_id
+ ,merchant_serial
+ ,aevent_serial_id
+ ,range
+ ,curr
+ ,cummulative_value
+ ,cummulative_frac
+ ) VALUES (
+ my_meta
+ ,in_merchant_serial
+ ,my_max_serial
+ ,my_ranges[my_i+1]
+ ,my_currency
+ ,in_delta_value
+ ,in_delta_frac);
+ END IF;
+ ELSE
+ -- events are obsolete, delete them
+ DELETE FROM merchant_statistic_amount_event
+ WHERE imeta_serial_id = my_meta
+ AND instance_id = my_instance_id
+ AND slot < my_time - my_range;
+ END IF;
+ END IF;
+
+ my_rval.range = my_range;
+ RETURN NEXT my_val;
+ END LOOP;
+ CLOSE my_curs;
+
+ END LOOP; -- over my_currency
END $$;
COMMENT ON FUNCTION merchant_statistic_interval_amount_get
@@ -762,4 +947,3 @@ END $$;
COMMENT ON PROCEDURE merchant_statistic_counter_gc
IS 'Performs garbage collection of the merchant_statistic_bucket_counter and merchant_statistic_bucket_amount tables';
-
