mysql - 我如何在mysql中进行模糊分组？

Question

我在 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”一样。我该怎么做呢？

Answer 1

转换组前的值，像这样:

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