topic Re: How do I give same rank for same score? in Splunk Search

How do I give same rank for same score?

LearningGuy — Fri, 17 Nov 2023 02:23:15 GMT

Hello,
How do I give same rank for same score?
Student d and e has the same score of 73, thus they both Rank 4, but student f has Rank 6. Rank 5 is skipped because Student d and e has the same score.
Thank you for your help

Expected result:

Student	Score	Rank
a	100	1
b	95	2
c	84	3
d	73	4
e	73	4
f	54	6
g	43	7
h	37	8
i	22	9
j	12	10

This is what I figured out so far, but i won't take into consideration of same Score

| makeresults format=csv data="Student, Score a,100 b,95 c,84 d,73 e,73 f,54 g,43 h,37 i,22 j,12" | streamstats count

Re: How do I give same rank for same score?

PickleRick — Thu, 16 Nov 2023 22:16:01 GMT

From the top of my head. Untested, might need some tweaking.

| stats values(App) as App count by Score
| streamstats sum(count) as rank
| mvexpand App

Re: How do I give same rank for same score?

ITWhisperer — Thu, 16 Nov 2023 22:24:17 GMT

| streamstats count as Rank | streamstats window=2 range(Score) as range | eval Rank=if(Rank=1 OR range != 0, Rank, null()) | filldown Rank

Re: How do I give same rank for same score?

bowesmana — Fri, 17 Nov 2023 01:10:26 GMT

@LearningGuy as I said in the other post - you can probably solve that problem 😁

and as usual, @ITWhisperer comes up with the perfect elegant solution!

Re: How do I give same rank for same score?

LearningGuy — Fri, 17 Nov 2023 03:13:46 GMT

Hello
I tried your suggestion and it worked. I accepted this solution and will try on real data.
1) Can you explain what this eval for?
It looks like if range is 0, you replace the pos with NULL and fill down with previous value, except for position one?

| eval Rank=if(Rank=1 OR range != 0, Rank, null())

2) Would it be possible to use only 1 streamstats instead of 2 streamstats?

Thank you so much for your help

Re: How do I give same rank for same score?

ITWhisperer — Fri, 17 Nov 2023 09:42:51 GMT

1) If the Rank is 1, it needs to remain 1, or if there is a difference in values, the rank needs to remain the same (as it is already correct), otherwise, if there is no difference between the current and previous value, the rank should be the same as the previous rank. By setting it to null(), when the filldown happens, the rank is copied down to all positions with the same rank.

2) It is not possible to do with just one streamstats because the first streamstats has to operate over the whole pipeline, whereas the second has to operate with a (rolling) window of two events.

Re: How do I give same rank for same score?

PickleRick — Fri, 17 Nov 2023 09:53:24 GMT

2) Actually you can get away with just one streamstats. Replace the other one with autoregress. (But yes, it will still give you two separate passes across your results)

Re: How do I give same rank for same score?

ITWhisperer — Fri, 17 Nov 2023 10:44:21 GMT

Autoregress is the same as

| streamstats window=2 current=f last(Score) as Score_p1

Re: How do I give same rank for same score?

PickleRick — Fri, 17 Nov 2023 12:21:19 GMT

Sure, it is. But it's formally a different command 😉

Re: How do I give same rank for same score?

shalomsuresh — Wed, 18 Sep 2024 17:10:07 GMT

If you want to avoid using 2 streamstats you shall try this way,

| streamstats count as Rank | delta Score as Diff | eval Rank=if(Diff=0,Rank-1,Rank) | fields - Diff

And with 2 streamstats you shall try this so to avoid 1 extra filldown command,

| streamstats count as Rank | streamstats window=2 range(Score) as range | eval Rank=if(Rank=1 OR range != 0, Rank, Rank-1)

Re: How do I give same rank for same score?

ITWhisperer — Wed, 18 Sep 2024 16:23:28 GMT

@shalomsuresh These solutions don't work if there are more than 3 with the same score, e.g. if "f" had a score of 73 as well.

Re: How do I give same rank for same score?

shalomsuresh — Thu, 19 Sep 2024 14:11:24 GMT

Yep you are right, I wasn't thinking about that. We can still use the following,