drop table r cascade;
create table r(S integer, E integer, X char(1));

insert into r values ( 1,  5, 'a');
insert into r values ( 3,  7, 'a');
insert into r values ( 7,  9, 'a');
insert into r values (11, 12, 'a');
insert into r values ( 4, 12, 'b');

select * from r;

analyze;

-----------------------------------------------
-- Task 1.1: coalescing with iterated SQL statement

drop function r_coal();

CREATE OR REPLACE FUNCTION r_coal ()
RETURNS TABLE (S integer, E integer, X char(1)) AS
$$
BEGIN
  CREATE TEMP TABLE r_coal ON COMMIT DROP AS select * from r;

  LOOP
    INSERT INTO r_coal
      SELECT DISTINCT l.S, h.E, l.X
      FROM r_coal l, r_coal h
      WHERE l.X = h.X
      AND l.S < h.S
      AND h.S <= l.E
      AND l.E < h.E
      EXCEPT
      SELECT * FROM r_coal;

    IF NOT FOUND THEN RETURN QUERY
      SELECT * FROM r_coal AS r1 WHERE NOT EXISTS (
           SELECT * FROM r_coal AS r2
           WHERE r1.X=r2.X AND r2.S<r1.S AND r2.E>=r1.E
              OR r1.X=r2.X AND r2.S<=r1.S AND r2.E>r1.E);
      EXIT;
    END IF;
  END LOOP;
END;
$$ LANGUAGE PLPGSQL;

SELECT * FROM r_coal();

-----------------------------------------------
-- Task 1.2: coalesing in plain SQL-89

SELECT distinct f.S, l.E, f.X
FROM r f, r l
WHERE f.S < l.E
AND f.X = l.X
AND NOT EXISTS (
  SELECT *
  FROM r m
  WHERE m.X = f.X
  AND f.S < m.S AND m.S < l.E
  AND NOT EXISTS (
    SELECT *
    FROM r a1
    WHERE a1.X = f.X
    AND a1.S < m.S AND m.S <= a1.E))
AND NOT EXISTS (
  SELECT *
  FROM r a2
  WHERE a2.X = f.X
  AND (a2.S < f.S AND f.S <= a2.E OR
       a2.S <= l.E AND l.E < a2.E));

-----------------------------------------------
-- Task 1.4: coalescing with window function

WITH
T1 AS (
  SELECT 1 AS S, 0 AS E, S AS T, X FROM r
  UNION ALL
  SELECT 0, 1, E T, X FROM r
),
T2 AS (
  SELECT
  SUM(S) OVER (PARTITION BY X ORDER BY T, E) cS,
  SUM(E) OVER (PARTITION BY X ORDER BY T, E) cE,
  SUM(S) OVER (PARTITION BY X ORDER BY T, E) - S pS,
  SUM(E) OVER (PARTITION BY X ORDER BY T, E) - E pE,
  T,
  X
  FROM T1
),
T3 AS (
  SELECT cS, cE, X, LAG(T, 1) OVER (ORDER BY X, T) AS S, T AS E
  FROM T2 
  WHERE cS = cE
  OR pS = pE 
)
SELECT X, S, E FROM T3 WHERE cS = cE;

-----------------------------------------------
-- Task 1.3: coalescing with a cursor

drop function r_coal();

CREATE OR REPLACE FUNCTION r_coal ()
RETURNS SETOF r AS
$$
DECLARE
  curs CURSOR FOR SELECT * FROM r ORDER BY X, S;
  c r%ROWTYPE;
  n r%ROWTYPE;
BEGIN
  OPEN curs;
  FETCH curs INTO c;
  LOOP
    FETCH curs INTO n;
    IF NOT FOUND THEN EXIT; END IF;
    IF c.X = n.X AND n.S <= c.E THEN
      c.E = n.E;
    ELSE
      RETURN NEXT c;
      c = n;
    END IF;
  END LOOP;
  RETURN NEXT c;
END;
$$ LANGUAGE PLPGSQL;

SELECT * FROM r_coal();

-----------------------------------------------

drop table e;
create table e(n char, d char, s integer);
insert into e values('a', 'x', 3);
insert into e values('b', 'x', 7);
insert into e values('c', 'x', 8);
insert into e values('d', 'y', 5);
insert into e values('e', 'y', 9);
select * from e;

SELECT d, n, s, avg(s) OVER (PARTITION BY d) FROM e;

 d | n | s | avg         
---+---+---+-----
 x | a | 3 |   6
 x | b | 7 |   6
 x | c | 8 |   6
 y | d | 5 |   7
 y | e | 9 |   7

SELECT d, n, s, rank() OVER (PARTITION BY d ORDER BY s) FROM e;
SELECT d, n, s, avg(s) OVER (PARTITION BY d ORDER BY s) FROM e;

 d | n | s | rank 
---+---+---+------
 x | a | 3 |    1
 x | b | 7 |    2
 x | c | 8 |    3
 y | d | 5 |    1
 y | e | 9 |    2

SELECT n, s, SUM(s) OVER (ORDER BY n) FROM e;
SELECT n, s, SUM(s) OVER (ORDER BY n ROWS 1 PRECEDING) FROM e;

 n | s | sum 
---+---+-----
 a | 3 |   3
 b | 7 |  10
 c | 8 |  18
 d | 5 |  23
 e | 9 |  32

SELECT d, s, lag(s) over (partition BY d ORDER BY s) from e;

 d | s | lag 
---+---+-----
 x | 3 |    
 x | 7 |   3
 x | 8 |   7
 y | 5 |    
 y | 9 |   5
