I have the following table:

 ------- ------------ 
| level | count(uid) |
 ------- ------------ 
|     3 |          1 |
|     4 |          1 |
|     0 |          1 |
|     2 |          3 |
 ------- ------------ 

I want to return:

 ------- ------------ 
| level | ans |
 ------- ------------ 
|     3 |          2 |
|     4 |          1 |
|     0 |          6 |
|     2 |          5 |
 ------- ------------ 

I want to calculate: for each level, calculate select sum(count(uid)) where level >= level For example, the first row, we sum all levels >= level 3, which yields 1 1 = 2. for the last row, we sum all levels >= level 2 which is 3 1 1 = 5

CodePudding user response：

for each level, you can sum up the uid for all the levels that are higher than the current one you are checking.

SELECT a.leveled, 
count_uid   (
            select 
            if (sum(count_uid) is null, 0, sum(count_uid))
            from levels b
            WHERE a.leveled < b.leveled
            ORDER BY a.leveled
            ) as ans
FROM levels a

db fiddle link

CodePudding user response：

I figured it out! Suppose the table name is tmp and count(uid) as res

There are 2 solutions: The following example works for StarRocks OLAP db

Self inner join:

select 
  t1.level, 
  SUM(t2.res) as retention 
from 
  tmp t1 
  inner join tmp t2 on t1.level <= t2.level 
group by 
  t1.level;

Join is expensive, we can optimize it w/ a simple window function:

select 
  tmp.level, 
  SUM(tmp.res) OVER(
    ORDER BY 
      tmp.level ROWS BETWEEN CURRENT ROW 
      AND UNBOUNDED FOLLOWING
  ) AS retention 
from 
  tmp 
order by 
  tmp.level;

Result:

 ------- ----------- 
| level | retention |
 ------- ----------- 
|     0 |         6 |
|     2 |         5 |
|     3 |         2 |
|     4 |         1 |
 ------- ----------- 
4 rows in set (0.02 sec)