# rollup // combining data sources PoC


#### Summary
PoC demonstrating rolling up source data to a parent view, supporting start and end dates in the aggregator configurations.

### schema definition

create table iot_source_data (id numeric, date date, unit varchar, value numeric);
create index iot_source_data_idx on iot_source_data (id, date);

create table iot_source_aggregator (id numeric, property_id numeric, data_id numeric, start_date date, end_date date);
create index iot_source_aggregator_idx on iot_source_aggregator (property_id);

create table other_data (id numeric, date date, unit varchar, value numeric);
create index other_data_idx on other_data (id, date);

create table other_aggregator (id numeric, property_id numeric, data_id numeric, start_date date, end_date date);
create index other_aggregator_idx on other_aggregator (property_id);

create or replace view iot_source_property_data as (
select
  property_id,
  data_id,
  sum(value) as value,
  date
from
  iot_source_aggregator
    inner join iot_source_data on data_id = iot_source_data.id
where
  (date >= start_date or start_date is null) and 
  (date <= end_date or end_date is null)
group by
  property_id,
  data_id,
  date
);


create or replace view other_property_data as (
select
  property_id,
  data_id,
  sum(value) as value,
  date
from
  other_aggregator
    inner join other_data on data_id = other_data.id
where
  (date >= start_date or start_date is null) and 
  (date <= end_date or end_date is null)
group by
  property_id,
  data_id,
  date
);


create view property_data as (
  select * from iot_source_property_data 
    union all
  select * from other_property_data
);

### data load

insert into iot_source_aggregator values (1, 10, 1, '2022-01-01', '2022-01-05');
insert into iot_source_aggregator values (2, 10, 2, '2022-01-06', '2022-01-10');

insert into iot_source_data values (1, '2022-01-01', 'kwh', 1);
insert into iot_source_data values (1, '2022-01-02', 'kwh', 1);
insert into iot_source_data values (1, '2022-01-03', 'kwh', 1);
insert into iot_source_data values (1, '2022-01-04', 'kwh', 1);
insert into iot_source_data values (1, '2022-01-05', 'kwh', 1);
insert into iot_source_data values (1, '2022-01-06', 'kwh', 1);
insert into iot_source_data values (1, '2022-01-07', 'kwh', 1);
insert into iot_source_data values (1, '2022-01-08', 'kwh', 1);
insert into iot_source_data values (1, '2022-01-09', 'kwh', 1);
insert into iot_source_data values (1, '2022-01-10', 'kwh', 1);

insert into iot_source_data values (2, '2022-01-01', 'kwh', 2);
insert into iot_source_data values (2, '2022-01-02', 'kwh', 2);
insert into iot_source_data values (2, '2022-01-03', 'kwh', 2);
insert into iot_source_data values (2, '2022-01-04', 'kwh', 2);
insert into iot_source_data values (2, '2022-01-05', 'kwh', 2);
insert into iot_source_data values (2, '2022-01-06', 'kwh', 2);
insert into iot_source_data values (2, '2022-01-07', 'kwh', 2);
insert into iot_source_data values (2, '2022-01-08', 'kwh', 2);
insert into iot_source_data values (2, '2022-01-09', 'kwh', 2);
insert into iot_source_data values (2, '2022-01-10', 'kwh', 2);


insert into meter_aggregator_data_stream values (null, '00000000000000000000000000000000', '1', 'iot_source'::data_stream_type, '2022-01-01', '2022-01-05');
insert into meter_aggregator_data_stream values (null, '00000000000000000000000000000000', '2', 'iot_source'::data_stream_type, '2022-01-06', '2022-01-10');

insert into meter_aggregator values ('00000000000000000000000000000000', 'agg1', 50, 50, 10, 'kwh');

### usage

select
  sum(value) as value,
  date_trunc('month', date) as date,
  property_id
from
  property_data
where 
  property_id = 10
group by
  property_id,
  date_trunc('month', date);

### clean up

drop view property_data;
drop view iot_source_property_data;
drop view other_property_data;
drop table iot_source_data;
drop table iot_source_aggregator;
drop table other_data;
drop table other_aggregator;