PostgreSQL教程

基本数学函数

绝对值函数

ABS函数返回数值的绝对值：

SELECT 
    ABS(-10) AS absolute_positive,     -- 返回 10
    ABS(10) AS absolute_negative,      -- 返回 10
    ABS(-3.14) AS absolute_decimal;    -- 返回 3.14

取整函数

不同的取整函数适用于不同场景：

SELECT 
    CEIL(3.14) AS ceiling_value,       -- 向上取整，返回 4
    FLOOR(3.14) AS floor_value,        -- 向下取整，返回 3
    ROUND(3.14159) AS round_default,   -- 四舍五入到整数，返回 3
    ROUND(3.14159, 2) AS round_2_dec,  -- 四舍五入到2位小数，返回 3.14
    TRUNC(3.14159) AS trunc_default,   -- 截断到整数，返回 3
    TRUNC(3.14159, 2) AS trunc_2_dec;  -- 截断到2位小数，返回 3.14

幂运算函数

用于指数和根号计算：

SELECT 
    POWER(2, 3) AS power_result,       -- 2的3次方，返回 8
    POWER(9, 0.5) AS square_root,      -- 9的平方根，返回 3
    SQRT(16) AS sqrt_result,           -- 16的平方根，返回 4
    CBRT(27) AS cube_root;             -- 27的立方根，返回 3

对数函数

各种对数计算函数：

SELECT 
    LN(2.718281828) AS natural_log,    -- 自然对数，返回约 1
    LOG(100) AS base10_log,            -- 以10为底的对数，返回 2
    LOG(2, 8) AS base2_log;            -- 以2为底8的对数，返回 3

三角函数

基本三角函数

PostgreSQL提供完整的三角函数支持：

SELECT 
    SIN(PI()/2) AS sine_value,         -- 正弦函数，返回 1
    COS(0) AS cosine_value,            -- 余弦函数，返回 1
    TAN(PI()/4) AS tangent_value,      -- 正切函数，返回 1
    ASIN(1) AS arcsine_value,          -- 反正弦函数，返回 π/2
    ACOS(0) AS arccosine_value,        -- 反余弦函数，返回 π/2
    ATAN(1) AS arctangent_value;       -- 反正切函数，返回 π/4

双曲函数

双曲三角函数：

SELECT 
    SINH(1) AS hyperbolic_sine,        -- 双曲正弦
    COSH(1) AS hyperbolic_cosine,      -- 双曲余弦
    TANH(1) AS hyperbolic_tangent;     -- 双曲正切

随机数函数

随机数生成

生成随机数和随机选择：

SELECT 
    RANDOM() AS random_value,          -- 生成0到1之间的随机小数
    RANDOM() * 100 AS random_100,      -- 生成0到100之间的随机数
    FLOOR(RANDOM() * 100) + 1 AS random_int_1_to_100; -- 生成1到100之间的随机整数

实际应用示例

-- 生成测试数据
INSERT INTO test_scores (student_id, score)
SELECT 
    generate_series(1, 100) AS student_id,
    FLOOR(RANDOM() * 100) + 1 AS score;

-- 随机选择记录
SELECT * FROM users 
ORDER BY RANDOM() 
LIMIT 10;

数值函数

符号和大小函数

判断数值符号和大小：

SELECT 
    SIGN(-5) AS negative_sign,         -- 负数返回 -1
    SIGN(0) AS zero_sign,              -- 零返回 0
    SIGN(5) AS positive_sign,          -- 正数返回 1
    GREATEST(10, 20, 30) AS max_value, -- 返回最大值 30
    LEAST(10, 20, 30) AS min_value;    -- 返回最小值 10

模运算

取余数运算：

SELECT 
    MOD(10, 3) AS modulo_result,       -- 10除以3的余数，返回 1
    10 % 3 AS modulo_operator;         -- 使用%操作符，返回 1

高级数学函数

指数和对数函数

指数和对数相关函数：

SELECT 
    EXP(1) AS euler_number,            -- e的1次方，返回约 2.718
    EXP(2) AS e_squared,               -- e的2次方
    LN(EXP(1)) AS ln_exp_identity;     -- 验证ln和exp的互逆关系

角度转换

角度和弧度转换：

SELECT 
    DEGREES(PI()) AS degrees_value,    -- 弧度转角度，返回 180
    RADIANS(180) AS radians_value;     -- 角度转弧度，返回 π

实际应用场景

数据分析计算

-- 计算销售额的统计信息
SELECT 
    COUNT(*) AS total_orders,
    SUM(total_amount) AS total_sales,
    AVG(total_amount) AS avg_order_value,
    ROUND(STDDEV(total_amount), 2) AS std_deviation,
    MIN(total_amount) AS min_order,
    MAX(total_amount) AS max_order
FROM orders 
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

距离计算

-- 使用球面余弦定理计算两点间距离
CREATE OR REPLACE FUNCTION calculate_distance(
    lat1 DOUBLE PRECISION,
    lon1 DOUBLE PRECISION,
    lat2 DOUBLE PRECISION,
    lon2 DOUBLE PRECISION
)
RETURNS DOUBLE PRECISION AS $$
DECLARE
    R DOUBLE PRECISION := 6371; -- 地球半径（公里）
    dLat DOUBLE PRECISION;
    dLon DOUBLE PRECISION;
    a DOUBLE PRECISION;
    c DOUBLE PRECISION;
BEGIN
    dLat := RADIANS(lat2 - lat1);
    dLon := RADIANS(lon2 - lon1);
    
    a := SIN(dLat/2) * SIN(dLat/2) + 
         COS(RADIANS(lat1)) * COS(RADIANS(lat2)) * 
         SIN(dLon/2) * SIN(dLon/2);
         
    c := 2 * ATAN2(SQRT(a), SQRT(1-a));
    
    RETURN R * c;
END;
$$ LANGUAGE plpgsql;

-- 使用函数计算距离
SELECT calculate_distance(39.9042, 116.4074, 31.2304, 121.4737) AS distance_km; -- 北京到上海的距离

财务计算

-- 计算复利
CREATE OR REPLACE FUNCTION compound_interest(
    principal NUMERIC(12,2),    -- 本金
    rate NUMERIC(5,4),          -- 利率（小数形式）
    time INTEGER,               -- 时间（年）
    compounds INTEGER DEFAULT 1 -- 每年复利次数
)
RETURNS NUMERIC(12,2) AS $$
BEGIN
    RETURN principal * POWER(1 + rate/compounds, compounds * time);
END;
$$ LANGUAGE plpgsql;

-- 计算10000元年利率5%存10年的复利
SELECT compound_interest(10000, 0.05, 10, 12) AS future_value; -- 每月复利

性能优化建议

索引考虑

在使用数学函数的查询中，注意索引的使用：

-- 不好的查询（可能无法使用索引）
SELECT * FROM products WHERE SQRT(price) > 10;

-- 优化后的查询（可以使用索引）
SELECT * FROM products WHERE price > 100; -- 因为SQRT(100) = 10

函数索引

为常用数学函数创建索引：

-- 为价格的平方根创建索引
CREATE INDEX idx_products_sqrt_price ON products (SQRT(price));

-- 为对数创建索引
CREATE INDEX idx_products_log_price ON products (LOG(price));

-- 创建销售报告函数
CREATE OR REPLACE FUNCTION generate_sales_report(start_date DATE, end_date DATE)
RETURNS TABLE(
    period TEXT,
    total_orders BIGINT,
    total_sales NUMERIC(15,2),
    avg_order_value NUMERIC(10,2),
    min_order NUMERIC(10,2),
    max_order NUMERIC(10,2),
    std_deviation NUMERIC(10,2)
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        CONCAT(start_date, ' to ', end_date) AS period,
        COUNT(*) AS total_orders,
        SUM(total_amount) AS total_sales,
        ROUND(AVG(total_amount), 2) AS avg_order_value,
        MIN(total_amount) AS min_order,
        MAX(total_amount) AS max_order,
        ROUND(STDDEV(total_amount), 2) AS std_deviation
    FROM orders
    WHERE order_date BETWEEN start_date AND end_date;
END;
$$ LANGUAGE plpgsql;

-- 使用函数生成报告
SELECT * FROM generate_sales_report('2023-10-01', '2023-10-31');