Re: Window function sort order help

From: Nicoletta Maia <nicoletta(dot)maia+pgsql-sql(at)gmail(dot)com>
To: Dianna Harter <dharter(at)mynewplace(dot)com>
Cc: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Window function sort order help
Date: 2011-09-14 08:30:34
Message-ID: CALNf6XOb=HhDwkvj1AbV4ajT73Vk2FF=6FRcuA1s=C8_=oMLWQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

2011/9/13 Dianna Harter <dharter(at)mynewplace(dot)com>:

Hi,

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

I tried to write the query without using the window:

SELECT `X`.`consumer_id`, `X`.`move_date` , `X`.`history_timestamp` ,
MIN( `Y`.`history_timestamp` ) AS `start_time`
FROM
`Table` AS `X`
JOIN
`Table` AS `Y`
ON `X`.`consumer_id` = `Y`.`consumer_id`
AND `X`.`move_date` = `Y`.`move_date`
AND `X`.`history_timestamp` >= `Y`.`history_timestamp`
LEFT JOIN
`Table` AS `Z`
ON `X`.`consumer_id` = `Z`.`consumer_id`
AND `X`.`move_date` <> `Z`.`move_date`
AND `X`.`history_timestamp` >= `Z`.`history_timestamp`
AND `Y`.`history_timestamp` <= `Z`.`history_timestamp`
WHERE `Z`.`consumer_id` IS NULL
GROUP BY `X`.`consumer_id`, `X`.`move_date` , `X`.`history_timestamp`
ORDER BY `X`.`consumer_id`, `X`.`history_timestamp` ASC

To avoid the LEFT JOIN, you can move the control in the sub-query:

SELECT `X`.`consumer_id`, `X`.`move_date` , `X`.`history_timestamp` ,
MIN( `Y`.`history_timestamp` ) AS `start_time`
FROM
`Table` AS `X`
JOIN
`Table` AS `Y`
ON `X`.`consumer_id` = `Y`.`consumer_id`
AND `X`.`move_date` = `Y`.`move_date`
AND `X`.`history_timestamp` >= `Y`.`history_timestamp`
WHERE NOT EXISTS (
SELECT *
FROM `Table` AS `Z`
WHERE `X`.`consumer_id` = `Z`.`consumer_id`
AND `X`.`move_date` <> `Z`.`move_date`
AND `X`.`history_timestamp` >= `Z`.`history_timestamp`
AND `Y`.`history_timestamp` <= `Z`.`history_timestamp`
)
GROUP BY `X`.`consumer_id`, `X`.`move_date` , `X`.`history_timestamp`
ORDER BY `X`.`consumer_id`, `X`.`history_timestamp` ASC

With Y I select history_timestamp preceding the current row with the
same move_date.
With Z I verify that no changes have occurred to move_date between
X.history_timestamp and Y.history_timestamp.

Sorry, I responded with the wrong email address..

Nicoletta

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Thomas Kellerer 2011-09-14 09:02:18 Re: Window function sort order help
Previous Message nicoletta maia 2011-09-13 20:53:13 Re: Window function sort order help