Snowflake中如何筛选精度更高的NUMBER类型经纬度记录？

要实现你的需求，核心是计算经纬度实际存储的小数位数（而非列定义的固定长度），再结合窗口函数筛选符合条件的记录。以下是具体实现步骤：

1. 计算实际小数位数 #

由于NUMBER类型存储的是精确值，我们可以通过TO_CHAR配合格式掩码FM（去除无意义的尾部零和空格）将数值转为字符串，再提取小数部分计算长度：

• 纬度小数位数计算：

  CASE 
    WHEN INSTR(TO_CHAR(LATITUDE, 'FM999999999.9999999'), '.') > 0 
    THEN LENGTH(SUBSTR(TO_CHAR(LATITUDE, 'FM999999999.9999999'), INSTR(TO_CHAR(LATITUDE, 'FM999999999.9999999'), '.') + 1))
    ELSE 0 
  END AS lat_decimal_len
  

• 经度小数位数计算：

  CASE 
    WHEN INSTR(TO_CHAR(LONGITUDE, 'FM9999999999.9999999'), '.') > 0 
    THEN LENGTH(SUBSTR(TO_CHAR(LONGITUDE, 'FM9999999999.9999999'), INSTR(TO_CHAR(LONGITUDE, 'FM9999999999.9999999'), '.') + 1))
    ELSE 0 
  END AS lon_decimal_len
  

  格式掩码FM999999999.9999999对应纬度NUMBER(9,7)的结构（2位整数+7位小数），经度的FM9999999999.9999999对应NUMBER(10,7)（3位整数+7位小数），确保转换时不会截断有效数字。

2. 筛选目标记录 #

使用窗口函数ROW_NUMBER()按ST_GEOHASH分组，先按经纬度小数位数总和降序排序（优先保留精度更高的记录），再按纬度值降序排序（精度相同时选纬度更大的），最后取每组的第一条记录：

WITH coord_precision AS (
  SELECT 
    ST_GEOHASH,
    LATITUDE,
    LONGITUDE,
    -- 计算纬度小数位数
    CASE 
      WHEN INSTR(TO_CHAR(LATITUDE, 'FM999999999.9999999'), '.') > 0 
      THEN LENGTH(SUBSTR(TO_CHAR(LATITUDE, 'FM999999999.9999999'), INSTR(TO_CHAR(LATITUDE, 'FM999999999.9999999'), '.') + 1))
      ELSE 0 
    END AS lat_decimal_len,
    -- 计算经度小数位数
    CASE 
      WHEN INSTR(TO_CHAR(LONGITUDE, 'FM9999999999.9999999'), '.') > 0 
      THEN LENGTH(SUBSTR(TO_CHAR(LONGITUDE, 'FM9999999999.9999999'), INSTR(TO_CHAR(LONGITUDE, 'FM9999999999.9999999'), '.') + 1))
      ELSE 0 
    END AS lon_decimal_len
  FROM your_table_name
)
SELECT 
  ST_GEOHASH,
  LATITUDE,
  LONGITUDE
FROM (
  SELECT 
    *,
    ROW_NUMBER() OVER (
      PARTITION BY ST_GEOHASH 
      ORDER BY (lat_decimal_len + lon_decimal_len) DESC, LATITUDE DESC
    ) AS rn
  FROM coord_precision
)
WHERE rn = 1;

效果验证 #

针对你提供的测试数据：

• 第一组d3f71dbq87gk：两条记录纬度小数位数都是7，经度分别是6和7，总和13 vs 14，会选中经度为-75.5196609的记录；
• 第二组dpsb7fr5gqs4：两条记录经纬度小数位数总和都是14，会选中纬度更大的42.2444840的记录。

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