How do I get the following stock information from SQL?

Suppose I have the following table columns

• DATE
• TIME
• SECURITY
• TRADEPRICE

For each date, I wish to get the first and last tradeprice for every security that tra开发者_如何转开发ded on that particular date. How can I do this in SQL?

---

Postgresql's windowing functions give several ways of getting what you want. I haven't used them enough to know which (if any) of the slight variants is best-optimized, but one version is

SELECT dt, sec, t0, p0, t1, p1 FROM
(
SELECT dt, sec,  first_value(tm) over date_window as t0,
  first_value(price) OVER date_window AS p0,
  last_value(tm) OVER date_window AS t1,
  last_value(price) OVER date_window AS p1,
  rank() OVER date_window AS r
 FROM a
 WINDOW date_window AS (PARTITION BY dt, sec ORDER BY tm
  RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
) AS subquery
WHERE r=1;

I've changed the field names to avoid reserved words.

---

try

   Select Coalesce(f.Security, l.Security) security.
      Coalesce(f.Date, l.Date) Date,
      f.TradePrice firstPrice,
      l.TradePrice lastPrice
   From table f Full Join table l
      On f.Security = l.Security
         And f.Date = l.Date
         And f.time = (Select Min(Time)
                       From table
                       Where Security = f.security 
                          And Date = f.Date)
         And l.Time = (Select Max(Time)
                       From table
                       Where Security = l.security 
                          And Date = l.Date)