SELECT
    REGEXP_REPLACE(Referer, '^https?://(?:www\\.)?([^/]+)/.*$', '\\1') AS k,
    AVG(length(Referer)) AS l,
    COUNT(*) AS c,
    MIN(Referer)
FROM hits
WHERE Referer != ''
GROUP BY k
HAVING COUNT(*) > 100000
ORDER BY l DESC
LIMIT 25
FORMAT `Null`
SETTINGS optimize_rewrite_regexp_functions = 0

0 rows in set. Elapsed: 2.863 sec. Processed 99.93 million rows, 7.56 GB (34.91 million rows/s., 2.64 GB/s.)
Peak memory usage: 3.21 GiB.


SELECT
    REGEXP_REPLACE(Referer, '^https?://(?:www\\.)?([^/]+)/.*$', '\\1') AS k,
    AVG(length(Referer)) AS l,
    COUNT(*) AS c,
    MIN(Referer)
FROM hits
WHERE Referer != ''
GROUP BY k
HAVING COUNT(*) > 100000
ORDER BY l DESC
LIMIT 25
FORMAT `Null`
SETTINGS optimize_rewrite_regexp_functions = 1

0 rows in set. Elapsed: 1.564 sec. Processed 99.93 million rows, 7.56 GB (63.89 million rows/s., 4.83 GB/s.)
Peak memory usage: 1.53 GiB.