Re: [SQL] Window function sort order help

2011-09-14 Thread Nicoletta Maia 
2011/9/13 Dianna Harter :

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

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Window function sort order help

2011-09-14 Thread Thomas Kellerer 

Nicoletta Maia, 14.09.2011 10:30:

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.


That is not a valid PostgreSQL SQL statement.

Postgres does not use "backticks" for quoting, it uses the standard double 
quotes.

Thomas



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Window function sort order help

2011-09-14 Thread Dianna Harter 
Thank you.  Interesting solution.  Unfortunately, it's performance is not very 
good, since it involves joining a large table 3 times.  I do have a solution 
that uses a temp table, but I was trying to rework it into a single query to 
improve performance.  

Thank you again for your help.
Dianna

2011/9/13 nicoletta maia :

[snip]
> 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



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Window function sort order help

2011-09-14 Thread Nicoletta Maia 
2011/9/14 Dianna Harter :

> [snip]
> Thank you again for your help.
You're welcome. Sorry about the syntax, but I could only try on
MySQL... and I was curious about the problem :)

Nicoletta

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql