Window function sort order help

From: Dianna Harter <dharter(at)mynewplace(dot)com>
To: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: Window function sort order help
Date: 2011-09-13 18:04:34
Message-ID: 25770AAABEA9A2499B5427E0DCEE9C9B323706179A@BE262.mail.lan
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Having trouble getting a window function to sort correctly.

Given this data

consumer_id | move_date | history_timestamp
-------------+------------+----------------------------
12345| 2008-01-05 | 2007-12-11 06:02:26.842171
12345| 2008-02-29 | 2008-02-05 07:22:38.04067
12345| 2008-02-29 | 2008-07-11 09:03:42.44044
23456| 2009-01-01 | 2008-11-12 07:33:32.656658 <--
23456| 2009-01-28 | 2008-11-14 01:57:40.264335
23456| 2009-01-01 | 2008-12-04 17:14:20.279999 <--
23456| 2009-01-01 | 2008-12-31 00:33:37.204968
23456| 2009-01-01 | 2011-06-08 04:16:41.646521
34567| 2010-05-07 | 2010-06-08 05:14:43.842172

I'm trying to get the timestamp when the consumer last changed their move_date. (Notice consumer_id 23456 set their move_date to 2009-01-01 then changed and then changed it back. In the end, I want the timestamp from when they changed it to 2009-01-01 the second time.)

My thought was to do an intermediary step to find the timestamp for each time it switched. From there I can grab the max(timestamp) for each consumer.

Here's my query.
select ch.consumer_id, ch.move_date,ch.history_timestamp, min(ch.history_timestamp) over w as start_time
from consumer_hist ch
window w as (partition by ch.consumer_id, ch.move_date order by ch.consumer_id,ch.history_timestamp asc)
order by ch.consumer_id , ch.history_timestamp asc

I expect the following result
consumer_id | move_date | history_timestamp | start_time
-------------+------------+----------------------------+----------------------------
12345 | 2008-01-05 | 2007-12-11 06:02:26.842171 | 2007-12-11 06:02:26.842171
12345 | 2008-02-29 | 2008-02-05 07:22:38.04067 | 2008-02-05 07:22:38.04067
12345 | 2008-02-29 | 2008-07-11 09:03:42.44044 | 2008-02-05 07:22:38.04067
23456 | 2009-01-01 | 2008-11-12 07:33:32.656658 | 2008-11-12 07:33:32.656658 <--
23456 | 2009-01-28 | 2008-11-14 01:57:40.264335 | 2008-11-14 01:57:40.264335
23456 | 2009-01-01 | 2008-12-04 17:14:20.279999 | 2008-12-04 17:14:20.279999 <--
23456 | 2009-01-01 | 2008-12-31 00:33:37.204968 | 2008-12-04 17:14:20.279999
23456 | 2009-01-01 | 2011-06-08 04:16:41.646521 | 2008-12-04 17:14:20.279999
34567 | 2010-05-07 | 2010-06-08 05:14:43.842172 | 2010-06-08 05:14:43.842172

But the windowing function appears to be doing the partitioning first then the order by.

So instead, I'm getting

consumer_id | move_date | history_timestamp | start_time
-------------+------------+----------------------------+----------------------------
12345 | 2008-01-05 | 2007-12-11 06:02:26.842171 | 2007-12-11 06:02:26.842171
12345 | 2008-02-29 | 2008-02-05 07:22:38.04067 | 2008-02-05 07:22:38.04067
12345 | 2008-02-29 | 2008-07-11 09:03:42.44044 | 2008-02-05 07:22:38.04067
23456 | 2009-01-01 | 2008-11-12 07:33:32.656658 | 2008-11-12 07:33:32.656658 <--
23456 | 2009-01-28 | 2008-11-14 01:57:40.264335 | 2008-11-14 01:57:40.264335
23456 | 2009-01-01 | 2008-12-04 17:14:20.279999 | 2008-11-12 07:33:32.656658 <--
23456 | 2009-01-01 | 2008-12-31 00:33:37.204968 | 2008-11-12 07:33:32.656658
23456 | 2009-01-01 | 2011-06-08 04:16:41.646521 | 2008-11-12 07:33:32.656658
34567 | 2010-05-07 | 2010-06-08 05:14:43.842172 | 2010-06-08 05:14:43.842172

Any suggestions to get the order by to occur first then the partition by or maybe there another approach that I could use?

Dianna

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message nicoletta maia 2011-09-13 20:53:13 Re: Window function sort order help
Previous Message Guillaume Roger 2011-09-13 11:25:39 Partition over a sliding date window