CODEWITHSUNDEEP
sqlsqlite
qwa
qwa

Reputation: 143

sql query to compare table entries and detect missing data (exceedding a threshold)

I am running a science test and logging my data inside two sqlite tables.

I need to analyze the measurements and create a 3rd table view with the results with the following points in mind:

1- The entries in the tables are completely independent and irrelevent to each other. The id at TX side shows the test number.

2- for each test at TX side (Table-1) there might be a corresponding entry at RX side (Table-2).

2-1- Well how are these 2 tables related to each other? For each test I make (i.e. sending a number from TX side) I expect to receive a number @RX side with a maximum delay of 1.5 seconds. Any entry with a delay of more than that threshold should be considered as a miss/NULL.

Question: How should i write the sql query in SQLITE to produce the analysis and test result given in table3?

Thanks a lot in advance.

Table (1) TX
id | time         | measurement
------------------------
1  | 09:40:10.221 | 100
2  | 09:40:15.340 | 60
3  | 09:40:21.100 | 80
4  | 09:40:25.123 | 90
5  | 09:40:29.221 | 45


Table (2) RX
time         | measurement
------------------------
09:40:10.521 | 100
09:40:21.560 | 80
09:40:26.210 | 90
09:40:30.414 | 50



Table (3) Test Result
id | delta_time(s)| delta_value
----------------------------------------
1  | 0.300        | 0
2  | NULL         | NULL (i.e. missed)
3  | 0.460        | 0
4  | 1.087        | 0
5  | 1.193        | 5

Upvotes: 1

Views: 73

Answers (2)

forpas
forpas

Reputation: 164099

Join the tables on your conditions and use window functions MIN() and FIRST_VALUE():

select distinct
  t.id,
  round(min((julianday(r.time) - julianday(t.time)) * 24 * 60 * 60) over (partition by id), 3) [delta_time(s)],
  first_value(r.measurement - t.measurement) over (partition by id order by (julianday(r.time) - julianday(t.time))) [delta_value]     
from TX t left join RX r
on (julianday(r.time) - julianday(t.time)) * 24 * 60 * 60 between 0 and 1.5;

See the demo.
Results:

> id | delta_time(s) | delta_value
> -: | ------------: | ----------:
>  1 |           0.3 |           0
>  2 |          null |        null
>  3 |          0.46 |           0
>  4 |         1.087 |           0
>  5 |         1.193 |           5

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270091

You can use a correlated subquery:

select tx.*,
       (select rx.measurement - tx.measurement
        from rx
        where rx.time >= tx.time and
              rx.time < datetime(tx.time, '+1.5 seconds')
        limit 1  -- in case there is more than one
       ) delta_value
from tx;

Upvotes: 1

Related Questions