Doubt when creating table with dates in postgresql

1

I have the following table structure:

Iwouldneedtocreatea3rdtablethatwouldlooklikethis:

I have the smallest and longest date in a table and in another table I have changes made, I would need to create a script that would generate a 3rd table, creating ranges of values.

Obs: A coluna id é um AUTOINCREMENTO.

If we turn into words, it would look like this:

  

Between the days 05-06-2018 until 06/19/2018 the value was 50.

     

Between 19-06-2018 and 21-06-2018 the value was 150.

     

Between the days 21-06-2018 and 25-06-2018 the value was 180.

     

Between 25-06-2018 until 05-07-2018 the value was 200.

I thought about using WHILE and variables, but I could not complete the script. How could this be done? It does not seem to be so complex, but I just did not get it.

    
asked by anonymous 06.07.2018 / 00:09

2 answers

1

You can solve your problem by using Window Function calling lead() combined with an UNION ALL .

Assuming your tables are something like:

CREATE TABLE tb_tabela1
(
  id BIGSERIAL PRIMARY KEY,
  data DATE
);

INSERT INTO
  tb_tabela1 ( id, data )
VALUES
  ( 1, '2018-06-05' ),
  ( 2, '2018-07-05' );

CREATE TABLE tb_tabela2
(
  id BIGSERIAL PRIMARY KEY,
  dataAlteracao DATE,
  valorAnterior INTEGER,
  valorAtual INTEGER
);

INSERT INTO
  tb_tabela2 ( id, dataAlteracao, valorAnterior, valorAtual )
VALUES
  ( 1, '2018-06-19', 50, 150 ),
  ( 2, '2018-06-21', 150, 180 ),
  ( 3, '2018-06-25', 180, 200 );

Your query would look like this:

(SELECT
   dataAlteracao AS dataInicio,
   lead(dataAlteracao,1,(SELECT max(data) FROM tb_tabela1)) OVER (ORDER BY dataAlteracao) AS dataFim,
   valorAtual AS valor
 FROM
    tb_tabela2)
UNION ALL
(SELECT
   (SELECT min(data) FROM tb_tabela1),
   dataAlteracao,
   valorAnterior
 FROM
   tb_tabela2 ORDER BY dataAlteracao LIMIT 1
) ORDER BY dataFim;

Output:

| datainicio |    datafim | valor |
|------------|------------|-------|
| 2018-06-05 | 2018-06-19 |    50 |
| 2018-06-19 | 2018-06-21 |   150 |
| 2018-06-21 | 2018-06-25 |   180 |
| 2018-06-25 | 2018-07-05 |   200 |

See working in SQLFiddle.com

To insert your data into a third table, you can do something like:

CREATE TABLE tb_tabela3
(
  id BIGSERIAL PRIMARY KEY,
  dataInicio DATE,
  dataFim DATE,
  valor INTEGER
);

Entering the data:

INSERT INTO tb_tabela3 ( dataInicio, dataFim, valor ) (
(SELECT
   dataAlteracao AS dataInicio,
   lead(dataAlteracao,1,(SELECT max(data) FROM tb_tabela1)) OVER (ORDER BY dataAlteracao) AS dataFim,
   valorAtual AS valor
 FROM
    tb_tabela2)
UNION ALL
(SELECT
   (SELECT min(data) FROM tb_tabela1),
   dataAlteracao,
   valorAnterior
 FROM
   tb_tabela2 ORDER BY dataAlteracao LIMIT 1
));

Testing:

SELECT * FROM tb_tabela3 ORDER BY dataInicio;

Output:

| id | datainicio |    datafim | valor |
|----|------------|------------|-------|
|  1 | 2018-06-05 | 2018-06-19 |    50 |
|  2 | 2018-06-19 | 2018-06-21 |   150 |
|  3 | 2018-06-21 | 2018-06-25 |   180 |
|  4 | 2018-06-25 | 2018-07-05 |   200 |

See working in SQLFiddle

    
06.07.2018 / 15:25
-1

As I mentioned in the comments, I do not see why of table1, it seems to me just query parameter.

Anyway, I used only the smallest date to be displayed as the first record's initial.

The query is very simple: Defining DateChange being the End Data of the record, and the DataInicio being the largest date of the previous record, just do a sub-select:

SELECT t.id,
       coalesce(
                  (SELECT max(x.dataAlteracao)
                   FROM tabela2 x
                   WHERE x.id < t.id),
                  (SELECT min(DATA)
                   FROM tabela1)) AS data_inicio,
       t.dataAlteracao,
       t.valorAnterior,
       t.ValorAtual
FROM tabela2 t

I put it in SQLFiddle

    
06.07.2018 / 03:40