From: | Jan Danielsson <jan(dot)m(dot)danielsson(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Average over time |
Date: | 2007-03-03 02:04:02 |
Message-ID: | 45E8D792.1060500@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Moginraj Mohandas | 2007-03-03 02:25:18 | unsubscribe |
Previous Message | David Monarchi | 2007-03-02 19:49:00 | Hash index on function which returns varying length arrays |