Window function query in MySQL-CodePudding

We have columns Date, CID, Budget. We want to create a column Budget_rank which gives us result as: Observe that the resultant table looks sorted on date first and then whichever same Budget values were there, they got the same rank

Expected Output:

Date       | CID | Budget | Budget_rank 
---------------------------------
2022-05-24 | 123 | 12500  | 1
2022-05-23 | 123 | 12500  | 1
2022-05-16 | 123 | 12500  | 1
2022-05-15 | 123 | 9800   | 2
2022-05-13 | 123 | 9800   | 2
2022-05-12 | 123 | 8400   | 3
2022-05-08 | 123 | 8400   | 3
2022-05-04 | 123 | 15600  | 4
2022-05-02 | 123 | 15600  | 4

Can anyone please assist with the SQL query that may help us generate this column budget_rank.?

What I tried is:

SELECT 
  Date, 
  CID, 
  Budget, 
  DENSE_RANK() OVER (ORDER BY Budget)
FROM table;

But this dint gave us the expected output!

I need to make sure that the Budget_rank column is generated, exactly in the same fashion as shown in the table above. Any help with the query or direction will really help. TIA.

CodePudding user response：

You can't approach this problem with a DENSE_RANK function, as your ranking does not follow a specific order of budgets. What you can instead do is using a variable that assigns a value until a new budget is encountered. You check for new budget using the LAG window function:

SET @ranking = 0;

WITH cte AS (
    SELECT *,
           LAG(Budget) OVER(PARTITION BY CID) AS LastBudget
    FROM tab
)
SELECT Date_,
       CID,
       Budget,
       IF(LastBudget = Budget,
          @ranking, 
          @ranking := @ranking  1
       ) YourRanking
FROM cte

Check this SQL Fiddle.

CodePudding user response：

First use LAG() window function to get the previous Budget of each row and create a boolean flag of 1 or 0 depending on whether the previous Budget is different than the current Budget and then sum over the flags to create the column Budget_rank:

SELECT Date, CID, Budget,
       SUM(flag) OVER (PARTITION BY CID ORDER BY Date DESC) Budget_rank
FROM (
  SELECT *, Budget <> LAG(Budget, 1, -1) OVER (PARTITION BY CID ORDER BY Date DESC) flag
  FROM tablename
) t
ORDER BY Date DESC;

See the demo.