mysql - 计数函数返回零 - Mysql

Question

问题来了

我有一张非常大的 table ，如下所示:

Epoch_time              MAC
-------------------------------------
1395275303  |   84:xx:xx:xx:xx:xx 
1395275423  |   bc:xx:xx:xx:xx:xx 
1395275423  |   84:xx:xx:xx:xx:xx 

我想计算每小时唯一的 MAC 地址。所以我这样做了

    SELECT Count(DISTINCT mac_adress), FROM_UNIXTIME(`date_ecoute`, '%d.%m.%Y.%H') as ndate FROM box1 
WHERE date_ecoute > unix_timestamp(CURRENT_TIMESTAMP - INTERVAL 2640 MINUTE) 
AND signal_strength >= -60 group by ndate

效果很好，这是每小时的结果:

Count(DISTINCT mac_adress)  ndate
--------------------------------------
2              |          20.03.2014.03
8              |          20.03.2014.06
11             |          20.03.2014.07

问题是，有些小时我的表中没有行，所以正如您在上面的结果中看到的那样，没有 20.03.2014.04 甚至 05 的行。我想要这样的输出:

  Count(DISTINCT mac_adress)    ndate
    --------------------------------------
    2              |          20.03.2014.03
    0              |          20.03.2014.04
    0              |          20.03.2014.05
    8              |          20.03.2014.06
    11             |          20.03.2014.07

我尝试了IS NULL、IF NULL、LEFT JOIN、COALESCE。我不擅长使用 Sql...

IFNULL(Count(DISTINCT mac_adress),0) // At the beginning, Doesn't work

Answer 1

如果您假设数据中有一些所有小时的数据，那么您可以通过使用条件聚合来获取 0。也就是说，将条件从 where 子句移动到 select 子句中的 case 语句:

SELECT Count(DISTINCT case when signal_strength >= -60 then mac_adress end),
      FROM_UNIXTIME(`date_ecoute`, '%d.%m.%Y.%H') as ndate
FROM box1 
WHERE date_ecoute > unix_timestamp(CURRENT_TIMESTAMP - INTERVAL 2640 MINUTE) 
group by ndate
order by ndate;

如果您没有所有时间戳的数据，那么您将需要使用“驱动程序”表(或类似的表)，其中包含您想要输出的所有小时。

编辑:

如果您有每天的数据，您可以这样做:

SELECT d.ndate, h.hour,
       Count(DISTINCT case when then mac_adress end)
from (select distinct FROM_UNIXTIME(`date_ecoute`, '%d.%m.%Y') as ndate
      from box1
      where date_ecoute > unix_timestamp(CURRENT_TIMESTAMP - INTERVAL 2640 MINUTE) 
     ) d cross join
     (select 0 as hour union all select 1 union all select 2 union all select 3 union all
      . . .
     ) h left outer join
     box1
where date_ecoute > unix_timestamp(CURRENT_TIMESTAMP - INTERVAL 2640 MINUTE)  and
      signal_strength >= -60 
group by ndate, hour
order by min(dte_ecoute), hour;

让我补充一下。 。 。如果可以选择，“YYYY-MM-DD”格式最适合用于字符串表示。特别是，您可以使用它直接订购。