MySQL如何通过数学函数优化计算逻辑 MySQL数学函数在数据分析中的应用

ROUND()

TRUNCATE()

AVG()

STDDEV()

(x - AVG(x)) / STDDEV(x)

sales

amount

SELECT
    amount,
    (amount - (SELECT AVG(amount) FROM sales)) / (SELECT STDDEV(amount) FROM sales) AS standardized_amount
FROM sales;

amount

amount

CASE

SELECT
    amount,
    CASE
        WHEN amount < 100 THEN 'Small'
        WHEN amount BETWEEN 100 AND 500 THEN 'Medium'
        ELSE 'Large'
    END AS sales_segment
FROM sales;

amount

COUNT()

SUM()

AVG()

MAX()

MIN()

SELECT
    MONTH(sale_date) AS month,
    AVG(amount) AS average_sales
FROM sales
GROUP BY MONTH(sale_date)
ORDER BY MONTH(sale_date);

MONTH()

AVG()

GROUP BY

ORDER BY

SELECT
    MONTH(sale_date) AS month,
    (SUM(amount) - LAG(SUM(amount), 1, 0) OVER (ORDER BY MONTH(sale_date))) / LAG(SUM(amount), 1, 0) OVER (ORDER BY MONTH(sale_date)) AS growth_rate
FROM sales
GROUP BY MONTH(sale_date)
ORDER BY MONTH(sale_date);

LAG()

(本月销售额 - 上月销售额) / 上月销售额

OVER (ORDER BY MONTH(sale_date))

LAG()

NULLIF()

SELECT
    product_name,
    SUM(profit) / SUM(sales) AS profit_margin
FROM product_sales
GROUP BY product_name;

NULLIF()

SELECT
    product_name,
    SUM(profit) / NULLIF(SUM(sales), 0) AS profit_margin
FROM product_sales
GROUP BY product_name;

NULLIF(SUM(sales), 0)

SUM(sales)

NULL

profit

sales

SUM(profit) / SUM(sales)

SELECT
    product_name,
    SUM(profit) / CAST(SUM(sales) AS DECIMAL(10, 2)) AS profit_margin
FROM product_sales
GROUP BY product_name;

CAST(SUM(sales) AS DECIMAL(10, 2))

SUM(sales)

DECIMAL(10, 2)

SELECT
    order_id,
    amount
FROM orders
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'
AND amount > (SELECT AVG(amount) FROM orders WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31');

sale_date

amount

CREATE INDEX idx_sale_date ON orders (sale_date);
CREATE INDEX idx_amount ON orders (amount);

orders

CREATE TABLE orders (
    order_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

orders

CREATE PROCEDURE CalculateAverageSales(IN start_date DATE, IN end_date DATE, OUT average_sales DECIMAL(10, 2))
BEGIN
    SELECT AVG(amount) INTO average_sales FROM orders WHERE sale_date BETWEEN start_date AND end_date;
END;

average_sales