2

This may already have been asked, but StackOverflow is massive and trying to google for something specific enough to actually help is a nightmare!

I've ended up with a fairly large SQL query, and was wondering if SO could maybe point out easier methods for doing it that I might have missed.

I have a table called usage with the following structure:

host    | character varying(32)       | 
usage   | integer                     | 
logtime | timestamp without time zone | default now()

I want to get the usage value for both the MAX and MIN logtimes. After working through some of my old textbooks (been a while since I really used SQL properly), I've ended up with this JOIN:

SELECT *
FROM (SELECT u.host,u.usage AS min_val,r2.min
  FROM usage u
  JOIN (SELECT host,min(logtime) FROM usage GROUP BY host) r2
  ON u.host = r2.host AND u.logtime = r2.min) min_table
NATURAL JOIN (SELECT u.host,u.usage AS max_val,r1.max
  FROM usage u
  JOIN (SELECT host,max(logtime) FROM usage GROUP BY host) r1
  ON u.host = r1.host AND u.logtime = r1.max) max_table
;

This seems like a messy way to do it, as I'm basically running the same query twice, once with MAX and once with MIN. I can get both logtime columns in one query by doing SELECT usage,MAX(logtime),MIN(logtime) FROM ..., but I couldn't work out how to then show the usage values that correspond to the two different records.

Any ideas?

shearn89
  • 798
  • 1
  • 9
  • 24
  • PostgreSQL 9.1.2! EDIT: although this will eventually connect to a database that's not managed by me, which is running 8.4.9, if that's going to make a big difference? – shearn89 Dec 31 '11 at 16:07

1 Answers1

3

With PostgreSQL 9.1 you have window functions at your disposal (8.4+):

SELECT DISTINCT
       u.host
      ,first_value(usage) OVER w AS first_usage
      ,last_value(usage) OVER w AS last_usage
FROM   usage u
WINDOW w AS (PARTITION BY host ORDER BY logtime, usage
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

I sort the partition by logtime and by usage in addition to break any ties and arrive at a stable result. Read about window functions in the manual.

For some more explanation and links you might want to refer to recent related answers here or here.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • So, the WINDOW bit basically does what GROUP BY does, with UNBOUNDED PRECEDING/FOLLOWING doing the min/max respectively? – shearn89 Dec 31 '11 at 16:11
  • Awesome, this sounds like just what I need! Cheers! – shearn89 Dec 31 '11 at 16:15
  • 1
    @shearn89: The `WINDOW` bit is just syntactical convenience so you don't have to spell out the `OVER` clause twice above. The smart part is where I use DISTINCT to arrive at the result in one step without subqueries. (DISTINCT is applied last.) – Erwin Brandstetter Dec 31 '11 at 16:18