Mercurial > hg > egg-tcls
diff quotedb.tcl @ 519:1cfa567f9500
quotedb: Improve statistics.
author | Matti Hamalainen <ccr@tnsp.org> |
---|---|
date | Sat, 02 May 2020 15:29:03 +0300 |
parents | 7fdc069840cc |
children | 3046ee0e574d |
line wrap: on
line diff
--- a/quotedb.tcl Sat May 02 07:41:41 2020 +0300 +++ b/quotedb.tcl Sat May 02 15:29:03 2020 +0300 @@ -369,14 +369,33 @@ set nmaxtop 5 set usql "SELECT user,count(*) AS uquotes FROM ${utable} GROUP BY user ORDER BY uquotes DESC LIMIT ${nmaxtop}" - set ntop {} - set nindex 0 + set ltopusers {} + set ntopusers 0 qdb_handle eval $usql { - incr nindex - lappend ntop "${user} (${uquotes})" + incr ntopusers + lappend ltopusers "${user} (${uquotes})" } - qdb_msg $ucmd $utable $upublic $unick $uchan "stats" [list $nquotes $nvotes $nindex [join $ntop ", "]] + set nmaxtop 3 + + set usql "SELECT count(*) AS uquotes,CAST(strftime('%Y',utime,'unixepoch') AS integer) AS uyear FROM ${utable} GROUP BY uyear ORDER BY uyear DESC LIMIT ${nmaxtop}" + set llatestyears {} + set nlatestyears 0 + qdb_handle eval $usql { + incr nlatestyears + lappend llatestyears "${uquotes} (${uyear})" + } + + set usql "SELECT count(*) AS uquotes,CAST(strftime('%Y',utime,'unixepoch') AS integer) AS uyear FROM ${utable} GROUP BY uyear ORDER BY uquotes DESC LIMIT ${nmaxtop}" + set ltopeyears {} + set ntopyears 0 + qdb_handle eval $usql { + incr ntopyears + lappend ltopeyears "${uquotes} (${uyear})" + } + + qdb_msg $ucmd $utable $upublic $unick $uchan "stats" [list $nquotes $nvotes] + qdb_msg $ucmd $utable $upublic $unick $uchan "stats2" [list $ntopusers [join $ltopusers ", "] $nlatestyears [join $llatestyears ", "] $ntopyears [join $ltopeyears ", "]] return 0 }