当查询太多行时，MySQL 查询返回奇怪的格式错误的结果

Question

Table columns here

表大约有 140 万行。

1 年查询:

SELECT windDir, 
count(windDir)/(
    SELECT COUNT(windDir) 
    FROM archive 
    WHERE windDir is not NULL AND dateTime >= 1466719200) as frequency 
FROM weewx.archive 
WHERE windDir is not NULL AND dateTime >= 1466719200 
GROUP BY windDir 
ORDER BY windDir ASC;

返回正确结果(cba 复制频率结果)

+---------+-----------+
| windDir | frequency |
+---------+-----------+
|   0     |     x     |
|  22.5   |     x     |
|  45     |     x     |
|  67.5   |     x     |
|  90     |     x     |
| 112.5   |     x     |
| 135     |     x     |
| 157.5   |     x     |
| 180     |     x     |
| 202.5   |     x     |
| 225     |     x     |
| 247.5   |     x     |
| 270     |     x     |
| 292.5   |     x     |
| 315     |     x     |
| 337.5   |     x     |
+---------+-----------+

2 年查询:

SELECT windDir, 
count(windDir)/(
    SELECT COUNT(windDir) 
    FROM archive 
    WHERE windDir IS NOT NULL AND dateTime >= 1435096800) as frequency 
FROM weewx.archive 
WHERE windDir IS NOT NULL AND dateTime >= 1435096800
GROUP BY windDir 
ORDER BY windDir ASC;

RETURNS WEIRD WRONG RESULTS

唯一的区别是日期时间，它改变了计算的行数。

这可能是某种内存问题吗？

甚至将查询简化为类似的内容

SELECT DISTINCT windDir
from weewx.archive
WHERE dateTime >= 1435096800 
ORDER BY windDir ASC;

返回格式错误的查询，如上面的查询。

这是怎么回事？

Answer 1

列 winDir 的类型为 double，因此那些“奇怪的格式错误”结果只是存储在表中的 double 类型的值。 (另请注意，它们的范围从 0 到 360，因此它们是“度”。)如果您想要更易于阅读的可预测结果，请调整 winDir 输出的精度，也许可以使用 ROUND( )比如这样:

select
       w.windir 
     , w.numerator / f.denominator AS frequency
from (
      SELECT
            ROUND(windDir,1) AS windDir
          , COUNT(windDir)   AS numerator
      FROM weewx.archive
      WHERE windDir IS NOT NULL
      AND DATETIME >= 1435096800
      GROUP BY
            ROUND(windDir,1)
     ) AS w
CROSS JOIN (
      SELECT
            COUNT(windDir) * 1.0 AS denominator
      FROM archive
      WHERE windDir IS NOT NULL
      AND DATETIME >= 1435096800
      ) AS f
ORDER BY windDir ASC
;

注意，我已将分母的计算插入交叉连接中，我认为这会提高效率。