Reputation: 3335
I'm using the command
select video_info count(distinct src_ip) from videoinfo
group by video_info
order by count(distinct src_ip) DESC
to fetch 2 columns of data, the first column represents identifiers for each video items, the second column records how many times they have been downloaded.
I also want to calculate, for example, 5% of videos have been downloaded twice, 10% videos have been downloaded 10 times, then record the percentage numbers and the download times into two arrays.
Does Mysql support such calculation? or I need to switch to Python to do that? If so, how to do that in Python?
Upvotes: 0
Views: 108
Reputation: 213125
In Python you can use this:
cur.execute("SELECT video_info, COUNT(distinct src_ip) "
"FROM videoinfo "
"GROUP BY video_info "
"ORDER BY COUNT(DISTINCT src_ip) DESC")
counter = dict(cur)
for n in xrange(1, max(counter.itervalues()) + 1):
perc = 100. / sum(1 for nb in counter.itervalues() if nb == n) / len(counter)
if perc:
print '%.f%% videos have been downloaded %d times' % (perc, n)
With counter = {'a': 1, 'b': 1, 'c': 2, 'd': 1, 'e': 2, 'f': 3}
this prints:
50% videos have been downloaded 1 times
33% videos have been downloaded 2 times
17% videos have been downloaded 3 times
Upvotes: 2