如何在Databricks SQL中基于历史数据统计月度重复文本数？

我有一张包含历史数据的表，含两个字符串类型的列，示例如下：

示例数据表 #

基于该表，我希望统计每个月中，与过往月份出现过的text字符串重复的数量。上述示例的期望输出为：

期望输出表 #

请问如何用SQL实现？我原本考虑将表自连接，关联条件为text相等且t1.year_month > t2.year_month，但有没有更高效的实现方式？说明：我使用的是Databricks SQL。

更新：以下是我提到的自连接方案，该查询在同一月份无重复数据的前提下有效，若要放宽该限制可添加行ID。

SELECT year_month, SUM(is_duplicate) as duplicate_count FROM

  -- Inner query that does self join
  -- It tags a row in the input table as duplicate or not
  (SELECT t1.year_month, t1.text, MAX(CASE WHEN t2.year_month IS NULL THEN 0 ELSE 1 END) AS is_duplicate
  FROM input_table t1
  LEFT JOIN input_table t2 ON t1.text = t2.text AND 
  t1.year_month > t2.year_month
  group by t1.year_month, t1.text) as T

GROUP BY year_month

更高效的实现方案（Databricks SQL） #

相比自连接，利用窗口函数的方式可以避免表与表之间的关联操作，在数据量较大时性能更优：

WITH text_first_occurrence AS (
    SELECT
        text,
        year_month,
        -- 计算每个text首次出现的月份
        MIN(year_month) OVER (PARTITION BY text) AS first_occurrence_month
    FROM input_table
)
SELECT
    year_month,
    -- 统计当月中出现时间晚于首次出现时间的text数量
    SUM(CASE WHEN year_month > first_occurrence_month THEN 1 ELSE 0 END) AS duplicate_count
FROM text_first_occurrence
GROUP BY year_month
-- 可按月份排序，按需调整
ORDER BY year_month DESC;

方案说明：

• 首先通过MIN() OVER (PARTITION BY text)窗口函数，为每个text标记出它第一次出现的月份。
• 然后判断当前行的year_month是否晚于该text的首次出现月份，若是则说明该text在过往月份已出现过，属于重复记录。
• 最后按year_month分组求和，得到每个月的重复数量。
• 该方案天然兼容同一月份内的重复text（同一月份的重复text不会被统计，因为首次出现月份就是当前月），符合需求逻辑。

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