触发该错误是因为在计算GROUP BY子句时尚未生成字段“ci_ku”。此外，您的查询中还存在其他一些问题：

• GROUP BY子句中并非所有未聚合的行都存在(应包含“ku”和“last_REFRESH_DATE”)
• DISTINCT关键字将删除重复行，尽管SELECT语句后没有重复行。

与使用聚合不同，ROW_NUMBER窗口函数可能会更快地获得结果。它将为您的每个“ci_ku”值(PARTITION BY ci_ku)生成一个递增的数字，并按“l1M_ACCESSIONS”(ORDER BY ci_ku)排序，这样等于1的行号将表示每个“ci_ku”的最小“l1M_ACCESSIONS”。

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

如果您使用的是PostgreSQL，您还可以使用FETCH n ROWS WITH TIES子句来检索每个绑定行号的第一行(它将选择每个行号=1)：

WITH tab_with_ci_ku AS (
    SELECT REGEXP_REPLACE(ku, CONCAT('-',CAST(v_nbr AS varchar)), '') AS ci_ku,
           ku,
           l1m_visits,
           last_refresh_date
    FROM db.schema.table 
)
SELECT ku,
       ci_ku,
       l1m_visits
FROM ranked_visits
ORDER BY ROW_NUMBER() OVER(PARTITION BY ci_ku ORDER BY l1m_visits)
FETCH FIRST 1 ROWS WITH TIES;