Support/Interesting queries: Difference between revisions
< Support
Jump to navigation
Jump to search
No edit summary |
No edit summary |
||
| Line 8: | Line 8: | ||
SELECT DATE(FROM_UNIXTIME(commentDate)) d, COUNT(*) c FROM tiki_comments WHERE objectType = 'forum' GROUP BY d ORDER BY d; | SELECT DATE(FROM_UNIXTIME(commentDate)) d, COUNT(*) c FROM tiki_comments WHERE objectType = 'forum' GROUP BY d ORDER BY d; | ||
==Instances of a pattern== | |||
Finds the most common strings in forum posts that match the pattern given. | |||
echo "SELECT data FROM tiki_comments WHERE objectType = 'forum' AND data LIKE '%.dll%'" > test.sql | |||
mysql -u root sumo < ./test.sql > output.txt | |||
grep -o "\S*\.dll" output.txt > output2.txt | |||
sort output2.txt | uniq -c | sort -nr > report.txt | |||
Revision as of 04:30, 22 July 2008
Threads per day
The number of threads (questions) posted each day.
SELECT DATE(FROM_UNIXTIME(commentDate)) d, COUNT(*) c FROM tiki_comments WHERE parentId = 0 AND objectType = 'forum' GROUP BY d ORDER BY d;
Replies per day
The number of posts (questions and replies) posted each day.
SELECT DATE(FROM_UNIXTIME(commentDate)) d, COUNT(*) c FROM tiki_comments WHERE objectType = 'forum' GROUP BY d ORDER BY d;
Instances of a pattern
Finds the most common strings in forum posts that match the pattern given.
echo "SELECT data FROM tiki_comments WHERE objectType = 'forum' AND data LIKE '%.dll%'" > test.sql mysql -u root sumo < ./test.sql > output.txt grep -o "\S*\.dll" output.txt > output2.txt sort output2.txt | uniq -c | sort -nr > report.txt