CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-groupwindow-functions
Ido Barash
Ido Barash

Reputation: 5132

MYSql window function - get last value

I am trying to fetch the last value of player balances in a certain time window.

I have a transactions table.

The player balance is not the maximum or the minimum one.

SELECT  project_id, 
        player_id,
        FIRST_VALUE(balance) OVER (PARTITION BY player_id ORDER BY event_arrival_time DESC) AS balance
FROM transactions
WHERE event_arrival_time BETWEEN '2019-12-02 00:00:00' AND '2019-12-03 23:59:59'
        AND project_id='aaa' 
GROUP BY project_id, player_id

I get values, but if I test them using the query blown on a single player I get different balance and I see the given result balance somewhere in the middle of the period.

Also, if I run this query several times, I get different balance, like it chooses the different transaction (we are talking about 10 minutes difference).

SELECT * 
FROM transacitions
where event_arrival_time BETWEEN '2019-12-02 00:00:00' AND '2019-12-03 23:59:59'
        AND project_id='aaa' and player_id = 'player1'
ORDER BY event_arrival_time desc

I want to get list of players in that period, and their latest balance (not MAX value - maybe max date).

Upvotes: 0

Views: 1259

Answers (3)

GMB
GMB

Reputation: 222592

You need filtering, not aggregation.

You can do this with a correlated subquery:

SELECT project_id, player_id, balance
FROM transactions t
WHERE event_arrival_time = (
    SELECT MAX(t1. event_arrival_time)
    FROM transactions t1
    WHERE 
        t1.player_id = t.player_id
        AND t1.event_arrival_time >= '2019-12-02'
        AND t1.event_arrival_time < '2019-12-03'
        AND t1.project_id = 'aaa' 
    )

For performance, you want an index on (project_id, player_id, event_arrival_time). You might also try a covering index: (project_id, player_id, event_arrival_time, balance); with such an index, the database would possibly execute the whole query by looking at the index only, without actually accessing the underlying data.

You can also use window functions:


SELECT project_id, player_id, balance
FROM (
    SELECT 
        t.*,
        RANK() OVER(PARTITION BY player_id ORDER BY event_arrival_time DESC) rn
    FROM transactions t
    WHERE
        event_arrival_time >= '2019-12-02'
        AND event_arrival_time < '2019-12-03'
        AND project_id='aaa'
) t
WHERE rn = 1

Upvotes: 1

Nae
Nae

Reputation: 15345

I think this should work. Hard to tell without sample.

SELECT
     t.project_id,
     t.player_id,
     GROUP_CONCAT(t.balance) AS Balance -- supposed to have single value
FROM transacitions t
LEFT JOIN transactions t2 ON t.project_id = t2.project_id AND t.player_id = t2.player_id
AND t.event_arrival_time < t2.event_arrival_time
where t2.player_id IS NULL
AND t.event_arrival_time BETWEEN '2019-12-02 00:00:00' AND '2019-12-03 23:59:59'
GROUP BY t.project_id, t.player_id
ORDER BY t.event_arrival_time desc

Upvotes: 0

forpas
forpas

Reputation: 164154

Remove the GROUP BY clause and if needed use DISTINCT in SELECT:

SELECT DISTINCT 
  project_id, 
  player_id,
  FIRST_VALUE(balance) OVER (PARTITION BY player_id ORDER BY event_arrival_time DESC) AS balance
FROM transactions
WHERE event_arrival_time BETWEEN '2019-12-02 00:00:00' AND '2019-12-03 23:59:59'
  AND project_id='aaa'

Upvotes: 3

Related Questions