Daily post stats

[Ari Rahikkala]

Scott seemed to be interested in something like this...

Straight from the database's mouth... and then munged in random ways because I couldn't actually get them out of the database in a sane form (it's funny how that works - first I needed to sort them by date which the database had refused to do for some reason I can't understand at all, then I needed to add in those days that had 0 posts... I did the former with the unix utility sort, and the latter with a custom Haskell program). I can munge the data further if anyone needs it... and I'll consider automating these reports and making the format more palatable in the unknown future (certainly not before I finally get the automatic forum database backups in order, I've been doing those by hand so far).

The stats can be found at http://shireroth.kuroshiro.net/posts.txt

[Neike Taika-Tessaro]

...no-o-o... this is going to sound horribly smart-arsey... and I don't mean it to be that way...

*gets over self and types*
I see what you mean with dates that didn't have any posts... but wouldn't the rest of it be easy to get?

Code: Select all

SELECT DATE_FORMAT(FROM_UNIXTIME(post_time),GET_FORMAT(DATE,'JIS')) AS date, COUNT(post_id) FROM `phpbb_posts` GROUP BY date ORDER BY date ASC

Or aren't you guys running on MySQL? In case of <4.1.1, replace GET_FORMAT(DATE,'JIS') with '%Y-%m-%d'... or anything you like for date formatting, really)

BTW, I find that ridiculously insightful. *now meanders off to use the query on all her forums, curious*

[Ari Rahikkala]

In case you weren't aware, I don't know everything about SQL .

In any case, it turns out that using the %Y-%m-%d date format instead of %Y-%c-%e as I did fixes the sorting issue, and I appreciate that you showed me the query because I don't think I would have noticed that myself... however, the only way to add in the dates with zero posts that I can see involves actually generating the list of all dates between two dates, and I don't exactly feel any urge to do this in SQL.

(btw, we run on MySQL 4.0.24... if it were up to me it would be 5.0.x, but hey, you can't have everything)

I worked on the program for weaving in the days with zero posts a bit, btw... in case anybody's interested:

Code: Select all

import Data.Time.Calendar.MonthDay
import Data.Time.Calendar

inputFile = "daily-posts-before-2007-11-20.txt"

generatedDates :: [(String, Int)]
generatedDates = [(show year ++ "-" ++ show month ++ "-" ++ show day, 0) |
                  year <- [2000..2007],
                  month <- [1..12],
                  day <- [1..monthLength (isLeapYear year) month]]

-- first argument needs to contain every day used in second argument, both arguments need to be sorted
weave :: [(String, Int)] -> [(String, Int)] -> [(String, Int)]
weave [] _ = []
weave _ [] = []
weave aa@((adate, aposts):as) ba@((bdate, bposts):bs)
    | adate == bdate = (adate, aposts + bposts) : weave as bs
    | otherwise = (adate, aposts) : weave as ba

input :: IO [(String, Int)]
input = (return . map (\ [a, b] -> (a, read b)) . map words . lines) =<< readFile inputFile

main = 
    (putStrLn . unlines . map show . dropWhile ((==) 0 . snd) . weave generatedDates) =<< input

[Neike Taika-Tessaro]

In case you weren't aware, I don't know everything about SQL .

Well, neither do I, of course. ^_^ I have, though, noticed that it's ridiculously powerful... I spent a lot of my life not even knowing salt about OUTER JOINs, imagine my delight when I discovered that...! Since then, I've been in pretty firm belief that almost all database sorting/querying issues can be solved entirely in SQL, and so I frequently visit the MySQL.com website to find methods to do my bidding. Or my quasi-bidding if my bidding is too much to ask for.

Anyway, glad I could help!

however, the only way to add in the dates with zero posts that I can see involves actually generating the list of all dates between two dates, and I don't exactly feel any urge to do this in SQL.

I agree whole-heartedly. I don't even know if it's possible, tbh. I pretty much assume it is possible (see musing above), and then it's just a question of [SOME] OUTER JOIN ... WHERE posts IS NULL, but, like you, I'd probably rather code a quick program to do it than try and figure that one out.

(btw, we run on MySQL 4.0.24... if it were up to me it would be 5.0.x, but hey, you can't have everything)

Egads! I know how frustrating that is. Personally, I love ON DUPLICATE KEY UPDATE - oh, and DATEDIFF(). Both are MySQL 4.1'ers, and I go absolutely apeshit if I have to do without them... and I frequently do... ugh.

in case anybody's interested

*is always interested* ^_^ Though I won't be able to make much sense of that until I finish your Haskell lessons. (And, yes, I do bloody well want to finish them, Haskell rocks.)

[Kaiser Mors V]

I makes me so happy to see CS geekery in this place again...

[Ari Rahikkala]

Heh... you know, in the parts that I frequent, it doesn't happen every day that SQL is described as a powerful language. I mean, sure, for the rather small area of its intended function it *is* powerful, sure, but consider trying to express something like generatedDates here in it for instance...

As for the Haskell lessons... if you're still interested in them, I might go ahead and try something different with them... something more useful than these lessons which you could just as well read from http://haskell.org or the like...

[Ari Rahikkala]

Didn't get around to automating this yet. Here's a stat update anyway in case somebody's curious. The format's different from last time and there's a couple of days right at the beginning where there were no posts which this list doesn't mention at all. I'll munge the data into a more palatable/automatically usable form if someone shows interest in using it for something.

Code: Select all

mysql> SELECT DATE_FORMAT(FROM_UNIXTIME(post_time),'%Y-%m-%d') AS date, COUNT(post_id) FROM `phpbb_posts` WHERE (post_time >  1192766400) GROUP BY date ORDER BY date ASC;

+------------+----------------+
| date       | COUNT(post_id) |
+------------+----------------+
| 2007-10-20 |              1 |
| 2007-10-21 |              1 |
| 2007-10-23 |              2 |
| 2007-10-25 |              5 |
| 2007-10-26 |             27 |
| 2007-10-27 |            135 |
| 2007-10-28 |             64 |
| 2007-10-29 |             36 |
| 2007-10-30 |             38 |
| 2007-10-31 |             28 |
| 2007-11-01 |             43 |
| 2007-11-02 |             26 |
| 2007-11-03 |             61 |
| 2007-11-04 |             31 |
| 2007-11-05 |             42 |
| 2007-11-06 |             35 |
| 2007-11-07 |             97 |
| 2007-11-08 |             41 |
| 2007-11-09 |             61 |
| 2007-11-10 |             33 |
| 2007-11-11 |             28 |
| 2007-11-12 |             31 |
| 2007-11-13 |             24 |
| 2007-11-14 |             47 |
| 2007-11-15 |             90 |
| 2007-11-16 |             71 |
| 2007-11-17 |             86 |
| 2007-11-18 |             56 |
| 2007-11-19 |             58 |
| 2007-11-20 |             67 |
| 2007-11-21 |            104 |
| 2007-11-22 |             96 |
| 2007-11-23 |            143 |
| 2007-11-24 |             73 |
| 2007-11-25 |            108 |
| 2007-11-26 |            232 |
| 2007-11-27 |            212 |
| 2007-11-28 |            148 |
| 2007-11-29 |            119 |
| 2007-11-30 |            129 |
| 2007-12-01 |            131 |
| 2007-12-02 |             46 |
| 2007-12-03 |             80 |
| 2007-12-04 |             67 |
| 2007-12-05 |             89 |
| 2007-12-06 |             84 |
| 2007-12-07 |             72 |
| 2007-12-08 |             49 |
| 2007-12-09 |             42 |
| 2007-12-10 |             65 |
| 2007-12-11 |            121 |
| 2007-12-12 |            176 |
| 2007-12-13 |            145 |
| 2007-12-14 |            166 |
| 2007-12-15 |            186 |
| 2007-12-16 |            106 |
| 2007-12-17 |             30 |
| 2007-12-18 |             51 |
| 2007-12-19 |             41 |
| 2007-12-20 |             55 |
| 2007-12-21 |             94 |
| 2007-12-22 |             58 |
| 2007-12-23 |             54 |
| 2007-12-24 |             31 |
| 2007-12-25 |             73 |
| 2007-12-26 |             61 |
| 2007-12-27 |             69 |
| 2007-12-28 |             74 |
| 2007-12-29 |            113 |
| 2007-12-30 |            149 |
| 2007-12-31 |            119 |
| 2008-01-01 |            338 |
| 2008-01-02 |            254 |
| 2008-01-03 |            164 |
| 2008-01-04 |            142 |
| 2008-01-05 |            143 |
| 2008-01-06 |             86 |
| 2008-01-07 |             45 |
| 2008-01-08 |            121 |
| 2008-01-09 |             79 |
| 2008-01-10 |             68 |
| 2008-01-11 |             53 |
| 2008-01-12 |             35 |
| 2008-01-13 |             18 |
| 2008-01-14 |             21 |
| 2008-01-15 |             36 |
| 2008-01-16 |             88 |
| 2008-01-17 |             84 |
| 2008-01-18 |             43 |
| 2008-01-19 |             44 |
| 2008-01-20 |             42 |
| 2008-01-21 |             32 |
| 2008-01-22 |             54 |
| 2008-01-23 |             22 |
| 2008-01-24 |             28 |
| 2008-01-25 |             16 |
| 2008-01-26 |             16 |
| 2008-01-27 |             16 |
| 2008-01-28 |             16 |
| 2008-01-29 |             17 |
| 2008-01-30 |             14 |
| 2008-01-31 |             36 |
| 2008-02-01 |             62 |
| 2008-02-02 |             22 |
| 2008-02-03 |             34 |
| 2008-02-04 |             41 |
| 2008-02-05 |             28 |
| 2008-02-06 |             31 |
| 2008-02-07 |             19 |
| 2008-02-08 |              8 |
| 2008-02-09 |              9 |
| 2008-02-10 |              2 |
| 2008-02-11 |             12 |
| 2008-02-12 |             41 |
| 2008-02-13 |             24 |
| 2008-02-14 |             15 |
| 2008-02-15 |             12 |
| 2008-02-16 |              4 |
| 2008-02-17 |             11 |
| 2008-02-18 |             13 |
| 2008-02-19 |             26 |
| 2008-02-20 |             97 |
| 2008-02-21 |            143 |
| 2008-02-22 |             74 |
| 2008-02-23 |             67 |
| 2008-02-24 |             51 |
| 2008-02-25 |             85 |
| 2008-02-26 |            111 |
| 2008-02-27 |            122 |
| 2008-02-28 |             82 |
| 2008-02-29 |            109 |
| 2008-03-01 |            134 |
| 2008-03-02 |            106 |
| 2008-03-03 |             44 |
| 2008-03-04 |             43 |
| 2008-03-05 |             41 |
| 2008-03-06 |             46 |
| 2008-03-07 |             63 |
| 2008-03-08 |             97 |
| 2008-03-09 |             63 |
| 2008-03-10 |             59 |
| 2008-03-11 |             39 |
| 2008-03-12 |             62 |
| 2008-03-13 |             87 |
| 2008-03-14 |             73 |
| 2008-03-15 |             35 |
| 2008-03-16 |             17 |
| 2008-03-17 |             55 |
| 2008-03-18 |             51 |
| 2008-03-19 |             43 |
| 2008-03-20 |             29 |
| 2008-03-21 |             25 |
| 2008-03-22 |             32 |
| 2008-03-23 |             32 |
| 2008-03-24 |             21 |
| 2008-03-25 |             23 |
| 2008-03-26 |             45 |
| 2008-03-27 |             31 |
| 2008-03-28 |              9 |
| 2008-03-29 |             19 |
| 2008-03-30 |             12 |
| 2008-03-31 |              9 |
| 2008-04-01 |             18 |
| 2008-04-02 |             32 |
| 2008-04-03 |             11 |
| 2008-04-04 |              6 |
| 2008-04-05 |              7 |
| 2008-04-06 |             11 |
| 2008-04-07 |              9 |
| 2008-04-08 |              7 |
| 2008-04-09 |             13 |
| 2008-04-10 |             16 |
| 2008-04-11 |             15 |
| 2008-04-12 |              6 |
| 2008-04-13 |             15 |
| 2008-04-14 |             10 |
| 2008-04-15 |             31 |
| 2008-04-16 |             36 |
| 2008-04-17 |             25 |
| 2008-04-18 |             17 |
| 2008-04-19 |              8 |
| 2008-04-20 |              6 |
| 2008-04-21 |             19 |
| 2008-04-22 |              6 |
| 2008-04-23 |             34 |
| 2008-04-24 |            100 |
+------------+----------------+