SQL分组计算累计总和（Running total）：按双日期列统计去重交易ID累计数

你现有的SQL仅实现了按InvoiceDate、ChargeDate两个维度分组的当期去重交易数统计，where InvoiceDate<=InvoiceDate为恒真条件没有实际过滤作用，自然无法得到累计结果。
从你给出的样例输出可以明确累计规则：按发票日期分组，组内按收费日期升序排序，计算截至当前收费日期的去重交易ID累计值。

支持窗口函数DISTINCT的数据库（PostgreSQL、Spark SQL、Hive 2.1+等） #

可直接在窗口函数中做去重累计：

SELECT DISTINCT
    InvoiceDate,
    ChargeDate,
    COUNT(DISTINCT TransactionID) OVER (
        PARTITION BY InvoiceDate 
        -- 注意把字符串日期转为可排序的日期类型，不同数据库的转换函数/格式化字符串可自行调整
        ORDER BY TO_DATE(ChargeDate, 'MON/DD/YYYY')
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulativeCount
FROM mytable1
ORDER BY TO_DATE(InvoiceDate, 'MON/DD/YYYY'), TO_DATE(ChargeDate, 'MON/DD/YYYY')

不支持窗口函数DISTINCT的数据库（旧版MySQL、部分国产数据库等） #

先算每个分组的当期计数，再做累计求和：

WITH group_count AS (
    -- 先统计每个(InvoiceDate, ChargeDate)组合的当期去重交易数
    SELECT 
        InvoiceDate,
        ChargeDate,
        TO_DATE(ChargeDate, 'MON/DD/YYYY') AS charge_dt,
        TO_DATE(InvoiceDate, 'MON/DD/YYYY') AS invoice_dt,
        COUNT(DISTINCT TransactionID) AS current_cnt
    FROM mytable1
    GROUP BY InvoiceDate, ChargeDate
)
SELECT 
    InvoiceDate,
    ChargeDate,
    SUM(current_cnt) OVER (
        PARTITION BY InvoiceDate 
        ORDER BY charge_dt
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulativeCount
FROM group_count
ORDER BY invoice_dt, charge_dt

不同数据库的日期字符串转换函数存在差异：

• MySQL可替换为STR_TO_DATE(ChargeDate, '%b/%d/%Y')
• SQL Server可替换为CONVERT(DATE, ChargeDate, 107)
  核心是要把字符串格式的日期转为可正确按时间顺序排序的日期类型，避免字符串排序逻辑异常。

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