1

I have a table with (suppose) just 2 columns:

- fecha (a date)
- ignicion (0/1 representing boolean state)

The goal is to retrieve which periods of time was ignicion held at '1', for such task I figured out an iterative approach like the one in the following queries:

select min(fecha) from reports.avl_historico_11557000631206 where ignicion=1  and fecha > '2016-05-11 00:00:00-03' and fecha < '2016-05-12 00:00:00-03'; 

select min(fecha) from reports.avl_historico_11557000631206 where ignicion=0  and fecha > '2016-05-11 03:01:38+00' and fecha < '2016-05-12 00:00:00-03';

select min(fecha) from reports.avl_historico_11557000631206 where ignicion=1  and fecha > '2016-05-11 03:42:19+00' and fecha < '2016-05-12 00:00:00-03';

Programming that is no problem, but executing it might be an issue, as the report may involve a month's wide of data, and not from just a single table being "scanned", but around 500 with the same data structure (just different table name).

Any ideas on how this could be achieved in a cleaner/better/faster way?

I've been advised on "window" functions, but I don't really know if any of those actually fit this task.

  • Yes @ErwinBrandstetter forgot to mark it as approved... Thanks a lot! – Gonzalo Vasquez Jan 23 '17 at 18:34
2

This can be surprisingly simple with a smart application of a window function.
Based on this table definition:

CREATE TEMP TABLE tbl (
  fecha timestamp PRIMARY KEY
, ignicion bool NOT NULL  -- use an actual boolean!!
);

Query:

SELECT period, min(fecha), max(fecha), count(*) AS row_ct
FROM  (
   SELECT *, count(*) FILTER (WHERE NOT ignicion) OVER (ORDER BY fecha) AS period
   FROM   tbl
   ) sub
WHERE  ignicion
GROUP  BY period
ORDER  BY period;

... combining the new aggregate FILTER clause (pg 9.4+) with a window function in a subquery.

The trick is to compute a running count rows with ignicion = FALSE in the subquery, and then group rows with ignicion = TRUE by that count.

If fecha is not unique or one of the columns can be NULL, you need to do more.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.