如何计算常规周及滚动周群组（cohort）的去重ID数量？

Hey there! Let's break down your two cohort analysis questions and fix that SQL query you've been working on.

1. Calculating Unique IDs for Fixed Weekly Cohorts #

If your "weekly cohort" refers to fixed natural weeks (e.g., Monday to Sunday, or Sunday to Saturday), we can group records by truncated weekly dates, then count distinct IDs while grouping by country and language:

For PostgreSQL/BigQuery (supports DATE_TRUNC) #

SELECT
  DATE_TRUNC('week', TO_DATE(date, 'DD-MM-YYYY')) AS cohort_week,
  country,
  language,
  COUNT(DISTINCT id) AS count_ids
FROM raw_data
GROUP BY cohort_week, country, language
ORDER BY cohort_week, country, language;

For MySQL (uses WEEK() function) #

SELECT
  -- Format to get the first day of the week (adjust %w if your week starts on Sunday)
  STR_TO_DATE(CONCAT(YEAR(STR_TO_DATE(date, 'DD-MM-YYYY')), '-', WEEK(STR_TO_DATE(date, 'DD-MM-YYYY')), '-1'), '%X-%V-%w') AS cohort_week,
  country,
  language,
  COUNT(DISTINCT id) AS count_ids
FROM raw_data
GROUP BY cohort_week, country, language
ORDER BY cohort_week, country, language;

This query groups records into natural weekly cohorts and returns unique ID counts per country and language.

2. Calculating Unique IDs for Rolling 7-Day Cohorts #

Your requirement is a daily-updated sliding 7-day window (e.g., 2019-05-07 counts IDs from 2019-05-01 to 2019-05-07; 2019-05-08 counts from 2019-05-02 to 2019-05-08), grouped by country and language. Your original SQL fails because the subquery returns multiple rows, but the outer query expects a single value per table_a.date. Here are two working solutions:

Solution 1: Self-Join (compatible with most databases) #

Use your table_a (unique dates) to join with raw_data records falling within the 7-day window, then aggregate:

CREATE TABLE table_b AS
SELECT
  ta.date AS report_date,
  rd.country,
  rd.language,
  COUNT(DISTINCT rd.id) AS count_ids
FROM table_a ta
LEFT JOIN raw_data rd
  -- Adjust date function syntax if needed (e.g., MySQL uses DATE_SUB(ta.date, INTERVAL 6 DAY))
  ON TO_DATE(rd.date, 'DD-MM-YYYY') BETWEEN ta.date - INTERVAL '6 days' AND ta.date
GROUP BY ta.date, rd.country, rd.language
ORDER BY ta.date, rd.country, rd.language;

Solution 2: Window Functions (for databases that support COUNT(DISTINCT) in windows) #

If you're using BigQuery, PostgreSQL 11+, or similar, you can use a sliding window to calculate counts directly:

CREATE TABLE table_b AS
WITH daily_records AS (
  SELECT
    TO_DATE(date, 'DD-MM-YYYY') AS event_date,
    country,
    language,
    id
  FROM raw_data
)
SELECT
  event_date AS report_date,
  country,
  language,
  COUNT(DISTINCT id) OVER (
    PARTITION BY country, language
    ORDER BY event_date
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS count_ids
FROM daily_records
GROUP BY event_date, country, language
ORDER BY event_date, country, language;

This method computes the rolling 7-day unique ID count for each date, country, and language combination.

Example Output with Your Sample Data #

Testing Solution 1 with your small dataset (assuming table_a has 2019-05-07 and 2019-05-08):

• For 2019-05-07 (window: 2019-05-01 to 2019-05-07):
  • US EN: 1 unique ID (e002)
  • CH LN: 1 unique ID (a001)
  • IN EN: 1 unique ID (f002)
• For 2019-05-08 (window: 2019-05-02 to 2019-05-08):
  • US EN: 1 unique ID (e002)
  • IN EN: 1 unique ID (f002)

(The numbers in your sample output are placeholders; real results will match your full dataset.)

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