as_of_join.sql

-- Fast SQL as-of join (Postgres version), contributed by Rand Merchant Bank e-Quant team 
-- Kabelo Masemola, Paul Jacot-Guillarmod, Michael Stephanou 
-- Rand Merchant Bank does not make any representations or give any warranties as to the correctness, accuracy or completeness of the included code; nor does Rand Merchant Bank assume liability for any losses arising from errors or omissions in the code.
 
-- Create tables
 
CREATE TABLE table1 ( time timestamptz, val double precision);
CREATE TABLE table2 ( time timestamptz, val double precision);
 
-- Create aggregate functions
 
create or replace function custom_locf_helper(a float, b float)
returns float
language sql
as '
  select coalesce(b, a)
';
 
drop aggregate if exists custom_locf(float);
create aggregate custom_locf(float) (
  SFUNC = custom_locf_helper,
  STYPE = float
);
 
-- Create the union view
create or replace
view view_union as 
     select
    *
from
    (
    select
        "time",
        null as value,
        null as row_type
    from
        table1
union all
    select
        "time",
        val as value,
        1 as row_type
    from
        table2) union_query
order by
    union_query.time asc
 
-- Create join view
 
create or replace view view_join as
select inner_q.*,t.val as value1 from (
select
    time,
    custom_locf(value) over (ORDER by time asc rows between unbounded preceding and current row) value2 ,
    row_type
from
    view_union
) inner_q
join table1 t on t."time" = inner_q.time
 
-- Final output 
select time,value1,value2 from view_join where row_type is null