Postgresql – Calculations using two tables with different date frequencies

postgresqltimestamp

I'm looking to align dates in one table with dates in another table that have different frequencies. I want the values associated with the low frequency table to repeat until there is a new date so I can do calculations on data involving both tables.

To facilitate this I thought building an index range scheme would be useful and faster.

This might make it clearer…

Let's call this the daily table:

CREATE TEMP TABLE daily AS
SELECT date::date, val FROM ( VALUES
  ('2017-01-01',1),
  ('2017-01-02',2),
  ('2017-01-03',1),
  ('2017-01-04',56),
  ('2017-01-05',7),
  ('2017-01-06',6),
  ('2017-01-07',8),
  ('2017-01-08',6),
  ('2017-01-09',4),
  ('2017-01-10',4),
  ('2017-01-11',6),
  ('2017-01-12',8)
) AS t(date,val);

And this is the low_fq (low frequency) table:

CREATE TEMP TABLE lowfq AS
SELECT date::date, val FROM ( VALUES
  ( '2017-01-02',700 ),
  ( '2017-01-06',100 ),
  ( '2017-01-08',200 ),
  ( '2017-01-12',500 )
) AS t(date,val);

The result should look something like this:

╔════════════╦═════╦══╦════════════╦══════╦══╦══════════════╗
║   dialy    ║     ║  ║   low_fq   ║      ║  ║ low_fg/daily ║
╠════════════╬═════╬══╬════════════╬══════╬══╬══════════════╣
║ date       ║ val ║  ║ date       ║ val  ║  ║ calc         ║
║ 2017-01-01 ║ 1   ║  ║ 2017-01-02 ║ null ║  ║ null         ║
║ 2017-01-02 ║ 2   ║  ║ 2017-01-02 ║ 700  ║  ║ 350          ║
║ 2017-01-03 ║ 1   ║  ║ 2017-01-06 ║ 700  ║  ║ 700          ║
║ 2017-01-04 ║ 56  ║  ║ 2017-01-06 ║ 700  ║  ║ 12.5         ║
║ 2017-01-05 ║ 7   ║  ║ 2017-01-06 ║ 700  ║  ║ 100          ║
║ 2017-01-06 ║ 6   ║  ║ 2017-01-06 ║ 100  ║  ║ 16.66666667  ║
║ 2017-01-07 ║ 8   ║  ║ 2017-01-08 ║ 100  ║  ║ 12.5         ║
║ 2017-01-08 ║ 6   ║  ║ 2017-01-08 ║ 200  ║  ║ 33.33333333  ║
║ 2017-01-09 ║ 4   ║  ║ 2017-01-12 ║ 200  ║  ║ 50           ║
║ 2017-01-10 ║ 4   ║  ║ 2017-01-12 ║ 200  ║  ║ 50           ║
║ 2017-01-11 ║ 6   ║  ║ 2017-01-12 ║ 200  ║  ║ 33.33333333  ║
║ 2017-01-12 ║ 8   ║  ║ 2017-01-12 ║ 500  ║  ║ 62.5         ║
╚════════════╩═════╩══╩════════════╩══════╩══╩══════════════╝

where Low_fg/daily is just dividing the low_fg value by the daily value.

I don't need the 2017-01-01 calculation, so handling the null could mean simply filtering it out ahead of time.

Note the repeating values until there is a date change in the low_fq table.

Real world:

As mentioned, in the real world, to do this I am trying to build a partition as described by adamlamar in this question. Except I am building a FK, I have dates, and my values aren't null, but hopefully you get the idea: FK integers assigned to a range of dates.

I'm happy to skip the FK assignment problem, but I'm thinking that will make the calculation easier and faster.

What is the best strategy here and how do I implement it?

Here are my real world tables:

Additional details

Low freq data and a table I have to join with to get dates:

CREATE TABLE fund_data
(
  id serial NOT NULL,
  fund_entries_id integer NOT NULL,
  fund_val numeric(25,6) NOT NULL,
  bbg_pulls_id integer NOT NULL,
  CONSTRAINT fund_data_pkey PRIMARY KEY (id),
  CONSTRAINT fund_data_bbg_pulls_id_fkey FOREIGN KEY (bbg_pulls_id)
      REFERENCES bbg_pulls (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fund_data_fund_entries_id_fkey FOREIGN KEY (fund_entries_id)
      REFERENCES fund_entries (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fund_data_fund_entries_id_bbg_pulls_id_key UNIQUE (fund_entries_id, bbg_pulls_id)
)

CREATE TABLE ern_dt
(
  company_id integer NOT NULL,
  ern_release_date date NOT NULL,
  fiscal_prd character varying(7) NOT NULL,
  id serial NOT NULL,
  ern_release_date_update timestamp without time zone,
  gen_qtr_end_dt_id integer,
  CONSTRAINT ern_dt_pkey PRIMARY KEY (id),
  CONSTRAINT ern_dt_company_id_fkey FOREIGN KEY (company_id)
      REFERENCES company (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT ern_dt_gen_qtr_end_dt_id_fkey11 FOREIGN KEY (gen_qtr_end_dt_id)
      REFERENCES gen_qtr_end_dt (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT set UNIQUE (company_id, ern_release_date, fiscal_prd)
)

High freq data:

CREATE TABLE daily_data
(
  id serial NOT NULL,
  company_id integer NOT NULL,
  trade_date date NOT NULL,
  daily_val numeric(13,6) NOT NULL,
  bbg_pulls_id integer NOT NULL,
  gen_qtr_end_dt_id integer,
  CONSTRAINT daily_data_pkey PRIMARY KEY (id),
  CONSTRAINT daily_data_bbg_pulls_id_fkey FOREIGN KEY (bbg_pulls_id)
      REFERENCES bbg_pulls (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT daily_data_company_id_fkey FOREIGN KEY (company_id)
      REFERENCES company (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT daily_data_company_id_trade_date_bbg_pulls_id_key UNIQUE (company_id, trade_date, bbg_pulls_id)
)

I'm using PostgreSQL 9.3.5.

UPDATE: [per request, removed and put in a self-answer to this question]

Best Answer

The problem here is how to add a partition, it is accomplished using:

sum((case when lf.d1 is null then 0 else 1 end)) over (order by hf.cia, hf.d1) + 1 + hf.cia

Notice I've used + 1 + hf.cia to take care when d2 is NULL but cia has changed.

with tbl as
(
    select hf.cia, hf.d1, (hf.val)::float, lf.d1 d2, (lf.val)::float val2
           ,sum((case when lf.d1 is null then 0 else 1 end)) over (order by hf.cia, hf.d1) + 1 + hf.cia as vpart
    from hf
         left join lf on lf.cia = hf.cia and lf.d1 = hf.d1
    order by hf.cia, hf.d1
)
select
     t.cia, t.d1, t.val, t2.d2, t2.val2 ,t2.val2 / val calc, t.vpart
from tbl t
     inner join 
                (select d2, val2::float, vpart
                 from tbl
                 where d2 is not null) t2
     on t2.vpart = t.vpart
order by vpart;

I thank Evan Carroll his contribution on the use of the named WINDOW used on the initial solution. And thanks to @ypercubeᵀᴹ, that has pointed out that out of memory issue could be caused by pgAdmin instead of a server problem.

This is the result:

+-----+------------+-----+------------+------+---------+-------+
| cia | d1         | val | d2         | val2 |    calc | vpart |
+-----+------------+-----+------------+------+---------+-------+
|  1  | 2017.01.02 |  2  | 2017.01.02 |  700 |  350.00 |   3   |
|  1  | 2017.01.03 |  1  | 2017.01.02 |  700 |  700.00 |   3   |
|  1  | 2017.01.04 |  56 | 2017.01.02 |  700 |   12.50 |   3   |
|  1  | 2017.01.05 |  7  | 2017.01.02 |  700 |  100.00 |   3   |
+-----+------------+-----+------------+------+---------+-------+
|  1  | 2017.01.06 |  6  | 2017.01.06 |  100 |   16.67 |   4   |
|  1  | 2017.01.07 |  8  | 2017.01.06 |  100 |   12.50 |   4   |
+-----+------------+-----+------------+------+---------+-------+
|  1  | 2017.01.08 |  6  | 2017.01.08 |  200 |   33.33 |   5   |
|  1  | 2017.01.09 |  4  | 2017.01.08 |  200 |   50.00 |   5   |
|  1  | 2017.01.10 |  4  | 2017.01.08 |  200 |   50.00 |   5   |
|  1  | 2017.01.11 |  6  | 2017.01.08 |  200 |   33.33 |   5   |
+-----+------------+-----+------------+------+---------+-------+
|  1  | 2017.01.12 |  8  | 2017.01.12 |  500 |   62.50 |   6   |
+-----+------------+-----+------------+------+---------+-------+
|  2  | 2017.01.02 |  2  | 2017.01.02 |  700 |  350.00 |   8   |
|  2  | 2017.01.03 |  1  | 2017.01.02 |  700 |  700.00 |   8   |
|  2  | 2017.01.04 |  56 | 2017.01.02 |  700 |   12.50 |   8   |
|  2  | 2017.01.05 |  7  | 2017.01.02 |  700 |  100.00 |   8   |
+-----+------------+-----+------------+------+---------+-------+
|  2  | 2017.01.06 |  6  | 2017.01.06 |  100 |   16.67 |   9   |
|  2  | 2017.01.07 |  8  | 2017.01.06 |  100 |   12.50 |   9   |
+-----+------------+-----+------------+------+---------+-------+
|  2  | 2017.01.08 |  6  | 2017.01.08 |  200 |   33.33 |   10  |
|  2  | 2017.01.09 |  4  | 2017.01.08 |  200 |   50.00 |   10  |
|  2  | 2017.01.10 |  4  | 2017.01.08 |  200 |   50.00 |   10  |
|  2  | 2017.01.11 |  6  | 2017.01.08 |  200 |   33.33 |   10  |
+-----+------------+-----+------------+------+---------+-------+
|  2  | 2017.01.12 |  8  | 2017.01.12 |  500 |   62.50 |   11  |
+-----+------------+-----+------------+------+---------+-------+

Check it here: http://rextester.com/DRAW20062

pgAdmin timing

When you execute a query from the query tool, the message pane shows something like:

Total query runtime: 62 ms.

And the status line shows the same time. I quote pgAdmin help about that:

The status line will show how long the last query took to complete. If a dataset was returned, not only the elapsed time for server execution is displayed, but also the time to retrieve the data from the server to the Data Output page.

If you want to see the time on the server you need to use SQL EXPLAIN ANALYZE or the built in Shift + F7keyboard shortcut or Query -> Explain analyze. Then, at the bottom of the explain output you get something like this:

Total runtime: 0.269 ms

Postgresql – Type of an id field that references another id field

It should be int. The referencing and the refernced columns have to be of the same datatype but it doesn't make sense for a serial to reference another serial because serials are just int with auto incrementing values provided by a sequence.

And when you will be inserting in the second, referencing table (client), you'll want to use the values already inserted in the 1st, referenced table (user). You don't want automatically provided values as these may not match the existing values you already have in the 1st table.

The Postgres docs about numeric datatypes explain:

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Additional details

Best Answer

Related Solutions

Postgresql – Postgres multiple joins slow query, how to store default child record

pgAdmin timing

Postgresql – Type of an id field that references another id field

Related Question