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!!!
