I have a table like this:

Table "*wallet"

And here is the expected result: (one row per day)

Here is my current query:

SELECT MIN(balance) min_balance,
       DATE(timestamp) date
FROM wallet
GROUP BY date

How can I add last_balance? Sadly there is no something like LAST(balance) in MySQL. By "last" I mean bigger timestamp.

CodePudding user response：

With MIN() and FIRST_VALUE() window functions:

SELECT DISTINCT
       MIN(balance) OVER (PARTITION BY DATE(timestamp)) AS min_balance, 
       FIRST_VALUE(balance) OVER (PARTITION BY DATE(timestamp) ORDER BY timestamp DESC) AS last_balance,
       DATE(timestamp) AS date
FROM wallet;

See the demo.

CodePudding user response：

If you are running MySQL 8 or later, then we can use ROW_NUMBER() here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY DATE(timestamp) ORDER BY balance) rn_min,
              ROW_NUMBER() OVER (PARTITION BY DATE(timestamp) ORDER BY timestamp DESC) rn_last
    FROM yourTable
)

SELECT
    DATE(timestamp) AS date,
    MAX(CASE WHEN rn_min = 1 THEN balance END) AS min_balance,
    MAX(CASE WHEN rn_last = 1 THEN balance END) AS last_balance
FROM cte
GROUP BY 1
ORDER BY 1;