So

Forest is people. People is you. You is us.
Post Reply
swithun
Posts: 2683
Joined: Wed Mar 29, 2006 12:24 pm

So

Post by swithun » Thu Jun 17, 2010 9:03 pm

At last, someone else has noticed that so many sentences start with 'so': http://anand.ly/articles/so-pushes-to-t ... f-the-line. There are quite a few offenders on the BB, or so I've noticed. I was going to say that it would be nice if there was a way to work out who the so and so's were. And so there is, with a recent back up of the BB database and a MySQL server on my netbook:

Code: Select all

mysql> SELECT sos.offender, sos.so, 100 / totals.total * sos.so AS percentage FROM (SELECT phpbb3_posts.poster_id, phpbb3_users.username AS offender, COUNT(*) AS so FROM phpbb3_posts INNER JOIN phpbb3_users ON phpbb3_posts.poster_id = phpbb3_users.user_id WHERE phpbb3_posts.post_text RLIKE '^so[^a-z]' GROUP BY phpbb3_users.username HAVING so >= 10) AS sos INNER JOIN (SELECT phpbb3_posts.poster_id, COUNT(*) AS total FROM phpbb3_posts GROUP BY phpbb3_posts.poster_id) AS totals WHERE sos.poster_id = totals.poster_id ORDER BY so DESC LIMIT 10;
+----------+----+------------+
| offender | so | percentage |
+----------+----+------------+
| James    | 62 |     5.2498 |
| ravanwin | 54 |     1.2127 |
| Shannon  | 40 |     1.7637 |
| baloo    | 34 |     8.8083 |
| dan      | 32 |     2.6622 |
| Gaz      | 31 |     2.3718 |
| chris    | 30 |     1.4641 |
| beev     | 29 |     1.8046 |
| chombee  | 27 |     2.4150 |
| nix      | 24 |     2.7939 |
+----------+----+------------+
10 rows in set (3.83 sec)
which shows the top 10 offenders by raw 'so' count. Or, if the query is done like so, to order by percentage of total posts, then you get this:

Code: Select all

mysql> SELECT sos.offender, sos.so, 100 / totals.total * sos.so AS percentage FROM (SELECT phpbb3_posts.poster_id, phpbb3_users.username AS offender, COUNT(*) AS so FROM phpbb3_posts INNER JOIN phpbb3_users ON phpbb3_posts.poster_id = phpbb3_users.user_id WHERE phpbb3_posts.post_text RLIKE '^so[^a-z]' GROUP BY phpbb3_users.username HAVING so >= 10) AS sos INNER JOIN (SELECT phpbb3_posts.poster_id, COUNT(*) AS total FROM phpbb3_posts GROUP BY phpbb3_posts.poster_id) AS totals WHERE sos.poster_id = totals.poster_id ORDER BY percentage DESC LIMIT 10;
+----------+----+------------+
| offender | so | percentage |
+----------+----+------------+
| baloo    | 34 |     8.8083 |
| James    | 62 |     5.2498 |
| simone   | 23 |     4.1516 |
| nix      | 24 |     2.7939 |
| dan      | 32 |     2.6622 |
| chombee  | 27 |     2.4150 |
| Gaz      | 31 |     2.3718 |
| bill     | 13 |     2.1417 |
| beev     | 29 |     1.8046 |
| Shannon  | 40 |     1.7637 |
+----------+----+------------+
10 rows in set (3.93 sec)
- a slightly different set of grammatical guerillas, so to say.

I have so only counted users who have started a post with 'so' at least 10 times.

So what?

User avatar
ravanwin
Posts: 5060
Joined: Thu Aug 26, 2004 8:08 pm
Location: edinburgh
Contact:

Re: So

Post by ravanwin » Fri Jun 18, 2010 12:55 am

oddly fascinating.

User avatar
Jane
Posts: 1557
Joined: Thu Sep 16, 2004 3:10 pm

Re: So

Post by Jane » Fri Jun 18, 2010 3:54 am

AM-azing (or maybe I'm just happy to be a non-offender....) x
"We all tend to idealise kindness and tolerance, then wonder why we find ourselves infested with losers and nutcases." Sebastian Horsley

simone
Posts: 993
Joined: Wed Mar 19, 2008 11:47 am

Re: So

Post by simone » Fri Jun 18, 2010 8:56 am

so, i am not quite sure what this means about my use of the english language....and does it point to something deeper in my inner psyche! hmmmmmmm
I saw two shooting stars last night, i wished on them, but they were only satellites.
is it wrong to wish on space hardware?
(Billy Bragg)

User avatar
dan
Posts: 1410
Joined: Wed Apr 13, 2005 1:04 pm

Re: So

Post by dan » Fri Jun 18, 2010 9:46 am

no
Our big brother's got no heart,
when I get my chance I'm going to punch him in the nose, in the nose, in the nose

DavidW
Posts: 162
Joined: Wed Nov 18, 2009 1:45 pm

Re: So

Post by DavidW » Sat Jun 19, 2010 9:31 am

*flex*...

Post Reply