SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Awesome Addict
    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!

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    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.

  3. #3
    Awesome Addict
    Join Date
    Mar 2004
    Location
    Toronto, Canada
    Posts
    326
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck
    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.
    Thanks longneck! I'm not too hot with SQL so any performance tips you have would be great!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •