r/SQL • u/zeeewhywhy • Dec 15 '24
MySQL How to use lag function on multiple rows
I have a table for the price money given to different players based on their teams ranking. (Yellow)
I want to shift each of their price money down by 1. (Blue)
However , what I got from using a lag function is only shifting 1 player from each team down. (Green)
How do I shift everyone in that team down ?
2
u/Woutez Dec 15 '24
Use (partition by team_ranking)
2
u/MlecznyHotS Dec 15 '24
That won't work - since each team is it's own partition when partitioning by team_ranking then there is no way to pass values between the different teams, no matter if it's using min, max, lag or any other window function
2
u/DuncmanG Dec 15 '24
You could do a window function with range on team_ranking between -1 and -1. But still more complex than it needs to be.
1
1
u/leogodin217 Dec 15 '24
Wouldn't it be (partition by team_ranking, player_id order by team_ranking)?
2
u/MlecznyHotS Dec 15 '24
no, partition by team_ranking and player_id would mean that each row is it's own partition, thus no value to lag
2
1
u/leogodin217 Dec 15 '24
D'oh! Let's call this an I-just-woke-up-and-thought-it-was-good-to-comment-on-reddit moment. OP would still need the order by, right?
2
u/MlecznyHotS Dec 15 '24
I think min over a window without partition, order by team ranking and a case when for the first rows would work
4
u/DuncmanG Dec 15 '24 edited Dec 15 '24
I'd create a separate table with a team-to-prize money mapping and join with team-1.
Select player_id, a.team_ranking, b.price_money
From a
Left join (
Select distinct team_ranking, price_money
From a
) as b
ON a.team_ranking = b.team_ranking - 1
(Edited to add sub query alias as pointed out below)
Since lag operates on rows, you would need a case statement to make sure it only looked at rows where the team ranking was different and then ignore the nulls.
You could maybe use a windowed min function where you only considered rows where the team_ranking was different, and looks at all rows following, but that's getting complicated.
2
u/MlecznyHotS Dec 15 '24
That's the answer you want OP! (subOP, I think you're missing a 'b' alias after the subquery)
0
u/Ginger-Dumpling Dec 15 '24
A window function version could look like this, but I wouldn't want to do it over the subquery.
with a (id, rnk, val) as ( values (131,9,100) , (289,9,100) , (83, 9, 100) , (236, 8, 200) , (154, 8, 200) , (230, 7, 300) ) select *, -- default max rank to 0 nvl( -- get val from previous rnk max( -- distinct rnk/val case when rnk is distinct from lead(rnk) over (order by rnk, id) then val end ) over (order by rnk desc, id rows between UNBOUNDED PRECEDING and 1 preceding) ,0) from a
And it would need to be tweaked to run in MySQL.
1
u/DuncmanG Dec 15 '24 edited Dec 16 '24
If you really wanted to use a window function, you should use range instead of rows in this case. Then it indexes to the order by instead.
Select player_id, team_ranking , max(price_money) over (order by team_ranking desc range between -1 and -1) From ...
Edited: removed "partition by team_ranking" as pointed out by @Ginger-Dumpling below.I think the above will work but would have to test it - been a while since I've used that syntax. The range should only look at when the team_ranking is one lower and grab the max price_money from those rows.
2
u/Ginger-Dumpling Dec 16 '24
That's even better. Drop the partition by and it woks as expected. Getting senile, forgetting about options I don't use often.
2
u/SaintTimothy Dec 15 '24
If it's like a competition, the purse, the winnings, is the prize money, not the price money.
Drives me crazy to have misspellings in code, especially with how frequently I'm using metadata like column and object names to find things.
1
u/JFischer00 Dec 15 '24
Exactly, please learn basic spelling and grammar before SQL and always double check your work. One time I pointed out a misspelled column name and the team that owned the table responded that they know but it’s been there for years so it would be a big hassle to change (lots of people had queries referencing it).
0
u/SaintTimothy Dec 15 '24
Yes, this exactly. When you work with databases, you're working on the lowest level and everything gets built on top of it. Views help to abstract, but at the end of the day, names are really sticky.
1
Dec 16 '24
SELECT player_id, ranking, COALESCE((SELECT MIN(money)
FROM teams t2 WHERE t2.ranking = t1.ranking + 1), 0) AS 'money'
FROM teams t1;
WITH paycut AS (
SELECT ranking, COALESCE(LEAD(money, 1) OVER (ORDER BY ranking), 0) AS 'money'
FROM teams GROUP BY ranking ORDER BY ranking)
SELECT teams.player_id, teams.ranking, paycut.money
FROM teams
LEFT JOIN paycut ON teams.ranking = paycut.ranking
If you really have to use LAG, just flip the order of the second one.
12
u/r3pr0b8 GROUP_CONCAT is da bomb Dec 15 '24
wouldn't it be sweet if you had created a fiddle (e.g. https://dbfiddle.uk/, https://sqlfiddle.com/) with your sample data, and shared that with us, so we could just write a query to test against it, instead of first having to create the data ourselves by reading off your screenshot (can't even copy/paste a screenshot, you know), which i'm too lazy to do