hll_insert_select.sql

CREATE TABLE http_request (
  site_id INT,
  ingest_time TIMESTAMPTZ DEFAULT now(),

  url TEXT,
  request_country TEXT,
  ip_address TEXT,

  status_code INT,
  response_time_msec INT
);



CREATE TABLE http_request_1min (
      site_id INT,
      ingest_time TIMESTAMP, -- which minute this row represents

      error_count INT,
      success_count INT,
      request_count INT,
      average_response_time_msec INT,
      CHECK (request_count = error_count + success_count),
      CHECK (ingest_time = date_trunc('minute', ingest_time)),
      distinct_ip_addresses hll
);

SELECT create_distributed_table('http_request', 'site_id');
SELECT create_distributed_table('http_request_1min', 'site_id');

CREATE AGGREGATE sum (hll) ( sfunc = hll_union_trans, stype = internal, finalfunc = hll_pack );

INSERT INTO http_request_1min (site_id, request_count, success_count, error_count, average_response_time_msec)
SELECT site_id,
	   request_count, 
       success_count, 
       error_count, 
       Hll_cardinality(distinct_ip_addresses)::int AS distinct_ip_address_count 
FROM   http_request_1min 
WHERE  site_id = 1; 



INSERT INTO http_request_1min (site_id, average_response_time_msec)
SELECT site_id, hll_cardinality(SUM(distinct_ip_addresses)) 
FROM   http_request_1min 
WHERE  ingest_time BETWEEN timestamp '06-01-2016' AND '06-28-2016'
GROUP BY site_id;