Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are non-deterministic.
Questions tagged [ranking-functions]
193 questions
3
votes
2 answers
Reset Row Number on value change, but with repeat values in partition
I'm having trouble with something very similar to this question
T-sql Reset Row number on Field Change
The solution to this question is perfect, works fine. Except when I try with multiple other 'custno', it breaks down.
What I mean by that:
custno…
hello
- 33
- 1
- 3
3
votes
2 answers
How to order by one column, but rank based on a different column that is not numeric?
I have four columns that I am trying to rank. They need to be grouped by employee ID and then listed low to high by order number. Then when everything is in order, I'm really trying to get the ranking of where the city falls in that order. If the…
wowowtt
- 33
- 2
3
votes
2 answers
How to write an algorithm that takes into account 3 weighted actions, with time decay?
I'm interested in creating an algorithm that provides a user ranking based on 3 actions that are weighted in significance. Example:
Action A (50%)
Action B (30%)
Action C (20%)
I would then like to have a time decay which provider maximum value at…
AnApprentice
- 108,152
- 195
- 629
- 1,012
3
votes
4 answers
Case statement with Ranking function
Hello looking for help with ranking.
I'm working with Teradata using SQL and I'm trying to rank a list by a specific group and then by age.
For example: I want to rank by group then only rank those under the selected group that are under 21 years…
doraav
- 33
- 1
- 1
- 3
3
votes
3 answers
SQL Server Ranking issue
I am trying to apply ranking to my data set the logic is as follows:
For each ID , Order by ID2 ASC and Order by IsMaster Desc rank the row 1 and only change it when the ID4 value changes
My dataset and desired output looks like:
Test data
CREATE…
Jack Jones
- 335
- 1
- 2
- 8
3
votes
3 answers
Group By, Count and Delete on Consecutive Records
I have a tricky SQL question. This is based on SQL server 2008 R2.
From a Log table, I have to combine consecutive records which have the same messages (MSG), count how many messages are combined (COUNT), and then delete the duplicate messages. This…
padre
- 197
- 1
- 2
- 8
2
votes
1 answer
How to tile data evenly distributed across boundaries
I have a time-based set of records that I need to load into a partitioned table which is used for staging data. The stage table is partitioned by day. I have distributed the data load into the stage table across multiple "processors" (streams in…
Sean Fitzgerald
- 21
- 1
2
votes
2 answers
Ranking algorithms to compare "Rankings"
Is there an algorithm that allows to rank items based on the difference of the position of those items in two rankings but also "weighted" with the position, e.g. one Player that goes from position 2->1 should be ranked higher than a player that…
Alessio P.
- 51
- 1
- 4
2
votes
2 answers
Create a data frame with lagged values as numeric vector using dplyr
My data looks as follows
data <- data.frame(
A = c(10,20,30,40,50,60,70,80,90,100),
B = c(110,120,130,140,150,160,170,180,190,200)
)
I wish to create a new column where row-wise I take each value from column A and concatenate it with the…
TheGoat
- 2,587
- 3
- 25
- 58
2
votes
0 answers
How to calculate individual documents score BM25F score for each document?
I a trying to implement BM25F from scratch in Python.
Here is the simplified folumation of the BM25F:
However, after implementing it, I am getting the same score for all the documents in the collection.
After a careful inspection, the only…
HQuser
- 640
- 10
- 26
2
votes
1 answer
Unable to rank/ pivot data in Excel
The dataset below contains device entries for both "Noncompliant" and "Compliant" category, however the focus area is "Noncompliant" devices only for further action.
User
Manufacturer
Model
OS
OS Version
Last Synch…
Channdeep Singh
- 103
- 2
- 10
2
votes
2 answers
How to merge rows startdate enddate based on column values using Lag Lead or window functions?
I have a table with 4 columns: ID, STARTDATE, ENDDATE and BADGE. I want to merge rows based on ID and BADGE values but make sure that only consecutive rows will get merged.
For example, If input is:
Output will be:
I have tried lag lead,…
Bilal Shafqat
- 689
- 2
- 14
- 26
2
votes
1 answer
How to group into batches after assigning rank
I have a table that I'm first trying to group based on unique column values (using dense_rank) and then further group those items into batches of 5. Below is my…
aerolew2
- 45
- 4
2
votes
1 answer
Highest value of the rank for a grouped data
From the Data given below (Table : timeFramesDetail)
tfgroup
City
activeDTTM
Begin_time
End_time
RankOfTime
2
16
2021-04-05 02:30:03.510
2021-04-04 18:00:00.000
2021-04-05 06:00:00.000
1
2
16
2021-04-05 02:30:04.510
2021-04-04…
SChavan
- 57
- 5
2
votes
1 answer
Ranking of one variable by weighted value of another variable?
Super R beginner here. I am trying to get rankings of a certain variable by weighted values of a another column/variable. For example, I have a dataset shown below:
State <- rep(c("MN", "MN", "OR", "OR", "ME", "ME", "CO", "CO", "HI", "HI"), each =…
ayy__bee
- 21
- 2