mysql over用法

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

INSERT INTO sales (id, product, sale_date, amount) VALUES
(1, 'Product A', '2023-01-01', 100),
(2, 'Product B', '2023-01-02', 200),
(3, 'Product A', '2023-01-03', 150),
(4, 'Product C', '2023-01-04', 300),
(5, 'Product A', '2023-01-05', 200);

SELECT product, SUM(amount) OVER (PARTITION BY product) AS total_sales
FROM sales;

SELECT product, amount,
       RANK() OVER (PARTITION BY product ORDER BY amount DESC) AS rank
FROM sales;

SELECT product, sale_date, amount,
       LAG(amount) OVER (PARTITION BY product ORDER BY sale_date) AS prev_day_sales
FROM sales;

SELECT *
FROM (
    SELECT product, sale_date, amount,
           RANK() OVER (PARTITION BY product ORDER BY amount DESC) AS rank
    FROM sales
) subquery
WHERE rank = 1;