Teradata/Oracle无存储过程：查询账户每日前的唯一名称数

核心思路 #

要计算每个日期之前（不含当前日期）对应账户已出现的唯一NAME数量，关键是先锁定每个账户下每个NAME的首次出现日期，再针对每个日期统计该账户中首次出现日期早于当前日期的NAME总数。

示例数据 #

假设表名为account_updates，样例数据如下：

预期输出：

SQL 查询语句 #

方案一：CTE关联法（性能更优） #

WITH first_occurrences AS (
    SELECT 
        ACCOUNT,
        NAME,
        MIN(Date) AS first_date
    FROM account_updates
    GROUP BY ACCOUNT, NAME
)
SELECT 
    au.Date,
    au.ACCOUNT,
    CASE 
        WHEN MIN(au.Date) OVER (PARTITION BY au.ACCOUNT) = au.Date THEN NULL
        ELSE COUNT(DISTINCT fo.NAME)
    END AS unique_names_before
FROM account_updates au
LEFT JOIN first_occurrences fo
    ON au.ACCOUNT = fo.ACCOUNT
    AND fo.first_date < au.Date
GROUP BY au.Date, au.ACCOUNT
ORDER BY au.ACCOUNT, au.Date;

方案二：子查询法（逻辑更直观） #

SELECT
    Date,
    ACCOUNT,
    (SELECT COUNT(DISTINCT NAME)
     FROM account_updates au2
     WHERE au2.ACCOUNT = au1.ACCOUNT
       AND au2.Date < au1.Date) AS unique_names_before
FROM account_updates au1
ORDER BY ACCOUNT, Date;

语句说明 #

1. CTE方案：
   • 先通过first_occurrences分组计算每个账户下每个NAME的首次出现日期，避免重复统计同一NAME的多次出现。
   • 关联原表后，用CASE WHEN判断当前日期是否为该账户的最早日期，是则返回NULL，否则统计符合条件的唯一NAME数。
2. 子查询方案：
   • 逐行查询当前账户中所有早于当前日期的记录，统计其中的唯一NAME数量，逻辑直接但数据量大时性能可能稍差。

为什么DENSE_RANK不适用？ #

DENSE_RANK() OVER (PARTITION BY ACCOUNT ORDER BY NAME) 只能得到该账户下NAME的全局排名，无法限定“当前日期之前”的范围，因此无法满足需求。

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