Summary Tables with MySQL

At my new job I get to deal with GIS and eventually I’ll be putting their data on the web in one way or another. One of the recordsets I have dealt with already relates to households per voting district.

I was interested in seeing which district had the most constituent households. Ultimately, which representatives had the most constituents. I needed to pull a count of households per district—once I had those I could find out average households per district and other facts about the data based on the counts.

I hit a fork in the road…

*The Bad Way: *

Using select, on an ad hoc basis, scouring through large recordsets to find aggregate information. Example:

select field, count(field) as fieldcount from table group by field order by fieldcount asc

result:

value_a 111
value_b 222
value_c 333

value_n nnn

*The Good Way: *

Make a summary table.

create table fieldcounts select field, count(field) as fieldcount from table group by field order by fieldcount asc;

Sure, creating the table still takes some time—but now I can do my averages and other numerical analysis so much quicker and easier.

From there, I’ll just query my summary tables. Generate my charts/graphs from the summary table.

The only downside to this is data with high volatility – figure out the needs with the data and the frequency of reporting. If you only need to report on the data once a day, update the summary table once a day.

Advertisements

About andyhillky
I'm cool.

6 Responses to Summary Tables with MySQL

  1. Clayton says:

    I’ve been doing Atkins for four days, working out, and high pressure tanning. F*ck a bunch of b.s. kc girls giving me a hard time.

  2. bofe says:

    i’m going to start tanning, too once the financial situation clears itself up =) Say hello to mexi-bofe.

  3. UnluckyCat says:

    Mexi-bofe? I’m scared. AllRecipes has good food.

  4. Hey Bofeness,

    I’m honored that you thought of us. Yes, we have a metric TON (irony intended) of good recipes. Sometime you can come visit and if I know you’re coming, I’ll make copies of all the really good and healthy ones.

    I think we have like…. 25-30 or so that would be considered fairly healthy… we have others that aren’t particularly wonderful for you, but all of our stuff is naturally low in cholesterol and fat 🙂

    Aight man, if you want several (5-10) recipes to start with, let me know and we’ll dig through and type up a bunch into a word doc or some such and send ’em to ya.

  5. bofe says:

    if you have a scanner, feel free to scan/email one to me. or let me know what your sources are for the recipes. i don’t know when i will be visiting anywhere for a while.

    if you and shaina come to owensboro over the break, find me. i am living here now =)

  6. Actually, yeah… we’re coming to Owebsboro… Leaving early Thursday morning, intending to spend all day Thursday, and all day Friday there, coming back here Friday night ’cause my folks will be here Saturday morning.

    Maybe we’ll have to bring a couple with us, if I can remember… if not, then they are truly easy enough to type up quickly… then you can do some nerdy digitization of them to store in a fact/dimension data warehouse so you can easily run analysis cubes against them.

    Or something.

    Reading your post about life being slower and more simple being back in O-boro truly pains me… I really really really really really really long for simpler times… not easy, just simple.

    Anyway, hopefully we’ll catch up here in a day or two in the O-boro.

%d bloggers like this: