Calculate unique items seen by users via sql

user_5293754 0
Viewed 65

I need help to resolve the next case.

The data which users want to see is accessible by pagination requests and later these requests are stored in the database in the next form:

+----+---------+-------+--------+
| 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   |
+----+---------+-------+--------+

The table is ordered by user id asc, first asc, amount desc.

The task is to write the SQL statement which calculate what total unique amount of data the user has seen.

For the first user total amount must be 20, since the request with id=1 returned first 5 items, with id=2 returned another 10 items. Request with id=3 returns data already 'seen' by request with id=2. Request with id=4 intersects with id=2, but still returns 5 'unseen' pieces of data.

For the second user total amount must be 15.

As a result of SQL statement, I should get the next output:

+---------+-------+
| user id | total |
+---------+-------+
|    1    |   20  |
+---------+-------+
|    2    |   15  |
+---------+-------+

I am using MySQL 5.7, so window functions are not available for me. I stuck with this task for a day already and still cannot get the desired output. If it is not possible with this setup, I will end up calculating the results in the application code. I would appreciate any suggestions or help with resolving this task, thank you!

mysql sql
2 Answers

This is a type of gaps and islands problem. In this case, use a cumulative max to determine if one request intersects with a previous request. If not, that is the beginning of an "island" of adjacent requests. A cumulative sum of the beginnings assigns an "island", then an aggregation counts each island.

So, the islands look like this:

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;

You then want this summed by userid, so that is one more level of aggregation:

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 is a db<>fiddle.

user_11440350

This logic is similar to Gordon's, but runs on older releases of MySQL, too.

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

See fiddle

user_25279050
Related