PostgreSQL周平均Backlog计算实现技术求助

Got it, let's break down how to calculate the weekly average backlog in PostgreSQL step by step, following your exact rules.

First, Let's Align on the Logic #

Before diving into code, let's recap the backlog eligibility to make sure we're on the same page:

• A customer is counted in daily backlog starting 1 day after reaching Stage 1
• They stop being counted either:
  • The day before they reach Stage 2 (if that's within 14 days of Stage 1)
  • On the 14th day after Stage 1 (if they never reach Stage 2, or take longer than 14 days to get there)
• Customers who reach Stage 2 on the same day or day after Stage 1 are excluded entirely

The Query Solution #

Assuming your table is named customer_pipeline and state_1/state_2 are date or timestamp fields (we'll cast to date to avoid time-related edge cases):

WITH customer_backlog_windows AS (
    -- Step 1: Calculate the date range each customer contributes to backlog
    SELECT
        id,
        state_1::date AS stage1_date,
        state_2::date AS stage2_date,
        -- Backlog starts 1 day after Stage 1
        (stage1_date + INTERVAL '1 day')::date AS backlog_start,
        -- Backlog ends either the day before Stage 2 (if within 14 days) OR 14 days after Stage 1
        CASE
            WHEN stage2_date IS NULL THEN (stage1_date + INTERVAL '14 days')::date
            ELSE LEAST((stage2_date - INTERVAL '1 day')::date, (stage1_date + INTERVAL '14 days')::date)
        END AS backlog_end
    FROM customer_pipeline
    -- Exclude customers who reached Stage 2 same day or day after Stage 1 (no backlog)
    WHERE (stage2_date IS NULL OR stage2_date > stage1_date + INTERVAL '1 day')
),
date_sequence AS (
    -- Step 2: Generate all dates we need to check (covers all possible backlog days)
    SELECT generate_series(
        (SELECT MIN(backlog_start) FROM customer_backlog_windows),
        (SELECT MAX(backlog_end) FROM customer_backlog_windows),
        INTERVAL '1 day'
    )::date AS log_date
),
daily_backlog_counts AS (
    -- Step 3: Count how many customers are in backlog each day
    SELECT
        ds.log_date,
        COUNT(cbw.id) AS daily_backlog
    FROM date_sequence ds
    LEFT JOIN customer_backlog_windows cbw
        ON ds.log_date BETWEEN cbw.backlog_start AND cbw.backlog_end
    GROUP BY ds.log_date
)
-- Step 4: Calculate weekly average backlog (weeks start on Monday, matching your example)
SELECT
    date_trunc('week', log_date)::date AS week,
    ROUND(AVG(daily_backlog), 1) AS backlog
FROM daily_backlog_counts
GROUP BY week
ORDER BY week;

How This Works #

Let's walk through each CTE (Common Table Expression) to clarify:

1. customer_backlog_windows: For each eligible customer, we define the exact date range they'll appear in the backlog. We filter out anyone who moved to Stage 2 too quickly (no backlog to count).
2. date_sequence: Generates a continuous list of dates spanning all possible backlog days—this ensures we don't miss any days when calculating daily counts.
3. daily_backlog_counts: Joins the date list with customer backlog ranges to count how many customers are in backlog each day.
4. Final aggregation: Groups daily counts by week (starting on Monday, matching your example output), then calculates the average daily backlog for each week. We round to 1 decimal place to match your sample format.

Edge Cases Handled #

• Customers who never reach Stage 2: Counted from day 1 to day 14 after Stage 1
• Customers who reach Stage 2 after 14 days: Only counted up to day 14
• Customers who reach Stage 2 within 1-14 days: Counted from day 1 to the day before they move to Stage 2
• No gaps in date coverage (even if some days have 0 backlog)

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