kylewolfe — 2012-08-14T12:48:13-04:00 — #1
So I don't have a need for this yet, but I got wondering how I might build it should the need arise (which i have a project coming up that will certainly need it). I was looking at kickstarter.com's mobile app screenshots today, and saw the common graph of total amount pledged by day. Aug 1st would show 100k, Aug 2 moves up to 101k, etc. So how would I prepare the data for a graph such as this with a data set in this form:
PLEDGE_DATE | PLEDGE_AMOUNT
08/01/2012 | 1000
08/01/2012 | 5000
08/02/2012 | 50
08/02/2012 | 500
08/02/2012 | 5848
08/02/2012 | 3838
08/02/2012 | 138
Who can beat r937 to the punch?
r937 — 2012-08-14T13:01:28-04:00 — #2
is your question related to how to pull the data out in a form suitable for graphing?
you'd need GROUP BY for that, and a theta self-join, i think, if you're looking for cumulative total
if just a total pledge by day, not cumulative, it's a lot simpler
kylewolfe — 2012-08-14T13:39:02-04:00 — #3
So Yes I suppose it would obviously need a sum, grouped by day. But where am I getting the fact that aug 1 + aug 2 pledges equals my total for aug2, aug 3 would be the sum of aug 1-3, etc (assuming the first pledge was aug 1). I feel like I'd have to do a subquery to find the sum for that project where the pledge date <= each date in the range.
With the sample data provided, the graph would show aug 1sts sum at 6,000, and the point for aug 2nd would be at 16374.
r937 — 2012-08-14T14:54:05-04:00 — #4
yeah, you could, but subqueries are occasionally very poor performers
i prefer a theta self-join
first, create this view --
CREATE VIEW pledges_by_date AS
, SUM(pledge_amt) AS pledge_sum
then, here's your cumulative query --
, SUM(one.pledge_sum) AS pledge_cumulative
FROM pledges_by_date AS two
JOIN pledges_by_date AS one
ON one.pledge_date <= two.pledge_date
i'm guessing you're on sql server, so you could also use WITH syntax instead of the view
kylewolfe — 2012-08-14T15:03:28-04:00 — #5
Ah, very clean and efficient. I'll be referring back to this in a few months I feel. I currently work with Oracle quite a bit, but I'll be using this on MySQL.
mittineague — 2014-09-20T03:09:44-04:00 — #6
This topic is now archived. It is frozen and cannot be changed in any way.