Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Rank based on cumulative value

I want to rank on ID and value columns based on ascending order of UID. Expected output has to change once value column has a different value than the previous value. Ranks has to restart on each new ID

UID ID  Value Expected Output
1   1   0         1
2   1   0         1
3   1   1         2
4   1   1         2
5   1   1         2
6   1   0         3
7   1   1         4
8   1   0         5
9   1   0         5
10  1   0         5
11  2   1         1
12  2   1         1
13  2   0         2
14  2   0         2
15  2   1         3

Here is a sample dataset that I have created:

CREATE TABLE [dbo].[Data] (
    [UID] [int] NOT NULL,
    [ID] [int] NULL,
    [Value] [int] NULL
);

INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (1, 1, 0);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (2, 1, 0);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (3, 1, 1);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (4, 1, 1);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (5, 1, 1);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (6, 1, 0);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (7, 1, 1);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (8, 1, 0);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (9, 1, 0);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (10, 1, 0);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (11, 2, 1);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (12, 2, 1);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (13, 2, 0);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (14, 2, 0);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (15, 2, 1);
like image 508
Amila Avatar asked Sep 17 '20 16:09

Amila


People also ask

What is the formula of ranking?

=RANK(number,ref,[order]) The RANK function uses the following arguments: Number (required argument) – This is the value for which we need to find the rank. Ref (required argument) – Can be a list of, or an array of, or reference to, numbers.

How do you calculate cumulative value?

To have cumulative totals, just add up the values as you go.

How do you do a conditional rank?

Conditional Formula to use it as RANKIF First of all, add a new column at the end of the table and name it “Subject Wise Rank”. in the D4 cell, enter this formula =SUMPRODUCT((–(C2=$C$2:$C$121)),(–(B2<$B$2:$B$121)))+1 and hit enter. After that, apply that formula to the end of the column, up to the last cell.


1 Answers

I think that the simplest approach to this gaps-and-islands problem is to use lag() to retrieve the "previous" value, and then a window sum that increments everytime the value changes.

select uid, id, value,
    1 + sum(case when value <> lag_value then 1 else 0 end) 
        over(partition by id order by uid) grp
from (
    select d.*, lag(value, 1, value) over(partition by id order by uid) lag_value
    from data d
) d
order by uid

Demo on DB Fiddle:

uid | id | value | grp
--: | -: | ----: | --:
  1 |  1 |     0 |   1
  2 |  1 |     0 |   1
  3 |  1 |     1 |   2
  4 |  1 |     1 |   2
  5 |  1 |     1 |   2
  6 |  1 |     0 |   3
  7 |  1 |     1 |   4
  8 |  1 |     0 |   5
  9 |  1 |     0 |   5
 10 |  1 |     0 |   5
 11 |  2 |     1 |   1
 12 |  2 |     1 |   1
 13 |  2 |     0 |   2
 14 |  2 |     0 |   2
 15 |  2 |     1 |   3
like image 71
GMB Avatar answered Nov 09 '22 14:11

GMB