Oracle 19c分析SQL：如何将当前行与组内其余行比较

核心结论 #

Oracle 19c（包括SQL标准）不支持在分析函数的聚合逻辑中直接引用当前行值，与窗口（partition by分组）内的所有行做逐行比较——也就是你期望的sum(case when current_row.dt = row.dt then 1 else 0 end) over (partition by x)这类写法，原生分析函数无法实现。

可行解决方案 #

1. 使用LATERAL关联子查询（推荐）

Oracle 12c及以上支持LATERAL子查询，它可以直接引用主查询中的列值，实现对每一行的组内（或全表）比较，语法比传统自连接更简洁，且Oracle优化器通常能生成高效的执行计划，避免多次连接的性能问题。

针对你的示例需求（统计全表中满足t2.num1 <= t1.num1 AND t2.num2 >= t1.num1的行数），原写法已经是合理的：

SELECT t1.name
     , t1.num1
     , t1.num2
     , cnt
  FROM t t1
       CROSS JOIN LATERAL (
                              SELECT COUNT(1) AS cnt
                                FROM t t2
                               WHERE t2.num1 <= t1.num1
                                     AND t2.num2 >= t1.num1
                          );

如果需要按指定分组（比如模拟partition by num1的组内比较），只需在子查询中添加分组过滤条件：

SELECT t1.name
     , t1.num1
     , t1.num2
     , cnt
  FROM t t1
       CROSS JOIN LATERAL (
                              SELECT COUNT(1) AS cnt
                                FROM t t2
                               WHERE t2.num1 = t1.num1 -- 限定为同一num1分组
                                     AND t2.num2 >= t1.num1
                          );

如果有多个统计需求，可以在同一个LATERAL子查询中计算多个指标，避免多次关联：

SELECT t1.name
     , t1.num1
     , t1.num2
     , cnt_match
     , cnt_greater_num2
  FROM t t1
       CROSS JOIN LATERAL (
                              SELECT COUNT(1) FILTER(WHERE t2.num1 <= t1.num1 AND t2.num2 >= t1.num1) AS cnt_match
                                    , COUNT(1) FILTER(WHERE t2.num2 > t1.num2) AS cnt_greater_num2
                                FROM t t2
                               WHERE t2.num1 = t1.num1
                          );

2. 自定义聚合函数（复用复杂逻辑）

如果你的比较逻辑非常复杂且需要多次复用，可以通过PL/SQL自定义聚合函数。以下是一个适配你示例需求的自定义聚合函数框架：

-- 定义聚合类型
CREATE OR REPLACE TYPE cnt_match_type AS OBJECT (
  target_num1 NUMBER,
  count_val NUMBER,
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT cnt_match_type) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT cnt_match_type, value IN NUMBER, value2 IN NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate(self IN cnt_match_type, returnValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT cnt_match_type, ctx2 IN cnt_match_type) RETURN NUMBER
);
/

-- 实现类型体
CREATE OR REPLACE TYPE BODY cnt_match_type IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT cnt_match_type) RETURN NUMBER IS
  BEGIN
    sctx := cnt_match_type(NULL, 0);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT cnt_match_type, value IN NUMBER, value2 IN NUMBER) RETURN NUMBER IS
  BEGIN
    IF self.target_num1 IS NOT NULL THEN
      IF value <= self.target_num1 AND value2 >= self.target_num1 THEN
        self.count_val := self.count_val + 1;
      END IF;
    END IF;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self IN cnt_match_type, returnValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER IS
  BEGIN
    returnValue := self.count_val;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT cnt_match_type, ctx2 IN cnt_match_type) RETURN NUMBER IS
  BEGIN
    self.count_val := self.count_val + ctx2.count_val;
    RETURN ODCIConst.Success;
  END;
END;
/

-- 定义聚合函数
CREATE OR REPLACE FUNCTION count_matches(p_target_num1 NUMBER, p_num1 NUMBER, p_num2 NUMBER) RETURN NUMBER
PARALLEL_ENABLE AGGREGATE USING cnt_match_type;
/

使用时仍需结合LATERAL子查询（因为要传递当前行的num1作为目标值）：

SELECT t1.name
     , t1.num1
     , t1.num2
     , count_matches(t1.num1, t2.num1, t2.num2) AS cnt
  FROM t t1
       CROSS JOIN LATERAL (
                              SELECT t2.num1, t2.num2
                                FROM t t2
                               WHERE t2.num1 = t1.num1
                          ) t2
 GROUP BY t1.name, t1.num1, t1.num2;

注意：自定义聚合函数的实现成本较高，仅当逻辑复杂且复用率高时推荐使用。

补充说明 #

传统自连接也能实现相同逻辑，但LATERAL子查询的写法更直观，尤其是在多指标统计场景下，能减少重复代码和连接次数。Oracle优化器对LATERAL的支持已经很成熟，性能上与自连接相当甚至更优。

内容的提问来源于stack exchange，提问作者Jörg