Window function sort order help
| От | Dianna Harter |
|---|---|
| Тема | Window function sort order help |
| Дата | |
| Msg-id | 25770AAABEA9A2499B5427E0DCEE9C9B323706179A@BE262.mail.lan обсуждение исходный текст |
| Ответы |
Re: Window function sort order help
(Nicoletta Maia <nicoletta.maia+pgsql-sql@gmail.com>)
Re: Window function sort order help (nicoletta maia <nicoletta.maia@gmail.com>) |
| Список | pgsql-sql |
<div class="WordSection1"><p class="MsoNormal">Having trouble getting a window function to sort correctly. <p class="MsoNormal"> <pclass="MsoNormal">Given this data<p class="MsoNormal"> <p class="MsoNormal"> consumer_id | move_date | history_timestamp<p class="MsoNormal">-------------+------------+----------------------------<p class="MsoNormal"> 12345| 2008-01-05 | 2007-12-11 06:02:26.842171<p class="MsoNormal"> 12345| 2008-02-29 | 2008-02-0507:22:38.04067<p class="MsoNormal"> 12345| 2008-02-29 | 2008-07-11 09:03:42.44044<p class="MsoNormal"> 23456| 2009-01-01 | 2008-11-12 07:33:32.656658 <--<p class="MsoNormal"> 23456| 2009-01-28| 2008-11-14 01:57:40.264335<p class="MsoNormal"> 23456| 2009-01-01 | 2008-12-04 17:14:20.279999 <--<pclass="MsoNormal"> 23456| 2009-01-01 | 2008-12-31 00:33:37.204968<p class="MsoNormal"> 23456| 2009-01-01| 2011-06-08 04:16:41.646521<p class="MsoNormal"> 34567| 2010-05-07 | 2010-06-08 05:14:43.842172<p class="MsoNormal"> <pclass="MsoNormal">I’m trying to get the timestamp when the consumer last changed their move_date. (Noticeconsumer_id 23456 set their move_date to 2009-01-01 then changed and then changed it back. In the end, I want thetimestamp from when they changed it to 2009-01-01 the second time.)<p class="MsoNormal"> <p class="MsoNormal">My thoughtwas 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.<p class="MsoNormal"> <p class="MsoNormal">Here’s my query. <p class="MsoNormal">selectch.consumer_id, ch.move_date,ch.history_timestamp, min(ch.history_timestamp) over w as start_time<pclass="MsoNormal"> from consumer_hist ch<p class="MsoNormal">window w as (partition by ch.consumer_id,ch.move_date order by ch.consumer_id,ch.history_timestamp asc)<p class="MsoNormal"> order by ch.consumer_id, ch.history_timestamp asc<p class="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal">I expect the followingresult<p class="MsoNormal">consumer_id | move_date | history_timestamp | start_time<p class="MsoNormal">-------------+------------+----------------------------+----------------------------<p class="MsoNormal"> 12345 | 2008-01-05 | 2007-12-11 06:02:26.842171 | 2007-12-11 06:02:26.842171<p class="MsoNormal"> 12345 | 2008-02-29 | 2008-02-05 07:22:38.04067 | 2008-02-05 07:22:38.04067<p class="MsoNormal"> 12345 | 2008-02-29 | 2008-07-11 09:03:42.44044 | 2008-02-05 07:22:38.04067<p class="MsoNormal"> 23456 | 2009-01-01 | 2008-11-12 07:33:32.656658 | 2008-11-12 07:33:32.656658 <--<p class="MsoNormal"> 23456 | 2009-01-28 | 2008-11-14 01:57:40.264335 | 2008-11-14 01:57:40.264335<p class="MsoNormal"> 23456 | 2009-01-01 | 2008-12-04 17:14:20.279999 | 2008-12-04 17:14:20.279999 <--<p class="MsoNormal"> 23456 | 2009-01-01 | 2008-12-31 00:33:37.204968 | 2008-12-04 17:14:20.279999<p class="MsoNormal"> 23456 | 2009-01-01 | 2011-06-08 04:16:41.646521 | 2008-12-04 17:14:20.279999<p class="MsoNormal"> 34567 | 2010-05-07 | 2010-06-08 05:14:43.842172 | 2010-06-08 05:14:43.842172<p class="MsoNormal"> <pclass="MsoNormal">But the windowing function appears to be doing the partitioning first then the orderby. <p class="MsoNormal"> <p class="MsoNormal">So instead, I’m getting<p class="MsoNormal"> <p class="MsoNormal">consumer_id| move_date | history_timestamp | start_time<p class="MsoNormal">-------------+------------+----------------------------+----------------------------<p class="MsoNormal"> 12345 | 2008-01-05 | 2007-12-11 06:02:26.842171 | 2007-12-11 06:02:26.842171<p class="MsoNormal"> 12345 | 2008-02-29 | 2008-02-05 07:22:38.04067 | 2008-02-05 07:22:38.04067<p class="MsoNormal"> 12345 | 2008-02-29 | 2008-07-11 09:03:42.44044 | 2008-02-05 07:22:38.04067<p class="MsoNormal"> 23456 | 2009-01-01 | 2008-11-12 07:33:32.656658 | 2008-11-12 07:33:32.656658 <--<p class="MsoNormal"> 23456 | 2009-01-28 | 2008-11-14 01:57:40.264335 | 2008-11-14 01:57:40.264335<p class="MsoNormal"> 23456 | 2009-01-01 | 2008-12-04 17:14:20.279999 | 2008-11-12 07:33:32.656658 <--<p class="MsoNormal"> 23456 | 2009-01-01 | 2008-12-31 00:33:37.204968 | 2008-11-12 07:33:32.656658<p class="MsoNormal"> 23456 | 2009-01-01 | 2011-06-08 04:16:41.646521 | 2008-11-12 07:33:32.656658<p class="MsoNormal"> 34567 | 2010-05-07 | 2010-06-08 05:14:43.842172 | 2010-06-08 05:14:43.842172<p class="MsoNormal"> <pclass="MsoNormal">Any suggestions to get the order by to occur first then the partition by or maybethere another approach that I could use?<p class="MsoNormal"> <p class="MsoNormal">Dianna</div>
В списке pgsql-sql по дате отправления: