Lastgoogle Lastgeist
Saturday, May 27, 2006
SQL Query: Top Domains
SELECT statement I use to tally top domains for a defined period:
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.)
-- 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
-- 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.)
c© 2006 - , some rights reserved