MySQL中累计距离达到1000阈值时停止求和的查询方案咨询

现有数据表 #

• Workout Data表：

Date       User        Distance     Calories        
1614944833   1           100            32
1614944232   2           100            43
1624944831   1           150            23
1615944832   3           250            63
1614644836   1           500            234 
1614954835   2           100            55
1614344834   3           100            34
1614964831   1           260            23
1614944238   1           200            44

• user_subdomain表：

User        sub_domain
1           3
2           3           
3           3
4           2

• Subdomain表：

subdomain       name
3               test1 
4               test2

需求说明 #

统计每个用户的累计运动距离，当用户累计距离>=1000时，不再统计该用户后续的记录；最终输出每个用户对应的截止记录Date、累计统计的记录数record_count、距离总和（累计超过1000则按1000计算，未超过则按实际累计最大值计算）、累计到截止节点的Calories总和。
预期输出：

Date       record_count Distance    Calories    
1614964831   4          1000        312
1614954835   2          200         98
1614344834   3          350         97

具体实现（以MySQL8.0+为例） #

可以通过窗口函数做累计求和，再结合INNER JOIN过滤目标用户的方式实现，查询语句如下：

WITH ordered_workout AS (
    -- 按用户分组、按日期升序排序，计算每条记录的累计距离、累计卡路里、行号
    SELECT 
        Date,
        User,
        Distance,
        Calories,
        SUM(Distance) OVER (PARTITION BY User ORDER BY Date ASC) AS cum_distance,
        SUM(Calories) OVER (PARTITION BY User ORDER BY Date ASC) AS cum_calories,
        ROW_NUMBER() OVER (PARTITION BY User ORDER BY Date ASC) AS row_num
    FROM `Workout Data`
),
cutoff_flag AS (
    -- 标记每个用户的截止行：第一个累计距离>=1000的行，或者所有记录累计未到1000的最后一行
    SELECT 
        *,
        CASE WHEN cum_distance >= 1000 THEN 1 ELSE 0 END AS is_over,
        MAX(row_num) OVER (PARTITION BY User) AS max_row
    FROM ordered_workout
)
SELECT 
    MAX(CASE WHEN is_over = 1 OR row_num = max_row THEN Date END) AS Date,
    MAX(CASE WHEN is_over = 1 OR row_num = max_row THEN row_num END) AS record_count,
    LEAST(MAX(CASE WHEN is_over = 1 OR row_num = max_row THEN cum_distance END), 1000) AS Distance,
    MAX(CASE WHEN is_over = 1 OR row_num = max_row THEN cum_calories END) AS Calories
FROM cutoff_flag
-- 关联过滤subdomain为test1的用户
INNER JOIN user_subdomain us ON cutoff_flag.User = us.User
INNER JOIN Subdomain s ON us.sub_domain = s.subdomain
WHERE s.name = 'test1'
GROUP BY cutoff_flag.User
ORDER BY cutoff_flag.User;

逻辑说明 #

1. 先对每个用户的运动记录按时间排序，逐行计算累计距离、累计卡路里，同时给记录编行号
2. 标记每个用户的截止节点：第一个累计距离超过1000的行，若用户所有记录累计距离未到1000，则取最后一条记录
3. 关联用户表和子域表过滤出需要统计的用户，按用户分组取截止节点对应指标，距离超过1000时按1000输出

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