rangejoin.sql

text/x-sql

Download

-- rate is billing rate in dollars per hour
CREATE OR REPLACE FUNCTION
  bill(rate NUMERIC, during TSTZRANGE)
RETURNS NUMERIC LANGUAGE plpgsql AS
$$
DECLARE
  usage_s NUMERIC;
BEGIN
  IF isempty(during) THEN
    usage_s := 0;
  ELSE
    usage_s := extract(epoch from (upper(during) - lower(during)));
  END IF;
  RETURN rate * (usage_s/3600.0);
END;
$$;

CREATE TABLE billing_rate(rate NUMERIC, during TSTZRANGE);
CREATE TABLE billing_usage(customer_id INT8, during TSTZRANGE);

INSERT INTO billing_rate VALUES
  (12.50, '[2010-01-01 14:00, 2010-01-01 15:00)');
INSERT INTO billing_rate VALUES
  (14.50, '[2010-01-01 15:00, 2010-01-01 15:45)');

INSERT INTO billing_usage VALUES
  (123, '[2010-01-01 14:30, 2010-01-01 15:30)');
INSERT INTO billing_usage VALUES
  (234, '[2010-01-01 14:15, 2010-01-01 15:45)');

SELECT
  customer_id,
  bill(rate, range_intersect(u.during, r.during)) AS bill
FROM billing_rate r, billing_usage u
WHERE r.during && u.during;