Lastgoogle Lastgeist

Saturday, May 27, 2006
SQL Query: Top Domains
SELECT statement I use to tally top domains for a defined period:

-- Lastgeist: Top Domains for Defined Period (v.2)
-- WHERE clause: change date values to define period
-- LIMIT clause: number in countdown
-- FROM clause: check table name

SELECT
REPLACE(SUBSTRING_INDEX( SUBSTRING_INDEX( result_link, '://', -1 ) , '/', 1 ), 'www.', '') AS result_domain,
count( REPLACE(SUBSTRING_INDEX( SUBSTRING_INDEX( result_link, '://', -1 ) , '/', 1 ), 'www.', '') ) AS result_count
FROM queries
WHERE query <> '' AND result_link REGEXP 'http[s]?://.*'
AND timestamp >= UNIX_TIMESTAMP('2006-01-01')
AND timestamp <= UNIX_TIMESTAMP('2006-01-31')
GROUP BY result_domain
ORDER BY result_count DESC
LIMIT 30


Copy and paste -- it will be more readable. If you know how to add a row number column ($n++), let me know. (AFIAK, can't be done in MySQL.)
» 8:30 AM

comments:

Post a Comment

links to this post:

Create a Link