Calculations using two tables with different date frequencies

Question

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]

Answer 1

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

Answer 2

You may find something like this slightly better not sure

WITH t AS (
  SELECT daily.date AS daily, daily.val, min(lowfq.date) AS lowfq
  FROM daily
  JOIN lowfq
    ON (daily.date <= lowfq.date)
  GROUP BY daily, daily.val
)
SELECT t.daily, t.lowfq, t.val, t2.val, t2.val/t.val AS "low_fg/daily"
FROM t
LEFT OUTER JOIN (
  SELECT DISTINCT ON (daily.date) daily.date AS daily, lowfq.date AS lowfq, lowfq.val
  FROM daily JOIN lowfq ON (daily.date >= lowfq.date)
  ORDER BY daily.date DESC, lowfq.date DESC
) AS t2 USING (daily)
ORDER BY daily, t.lowfq;

Here we run a CTE, t which produces the following.

   daily    | val |   lowfq   
------------+-----+-----------
 2017-01-01 |   1 | 2017-01-02
 2017-01-02 |   2 | 2017-01-02
 2017-01-03 |   1 | 2017-01-06
 2017-01-04 |  56 | 2017-01-06
 2017-01-05 |   7 | 2017-01-06
 2017-01-06 |   6 | 2017-01-06
 2017-01-07 |   8 | 2017-01-08
 2017-01-08 |   6 | 2017-01-08
 2017-01-09 |   4 | 2017-01-12
 2017-01-10 |   4 | 2017-01-12
 2017-01-11 |   6 | 2017-01-12
 2017-01-12 |   8 | 2017-01-12

Then we create a subquery that does the other side of what you're looking for..

SELECT DISTINCT ON (daily.date) daily.date AS daily, lowfq.date AS lowfq, lowfq.val
FROM daily JOIN lowfq ON (daily.date >= lowfq.date)
ORDER BY daily.date DESC, lowfq.date DESC

   daily    |   lowfq    | val 
------------+------------+-----
 2017-01-12 | 2017-01-12 | 500
 2017-01-11 | 2017-01-08 | 200
 2017-01-10 | 2017-01-08 | 200
 2017-01-09 | 2017-01-08 | 200
 2017-01-08 | 2017-01-08 | 200
 2017-01-07 | 2017-01-06 | 100
 2017-01-06 | 2017-01-06 | 100
 2017-01-05 | 2017-01-02 | 700
 2017-01-04 | 2017-01-02 | 700
 2017-01-03 | 2017-01-02 | 700
 2017-01-02 | 2017-01-02 | 700

Now we just have to join them together on daily. We use a left join so we don't lose any rows..

You can cut out the CTE entirely too if you want even faster and more efficient.

SELECT t.daily, t.lowfq, t.val, t2.val, t2.val/t.val AS "low_fg/daily"
FROM (
  SELECT daily.date AS daily, daily.val, min(lowfq.date) AS lowfq
  FROM daily
  JOIN lowfq
    ON (daily.date <= lowfq.date)
  GROUP BY daily, daily.val
) AS t
LEFT OUTER JOIN (
  SELECT DISTINCT ON (daily.date) daily.date AS daily, lowfq.date AS lowfq, lowfq.val
  FROM daily JOIN lowfq
    ON (daily.date >= lowfq.date)
  ORDER BY daily.date DESC, lowfq.date DESC
) AS t2
  USING (daily)
ORDER BY daily, t.lowfq;

Answer 3

I am providing an answer that uses the real world tables I defined in my OQ. It is modeled after an earlier version of McNets answer (thanks!). This query takes about 45 minutes to run (daily_table has 93 million rows and fund_data has 5 million records) , so I will be circling back to optimize things trying McNets and Evan Carroll's best/current answers (thanks!). However, at this time, due to time constraints, I am going with what I have working. This query exists in a trigger so some of the values are hard-coded here for illustration.

Feedback welcome!

When I revisit this again, I will update this answer with better options if any are discovered.

I am only posting this so you can see how I am using my defined tables in my OQ, not because it is the best answer.

        WITH tbl
        AS (
            SELECT hf.company_id
                ,hf.trade_date
                ,(hf.daily_val)::FLOAT
                ,lf.ern_release_date
                ,(lf.fund_val)::FLOAT fund_val
                ,sum((
                        CASE 
                            WHEN lf.ern_release_date IS NULL
                                THEN 0
                            ELSE 1
                            END
                        )) OVER (
                    ORDER BY hf.company_id
                        ,hf.trade_date
                    ) + 1 + hf.company_id AS vpart
            FROM (
                SELECT *
                FROM daily_data hf
                WHERE hf.bbg_pulls_id = 47140 -- gets passed into trigger
                ) hf
            LEFT JOIN (
                SELECT ed.ern_release_date
                    ,fd.fund_val
                    ,fe.company_id
                FROM fund_data fd
                    ,fund_entries fe
                    ,ern_dt ed
                WHERE fd.fund_entries_id = fe.id
                    AND fe.ern_dt_id = ed.id
                    AND fd.bbg_pulls_id = 20 -- gets passed into trigger
                ) lf ON lf.company_id = hf.company_id
                AND lf.ern_release_date = hf.trade_date
            ORDER BY hf.company_id
                ,hf.trade_date
            )

        INSERT INTO daily_data (
            company_id
            ,trade_date
            ,daily_val
            ,wh_calc_id
            )
        SELECT t.company_id
            ,t.trade_date
            ,t2.fund_val / daily_val daily_val_calc
            ,1 -- gets passed into trigger
        FROM (
            SELECT ern_release_date
                ,fund_val::FLOAT
                ,vpart
            FROM tbl
            WHERE ern_release_date IS NOT NULL
            ) t2
            ,tbl t
        WHERE t2.vpart = t.vpart