MySQLfilter

November 20, 2006 1:35 PM

Can anyone help a n00b with a MySQL (& coldfusion) problem?

I have a table with a list of transactions by date, so suppose:
(transactionid, userid, transactiontype, transactiondate, balance)
Where balance is the number/volume remaining for that user after the transaction. There are 2 transactiontypes.
I will have seed/initial balances available to me (assume 0 to start if it matters).

I want to graph the total aggregate balance of all users for each day in a given date range, one line for each transactiontype, both on the same chart.


What is the best way (performance-wise) to go about doing this?

I have a couple of implementations which involve a lot of looping + arithmetic in coldfusion but that's a bit of a slog performance-wise so I imagine that pushing more work over to the db would be a good thing here, but my MySQL is not really up to the task so maybe someone out there in AskMe land can help me out? My MySQL experience is limited to pretty much just getting the results I want returned; I have very little understanding of what the performance is like of a subquery vs a temporary table vs coldfusion query of a query. Any help would be appreciated.

Actually, on the coldfusion side, if anyone knows how to get 2 separate groups of "stacks" (seriesplacement="stacked") onto the same chart, that would save me a lot of work, but I have the feeling it's not possible.

Also, please don't hesitate to recommend a graphing package that hooks in easily to coldfusion if you know of one that would be the better way to go here.

Coldfusion MX 7.02 & MySql 4.1 on Apache/Linux if that matters.

posted by juv3nal to computers & internet (12 answers total)

"I want to graph the total aggregate balance of all users for each day in a given date range,"

I don't understand what you want; please show three examples.
posted by orthogonality at 2:58 PM on November 20, 2006

"I have very little understanding of what the performance is like of a subquery vs a temporary table vs coldfusion query of a query."

In general, subquery is faster than temp table is faster than client-side processing.
posted by orthogonality at 2:59 PM on November 20, 2006

"I have a table with a list of transactions by date, so suppose:
"(transactionid, userid, transactiontype, transactiondate, balance)"

If you replace balance with transaction_amount (which you should; balance is derived data, derived from transaction amount), we can show the aggregate transaction amount for each day. (For each day that actually has transactions; days without transactions won't show up. If you want to show every day regardless, see my answer to this askMefi.):

select transaction_type, transaction_date, sum( transaction_amount) as total_transaction_amount_per_type_for_date
from transactions
group by transaction_type, transaction_date
order by 2, 1
posted by orthogonality at 3:07 PM on November 20, 2006

Thanks orthogonality.
I'm only going to flag one because I'm lazy.

I'm going to pass on coming up with examples because you basically sussed what I'm going after when you suggested summing over transaction_amount. The problem is that in my case, the data is purchased and then straight-dumped so, in fact, transaction_amount would have to be derived since the way the data is, the table just has total balance-to-date.

Which makes summing tricky. I can't just sum over day N, because users that don't transact on day N still have balances that should be carried over and included in the total for that day.

That link to the other askmefi looks like it might be helpful.
posted by juv3nal at 3:55 PM on November 20, 2006

There's a tedious and costly way to do this, but it can be done. I think what you're saying is that you want the current aggregate balance for all users for any arbitrary day, yes?

Again, show me three examples of the data you have and the results it should give, and I'll give you the SQL. (I'll give you MySQL 5.x, not 4.1, because using views will make this much clearer.)
posted by orthogonality at 4:07 PM on November 20, 2006

I know a little about this, but probably only because I work for MySQL AB. You could write an aggregate function that does exactly this, for almost no cost. There's an example User-Defined Function that almost does what you want. See the AVGCOST functions in "sql/udf_example.c" file in the 5.0+ source code. Peek in the manual for UDF usage.

Store transaction amounts, and select whatever_the_function_name_is(transactions) ... and you'll get a running total per row. You could have created a temporary table containing one row per day that you're interested in, and then join that to your sum() of transactions, grouped per day. Thus, get transaction sum per day. Then, run that through the aggregate function.
posted by cmiller at 4:23 PM on November 20, 2006

Thanks cmiller, that looks a little complicated for me. My c is weaker than my sql.

Orthogonality, that's very generous of you. I'll have another crack at it myself and either post again or email you with some sample data if I can't hack it.

If it'll be tedious and costly regardless, that suggests to me that if I can come up with something that works it won't be stunningly worse than the optimal case so I may as well give it a shot.
posted by juv3nal at 4:48 PM on November 20, 2006

Costly in the database is still going to be far less costly than costly client-side, by a couple orders of magnitude. (And cmiller says it needn't be costly -- I can't tell until I see the data.)
posted by orthogonality at 4:51 PM on November 20, 2006

Yes, that's what I meant, I'm going to have a crack at a data-side implementation, not just running with my existing client side solution. Yay for temp tables & subqueries.
posted by juv3nal at 4:59 PM on November 20, 2006

juv3nal writes "I'm going to have a crack at a data-side implementation,"


Damnit, don't wave the crack under my nose and then take it back!
posted by orthogonality at 5:10 PM on November 20, 2006

Well it is my job after all, I should at least make some nominal effort to get competent at it.
The offer is appreciated though, and I may still take you up on it if it turns out I'm out of my depth.
posted by juv3nal at 10:48 PM on November 20, 2006

Yeah, sorry, SQL problems like this are like doing Sudoku for me.
posted by orthogonality at 4:43 AM on November 21, 2006