SQLite Join Tables With Different Primary Key Values-CodePudding

I have two tables in SQLITE one table FastData records data at a high rate while the other table SlowData records data at a lower rate. FastData and SlowData share a primary key (PK) that represents time of data capture. As such the two tables could look like:

Fast Data         Slow Data
Pk   Value1       Pk   Value2
2    1            1    1
3    2            4    2
5    3            7    3
6    4
7    5
9    6

I would like to create a Select statement that joins these two tables filling in the SlowData with the previous captured data.

Join Data         
Pk   Value1  Value2
2    1       1
3    2       1
5    3       2
6    4       2
7    5       3
9    6       3

CodePudding user response：

You may try the following approach which uses row_number to determine the most recent entry as it relates to Pk as the ideal entry for Value2 after performing a left join.

SELECT 
    Pk,
    Value1,
    Value2
FROM (
    SELECT
        f.Pk,
        f.Value1,
        s.Value2,
        ROW_NUMBER() OVER (
            PARTITION BY  f.Pk, f.Value1
            ORDER BY s.Pk DESC
        ) rn
    FROM
        fast_data f
    LEFT JOIN
        slow_data s ON f.Pk >= s.Pk
) t
WHERE rn=1;

Pk	Value1	Value2
2	1	1
3	2	1
5	3	2
6	4	2
7	5	3
9	6	3

View working demo on DB Fiddle

CodePudding user response：

You need a LEFT join of the tables and FIRST_VALUE() window function to pick Value2:

SELECT DISTINCT f.Pk, f.Value1,
       FIRST_VALUE(s.Value2) OVER (PARTITION BY f.Pk ORDER BY s.Pk DESC) Value2
FROM FastData f LEFT JOIN SlowData s
ON s.Pk <= f.Pk;

See the demo.