8

In my Postgres database table I can find the last 20 entries of cap_cs137 by date order:

select cap_cs137 FROM capintec ORDER BY cap_date DESC LIMIT 20;

I can also get the average:

select avg(cap_cs137) FROM capintec LIMIT 20;

However, how do I combine the above statements to get the average value for the last 20 entries by date order?

We have an outdated RHEL 5.8 server which supports Postgres 8.1.23 and therefore does not allow use of WITH queries.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
moadeep
  • 3,988
  • 10
  • 45
  • 72

2 Answers2

10

You can use a CTE:

WITH s AS
    (SELECT cap_cs137 FROM capintec ORDER BY cap_date DESC LIMIT 20)
SELECT avg(cap_cs137) FROM s;

The first query becomes cached as a temporary table "s", then we run the aggregate over it in the final query.

Edit:

It turned out the OP could not use a CTE since he is using an older version of Postgres, so the final answer was a sub-select (which I happen to like less than a CTE purely for readability; but does the exact same thing in this case):

SELECT avg(cap_cs137) FROM
   (SELECT cap_cs137 FROM capintec ORDER BY cap_date DESC LIMIT 20);
zxq9
  • 13,020
  • 1
  • 43
  • 60
  • Thanks for the solution. Unfortunately our server is outdated and using postgresql 8.1.23 and I am unable to use the WITH command – moadeep Dec 30 '13 at 10:43
  • Then an inner select, also suggested by gurka, should work. IIRC there are some funny rules about running aggregates over subqueries outside of a CTE (or was it running queries over aggregates in subqueries?), so YMMV: `SELECT avg(cap_cs137) FROM (SELECT cap_cs137 FROM capintec ORDER BY cap_date DESC LIMIT 20);` – zxq9 Dec 30 '13 at 10:45
  • A CTE is of no use here in any case. Just use a subselect. – Erwin Brandstetter Jan 03 '14 at 20:32
  • @ErwinBrandstetter I find CTEs to be immensely useful as named labels in place of sub-selects within the final query. Not *always* the most performant, but quite often more readable for others reading the SQL involved (or myself later). This case is trivial, but as an issue of style I much prefer it, since QLs like BS13 and RyuQ aren't available in Postgres. :-( IMO you're a genuine wizard who can see the Matrix in Postgres anyway, so you probably don't even consider readability in this way. For mere mortals like myself CTE labels are a lifesaver. – zxq9 Jan 04 '14 at 03:32
  • @zxq9: You are certainly right about readability. Formatting goes a long way in this respect, too. – Erwin Brandstetter Jan 04 '14 at 13:13
  • upvoted! how do I do the same problem if I had to get average from last N rows for each group? – PirateApp Jun 03 '18 at 07:35
  • 1
    @PirateApp Assuming there is an attribute "group" on the table, use a `WHERE` to limit the subquery to a given group. For example, to average the latest 20 entries of the `"Super Friends"`' `stuff` from `some_table`: `SELECT avg(stuff) FROM (SELECT stuff FROM some_table WHERE group = "Super Friends" ORDER BY date DESC LIMIT 20);`. – zxq9 Jun 03 '18 at 08:05
  • thanks! the query seems to specifically retrieve it for one group, what happens when multiple groups are involved – PirateApp Jun 03 '18 at 08:53
  • 1
    @PirateApp In that case you would be getting results mixed together. If, however, you were to make this query a subquery that is performed for each row in a new table of all groups, you could make a 3-stage query that returns a list of `{Group, RunningAverage}` for all groups. – zxq9 Jun 03 '18 at 12:30
  • `ERROR: subquery in FROM must have an alias`can be fixed by appending `as inner_qry` before `;` – gies0r Apr 04 '20 at 14:32
  • @gies0r What version of Postgres, and which query did you try (the CTE or the subselect)? – zxq9 Apr 07 '20 at 01:18
4

Just use an inner select:

SELECT AVG(cap_cs137)
FROM (SELECT cap_cs137 FROM capintec ORDER BY cap_date DESC LIMIT 20) AS sub;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
simon
  • 2,042
  • 2
  • 20
  • 31