如何在MySQL中进行模糊分组？

Di Zou 发表于 Dev

Di Zou

我在MySQL中有以下查询：

select 
    hour,
    traffic_source_name,
    COUNT(*)
from 
    my_data_table
group by
    hour,
    traffic_source_name

这给了我这样的结果：

'h', 'traffic_source_name', 'COUNT(*)
'2015-07-28 00:00', 'google', '628'
'2015-07-28 00:00', 'Google Tier 2 Search', '1'
'2015-07-28 00:00', 'Taboola/Outbrain Content', '3'
'2015-07-28 00:00', 'yahoo', '3'
'2015-07-28 00:00', 'Email', '67'
'2015-07-28 00:00', 'msn', '253'
'2015-07-28 00:00', 'Facebook Social Media', '139'
'2015-07-28 01:00', 'yahoo', '2'
'2015-07-28 01:00', 'Email', '46'
'2015-07-28 01:00', 'Popular Marketing', '1'
'2015-07-28 01:00', 'Yahoo Stream Ads Content', '3'
'2015-07-28 01:00', 'Facebook Social Media', '183'
'2015-07-28 01:00', 'google', '530'
'2015-07-28 01:00', 'msn', '210'

事实是，我想将“ google”和“ Google Tier 2 Search”合并为一行，例如“ google”，“ yahoo”和“ Yahoo Stream Ads Content”合并为“ yahoo”。我将如何去做呢？

于尔多

转换组之前的值，如下所示：

select 
    hour,
    IF (traffic_source_name IN ('google', 'Google Tier 2 Search'), 'google'
    , IF (traffic_source_name IN ('yahoo', 'Yahoo Stream Ads Content'), 'yahoo'
    , traffic_source_name
    )) AS tsn,
    COUNT(*)
from 
    my_data_table
group by
    hour,
    tsn

但是，这可能具有更好的可读性：

select 
    hour,
    CASE traffic_source_name 
    WHEN 'Google Tier 2 Search' THEN 'google'
    WHEN 'Yahoo Stream Ads Content' THEN 'yahoo'
    ELSE traffic_source_name
    END AS tsn,
    COUNT(*)
from 
    my_data_table
group by
    hour,
    tsn

第一个让您使分组值更明显，而第二个让您避免维护嵌套的IF（,, IF（,, IF ...）））东西....或者我们可以做到最好两者：

select 
    hour,
    CASE 
    WHEN traffic_source_name IN ('google', 'Google Tier 2 Search') 
      THEN 'google'
    WHEN traffic_source_name IN ('yahoo', 'Yahoo Stream Ads Content')
      THEN 'yahoo'
    ELSE traffic_source_name
    END AS tsn,
    COUNT(*)
from 
    my_data_table
group by
    hour,
    tsn