实现筛选含CALMONTH≥202301的COSTCENTER与KONTONUMMER组合的SQL查询

现有视图data的结构与数据 #

需求说明 #

仅保留存在至少一条CALMONTH ≥ 202301记录的COSTCENTER与KONTONUMMER组合的所有数据。

期望输出 #

当前问题 #

现有SQL仅实现了单COSTCENTER维度的筛选，无法满足双维度需求，代码如下：

SELECT "t"."CALMONTH", "t"."COSTCENTER", "t"."KONTONUMMER", "t"."FIELDNM003" 
FROM "data" AS t 
INNER JOIN ( 
    SELECT "COSTCENTER" 
    FROM "data" 
    GROUP BY "COSTCENTER" 
    HAVING MAX("CALMONTH") > 202301 
) AS s ON s.COSTCENTER = t.COSTCENTER

解决方案 #

以下是三种实现双维度筛选的方法：

方法1：修改子查询的分组维度 #

在子查询中同时按COSTCENTER和KONTONUMMER分组，判断该组合的最大月份是否符合条件，再关联原表获取完整数据：

SELECT "t"."CALMONTH", "t"."COSTCENTER", "t"."KONTONUMMER", "t"."FIELDNM003" 
FROM "data" AS t 
INNER JOIN ( 
    SELECT "COSTCENTER", "KONTONUMMER" 
    FROM "data" 
    GROUP BY "COSTCENTER", "KONTONUMMER" 
    HAVING MAX("CALMONTH") >= 202301
) AS s 
    ON s.COSTCENTER = t.COSTCENTER 
    AND s.KONTONUMMER = t.KONTONUMMER

方法2：使用EXISTS子查询 #

通过EXISTS直接判断当前行的COSTCENTER+KONTONUMMER组合是否存在符合条件的记录，逻辑更直观：

SELECT "CALMONTH", "COSTCENTER", "KONTONUMMER", "FIELDNM003"
FROM "data" AS t
WHERE EXISTS (
    SELECT 1
    FROM "data" AS s
    WHERE s.COSTCENTER = t.COSTCENTER
      AND s.KONTONUMMER = t.KONTONUMMER
      AND s.CALMONTH >= 202301
)

方法3：使用窗口函数 #

通过窗口函数计算每个COSTCENTER+KONTONUMMER组合的最大月份，再筛选出符合条件的记录：

WITH filtered_groups AS (
    SELECT 
        *,
        MAX(CALMONTH) OVER (PARTITION BY COSTCENTER, KONTONUMMER) AS max_month
    FROM "data"
)
SELECT CALMONTH, COSTCENTER, KONTONUMMER, FIELDNM003
FROM filtered_groups
WHERE max_month >= 202301

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