Calculations using two tables with different date frequencies

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 ║ ║

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

StackExchange Database Administrators Q#163397, answer score: 5