Spark SQL：无需窗口函数实现每行累计去重周数统计方案咨询

Since window functions don’t support DISTINCT in most SQL dialects, here are two straightforward approaches to calculate your lifetime_weeks column:

Approach 1: Correlated Subquery #

This method uses a subquery to count distinct weeks for each row by looking at all prior rows (ordered by days) for the same id.

SELECT
    t1.id,
    t1.some_date,
    t1.days,
    t1.weeks,
    (SELECT COUNT(DISTINCT t2.weeks)
     FROM your_table t2
     WHERE t2.id = t1.id AND t2.days <= t1.days) AS lifetime_weeks
FROM your_table t1
ORDER BY t1.id, t1.days;

How it works: #

For every row in t1, the subquery checks all rows in t2 with the same id and days less than or equal to the current row's days. It counts the unique weeks values in that subset, which gives the cumulative distinct week count up to that row.

Approach 2: Pre-Aggregate First Occurrences (More Efficient for Large Data) #

If you’re working with a large dataset, this approach reduces redundant calculations by first finding the earliest days value for each unique id and weeks pair, then counting how many of these first occurrences fall on or before each row's days.

WITH week_first_occurrence AS (
    SELECT
        id,
        weeks,
        MIN(days) AS first_day
    FROM your_table
    GROUP BY id, weeks
)
SELECT
    t.id,
    t.some_date,
    t.days,
    t.weeks,
    (SELECT COUNT(*)
     FROM week_first_occurrence w
     WHERE w.id = t.id AND w.first_day <= t.days) AS lifetime_weeks
FROM your_table t
ORDER BY t.id, t.days;

How it works: #

1. The CTE week_first_occurrence groups rows by id and weeks to get the first time each week appears for an id.
2. For each row in the original table, we count how many of these first occurrence days are <= the current row's days—this is equivalent to counting the unique weeks accumulated up to that point.

Expected Output #

Both queries will produce the desired result:

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