PostgreSQL Window Functions

Question

Consider the following table structure:

CREATE TABLE tb_log
(
  id INTEGER PRIMARY KEY,
  moment DATE,
  old INTEGER,
  actual INTEGER
);

Containing the data:

INSERT INTO
  tb_log ( id, moment, old, actual )
VALUES
  ( 1, '2018-06-19', 10, 20 ),
  ( 2, '2018-06-21', 20, 30 ),
  ( 3, '2018-06-25', 30, 40 );

I'm trying to get from tb_log the period (start date and end date) at which a value was in effect.

Trial #1 - Using lag() function:

SELECT
  lag( moment ) OVER (ORDER BY moment) date_start,
  moment AS date_end,
  old AS period_value
FROM
   tb_log;

Which returns the following data:

| date_start |   date_end | period_value |
|------------|------------|--------------|
|     (null) | 2018-06-19 |           10 |
| 2018-06-19 | 2018-06-21 |           20 |
| 2018-06-21 | 2018-06-25 |           30 |

Trial #2 - Using lead() function:

SELECT
  moment AS date_start,
  lead( moment ) OVER (ORDER BY moment) date_end,
  actual AS period_value
FROM
   tb_log;

Which returns the following data:

| date_start |   date_end | period_value |
|------------|------------|--------------|
| 2018-06-19 | 2018-06-21 |           20 |
| 2018-06-21 | 2018-06-25 |           30 |
| 2018-06-25 |     (null) |           40 |

SQLFiddle.com

Is there any trick using Window Functions to return something like this:

| date_start |   date_end | period_value |
|------------|------------|--------------|
|     (null) | 2018-06-19 |           10 |
| 2018-06-19 | 2018-06-21 |           20 |
| 2018-06-21 | 2018-06-25 |           30 |
| 2018-06-25 |     (null) |           40 |

Any Ideas?

Gordon Linoff · Accepted Answer · 2018-07-06 12:33:27Z

6

There is no trick using window functions, because window functions do not add a row to the data. It is much more natural (in my opinion) to use lead():

(SELECT moment, lead(moment) over (order by moment) as date_end,
       actual AS period_value
 FROM tb_log
)
UNION ALL
(SELECT null, moment, old
 FROM tb_log
 ORDER BY moment
 LIMIT 1
);

In general, using union all instead of union is a good idea. Union incurs overhead for removing duplicates.

answered Jul 6, 2018 at 12:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

saravanatn · Accepted Answer · 2018-07-06 12:32:08Z

4

    SELECT
      lag( moment ) OVER (ORDER BY moment) date_start,
      moment AS date_end,
      old AS period_value
    FROM
       tb_log
    union
    SELECT
      moment AS date_start,
      lead( moment ) OVER (ORDER BY moment) date_end,
      actual AS period_value
    FROM
       tb_log
order by 3;

edited Jul 6, 2018 at 12:32

answered Jul 6, 2018 at 12:07

saravanatn

6305 silver badges9 bronze badges

Collectives™ on Stack Overflow

PostgreSQL Window Functions

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related