如何编写SQL统计每月首次出现的唯一新ID？

Hey there! Let's work through these two SQL counting tasks together. Both are about tracking new unique IDs that haven't appeared in any prior months, so the core logic ties back to identifying each ID's first occurrence date. Here's how to approach each one:

The key here is to first find the earliest month each ID appears, then count how many unique IDs have their first occurrence in each month.

Assuming your table is named user_events with columns user_id (the ID we're tracking) and event_date (the date the ID was recorded), here's a straightforward SQL solution:

-- First, get the first month each ID appears
WITH first_seen AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(event_date)) AS first_seen_month
    FROM user_events
    WHERE user_id IS NOT NULL AND event_date IS NOT NULL -- Filter out invalid records
    GROUP BY user_id
)
-- Then count IDs per their first seen month
SELECT
    first_seen_month AS report_month,
    COUNT(DISTINCT user_id) AS new_id_count
FROM first_seen
GROUP BY first_seen_month
ORDER BY first_seen_month;

解释： #

• The CTE first_seen groups by each ID and uses MIN(event_date) to get its earliest occurrence, then truncates that date to the first day of the month (so all dates in the same month are grouped together).
• We then count the unique IDs for each first-seen month, which gives us exactly the number of new IDs that hadn't appeared in any prior months.

This is essentially the same core problem as the first one—we just need to adapt to your dataset's structure. Let's say your dataset has a table dataset_records with unique_id and record_date columns. An alternative approach using window functions works great here too:

SELECT
    DATE_TRUNC('month', record_date) AS report_month,
    COUNT(DISTINCT unique_id) AS new_unique_id_count
FROM (
    SELECT
        unique_id,
        record_date,
        -- Get the first date this ID ever appeared across all records
        MIN(record_date) OVER (PARTITION BY unique_id) AS first_record_date
    FROM dataset_records
    WHERE unique_id IS NOT NULL AND record_date IS NOT NULL
) AS id_records
-- Only keep rows where the record is the first occurrence of the ID
WHERE record_date = first_record_date
GROUP BY DATE_TRUNC('month', record_date)
ORDER BY report_month;

解释： #

• The inner query uses a window function MIN(record_date) OVER (PARTITION BY unique_id) to attach the first occurrence date to every row of the same ID.
• We filter to only keep rows where the current record date matches the first occurrence date—this ensures we're only counting each ID once, in the month it first appeared.
• Finally, we group by month and count the unique IDs to get the monthly new ID count.

注意事项： #

• Date truncation syntax varies by database:
  • MySQL: Use DATE_FORMAT(event_date, '%Y-%m-01') instead of DATE_TRUNC
  • Oracle: Use TRUNC(event_date, 'MONTH')
  • SQL Server: Use DATEFROMPARTS(YEAR(event_date), MONTH(event_date), 1)
• Always filter out NULL IDs or dates to avoid skewing your counts.
• If your dataset has duplicate entries for the same ID in the same month, the COUNT(DISTINCT) ensures we still count the ID only once.

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