MYSQL:按正则表达式模式分组

Question

我正在尝试进行统计跟踪。在我的数据库中，我存储了引用 url。我经常有类似于以下内容的网址:

http://www2.trafficadbar.com/__a4w4
http://trafficadbar.com/__a4w4
http://www.trafficadbar.com/__a4w4
http://4acesmailer.com/credit_click.php?userid=2472&openkey=gbyp2vcm
http://4acesmailer.com/credit_click.php?userid=2714&openkey=gbyp2vcm
http://4acesmailer.com/credit_click.php?userid=2723&openkey=gbyp2vcm
http://4acesmailer.com/credit_click.php?userid=3245&openkey=gbyp2vcm
http://4acesmailer.com/credit_click.php?userid=3259&openkey=gbyp2vcm

我想知道如何对正则表达式模式进行 GROUP BY 和 COUNT。基本上我想要的是返回如下:

trafficadbar 3
4acesmailer 5 

目前，当我尝试执行 GROUP BY 时，它仅在 url 完全相同的情况下有效。所以 www.blah.com 和 blah.com 是两个不同的结果，而且每个 url 变量 ?blah=1&blahblah=2 充当另一个独特的组，

我已经搜索了无数的解决方案，但它们大多似乎都非常针对所问的问题，而且几乎所有的解决方案似乎都显示了一些“非正则表达式”的解决方法——这很好……如果我能找到一种方法我可以申请。

Answer 1

要从主机名中检索紧接在顶级域之前的部分，您可以这样做:

SELECT
  REVERSE(SUBSTRING(SUBSTRING_INDEX(rev_hostname, '.', 2),
          LOCATE('.', rev_hostname) + 1)
         ) domain
  , COUNT(id) hits
FROM (
  SELECT
    id
    , CONCAT(REVERSE(SUBSTRING_INDEX(SUBSTRING(referring_site, 8),
                                     '/', 1)), '.') rev_hostname
  FROM TestData
  ) T
GROUP BY domain
;

它:

• 依赖于所有 referring_site 以 http:// 开头，并且
• 将失败 - 正如它所代表的那样 - 例如，4acesmailer.co.uk。

如果需要，可以(在某种程度上)解决其中任何一个问题。

查看实际效果 SQL Fiddle (对您的数据进行了一些调整/扩展以涵盖更多情况)。

如果这需要调整/更多细节，请发表评论。