指定周日期范围内用户访问网站A的每周平均访问天数计算需求

Got it, let's work through this problem clearly. The goal is to find the average number of unique days each user visited Website A in a specific week, and we need to exclude anyone who never visited A at all during that period.

Key Requirements Recap #

• Only include users who accessed Website A in the target week (e.g., 2018-01-01 to 2018-01-07)
• Ignore duplicate visits from the same user on the same day
• Compute the average number of unique visit days across qualifying users

Example SQL Solution (MySQL) #

First, let's break it into two parts: first get each user's unique visit days for A, then calculate the average from that dataset.

-- Calculate average unique days visited for Website A in the target week
SELECT 
    AVG(unique_days) AS avg_weekly_visit_days
FROM (
    -- Subquery to get each user's unique visit days for A
    SELECT 
        user_id,
        COUNT(DISTINCT DATE(visit_time)) AS unique_days
    FROM 
        User
    WHERE 
        website = 'A' -- Filter only Website A visits
        AND visit_time BETWEEN '2018-01-01 00:00:00' AND '2018-01-07 23:59:59' -- Target week range
    GROUP BY 
        user_id -- Group results by individual user
) AS user_a_visit_summary;

How This Works #

1. Subquery Logic:

   • The WHERE clause filters for only Website A visits within your specified date range. This automatically excludes users who never visited A (since their records won't show up here).
   • COUNT(DISTINCT DATE(visit_time)) ensures that even if a user visits A 5 times on the same day, it's counted as just 1 unique visit day. Using DATE() strips off the time component to group all visits from the same calendar day.

2. Outer Query Logic:

   • The outer query takes the unique_days values from each qualifying user and computes the average using AVG(), giving you the final average number of days users visited A in the week.

Notes for Adjustments #

• If your visit_time column is a DATE type (no time component), you can simplify to COUNT(DISTINCT visit_time).
• For date range precision (to avoid missing late-night visits), some databases prefer using visit_time >= '2018-01-01' AND visit_time < '2018-01-08' instead of BETWEEN—this works for both DATE and DATETIME types.
• If you need to extend this to multiple weeks, you could add a YEARWEEK(visit_time) grouping to calculate averages per week.

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