mysql - 令人困惑的MySQL百分比计算问题

Question

我正在对一些数据进行分组，然后尝试确定每条线占整体的百分比。所以我写了这个:

select Vendor, OS_Version, count(distinct device_uid), (select count(distinct device_uid) from device_information_latest dil2 where dil1.vendor = dil2.vendor limit 1)    from device_information_latest dil1 where vendor in ('Canonical') GROUP BY Vendor, OS_Version   order by vendor, OS_Version;

这给了我:

+-----------+------------+----------------------------+-----------------------------------------------------------------------------------------------------------------+
| Vendor    | OS_Version | count(distinct device_uid) | (select count(distinct device_uid) from device_information_latest dil2 where dil1.vendor = dil2.vendor limit 1) |
+-----------+------------+----------------------------+-----------------------------------------------------------------------------------------------------------------+
| Canonical | 14.04      |                          4 |                                                                                                              23 |
| Canonical | 16.04      |                         19 |                                                                                                              23 |
+-----------+------------+----------------------------+-----------------------------------------------------------------------------------------------------------------+

看起来不错。现在，我尝试将第三列除以第四列(注意，我只是将逗号替换为斜杠)。

select Vendor, OS_Version, count(distinct device_uid) / (select count(distinct device_uid) from device_information_latest dil2 where dil1.vendor = dil2.vendor limit 1)    from device_information_latest dil1 where vendor in ('Canonical') GROUP BY Vendor, OS_Version   order by vendor, OS_Version;

+-----------+------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Vendor    | OS_Version | count(distinct device_uid) / (select count(distinct device_uid) from device_information_latest dil2 where dil1.vendor = dil2.vendor limit 1) |
+-----------+------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Canonical | 14.04      |                                                                                                                                       0.0315 |
| Canonical | 16.04      |                                                                                                                                       0.1496 |
+-----------+------------+----------------------------------------------------------------------------------------------------------------------------------------------+

如果您查看第三列的值，您会发现它们的总和为 1.0 (100%)。但他们没有。

我错过了什么？

Answer 1

您的 SQL 查询看起来不错，但我不知道 device_information_latest 的确切结构。这个 device_uid 字段到底是什么？

我建议看看那些错误的值。假设 count(distinct device_uid) 总是返回正确的值 4 和 19。那么我们可以建立两个等式:

4 / x1 = 0.0315
19 / x2 =  0.1496

经过一些计算，结果是:

x1 = 4 / 0.0315 = 126,984126984127
x2 = 19 / 0.1496 = 127,0053475935829

所以在第一个查询中 (select count(distinct device_uid) from device_information_latest dil2 where dil1.vendor = dil2.vendor limit 1) 被评估为 23 但在第二个查询中它被评估为 127 . 显然，重要的是计算两个不同列中的两个简单表达式，而不是计算一列中的一个复杂表达式。我怀疑 where dil1.vendor = dil2.vendor 以某种方式过滤了不同的行，但这只是一个猜测。我不太熟悉 SQL 操作的顺序。两个查询的逻辑顺序应该相同，但可能会进行一些特定的 MySQL 优化。你能为这两个查询提供 EXPLAIN 的输出吗？

最终，如果这被证明是一些错误，您可以试试这个:

select
  Vendor,
  OS_Version,
  quantity/total as 'percentage'
from
(
  select 
    Vendor, 
    OS_Version, 
    count(distinct device_uid) as quantity, 
    (select count(distinct device_uid) from device_information_latest dil2 where dil1.vendor = dil2.vendor limit 1) as total
  from 
    device_information_latest dil1 
  where 
    vendor in ('Canonical') 
  GROUP BY 
    Vendor, OS_Version   
  order by vendor, OS_Version
) as tmp