sensor data analysis

atomino · #1 (**permalink**) 07-11-09, 09:16

Hi!
I am not an SQL programmer at all but I am trying to solve this problem for some data analysis.

I have two tables (meter, sensor).

The meter table contains values against a timestamp (epochtime), e.g.

record | timestamp | value_a
1 100001 5.4
2 100008 7.3
3 100015 6.4

The sensor table contains values taken at more intervals, e.g.

record | timestamp | value_b
1 100001 35
2 100003 36
3 100004 37
4 100006 36
5 100008 39
6 100010 40
7 100011 41
8 100015 35
9 100017 36
10 100020 36

What I need to do is to list value_a and value_b together in this way

timestamp | value_a | value_b
100001 5.4 35
100003 5.4 36
100004 5.4 37
100006 5.4 36
100008 7.3 39
100010 7.3 40
100011 7.3 41
100015 6.4 35
100017 6.4 36
100020 6.4 36

I would be grateful for any hint of how I can crack this problem!

r937 · #2 (**permalink**) 07-11-09, 10:25

Code:

SELECT sensor.timestamp
     , meter.value_a
     , sensor.value_b 
  FROM sensor
LEFT OUTER
  JOIN meter
    ON meter.timestamp =
       ( SELECT MAX(timestamp)
           FROM meter
          WHERE timestamp <= sensor.timestamp )

atomino · #3 (**permalink**) 07-11-09, 12:00

Many thanks for this quick and accurate help!!!

Internet Services

Web Development

Digital Marketing

Consumer Tech