问题的答案

问题历史。 运行唯一计数的必要性源于分析工作负载，跟踪诸如累积唯一客户获取或随时间推移的独特 SKU 引入等指标。在 ANSI SQL:2003 窗口函数扩展之前，分析师依赖自连接或相关子查询，这导致了对于现代数据量不可接受的二次时间复杂度。窗口函数的标准化提供了一种线性时间、基于集的机制，以在没有过程循环的情况下维持运行基数。

问题描述。 ANSI SQL 明确禁止在窗口聚合函数中使用 DISTINCT 关键字（例如，COUNT(DISTINCT col) OVER (...)）。这一限制阻止了在累积或滑动窗口内直接计算唯一值的能力。核心挑战在于识别每个实体在分区的排序顺序中的首次出现，并逐步汇总这些二进制标志（首次出现 = 1，否则 = 0）。

解决方案。 规范方法结合了 ROW_NUMBER() 来标记首次出现和条件 SUM() 窗口函数。通过按实体标识符对 ROW_NUMBER() 进行分区，按时间顺序的首次出现获得值 1；后续出现获得递增的整数。然后，外部查询对一个仅在行号等于 1 时返回 1 的 case 表达式进行求和，该表达式在一个无限的前面框架上进行评估。

SELECT 
  event_date,
  region_id,
  user_id,
  SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) 
    OVER (PARTITION BY region_id 
          ORDER BY event_date 
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
    AS cumulative_unique_users
FROM (
  SELECT 
    event_date,
    region_id,
    user_id,
    ROW_NUMBER() OVER (
      PARTITION BY region_id, user_id 
      ORDER BY event_date, event_id  -- event_id 作为平局者
    ) AS rn
  FROM user_activity
) flagged;

生活中的情境

问题描述。 一家金融科技初创公司需要通过跟踪每个销售区域在财政年度内累计登记的唯一商家来监控法规合规性。他们的 merchant_signups 表包含 1.2 亿行，字段包括 region_code、merchant_id 和 signup_timestamp。现有的 Python 批处理作业每晚需要 35 分钟来计算这些指标，导致报告延迟和陈旧的仪表板数据。要求是在严格的 ANSI SQL 下生成实时的累计计数，以便于跨云数据仓库的可移植性。

解决方案 A：自连接方法。 此方法将表与自身连接，在匹配的区域和较早的时间戳上，计算每个外部行的唯一商家。优点：它不需要窗口函数的支持，并能在传统的 SQL-92 引擎上运行。缺点：该算法显示 O(n²) 复杂性；对于数百万行，这会生成临时存储消耗千兆字节的中间笛卡尔积，无法在数小时内完成，导致操作不可行。

解决方案 B：相关标量子查询。 在这里，SELECT 子句嵌入了子查询：(SELECT COUNT(DISTINCT merchant_id) FROM merchant_signups m2 WHERE m2.region_code = m1.region_code AND m2.signup_timestamp <= m1.signup_timestamp)。优点：它是声明式的且逻辑上透明。缺点：子查询每行执行一次（1.2 亿次），防止谓词下推并导致大规模随机 I/O；数据库优化器无法在不同时间范围内去相关唯一聚合，导致估计执行时间超过 90 分钟。

解决方案 C：ANSI SQL 窗口函数技术。 利用 ROW_NUMBER() 来识别首次出现，然后运行 SUM()，如上面的代码示例所示。优点：这执行单表扫描和排序，利用优化器的窗口缓存能力，具有 O(n log n) 复杂性和有限的内存使用。缺点：它需要仔细处理时间上的平局；如果两个登记共享相同的时间戳，非确定性的排序可能会导致重复计数，除非将唯一的平局者（如 event_id）附加到 ORDER BY 子句。

选择的解决方案和结果。 实施了解决方案 C。通过在 ORDER BY 中包含 event_id 以确保确定的首次出现检测，查询在现有集群上执行了 4 分钟—比之前快了 9 倍。结果支持实时合规性仪表板，使风险官员能够监控登记多样性而不受 ETL 延迟的影响，并且该查询可以完全移植到 PostgreSQL、Snowflake 和 BigQuery，无需修改。

候选人常常忽略的内容

为什么 COUNT(DISTINCT column) OVER (ORDER BY ...) 在严格的 ANSI SQL 中引发语法错误？

SQL 标准明确禁止在窗口聚合函数（如 COUNT、SUM 或 AVG）的参数中使用 DISTINCT 关键字。尽管某些特定供应商（如 PostgreSQL 16+、Oracle）将其作为专有扩展提供，但 ANSI SQL:2011 及以前版本限制窗口聚合只能在定义的框架内操作所有行。这一限制的存在是因为标准语法并不要求在流式评估期间为每个可能的窗口框架维护一个唯一集哈希表。候选人必须认识到，DISTINCT 仅在缺少 OVER 子句的标准聚合函数中允许，或在如 PERCENTILE_CONT 这样的反向分布函数中允许，但在窗口的唯一计数中从不允许。

当确定实体的 "首次" 出现时，您如何处理重复的时间戳？

ROW_NUMBER() 在平局之间分配任意值，除非 ORDER BY 子句指定了完全排序。如果一个商家有两个具有相同时间戳的条目，则如果顺序是非确定性的，则两行都有可能收到 rn = 1，导致累计计数错误地递增两次。解决方法是将唯一主键或自增 ID 附加到 ORDER BY 子句中：ORDER BY signup_timestamp, merchant_signup_id。这确保了确定的序列，在其中较早分配的 ID 被视为首次出现，从而保持运行唯一计数的数学完整性。

可以将此技术调整为固定行数窗口下的移动唯一计数（例如，最近 100 次交易），而不是无限先行吗？

不，使用纯 ANSI SQL 不有效。无限先行的方法成功是因为唯一性是单调的——一旦实体出现，它将永远保持 "计数"。在滑动窗口（例如，ROWS BETWEEN 100 PRECEDING AND CURRENT ROW）中，离开窗口的实体必须减少计数，这需要知道离开的行是否代表该实体在当前框架内的唯一实例。ANSI SQL 在窗口框架内缺乏数组聚合或集合差异运算符，以有效跟踪这样的退出。实现这一点需要递归 CTE（在此情况下退化为 O(n²)）或像 ARRAY_AGG 结合集合操作这样的专有扩展，这两者都违反了严格的 ANSI 合规性。

详细说明如何计算有序分区中的累积唯一计数的技术，其中唯一实体的运行总计必须在每组内首次出现时递增，使用严格 ANSI SQL 窗口函数而没有相关子查询？

问题的答案

生活中的情境

候选人常常忽略的内容