Tell me more ×
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. It's 100% free, no registration required.
up vote 2 down vote favorite
1

I have the following table:

CREATE TABLE trans (
    id SERIAL PRIMARY KEY,
    trans_date date,
    trans_time time        
);

I want to have the following view

CREATE OR REPLACE VIEW daily_trans AS
SELECT trans_date,
    max(trans_time) as first, 
    min(trans_time) as last,
    calculate_status(min(trans_time), max(trans_time)) as status 
GROUP BY trans_date

with columns that specify the ids of the max and min trans_time.

How do I do that?

share|improve this question

migrated from stackoverflow.com Dec 28 '11 at 6:43

2 Answers

up vote 5 down vote accepted

You can calculate this in one step with window functions:

CREATE OR REPLACE VIEW daily_trans AS
SELECT DISTINCT
       trans_date
      ,first_value(trans_time) OVER w AS first_time
      ,first_value(id)         OVER w AS first_id
      ,last_value(trans_time)  OVER w AS last_time
      ,last_value(id)          OVER w AS last_id
      ,calculate_status(min(trans_time) OVER w,max(trans_time) OVER w) AS status 
FROM   trans
WINDOW w AS (PARTITION BY trans_date ORDER BY trans_time, id
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER  BY 1;

I added id as secondary ORDER column in the window clause, to make the sort order stable in case of identical times per day.

If you are not familiar with window functions, be sure to read this chapter of the manual.

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is a not so commonly used clause to adjust the window frame, because we want last_value() to always return the last value of the whole partition (the date), not the last value up to the current (including peers) according to the sort order.

Find an explanation on how you to combine DISTINCT with window functions in this related answer.

share|improve this answer
Thank you @Erwin Brandstetter, it's an excellent answer. – Random Joe Dec 28 '11 at 9:27
up vote 1 down vote

Roughly, you want something like

select min_trans_time, min_trans.id as min_trans_time_id,
       max_trans_time, max_trans.id as max_trans_time_id
from (
    select   trans_date,
             max(trans_time) as max_trans_time, 
             min(trans_time) as min_trans_time,
    from     trans        
    group by trans_date) min_max_trans,
       trans as min_trans,
       trans as max_trans
where min_trans.trans_time = min_max_trans.min_trans_time
and   max_trans.trans_time = min_max_trans.max_trans_time

Which might not be stable if several transes share the same trans_time (that is, running the query on the same set of data might yield different results. One easy way of solving this, if it's a concern, is to pick the max or min id, for instance. Of course, this might bias the results :).

You can also use Window Functions if you are using PostgreSQL 8.4 or later; they will provide a clearer query (clear if you are familiar with window functions :), and they let you do stuff which is pretty hard to do with the above strategy (for instance, getting the 2nd-higher value instead of the maximum). However, in my short experience, they performed worse than the self-join approach I propose here. Erwin's answer contains a version of the query using window functions. It would be interesting to note which query executes better and/or can be optimized better (by adding indexes, etc.).

share|improve this answer
+1 for considering non-unique times per day (although you did not solve that) and mentioning window functions (although you did not use them). – Erwin Brandstetter Dec 27 '11 at 20:56
I've upvoted yours. I propose this solution and keep it here 'cause window functions are not widely available in all RDBMSs, nor standardized, and because they might be less performant. However, I'll edit a bit my answer... – alex Dec 27 '11 at 21:41
2  
Actually, window functions are in the SQL:2003 standard and are supported by all major RDBMS (Oracle, PostgreSQL, SQL Server, ...) The only notable exception is MySQL. But then again, MySQL doesn't even support CTEs (another important SQL standard). – Erwin Brandstetter Dec 27 '11 at 21:50
Are they? I thought each RDBMS used different syntax. If it is, it boggles my mind that limit/offset is not standardized (which it wasn't last time I checked, IIRC- although that was ages ago). – alex Dec 27 '11 at 22:13
2  
RDBMS use slightly different syntax for most every detail. The most standard compliant RDBMS out there is probably PostgreSQL. That doesn't change the fact that SQL standards exist and that the major players implement them for the most part. – Erwin Brandstetter Dec 27 '11 at 22:19
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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