需求：为按日期排序的数据表新增ID累计出现次数列

架构老林

2026-5-21

Got it, let's figure out how to add that cumulative count column for each ID. Since your table is already sorted by date and each ID only shows up once per day, this is a common running total problem that's easy to solve across most data tools. Here are the go-to solutions for the most popular platforms:

SQL Solution

If you're working with a relational database (like PostgreSQL, MySQL 8+, SQL Server, etc.), window functions are your best bet. Since each ID has one entry per day and the table's sorted by date, we can partition the data by ID and count/number the rows in date order.

You can use either ROW_NUMBER() or COUNT() over a partition—both will give you the same result here because each ID has unique daily entries:

SELECT
    id,
    record_date,
    -- ROW_NUMBER() assigns a sequential number per ID, ordered by date
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY record_date) AS cumulative_count
    -- Alternatively, COUNT(*) works too:
    -- COUNT(*) OVER (PARTITION BY id ORDER BY record_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_count
FROM your_table_name;

The PARTITION BY id splits the data into groups for each unique ID, and ORDER BY record_date ensures the count follows the date sequence you already have.

Python (Pandas) Solution

For pandas users, this is super straightforward thanks to the cumcount() method. Since your DataFrame is already sorted by date, we just need to group by ID and count the rows in order:

import pandas as pd

# Assuming your DataFrame is named df, with columns 'id' and 'date'
df['cumulative_count'] = df.groupby('id').cumcount() + 1

cumcount() starts counting from 0, so we add 1 to get a 1-based cumulative count (which matches the "first occurrence = 1, second = 2" logic you need). No extra sorting is needed here because your data's already in date order.

Excel Solution

If you're working with Excel, a simple COUNTIF formula will do the trick. Let's say:

Column A contains your IDs
Column B contains the dates (already sorted)
You want the cumulative count in Column C

In cell C2, enter this formula and drag it down the entire column:

=COUNTIF($A$2:A2, A2)

The mixed reference $A$2:A2 locks the start of the range at A2 but lets the end expand as you drag down. This counts how many times the current ID has appeared from the top of the table up to the current row—perfect for your sorted date setup.

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