การใช้ฟังก์ชัน LAG() ใน MySQL

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sale_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO sales (sale_date, amount) VALUES
('2023-01-01', 100.00),
('2023-01-02', 150.00),
('2023-01-03', 200.00),
('2023-01-04', 250.00),
('2023-01-05', 300.00);

SELECT 
    sale_date,
    amount,
    LAG(amount) OVER (ORDER BY sale_date) AS previous_amount,
    amount - LAG(amount) OVER (ORDER BY sale_date) AS change_amount
FROM 
    sales;

+------------+--------+-----------------+--------------+
| sale_date  | amount | previous_amount | change_amount|
+------------+--------+-------- --------+--------------+
| 2023-01-01 | 100.00 |            NULL |         NULL |
| 2023-01-02 | 150.00 |          100.00 |        50.00 |
| 2023-01-03 | 200.00 |          150.00 |        50.00 |
| 2023-01-04 | 250.00 |          200.00 |        50.00 |
| 2023-01-05 | 300.00 |          250.00 |        50.00 |
+------------+--------+----------------+--------------+