Redshift SQL实现：计算每个ID过去60天内的历史记录数

To compute the Number_in_past60 column you need, we can leverage Redshift's window functions with a date-based range window. This approach efficiently counts historical records per ID within the 60-day window before the current record's date, and automatically handles the "first record = 0" requirement.

SQL Query #

SELECT
    ID,
    Date,
    Gender,
    COUNT(*) OVER (
        PARTITION BY ID
        ORDER BY Date
        RANGE BETWEEN INTERVAL '60 days' PRECEDING AND CURRENT ROW
    ) - 1 AS Number_in_past60
FROM your_table;

How It Works #

Let's break down the logic step by step:

• PARTITION BY ID: Groups the data by each unique ID, so we only count records belonging to the same user.
• ORDER BY Date: Ensures we process records in chronological order for each ID.
• RANGE BETWEEN INTERVAL '60 days' PRECEDING AND CURRENT ROW: Defines the window of records to include: all rows where the Date is within 60 days before the current record's date, up to and including the current record.
• COUNT(*) - 1: The count includes the current record itself, so subtracting 1 gives us the number of historical records (excluding the current one) in the 60-day window. For the first record of an ID, the count will be 1 (only the current row), so subtracting 1 gives 0 as required.

Verify Against Your Expected Output #

Let's cross-check with your sample data:

• For ID A on 2019-01-01: Only this row is in the window → 1 - 1 = 0 ✔️
• For ID A on 2019-02-01: Includes 2019-01-01 and 2019-02-01 → 2 - 1 = 1 ✔️
• For ID A on 2019-09-01: No prior records within 60 days → 1 - 1 = 0 ✔️
• For ID B on 2019-03-01: Includes 2019-02-01 and 2019-03-01 → 2 - 1 = 1 ✔️
• For ID C on 2019-03-01: Includes 2019-02-01 and 2019-03-01 → 2 - 1 = 1 ✔️

Adding the Column to Your Table #

If you want to permanently add this column to your existing table, Redshift recommends creating a new table (since updates on large tables are inefficient):

CREATE TABLE your_table_with_new_column AS
SELECT
    ID,
    Date,
    Gender,
    COUNT(*) OVER (
        PARTITION BY ID
        ORDER BY Date
        RANGE BETWEEN INTERVAL '60 days' PRECEDING AND CURRENT ROW
    ) - 1 AS Number_in_past60
FROM your_table;

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