[ Ventilation ] | I'm a Statistics FREAK

7-30-2008 4:35 am

fuzmeister: I'm a Statistics FREAK

So I'm playing around with the Vent code - specifically this page. I'm trying to add some relevant statistics to the page... however, I've run into an issue with SQL.

If you clicked that link you will notice the page is SLOW. That's because to find the post with the most comments (for the "most active post" column), I have it going through every post and basically if the amount of comments returned with the corresponding post number is greater than what's currently stored in the variable, I replace the variable. There has got to be a better way.

Is anyone aware of a way using SQL to return the the fields with the most occurrences of a like value? That would mean one query in the comments to get the post number that has the most...

Mood: Statistical

Tags (beta): vent, development

Comments (7)

fuzmeister - (The Real ST)

Code is still buggy too judging from my "most active post."

David - ()

SELECT the comments column FROM the username and ORDER BY the comments column. I'm not sure if it's ascending or decending by default, but the post your looking for should be the last or first item retrieved.

David - ()

Not sure how Brandon's database is laid out, but you may even be able to SELECT the postname column FROM the username and ORDER BY the comments column.

fuzmeister - (The Real ST)

Yes, that also makes sense. The tables are laid out in such a way that comments and posts are simply linked by a postid field. Overall, though, what are your thoughts on the addition? Not extremely useful, but kind of cool.

Brandon - (<-- The Electric Sunshine Man, yo!) - Administrator

So, here are some insights on the database layout.

We don't really keep a column with a running total of comments, we just have a postid column in the comments table that tells us which comment goes with which post. This makes doing something like this a little more tricky than it should be, but thats ok.

First off we can simplify getting the number of posts by using aggregate functions. Our initial SQL query looks like this:

Code:

SELECT vent_users.ID, vent_users.username, UNIX_TIMESTAMP(MAX(vent_posts.dateposted)) as timestamp,
COUNT(vent_posts.ID) AS postcount 
FROM vent_users, vent_posts 
WHERE vent_posts.userid = vent_users.ID
GROUP BY vent_users.ID 
ORDER BY timestamp DESC

Its pretty simple, it lets us get all the users, grab their number of posts, and get the date of their most recent post so we can sort the list by who has posted most recently.

Then we just have to loop through all the user ID's, and grab the post with the largest number of comments. Again, not to difficult.

Code:

SELECT vent_posts.ID, vent_posts.topic, COUNT(vent_comments.ID) AS commentcount
FROM
vent_posts, vent_comments 
WHERE
vent_posts.userid = $row[ID]
AND vent_comments.postid = vent_posts.ID 
GROUP BY vent_posts.ID 
ORDER BY commentcount DESC 
LIMIT 1

This runs fairly quickly since we limit the results to one, and sort it by the post with the highest number of comments. As you can see, I'm not completely insane!

Demannu - (<--- Eats Worms)

I was all excited to work up a query that did not involve work, but without an idea of the database structure or what particular flavor of SQL we were dealing with, I figured I wouldn't be able to give a specific query. Then of course Brandon chimed in and got all uppity with his inside knowledge. Don't that beat all.
Here's an idea. Don't know if it would be useful.
Update vent_users set username = 'puddintame' where username not like 'puddintame'

fuzmeister - (The Real ST)

I'm fairly certain that had I not cleared my first phase of Vent posts (mid 2002- mid 2004), my half-a-bunny post would have the most comments on Ventilation.

I never regretted deleting those posts until today. :(

Your account has been disabled.

Lyric

Yesterday I got so old, I thought that I could die...

User Journals

Survivors... by fuzmeister
Why?... by fuzmeister
Microsoft Sends Me Emails in Portuguese by David
No More Acolyte by David
Don't... by fuzmeister
Hello, The Year is Over Halfway Done by fuzmeister
I Forced Myself to Watch The Acolyte by David
Reddit... by David
Invincible Shield by fuzmeister
I broke some things. by Brandon

Active Users List

Your Hosts

Brandon's Journal
Beowulf's Journal
Vikki's Journal

Links

S+F Software
Acts of Gord
Nitrus Oxyde.net