如何筛选4个float64列中小数位数≥5位的行并保留6位小数？

核心思路 #

放弃字符串分割/匹配的方法，改用数学计算判断经纬度的精度是否达到5位小数要求——判断数值无法被4位小数精确表示（即存在第5位及以后的有效小数），同时用ROUND保留6位小数。这种方法避免了浮点数转字符串时的精度显示问题。

问题代码的核心缺陷 #

之前的字符串处理方法存在两个关键问题：

1. 浮点数转字符串的精度失真：比如0.1234在二进制浮点数存储中可能是0.1234000000000001或0.1233999999999999，转字符串时会被显示为0.1234，导致字符串长度判断错误。
2. 错误的0处理逻辑：直接移除所有0会误判如0.12340这类精度到第5位的数值（实际符合保留条件），同时无法区分0.1234和0.1234000001的精度差异。

正确SQL代码 #

1. 筛选+格式化数据

SELECT
  other_columns,
  ROUND(start_lat, 6) AS start_lat,
  ROUND(start_lng, 6) AS start_lng,
  ROUND(end_lat, 6) AS end_lat,
  ROUND(end_lng, 6) AS end_lng
FROM
  `my_dataset`
WHERE
  -- 判断start_lat精度≥5位小数（无法用4位小数精确表示）
  ABS(start_lat - ROUND(start_lat, 4)) > 1e-9
  -- 同理判断其他三个字段
  AND ABS(start_lng - ROUND(start_lng, 4)) > 1e-9
  AND ABS(end_lat - ROUND(end_lat, 4)) > 1e-9
  AND ABS(end_lng - ROUND(end_lng, 4)) > 1e-9

2. 验证结果的正确性

用以下SQL检查结果集中的小数位数分布，注意这里需要用FORMAT强制保留6位小数后再判断，避免浮点数默认显示的省略问题：

SELECT
  LENGTH(SPLIT(FORMAT('%.6f', start_lat), '.')[OFFSET(1)]) AS decimal_places,
  COUNT(*) AS counts
FROM
  `your_result_dataset`
GROUP BY
  decimal_places
ORDER BY
  decimal_places

逻辑说明 #

• ABS(val - ROUND(val, 4)) > 1e-9：如果数值与它保留4位小数后的差值大于极小值（1e-9），说明该数值存在第5位及以后的有效小数，符合精度要求；反之则说明该数值可以用4位小数精确表示，需要被筛除。
• ROUND(val, 6)：统一将所有符合条件的数值保留6位小数，满足格式化需求。

内容的提问来源于stack exchange，提问作者Steven Gee