我需要帮助来解决下一个问题。
用户想要查看的数据可以通过分页请求访问，稍后这些请求将以下一种形式存储在数据库中：
+----+---------+-------+--------+
| id | user id | first | amount |
+----+---------+-------+--------+
| 1 | 1 | 0 | 5 |
| 2 | 1 | 10 | 10 |
| 3 | 1 | 10 | 5 |
| 4 | 1 | 15 | 10 |
| 5 | 2 | 0 | 10 |
| 6 | 2 | 0 | 5 |
| 7 | 2 | 10 | 5 |
+----+---------+-------+--------+
该表按用户id asc、第一个asc、金额desc排序。
任务是编写SQL语句，计算用户看到的唯一数据总量。
对于第一个用户，总金额必须为20，因为id为1的请求返回了前5个项目，id为2的请求又返回了10个项目。id为3的请求返回id为2的请求已"看到"的数据。id为4的请求与id为2的请求相交，但仍返回5条"看不见"的数据。
对于第二个用户，总金额必须为15。
作为SQL语句的结果，我应该得到下一个输出：
+---------+-------+
| user id | total |
+---------+-------+
| 1 | 20 |
+---------+-------+
| 2 | 15 |
+---------+-------+
我使用的是MySQL 5.7，所以窗口功能对我来说不可用。我已经完成了一天的任务，仍然无法获得所需的输出。如果这种设置不可能，我将在应用程序代码中计算结果。如果有任何建议或帮助我解决这项任务，我将不胜感激，谢谢！
这是一种间隙和孤岛问题。在这种情况下，使用累积最大值来确定一个请求是否与前一个请求相交。如果不是，那就是"；岛；相邻请求的数量。开始的累积和分配一个"0"；"岛"；，则聚合对每个岛进行计数。
所以，这些岛屿看起来是这样的：
select userid, min(first), max(first + amount) as last
from (select t.*,
sum(case when prev_last >= first then 0 else 1 end) over
(partition by userid order by first) as grp
from (select t.*,
max(first + amount) over (partition by userid order by first range between unbounded preceding and 1 preceding) as prev_last
from t
) t
) t
group by userid, grp;
然后，您希望通过userid将其相加，这样就有了另一个聚合级别：
with islands as (
select userid, min(first) as first, max(first + amount) as last
from (select t.*,
sum(case when prev_last >= first then 0 else 1 end) over
(partition by userid order by first) as grp
from (select t.*,
max(first + amount) over (partition by userid order by first range between unbounded preceding and 1 preceding) as prev_last
from t
) t
) t
group by userid, grp
)
select userid, sum(last - first) as total
from islands
group by userid;
这里有一个db<gt；不停摆弄
这个逻辑与Gordon的类似，但也在旧版本的MySQL上运行。
select userid
-- overall length minus gaps
,max(maxlast)-min(minfirst) + sum(gaplen) as total
from
(
select userid
,prevlast
,min(first) as minfirst -- first of group
,max(last) as maxlast -- last of group
-- if there was a gap, calculate length of gap
,min(case when prevlast < first then prevlast - first else 0 end) as gaplen
from
(
select t.*
,first + amount as last -- last value in range
,( -- maximum end of all previous rows
select max(first + amount)
from t as t2
where t2.userid = t.userid
and t2.first < t.first
) as prevlast
from t
) as dt
group by userid, prevlast
) as dt
group by userid
order by userid
参见小提琴