mysql - 通过sql计算用户看到的唯一项目

Question

我需要帮助来解决下一个案例。

用户想要查看的数据可以通过分页请求来访问，随后这些请求以下面的形式存储在数据库中:

+----+---------+-------+--------+
| 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，因此窗口函数不可用。我已经坚持这个任务一天了，但仍然无法获得所需的输出。如果此设置无法实现，我最终将在应用程序代码中计算结果。我非常感谢任何建议或帮助解决此任务，谢谢!

Answer 1

这是一种间隙和岛屿问题。在这种情况下，使用累积最大值来确定一个请求是否与前一个请求相交。如果不是，那就是相邻请求“孤岛”的开始。开始的累积和分配一个“岛”，然后聚合对每个岛进行计数。

所以，岛屿看起来像这样:

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;

Here是一个数据库<> fiddle 。