Presto：regexp_replace生成ci_ku后GROUP BY报错，需取最小l1m_visits

错误原因 #

SQL执行顺序中，GROUP BY的执行早于SELECT，因此GROUP BY子句无法识别SELECT里定义的别名ci_ku。同时，ku和last_refresh_date既不在GROUP BY分组字段中，也未用聚合函数包裹，不符合分组查询的语法规则。

正确查询方案 #

要获取每个ci_ku对应最小l1m_visits的完整记录，推荐使用窗口函数ROW_NUMBER()实现：

WITH ranked_data AS (
    SELECT 
        ku,
        REGEXP_REPLACE(ku, CONCAT('-',CAST(v_nbr AS varchar)), '') AS ci_ku,
        l1m_visits,
        last_refresh_date,
        ROW_NUMBER() OVER (PARTITION BY REGEXP_REPLACE(ku, CONCAT('-',CAST(v_nbr AS varchar)), '') ORDER BY l1m_visits ASC) AS rn
    FROM db.schema.table
)
SELECT ku, ci_ku, l1m_visits, last_refresh_date
FROM ranked_data
WHERE rn = 1;

方案说明 #

1. 通过CTE生成带排名的数据集：
   • 先计算自定义字段ci_ku
   • 用PARTITION BY ci_ku按自定义字段分组，ORDER BY l1m_visits ASC让每组内最小的l1m_visits排在第1位，再用ROW_NUMBER()标记每条记录的组内排名
2. 最后筛选出排名为1的记录，即为每个ci_ku对应最小l1m_visits的完整行

若你的SQL引擎支持在窗口函数中直接引用别名（如PostgreSQL），可简化CTE内的PARTITION BY语句：

WITH ranked_data AS (
    SELECT 
        ku,
        REGEXP_REPLACE(ku, CONCAT('-',CAST(v_nbr AS varchar)), '') AS ci_ku,
        l1m_visits,
        last_refresh_date,
        ROW_NUMBER() OVER (PARTITION BY ci_ku ORDER BY l1m_visits ASC) AS rn
    FROM db.schema.table
)
SELECT ku, ci_ku, l1m_visits, last_refresh_date
FROM ranked_data
WHERE rn = 1;

补充说明 #

若仅需ci_ku和对应的最小l1m_visits，可修正原查询的GROUP BY问题，将别名替换为原始表达式：

SELECT 
    REGEXP_REPLACE(ku, CONCAT('-',CAST(v_nbr AS varchar)), '') AS ci_ku,
    MIN(l1m_visits) AS min_l1m_visits
FROM db.schema.table 
GROUP BY REGEXP_REPLACE(ku, CONCAT('-',CAST(v_nbr AS varchar)), '');

但该方式无法直接关联到对应的ku和last_refresh_date，因此更推荐窗口函数方案。

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