无窗口函数下的多分组SQL数据处理需求：关联用户表聚合收入并按子用户+小时维度取最大值

DevOps 小满

2026-4-28

Solution for Multi-Group Aggregation Without Window Functions

Since window functions aren't available on your remote server, we can fix this by first pre-processing the revenuetb table to eliminate duplicate subuser + hour records (keeping only the maximum revenue for each pair), then joining that cleaned data with usertable.

Here's the modified SQL query that meets all your requirements:

SELECT 
    u.Username,
    COALESCE(SUM(mr.Max_Revenue), 0) AS Total_Usage
FROM usertable u
LEFT JOIN (
    -- Step 1: Get max revenue per subuser + hour pair to remove duplicates
    SELECT 
        subuser,
        hour,
        MAX(Revenue) AS Max_Revenue
    FROM revenuetb
    WHERE hour BETWEEN 'XXX' AND 'XXX' -- Keep your original date range filter here
    GROUP BY subuser, hour
) mr ON SUBSTRING_INDEX(mr.subuser, '_', 1) = u.Username
WHERE u.Status = 'Active'
GROUP BY u.Username, u.ID -- Include u.ID to comply with strict SQL modes and ensure correct ordering
ORDER BY u.ID;

Breakdown of the changes:

Subquery mr: This inner query groups revenuetb by subuser and hour, grabbing the maximum Revenue for each unique pair. This eliminates duplicate entries like the two Mike_01 records at 7:00, retaining only the 22 value.
Left Join: We join this cleaned dataset with usertable using the subuser prefix match, ensuring even Active users with no revenue history (like Lena and Lara) show up in the results.
COALESCE: Converts NULL values (from users with no matching revenue records) to 0, which aligns perfectly with your desired output.
GROUP BY & ORDER BY: Adding u.ID to the GROUP BY clause ensures we can safely sort by user ID later (some strict SQL modes require all non-aggregated columns in the SELECT to be included in GROUP BY).