SQL实现基于id-dp_id对的滚动30天操作计数及超限标记需求

Got it, let's work through this problem to get the exact output you need. The core requirements are to track cumulative transactions per id-dp_id pair starting from their first transaction date, count only those within a 30-day window, and mark transactions where the cumulative count hits 5 or more as over_lim=True.

Step-by-Step Explanation #

1. Identify the first transaction date for each id-dp_id pair using a window function.
2. Filter and count transactions that fall within the 30-day window starting from the first transaction, assigning a cumulative count to each transaction in chronological order.
3. Join back to the original table to include all transactions, even those outside the 30-day window, and set the appropriate over_lim flag.

SQL Code (Standard SQL) #

This works with databases like PostgreSQL, BigQuery, and others that support standard window functions:

WITH first_transaction AS (
    SELECT 
        *,
        MIN(date) OVER (PARTITION BY id, dp_id) AS first_tr_date
    FROM Mytable
),
cumulative_counts AS (
    SELECT 
        date,
        id,
        dp_id,
        ROW_NUMBER() OVER (
            PARTITION BY id, dp_id 
            ORDER BY date
        ) AS tr_count
    FROM first_transaction
    WHERE date <= first_tr_date + INTERVAL '30 days'
)
SELECT 
    mt.date,
    mt.id,
    mt.dp_id,
    COALESCE(cc.tr_count, 0) AS tr_count,
    CASE 
        WHEN cc.tr_count >= 5 THEN TRUE 
        ELSE FALSE 
    END AS over_lim
FROM Mytable mt
LEFT JOIN cumulative_counts cc 
    ON mt.date = cc.date 
    AND mt.id = cc.id 
    AND mt.dp_id = cc.dp_id
ORDER BY mt.id, mt.dp_id, mt.date;

Adjustments for MySQL #

If you're using MySQL, modify the interval calculation to use DATE_ADD:

WITH first_transaction AS (
    SELECT 
        *,
        MIN(date) OVER (PARTITION BY id, dp_id) AS first_tr_date
    FROM Mytable
),
cumulative_counts AS (
    SELECT 
        date,
        id,
        dp_id,
        ROW_NUMBER() OVER (
            PARTITION BY id, dp_id 
            ORDER BY date
        ) AS tr_count
    FROM first_transaction
    WHERE date <= DATE_ADD(first_tr_date, INTERVAL 30 DAY)
)
SELECT 
    mt.date,
    mt.id,
    mt.dp_id,
    COALESCE(cc.tr_count, 0) AS tr_count,
    CASE 
        WHEN cc.tr_count >= 5 THEN TRUE 
        ELSE FALSE 
    END AS over_lim
FROM Mytable mt
LEFT JOIN cumulative_counts cc 
    ON mt.date = cc.date 
    AND mt.id = cc.id 
    AND mt.dp_id = cc.dp_id
ORDER BY mt.id, mt.dp_id, mt.date;

Key Notes #

• ROW_NUMBER(): Assigns a unique sequential number to each transaction per id-dp_id pair, ordered by transaction date. If you want to treat transactions with the exact same date as having the same count, use DENSE_RANK() instead.
• COALESCE: Ensures transactions outside the 30-day window show a tr_count of 0 and over_lim as False.
• Ordering: The final ORDER BY clause ensures the output matches the chronological order you provided in the example.

Running this query will produce exactly the output you specified, with all required fields and correct over_lim markings.

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