----- Original Message ----- From: "David Souza" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, August 25, 2004 7:18 PM Subject: Question about SQL statements......
> So I have all of this years apache access logs in a huge DB table, most > of what I wanted to get out is working fine, but I am trying to get top > 10 requests (hits) by day inside a certain section of the hierarchy, > but I can't seem to get the correct results. > > I have been trying many different ways of specifying the SQL statement, > so here is what I have so far: > > I have hits to a specific page, by week working fine here: > > mysql> SELECT week(time_stamp, 1) as week, request_uri, count(*) as hits > -> FROM `access_log` > -> WHERE (request_uri="/testing/folder/foldersetup.exe") AND > (time_stamp > 20040401000000 AND time_stamp < 20040823000000) > -> GROUP BY week > -> ORDER BY week ASC, hits DESC > -> LIMIT 20; > +------+---------------------------------+-------+ > | week | request_uri | hits | > +------+---------------------------------+-------+ > | 27 | /testing/folder/folderSetup.exe | 20069 | > | 28 | /testing/folder/folderSetup.exe | 3785 | > | 29 | /testing/folder/folderSetup.exe | 1607 | > | 30 | /testing/folder/folderSetup.exe | 878 | > | 31 | /testing/folder/folderSetup.exe | 858 | > | 32 | /testing/folder/folderSetup.exe | 779 | > | 33 | /testing/folder/folderSetup.exe | 721 | > | 34 | /testing/folder/folderSetup.exe | 1015 | > +------+---------------------------------+-------+ > 8 rows in set (0.85 sec) > > but when I try to get top hits with a like statement by day (only 2 > days) I get: > > mysql> SELECT day(time_stamp) as day, request_uri, count(DISTINCT > request_uri) as hits > -> FROM `access_log` > -> WHERE (time_stamp > 20040503000000 AND time_stamp < > 20040505000000) AND (request_uri LIKE "/docs/Carb/%") > -> GROUP BY day > -> ORDER BY day DESC, hits DESC > -> LIMIT 20; > +------+-------------------------------------------+------+ > | day | request_uri | hits | > +------+-------------------------------------------+------+ > | 4 | /docs/Carb/index.html | 7075 | > | 3 | /docs/Carb/Conceptual/index.html | 6979 | > +------+-------------------------------------------+------+ > 2 rows in set (39.68 sec) > > which seems to give me all the hits within /docs/Carb/ on the two days > and then displays the last request_uri that came through. Maybe I > cannot do this with one SQL statement or I just need more logic. > Ideally what I would want would be something like: > > +----- > +----------------------------------------------------------------------- > +------+ > | day | request_uri > | hits | > +----- > +----------------------------------------------------------------------- > +------+ > | 4 | /docs/Carb/index.html > | 1063 | > | 4 | /docs/Carb/Conceptual/test_system/new_test_system.html > | 930 | > | 4 | /docs/Carb/Conceptual/test_system/toc.html > | 921 | > | 4 | /docs/Carb/Conceptual/test_system/ > | 799 | > | 4 | /docs/Carb/Conceptual/Testing_book/toc.html > | 483 | > | 4 | > /docs/Carb/Conceptual/Testing_book/Intro/chapter_1_section_1.html | > 479 | > | 4 | /docs/Carb/Conceptual/Testing_book/index.html > | 433 | > | 4 | > /docs/Carb/Conceptual/newQuickTour/qt_intro/chapter_1_section_1.html | > 425 | > | 4 | /docs/Carb/date.html > | 421 | > | 4 | /docs/Carb/Conceptual/newQuickTour/toc.html > | 375 | > | 3 | /docs/Carb/index.html > | 1063 | > | 3 | /docs/Carb/Conceptual/test_system/new_test_system.html > | 930 | > | 3 | /docs/Carb/Conceptual/test_system/toc.html > | 921 | > | 3 | /docs/Carb/Conceptual/test_system/ > | 799 | > | 3 | /docs/Carb/Conceptual/Testing_book/toc.html > | 483 | > | 3 | > /docs/Carb/Conceptual/Testing_book/Intro/chapter_1_section_1.html | > 479 | > | 3 | /docs/Carb/Conceptual/Testing_book/index.html > | 433 | > | 3 | /docs/Carb/Conceptual/newQuickTour/chapter_1_section_1.html > | 425 | > | 3 | /docs/Carb/date.html > | 421 | > | 3 | /docs/Carb/Conceptual/newQuickTour/toc.html > | 375 | > +----- > +----------------------------------------------------------------------- > +------+ > > Any thoughts/help would be much appreciated!!! > Change your GROUP BY to this: GROUP BY day, request_uri The rest of the query should be fine the way it is. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
