基于Members表统计3/1-3/7会员周活跃天数分布并生成统计表

Got it, let's break down how to get the exact distribution you're looking for. Here's a step-by-step approach with SQL queries tailored to your table structure:

Core Logic #

First, we need to calculate how many unique days each member was active between March 1 and March 7, then group those counts to see how many members fall into each active day bucket (1 day, 2 days, ..., 7 days).

Basic Query (Shows Only Existing Buckets) #

This query will return the distribution for days where there are members with that active count:

SELECT days_active AS "#Days Active", COUNT(memberID) AS "Count"
FROM (
    -- Calculate each member's unique active days in the date range
    SELECT memberID, COUNT(DISTINCT S_Date) AS days_active
    FROM Members
    WHERE S_Date BETWEEN '2024-03-01' AND '2024-03-07'
    GROUP BY memberID
) AS member_active_days
GROUP BY days_active
ORDER BY days_active;

Query to Include All Days 1-7 (Even With Zero Members) #

If you want to ensure every day from 1 to 7 is shown (even if no members have that active count), use a common table expression (CTE) to generate all possible day values, then left join to your member data:

For PostgreSQL:

WITH active_days AS (
    -- Generate numbers 1 through 7
    SELECT generate_series(1,7) AS days_active
),
member_counts AS (
    SELECT memberID, COUNT(DISTINCT S_Date) AS days_active
    FROM Members
    WHERE S_Date BETWEEN '2024-03-01' AND '2024-03-07'
    GROUP BY memberID
)
SELECT ad.days_active AS "#Days Active", COALESCE(COUNT(mc.memberID), 0) AS "Count"
FROM active_days ad
LEFT JOIN member_counts mc ON ad.days_active = mc.days_active
GROUP BY ad.days_active
ORDER BY ad.days_active;

For MySQL (No generate_series):

WITH active_days AS (
    -- Manually create the 1-7 day range
    SELECT 1 AS days_active UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7
),
member_counts AS (
    SELECT memberID, COUNT(DISTINCT S_Date) AS days_active
    FROM Members
    WHERE S_Date BETWEEN '2024-03-01' AND '2024-03-07'
    GROUP BY memberID
)
SELECT ad.days_active AS "#Days Active", COALESCE(COUNT(mc.memberID), 0) AS "Count"
FROM active_days ad
LEFT JOIN member_counts mc ON ad.days_active = mc.days_active
GROUP BY ad.days_active
ORDER BY ad.days_active;

Key Notes: #

• Replace 2024 with the actual year you're working with (or use a dynamic year if needed).
• COUNT(DISTINCT S_Date) ensures that even if a member has multiple entries on the same day, it's counted as one active day (adjust to COUNT(S_Date) if each row represents a unique active event per day, but distinct is safer here).
• COALESCE replaces NULL values (for days with no members) with 0, so your table will show all 7 days consistently.

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