This little snippet for getting a frequency distribution out of an
sqlite database demonstrates an interesting approach. I stumbled
across it while trying to figure out how to more easily get at a
frequency distribution. I'm finding it a useful technique for a range
of statistical reporting needs.
I know its not ruby but for you guys working on community sites it can
make getting some types of reporting data a bit easier. Anybody got
any similar or better approaches for getting a frequency distribution
directly out of an sqlite or mysql database?
In this, tr_cg is a normalised value in the range 0 to +1 with actual
values of 0 up to 0.09, mostly always grouped around 0.04. By simply
taking up to the fourth character of the number converted to a string
as the frequency buckets I get what I need.
SELECT band, round(100*dt.cnt, 2) as percent
FROM ( SELECT (1.0*count(*)/(SELECT count(*) FROM recommend WHERE
bb=ss)) AS cnt,
SUBSTR(round(tr_cg,2), 0, 4) AS band FROM recommend WHERE bb=ss
AND tr_cg >0
GROUP BY SUBSTR(round(tr_cg,2), 0, 4)
) dt;
Applying the round(tr_cg,2) function is needed because some really
small numbers in the data set were being reported as exponents and
mangling the output. The output looks like:
band percentage
0.00 1.52
0.01 7.58
0.02 6.82
0.03 8.33
0.04 8.33
0.05 3.79
0.06 3.79
0.07 0.76
0.08 3.03
0.09 1.52
0.12 1.52
0.13 0.76
0.15 0.76
0.19 0.76
---------------------------------
John Jones
USA: (+1)646 387 0873
UK: (+44) (0)797 644-3043
"Ideas are like rabbits. You get a couple and learn how to handle
them, and pretty soon you have a dozen."
John Steinbeck
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"NWRUG" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/nwrug-members?hl=en
-~----------~----~----~----~------~----~------~--~---
