asked on

Sql Query question - Postgres 9.3

I have a log file (see below - actual about 1 million rows), and I get the last date for each sensor. Now I want to know how many rows back from that latest date before a success = 'f'.

How many checks in a row has the sensor been okay since the latest check- by each sensor number.

How would I write that query?

Desired Query Answer:
Sensor: Number of times success ='t' before a success = 'f'
1: 2
2:1
3:3

id, sensor number, date, success
1, 1, 2013-08-15, t
2, 1, 2013-08-14, t
3, 1, 2013-08-13, f
4, 1, 2013-08-12, t
5, 2, 2013-08-15, t
6, 2, 2013-08-14, f
7, 3, 2013-08-15, t
8, 3, 2013-08-15, t
9, 3, 2013-08-15, t
10, 3, 2013-08-15, f
11, 3, 2013-08-15, t

Guy Hengel [angelIII / a3]

can there be more that 1 'f' for a sensor?
you would then want to count the number for 't' before the last 'f', but after any previous 'f', right?
let's see if we can get it like this (not tested, I have no Postgresql, but I see the syntax should work)

with f_data as ( select id, sensor_number, date
   , row_number() over (partition over sensor_number) order by date desc ) rn
   from your_table 
   where success = 'f'
 )
select t.* 
   , ( select count(*) 
        from your_table x
      where x.sensor_number = x.sensor_number
         and x.id < f.id
         and x.success = 't'
         and ( f2.id IS NULL or x.id > f2.id )
     ) t_count
from your_table t
join f_data f on f.sensor_number = t.sensor_number and t.id = f.id and f.rn = 1
left join f_data f2 on f2.sensor_number = t.sensor_number f2.rn = 2

Open in new window

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

see: http://sqlfiddle.com/#!1/68af8/11

>> I have no Postgresql
happily sqlfiddle offers Postgres (along with MySQL, MSSQL, Oracle & SQLite)

ID: 39413516 line 16 is missing "AND": and f2.rn = 2

ID: 39414498 Postgres insists on aliases for a subquery

no points please