多字段分组时如何显示未出现列的count=0？能否无窗口函数聚合日均参与数？

Alright, let's tackle your two questions with straightforward SQL that gets you exactly the results you're looking for.

1. Getting 0 Counts for Days a User Didn't Participate #

The key here is to first generate all possible user-day combinations (even if the user had no activity that day), then left join your original table to fill in the activity data. Here's how to do it:

First, we need two lists: all unique users from your table, and all unique days that exist in the data. Cross-joining these two lists gives us every user paired with every day. Then we left join the user_event table to this combination—any day a user has no activity will show up as NULL, which we can count as 0.

Here's the full SQL:

SELECT
    COUNT(e.event_id) AS count,
    u.user_id,
    d.day
FROM
    (SELECT DISTINCT user_id FROM user_event) u
CROSS JOIN
    (SELECT DISTINCT day FROM user_event) d
LEFT JOIN
    user_event e ON u.user_id = e.user_id AND d.day = e.day
GROUP BY
    u.user_id, d.day
ORDER BY
    u.user_id, d.day;

How this works: #

• (SELECT DISTINCT user_id FROM user_event) u grabs every unique user in your dataset.
• (SELECT DISTINCT day FROM user_event) d grabs every unique day that has at least one activity.
• CROSS JOIN creates a row for every user-day pair (so user 300 gets a row for day 2, user 1 gets a row for day 3, etc.).
• LEFT JOIN matches each user-day pair to actual events; if there's no match, e.event_id is NULL.
• COUNT(e.event_id) ignores NULL values, so it returns 0 for days with no activity (exactly what you want!).

2. Calculating Daily Averages Without Window Functions #

Absolutely! You don't need window functions for this. Just take the user-day counts we generated above, wrap it as a subquery, and then group by day to calculate the average.

Here's the SQL to get your final average result:

SELECT
    ROUND(AVG(count), 2) AS avg,
    day
FROM
    (
        SELECT
            COUNT(e.event_id) AS count,
            u.user_id,
            d.day
        FROM
            (SELECT DISTINCT user_id FROM user_event) u
        CROSS JOIN
            (SELECT DISTINCT day FROM user_event) d
        LEFT JOIN
            user_event e ON u.user_id = e.user_id AND d.day = e.day
        GROUP BY
            u.user_id, d.day
    ) user_daily_counts
GROUP BY
    day
ORDER BY
    day;

How this works: #

• The inner subquery (user_daily_counts) gives us the full user-day counts including 0s, just like the first query.
• The outer query groups by day and uses AVG(count) to calculate the average number of activities per user for each day. We use ROUND(..., 2) to get the clean decimal format you showed.

This is all standard aggregate SQL—no window functions like PARTITION BY or ROW_NUMBER() required.

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