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?
Introduction to PostgreSQL Window Functions. PostgreSQL window functions are used to compare contrast data, and it is a key to analytic and various use cases in PostgreSQL. The window function in PostgreSQL is used to compare the values between current rows and related to the current rows from all values of a table.
The OVER clause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s).
In PostgreSQL, the ROW_NUMBER() function is used to assign a unique integer to every row that is returned by a query. Syntax: ROW_NUMBER() OVER( [PARTITION BY column_1, column_2, …] [ORDER BY column_3, column_4, …] )
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With