Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a PostgreSQL database and my data-set include time-series of temperature for two stations. I would like to look their pattern's similarity in similar time sequences.

For station number 1:

|temperature |dt                |
| 4.22       |9/15/2007 12:12:12|                  
| 5.11       |9/15/2007 13:14:16|
| 6.16       |9/15/2007 14:16:02|
| 6.01       |9/15/2007 15:18:23|
| 7.09       |9/15/2007 16:21:01|

For station number 2:

|temperature |dt                |
| 3.12       |9/15/2007 12:12:12|                  
| 4.15       |9/15/2007 13:14:16|
| 5.26       |9/15/2007 14:16:02|
| 6.15       |9/15/2007 15:18:23|
| 7.23       |9/15/2007 16:21:01|

I would like to ignore true values and compare time-series with respect to the pattern. Moreover, 1 time slice by 1 time slice comparison is enough for my work. Please help me on discrete Fourier transformation or slope to compare the patterns.

share|improve this question

closed as too localized by Jack Douglas May 28 '13 at 19:15

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

Could you describe how do you define 'pattern' here? – dezso Dec 5 '12 at 14:04
Jon, how does this question relate to database administration? Perhaps it would be better served on or even, god forbid, SO itself? – Max Vernon May 28 '13 at 14:45
@MaxVernon: this site isn't only about backup/restores and database installs. The faq does mention advanced querying and server-side db programming - and looks like this sort of thing would require a bit of both, possibly more. – Mat May 28 '13 at 15:37
@Max: This is an advanced SQL question. – Jon Seigel May 28 '13 at 17:13
@Max I'm wondering if isn't the place! – Jack Douglas May 28 '13 at 19:15
up vote 4 down vote accepted

Ok so this is what I would recommend:

  1. Use a custom aggregate for a moving average. the aggregate would use essentially an array to store values and return the average of the values in the array. New values would be appended to the end, and old values dropped off. One could use array_agg with unnest(), limit, and offset to do the work.

  2. Use a windowing function to tie the moving average to the row.

  3. Do a calculation between values in the above to generate your pattern....

  4. Create another aggregate which gives you a moving pattern returning an array

  5. Write an operator matching the array and use a windowing function call to tack this on to the returned record

I do think that the above can all be done in SQL with SQL-language functions. It is, however, way too complicated to even get into sample code here. However I can't help but wonder whether PL/R might not be helpful here as well.

share|improve this answer

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