SAP HANA中使用聚合函数时如何保留对应日期值

原始需求与查询 #

你通过CTE实现了过去30天的滚动出现次数统计，当前查询可按TYPE分组获取count_30_days的最大值，现在需要同时拿到该最大值对应的DATE：

WITH cte AS 
(
    SELECT
        t.ID, t.TYPE, t.DATE, COUNT(*) AS count_30_days
    FROM 
        ASQ_AFTERSALES.TYPE AS t
    INNER JOIN 
        ASQ_AFTERSALES.TYPE i ON t.DATE >= ADD_DAYS (i.DATE, -30)
                              AND t.DATE <= i.DATE
                              AND t.TYPE = i.TYPE
    GROUP BY 
        t.ID, t.TYPE, t.DATE
)
SELECT 
    TYPE, MAX(count_30_days) AS max_incidence
FROM 
    cte
GROUP BY 
    TYPE

尝试的错误写法及问题 #

你尝试直接在GROUP BY中加入DATE，但这样会按TYPE+DATE分组返回多条数据，无法实现“每个TYPE对应最大值及日期”的需求，且触发报错：

Single-row subquery returns more than one row.

另外，你尝试的KEEP语法属于Oracle特性，HANA不支持，因此出现语法错误。

解决方案：使用窗口函数筛选目标行 #

在HANA中，可通过窗口函数RANK()或ROW_NUMBER()对每个TYPE下的count_30_days排序，筛选出排名第一的行，即可同时获取最大值及对应日期：

WITH cte AS 
(
    SELECT
        t.ID, t.TYPE, t.DATE, COUNT(*) AS count_30_days
    FROM 
        ASQ_AFTERSALES.TYPE AS t
    INNER JOIN 
        ASQ_AFTERSALES.TYPE i ON t.DATE >= ADD_DAYS (i.DATE, -30)
                              AND t.DATE <= i.DATE
                              AND t.TYPE = i.TYPE
    GROUP BY 
        t.ID, t.TYPE, t.DATE
),
ranked_cte AS (
    SELECT
        TYPE,
        DATE,
        count_30_days AS max_incidence,
        -- 按TYPE分组，count_30_days降序排序，相同最大值保留所有行
        RANK() OVER (PARTITION BY TYPE ORDER BY count_30_days DESC) AS rnk
    FROM cte
)
SELECT TYPE, max_incidence, DATE
FROM ranked_cte
WHERE rnk = 1;

如果同一TYPE下存在多个日期对应相同最大值，且你只需要其中最新的日期，可将RANK()替换为ROW_NUMBER()，并调整排序规则：

ROW_NUMBER() OVER (PARTITION BY TYPE ORDER BY count_30_days DESC, DATE DESC) AS rnk

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