drop table emp1;
create table emp1(emp varchar(3), dept varchar(2), t int4range);
insert into emp1 values ('Sam', 'DB',  '[1,6)');
insert into emp1 values ('Ann', 'DB',  '[3,8)');
insert into emp1 values ('Ann', 'AI',  '[9,15)');
insert into emp1 values ('Joe', 'DB', '[14,19)');
select * from emp1;

drop table emp2;
create table emp2(emp varchar(3), dept varchar(2), t int4range);
insert into emp2 values ('Sam', 'DB',  '[4,11)');
insert into emp2 values ('Joe', 'DB', '[12,21)');
select * from emp2;

drop table proj;
create table proj(
  proj varchar(2), dept varchar(2), budg integer, t int4range);
insert into proj values ('P1', 'DB', 100,  '[2,7)');
insert into proj values ('P2', 'DB', 580, '[12,18)');
select * from proj;


-- ***********************************************************************
--  Example 1: temporal difference

--  Compute the temporal difference between emp1 and emp2, i.e., the
--  difference at each point in time.
--
--  Solution: We first normalize emp1 wrt emp2. Then we normalize emp2
--  wrt emp1. Then we compute the difference between the two
--  normalized relations.
--
-- ***********************************************************************

SELECT * FROM (emp1 NORMALIZE emp2 USING (emp, dept) WITH (t,t)) emp1a
EXCEPT
SELECT * FROM (emp2 NORMALIZE emp1 USING (emp, dept) WITH (t,t)) emp2a;

--  emp | dept |   t    
-- -----+------+--------
--  Ann | AI   | [9,15)
--  Sam | DB   | [1,4)
--  Ann | DB   | [3,8)


-- ***********************************************************************
--  Example 2: temporal aggregation

--  Compute the count of employees at each point in time.
--
--  Solution: We normalize emp wrt itself.  This splits ranges so that
--  all pair of ranges are either disjoint or identical. Standard
--  aggregation and grouping over the relation with the normalized
--  ranges yields the desired result.
--
-- ***********************************************************************

SELECT COUNT(*), t
FROM (emp1 NORMALIZE emp1 e2 USING () WITH (t,t)) emp1a
GROUP BY t
ORDER BY t;

--  count |    t    
-- -------+---------
--      1 | [1,3)
--      2 | [3,6)
--      1 | [6,8)
--      1 | [9,14)
--      2 | [14,15)
--      1 | [15,19)


-- ***********************************************************************
--  Example 3: temporal aggregation

--  For each department compute the count of employees at each point
--  in time. 
--
--  Solution: Similar to Example 2 except that we normalize the ranges
--  per department and also use the department as a grouping attribute
--  in the aggregation.
--
-- ***********************************************************************

SELECT dept, COUNT(*), t 
FROM (emp1 NORMALIZE emp1 e2 ON emp1.dept = e2.dept  WITH (t,t)) r
GROUP BY dept, t;

--  dept | count |    t    
-- ------+-------+---------
--  DB   |     2 | [3,6)
--  DB   |     1 | [14,19)
--  DB   |     1 | [6,8)
--  AI   |     1 | [9,15)
--  DB   |     1 | [1,3)

-- ***********************************************************************
--  Example 4: temporal antijoin
-- 
--  Determine the person with the largest budget. Determine the person
--  with the largest budget at each point in time.
--
--  Solution: For the first query a regular antijoin is used. It
--  yields the largest budget independent of the time. For the second
--  query we use the exact same approach except that the relations
--  must be aligned before computing the antijoin.
--
-- ***********************************************************************

drop table budg;
create table budg(name varchar(5), amnt integer, t daterange);
insert into budg values ('Joe', 5, '[2012/2/1,2012/9/1)');
insert into budg values ('Ann', 7, '[2012/5/1,2012/9/1)');
insert into budg values ('Per', 3, '[2012/4/1,2012/10/1)');
SELECT * FROM budg AS r;

SELECT *
FROM budg AS r
WHERE NOT EXISTS (
  SELECT *
  FROM budg AS s
  WHERE s.amnt > r.amnt );

--  name | amnt |            t            
-- ------+------+-------------------------
--  Ann  |    7 | [2012-05-01,2012-09-01)

SELECT *
FROM ( budg r ALIGN budg s ON s.amnt > r.amnt WITH (t,t)) r
WHERE NOT EXISTS (
  SELECT *
  FROM (budg s ALIGN budg r ON s.amnt > r.amnt WITH (t,t)) s
  WHERE s.amnt > r.amnt
  AND r.t = s.t  );

--  name | amnt |            t            
-- ------+------+-------------------------
--  Joe  |    5 | [2012-02-01,2012-05-01)
--  Ann  |    7 | [2012-05-01,2012-09-01)
--  Per  |    3 | [2012-09-01,2012-10-01)


-- ***********************************************************************
--  Example 5: average length of reservations at each point in time

--  Assume table res with hotel room reservation. For each point in time
--  determine the average duration of reservations.
--
--  Solution: We first retain and rename a copy of the range types that is
--  not affected by NORMALIZE, and then construct a temporal aggregation (as
--  as in Example 2) with the average duration over the copy of the range types.
--
-- ***********************************************************************

create table res(name varchar(5), t daterange);
insert into res values ('Ann', '[2012/1/1,2012/8/1)');
insert into res values ('Joe', '[2012/2/1,2012/7/1)');
insert into res values ('Ann', '[2012/8/1,2012/12/1)');

WITH c AS (SELECT t u, * FROM res)
SELECT cast(avg(upper(u) - lower(u)) as int), t
FROM ( c NORMALIZE res USING() with (t,t)) Radj
GROUP BY t;

--  avg |            t            
-- -----+-------------------------
--  213 | [2012-01-01,2012-02-01)
--  182 | [2012-02-01,2012-07-01)
--  213 | [2012-07-01,2012-08-01)
--  122 | [2012-08-01,2012-12-01)


-- ***********************************************************************
--  Example 6: temporal natural FOJ

--  Compute a temporal full outer natural join between emp1 and proj.
--  Include in the result the old and new interval timestamps.
--
--  Solution: We first retain and rename a copy of the range types that is
--  not affected by ALIGN, then we align emp1 wrt proj and proj
--  wrt emp1. Then we compute a NATURAL FULL JOIN between the two
--  aligned relations. 
--
--  Note: By replacing NATURAL FULL JOIN with NATURAL LEFT JOIN, NATURAL RIGHT 
--  JOIN or NATURAL JOIN the respective join type is performed.
--
-- ***********************************************************************

WITH
  emp1P AS ( SELECT emp, dept, t t1, t FROM emp1 ),
  projP AS ( SELECT proj, dept, t t2, t FROM proj )
SELECT r.emp, r.dept, r.t1, s.proj, s.dept, s.t2, t
FROM ( emp1P ALIGN proj ON emp1P.dept=proj.dept with (t,t)) r
     NATURAL FULL JOIN
     ( projP ALIGN emp1 ON emp1.dept=projP.dept with (t,t)) s
WHERE t = t1 * t2 OR t1 IS NULL OR t2 IS NULL;

--  emp | dept |   t1    | proj | dept |   t2    |    t    
-- -----+------+---------+------+------+---------+---------
--  Ann | AI   | [9,15)  |      |      |         | [9,15)
--  Sam | DB   | [1,6)   |      |      |         | [1,2)
--  Sam | DB   | [1,6)   | P1   | DB   | [2,7)   | [2,6)
--  Ann | DB   | [3,8)   | P1   | DB   | [2,7)   | [3,7)
--  Ann | DB   | [3,8)   |      |      |         | [7,8)
--      |      |         | P2   | DB   | [12,18) | [12,14)
--  Joe | DB   | [14,19) | P2   | DB   | [12,18) | [14,18)
--  Joe | DB   | [14,19) |      |      |         | [18,19)