Обсуждение: Average over time

Hello all,

   I have a table ("transactions") which has the following columns:

      id (serial)
      dt (date)
      amount (float)
      cat_id (int)
      sup_id (int)

   Each time I purchase something I record it in my database. I like to
extract statistics. I have - using some earlier help from group - been
able to get some neat data.

   But then I decided to get creative. I wanted to see how an "average
expense per day/week/month" line diagram would look like. I wrote a
function to do this for days, but I'm doing it using a for loop (I'm
writing this in Python, but that's not really important). Pseudocode:

   for idate in range(firstdate, lastdate):
      query("select avg(foo.asum) from (select dt,sum(amount) as asum
where dt >= '%s' AND dt <= '%s' group by dt) AS foo)" % (firstdate, idate)

   Well, as you gather, this will perform n unique queries, where n is
the number of days in the date range. It's actually pretty fast, but I
would *like* to get a table which looks something like:

   dt         | avg_asum
   -----------+------------
   2007-01-01 | 1024
   2007-01-02 | 962

   ...etc. Obviously, the avg_asum is the average for asum up to the dt
column's date. My gut feeling is that this can not be done -- but I
don't know why. Is it possible?


--
Kind regards,
Jan Danielsson

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


>    ...etc. Obviously, the avg_asum is the average for asum up to the dt
> column's date. My gut feeling is that this can not be done -- but I
> don't know why. Is it possible?

Perhaps something like this:

SELECT thisday, AVG(asum) AS avg_asum
FROM
  (SELECT dt, SUM(amount) AS asum FROM transactions GROUP BY dt) AS x,
  (SELECT ('2007-01-01'::date+'1 day'::interval*q)::date AS thisday
   FROM generate_series(0,10) AS q) AS y
WHERE dt <= thisday
GROUP BY 1 ORDER BY 1;

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200703031916
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFF6g/PvJuQZxSWSsgRAxxVAKC/wV0vh+b9ZTi3hEjEOjGK+4sniACfeSdU
gqlKeLxqxylcZCCbY6OD1wQ=
=srvv
-----END PGP SIGNATURE-----