0

This is a very simple question and I feel like the answer should be obvious, but it's been two hours I'm not finding a solution.

I am willing to transform discrete data flux stored in PostgreSQL. They are originally stored as high frequency samples. So for a single timestamp, I have may have multiple similar values. I wanted to make a variable-length sampling, so similar values would only be stored once in the sequence. It sounds like a simple count(*) group by, but since I want to keep the lowest timestamp of the sequence, it can't be handled that way.

t1 2     
t2 2      t1 2 3
t3 2  ->  t4 3 1
t4 3      t5 2 1
t5 2

I would like to have a request that is as fast as possible. Any help would be greatly appreciated.

Clockwork-Muse
  • 12,806
  • 6
  • 31
  • 45
Diane M
  • 1,503
  • 1
  • 12
  • 23
  • possible duplicate of [Select first row in each GROUP BY group?](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) – Marc B Mar 07 '14 at 17:29
  • No, because values can be visited more than once, and I need a separate timestamp for each visit (like t1 and t5)... this problem sound actually very ill-suited to SQL query – Diane M Mar 07 '14 at 19:36
  • This should be a gaps-and-islands problem. See [the questions in this tag](http://stackoverflow.com/questions/tagged/gaps-and-islands). – Clockwork-Muse Mar 10 '14 at 08:52

1 Answers1

0

I found a way to handle half the problem.

  1. Calculate a delta table, dn = tn - tn-1. Which isn't trivial but doable
  2. Take all times from 1st table which aren't 0 in delta table

I would miss the ultimate column, though, which would improve read performance. Now I need help to make an optimized request to do :

t1 2      t1 2 3
t4 3  ->  t4 3 1
t5 2      t5 2 1
Diane M
  • 1,503
  • 1
  • 12
  • 23
  • `dn = tn - tn-1` can easily be done using the `lag()` function –  Mar 10 '14 at 08:46
  • You need to be showing your work, so future readers can benefit. Also, this should really be an edit to your question, not an answer. (Answering your own question is fine, but please make it the final solution, not work-in-progress) – Clockwork-Muse Mar 10 '14 at 10:13