You rock!! Worked perfectly....
On Aug 25, 2004, at 4:31 PM, Rhino wrote:
----- 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
******************************************************************
David Souza voice:408-974-1992
Web Specialist http://developer.apple.com/
Worldwide Developer Relations mailto:[EMAIL PROTECTED]
******************************************************************
