SitePoint Sponsor |
|
User Tag List
Results 1 to 3 of 3
Thread: SQL Check Please!
-
Jul 25, 2005, 14:12 #1
- Join Date
- Mar 2004
- Location
- Toronto, Canada
- Posts
- 326
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
SQL Check Please!
I'm looking for some feedback regarding a few SQL queries I've put together. I just want a 'second' set of eyes to look over the queries so that I know they're doing what they should be. Here's what the table they are using looks like:
//table site_stats
track_id = auto incrementing int
track_refer = varchar(120) -- stores refering page
track_page = varchar(120) -- stores page being looked at
track_date = date -- stores date (YYYY-MM-DD)
track_time = time -- stores time (HH:MM:SS)
track_session = varchar(60) -- stores unique session id
Here's the SQL:
1. Get a count of the unique sessions (unique visitors):
SELECT COUNT(DISTINCT track_session) cnt FROM site_stats;
2. Get the Most Viewed Page:
SELECT COUNT(*) cnt, track_page
FROM site_stats
GROUP BY track_page
ORDER BY cnt DESC LIMIT 1;
3. Get 15 Most Viewed Pages:
SELECT COUNT(*) cnt, track_page
FROM site_stats
GROUP BY track_page
ORDER BY cnt DESC LIMIT 0, 15;
4. Get 15 Least Viewed Pages:
SELECT COUNT(*) cnt, track_page
FROM site_stats
GROUP BY track_page
ORDER BY cnt ASC LIMIT 0, 15
5. Get the Most Popular Day (day with most unique session id's):
SELECT track_date, COUNT(DISTINCT track_session) uniqueSessionCount
FROM site_stats
GROUP BY track_date
ORDER BY uniqueSessionCount DESC LIMIT 1
I'm using MySQL 4.0.22. Thanks for the input!
-
Jul 25, 2005, 14:27 #2
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
depending on the total number of records, queries 2, 3, and 4 could be rewritten to run faster. but yes, all those queries seem to do what you ask.
-
Jul 25, 2005, 15:56 #3
- Join Date
- Mar 2004
- Location
- Toronto, Canada
- Posts
- 326
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by longneck
Bookmarks