最强总结！数据库开窗函数完全指南！！

今天给大家总结的是SQL Server/MySQL/Oracle这三个关系数据库的函数内容，包含常用和不常用的。

这些总结都是此前整理好后保存的，最近集中发布，觉得有帮助，记得三连（点赞+转发+在看），笔者才会更有动力继续发布。此外，大家也可以留言需要哪方面的总结。

1. 字符串函数
2. 数值函数
3. 日期时间函数
4. 条件和控制函数
5. 窗口函数
6. JSON函数(MySQL 5.7+)
7. 加密和安全函数
8. XML函数(SQL Server)
9. 正则表达式函数
10. 系统信息函数
11. 高级聚合函数
12. 统计和数学函数
13. 字符串模式匹配函数
14. 条件和流程控制增强
15. 表分析函数
16. 实用复合函数示例

1. 字符串函数

1.1 基础字符串函数

1. LENGTH/LEN/LENGTH - 获取字符串长度

-- MySQL
SELECT LENGTH('Hello World');  -- 11
-- SQL Server  
SELECT LEN('Hello World');    -- 11
-- Oracle
SELECT LENGTH('Hello World') FROM DUAL;  -- 11

2. CHAR_LENGTH - 获取字符数(区别于字节长度)

-- MySQL & Oracle
SELECT CHAR_LENGTH('你好');  -- 2

3. SUBSTRING/SUBSTR - 截取字符串

-- MySQL & SQL Server
SELECT SUBSTRING('Hello World', 1, 5);  -- 'Hello'
SELECT SUBSTRING('Hello World', -5);     -- 'World'

-- Oracle
SELECT SUBSTR('Hello World', 1, 5) FROM DUAL;

4. LEFT/RIGHT - 从左/右截取

-- MySQL & SQL Server
SELECT LEFT('Hello World', 5);   -- 'Hello'
SELECT RIGHT('Hello World', 5);  -- 'World'

5. REPLACE - 替换字符串

-- 所有数据库通用
SELECT REPLACE('Hello World', 'World', 'SQL');  -- 'Hello SQL'

6. STUFF - 字符串替换(SQL Server特有)

SELECT STUFF('Hello World', 1, 5, 'Hi');  -- 'Hi World'

7. POSITION/INSTR/CHARINDEX - 查找子字符串位置

-- MySQL
SELECT POSITION('World' IN 'Hello World');  -- 7

-- Oracle
SELECT INSTR('Hello World', 'World') FROM DUAL;  -- 7

-- SQL Server
SELECT CHARINDEX('World', 'Hello World');  -- 7

8. REVERSE - 反转字符串

-- 所有数据库
SELECT REVERSE('Hello');  -- 'olleH'

9. SPACE - 生成空格字符串

-- SQL Server & MySQL
SELECT 'Hello' + SPACE(1) + 'World';  -- 'Hello World'

10. REPEAT/REPLICATE - 重复字符串

-- MySQL
SELECT REPEAT('SQL', 3);  -- 'SQLSQLSQL'

-- SQL Server
SELECT REPLICATE('SQL', 3);  -- 'SQLSQLSQL'

1.2 高级字符串函数

11. FORMAT - 格式化字符串

-- MySQL & SQL Server
SELECT FORMAT(123456.789, 2);  -- '123,456.79'

12. STRING_SPLIT(SQL Server)/SPLIT_STRING(MySQL) - 字符串分割

-- SQL Server
SELECT value FROM STRING_SPLIT('a,b,c', ',');

-- MySQL
SELECT SUBSTRING_INDEX('a,b,c', ',', 1);  -- 'a'

13. GROUP_CONCAT/STRING_AGG - 字符串聚合

-- MySQL
SELECT GROUP_CONCAT(name SEPARATOR ',') FROM employees;

-- SQL Server
SELECT STRING_AGG(name, ',') FROM employees;

-- Oracle
SELECT LISTAGG(name, ',') WITHIN GROUP (ORDER BY name) FROM employees;

2. 数值函数

2.1 基础数学函数

1. ROUND/TRUNC/TRUNCATE - 截断

-- 所有数据库
SELECT ROUND(123.456, 2);  -- 123.46

-- Oracle
SELECT TRUNC(123.456, 2) FROM DUAL;  -- 123.45

-- MySQL
SELECT TRUNCATE(123.456, 2);  -- 123.45

2. MOD - 取模

-- 所有数据库
SELECT MOD(10, 3);  -- 1

3. SQRT - 平方根

SELECT SQRT(16);  -- 4

4. SIGN - 获取数字符号

SELECT SIGN(-10);  -- -1
SELECT SIGN(10);   -- 1
SELECT SIGN(0);    -- 0

2.2 高级数学函数

5. LOG/LOG10/LN - 对数运算

SELECT LOG(10, 100);  -- 2
SELECT LOG10(100);    -- 2
SELECT LN(2.7);       -- 0.993

6. EXP - 指数运算

SELECT EXP(1);  -- 2.718281828459045

7. RAND/RANDOM - 随机数

-- MySQL & SQL Server
SELECT RAND();

-- Oracle
SELECT DBMS_RANDOM.VALUE FROM DUAL;

3. 日期时间函数

3.1 获取日期时间

1. NOW/GETDATE/SYSDATE - 当前日期时间

-- MySQL
SELECT NOW();

-- SQL Server
SELECT GETDATE();

-- Oracle
SELECT SYSDATE FROM DUAL;

2. CURDATE/CURRENT_DATE - 当前日期

-- MySQL
SELECT CURDATE();

-- Oracle & SQL Server
SELECT CURRENT_DATE;

3. CURTIME/CURRENT_TIME - 当前时间

-- MySQL
SELECT CURTIME();

-- Oracle & SQL Server
SELECT CURRENT_TIME;

3.2 日期时间处理

4. DATE_ADD/DATEADD - 日期加减

-- MySQL
SELECT DATE_ADD('2024-03-12', INTERVAL 1 DAY);
SELECT DATE_ADD('2024-03-12', INTERVAL 1 MONTH);
SELECT DATE_ADD('2024-03-12', INTERVAL 1 YEAR);

-- SQL Server
SELECT DATEADD(day, 1, '2024-03-12');
SELECT DATEADD(month, 1, '2024-03-12');
SELECT DATEADD(year, 1, '2024-03-12');

5. DATE_FORMAT/FORMAT - 日期格式化

-- MySQL
SELECT DATE_FORMAT('2024-03-12', '%Y年%m月%d日');  -- '2024年03月12日'

-- SQL Server
SELECT FORMAT(GETDATE(), 'yyyy年MM月dd日');

6. EXTRACT/DATEPART - 提取日期部分

-- MySQL & Oracle
SELECT EXTRACT(YEAR FROM '2024-03-12');
SELECT EXTRACT(MONTH FROM '2024-03-12');
SELECT EXTRACT(DAY FROM '2024-03-12');

-- SQL Server
SELECT DATEPART(year, '2024-03-12');
SELECT DATEPART(month, '2024-03-12');
SELECT DATEPART(day, '2024-03-12');

7. LAST_DAY - 获取月末日期

-- MySQL & Oracle
SELECT LAST_DAY('2024-03-12');  -- '2024-03-31'

4. 条件和控制函数

1. IF/IIF - 条件判断

-- MySQL
SELECT IF(1 > 0, 'True', 'False');

-- SQL Server
SELECT IIF(1 > 0, 'True', 'False');

2. IFNULL/ISNULL/NVL - NULL值处理

-- MySQL
SELECT IFNULL(NULL, 'Default');

-- SQL Server
SELECT ISNULL(NULL, 'Default');

-- Oracle
SELECT NVL(NULL, 'Default') FROM DUAL;

3. NULLIF - 相等返回NULL

SELECT NULLIF(10, 10);  -- NULL
SELECT NULLIF(10, 20);  -- 10

4. GREATEST/LEAST - 最大最小值

-- MySQL & Oracle
SELECT GREATEST(1, 2, 3, 4, 5);  -- 5
SELECT LEAST(1, 2, 3, 4, 5);     -- 1

5. 窗口函数

1. ROW_NUMBER/RANK/DENSE_RANK - 排序

SELECT 
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num,
    RANK() OVER (ORDER BY salary DESC) as rank_num,
    DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank_num
FROM employees;

2. FIRST_VALUE/LAST_VALUE - 首尾值

SELECT 
    name,
    department,
    salary,
    FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) as highest_salary,
    LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC 
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as lowest_salary
FROM employees;

3. LAG/LEAD - 前后行

SELECT 
    name,
    department,
    salary,
    LAG(salary) OVER (PARTITION BY department ORDER BY salary) as prev_salary,
    LEAD(salary) OVER (PARTITION BY department ORDER BY salary) as next_salary
FROM employees;

4. NTILE - 分组

SELECT 
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary) as quartile
FROM employees;

6. JSON函数(MySQL 5.7+)

1. JSON_EXTRACT - 提取JSON值

SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');  -- "John"

2. JSON_OBJECT - 创建JSON对象

SELECT JSON_OBJECT('name', 'John', 'age', 30);

3. JSON_ARRAY - 创建JSON数组

SELECT JSON_ARRAY(1, 2, 3, 4, 5);

4. JSON_CONTAINS - 检查JSON包含

SELECT JSON_CONTAINS('{"a": 1, "b": 2}', '1', '$.a');  -- 1

7. 加密和安全函数

1. MD5 - MD5加密

-- MySQL & SQL Server
SELECT MD5('password');

2. SHA1/SHA2 - SHA加密

-- MySQL
SELECT SHA1('password');
SELECT SHA2('password', 256);

3. ENCRYPT/DECRYPT - 加密解密

-- MySQL
SET @key = 'secret_key';
SET @encrypted = AES_ENCRYPT('text', @key);
SELECT AES_DECRYPT(@encrypted, @key);

8. XML函数(SQL Server)

1. FOR XML PATH - 生成XML

SELECT name, age
FROM employees
FOR XML PATH('employee'), ROOT('employees')

2. XML数据类型方法

DECLARE @xml XML
SET @xml = '<root><child>value</child></root>'
SELECT @xml.value('(/root/child)[1]', 'varchar(50)')

9. 正则表达式函数

1. REGEXP/RLIKE - 正则匹配(MySQL)

SELECT 'hello' REGEXP '^h';  -- 1
SELECT 'hello' RLIKE 'l+';   -- 1

2. REGEXP_LIKE - 正则匹配(Oracle)

SELECT * FROM employees WHERE REGEXP_LIKE(email, '^[A-Za-z]+@[A-Za-z]+\.[A-Za-z]{2,4}#39;);

10. 系统信息函数

1. VERSION - 数据库版本

-- MySQL
SELECT VERSION();

-- SQL Server
SELECT @@VERSION;

-- Oracle
SELECT * FROM V$VERSION;

2. USER/CURRENT_USER - 当前用户

-- 所有数据库
SELECT USER;
SELECT CURRENT_USER;

3. DATABASE/DB_NAME - 当前数据库

-- MySQL
SELECT DATABASE();

-- SQL Server
SELECT DB_NAME();

11. 高级聚合函数

1. GROUPING SETS - 多维度聚合

SELECT department, location, COUNT(*)
FROM employees
GROUP BY GROUPING SETS (
    (department, location),
    (department),
    (location),
    ()
);

2. CUBE - 所有可能的组合

SELECT department, location, COUNT(*)
FROM employees
GROUP BY CUBE (department, location);

3. ROLLUP - 层次聚合

SELECT 
    COALESCE(department, 'Total') as department,
    COALESCE(location, 'Subtotal') as location,
    COUNT(*) as employee_count,
    AVG(salary) as avg_salary
FROM employees
GROUP BY ROLLUP (department, location);

4. PIVOT - 行转列

-- SQL Server
SELECT *
FROM (
    SELECT department, location, salary
    FROM employees
) AS SourceTable
PIVOT (
    AVG(salary)
    FOR location IN ([New York], [London], [Tokyo])
) AS PivotTable;

12. 统计和数学函数

1. PERCENTILE_CONT/PERCENTILE_DISC - 百分位数

SELECT 
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) as median_salary,
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) as discrete_median
FROM employees;

2. CORR - 相关系数

SELECT CORR(salary, performance_score)
FROM employees;

3. STDDEV/VARIANCE - 标准差和方差

SELECT 
    department,
    AVG(salary) as avg_salary,
    STDDEV(salary) as salary_stddev,
    VARIANCE(salary) as salary_variance
FROM employees
GROUP BY department;

4. FIRST/LAST - 组内第一个/最后一个值

-- Oracle
SELECT 
    department,
    FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY hire_date) as first_salary,
    LAST_VALUE(salary) OVER (
        PARTITION BY department 
        ORDER BY hire_date
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as last_salary
FROM employees;

13. 字符串模式匹配函数

1. LIKE模式匹配增强

-- 复杂LIKE模式
SELECT * FROM employees
WHERE 
    name LIKE '[A-M]%' -- SQL Server, 以A到M开头的名字
    AND email LIKE '%@__%.__%'; -- 标准email模式

14. 条件和流程控制增强

1. CHOOSE - 索引选择

-- SQL Server
SELECT CHOOSE(2, 'First', 'Second', 'Third');  -- 返回 'Second'

2. 复杂CASE表达式

SELECT 
    employee_name,
    salary,
    CASE 
        WHEN salary <= (SELECT AVG(salary) FROM employees) THEN 'Below Average'
        WHEN salary <= (SELECT AVG(salary) + STDDEV(salary) FROM employees) THEN 'Average'
        WHEN salary <= (SELECT AVG(salary) + 2*STDDEV(salary) FROM employees) THEN 'Above Average'
        ELSE 'Exceptional'
    END as salary_category
FROM employees;

15. 表分析函数

1. PERCENT_RANK - 百分比排名

SELECT 
    name,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) as salary_percentile
FROM employees;

2. CUME_DIST - 累积分布

SELECT 
    name,
    salary,
    CUME_DIST() OVER (ORDER BY salary) as salary_distribution
FROM employees;

16. 实用复合函数示例

1. 年龄计算

-- MySQL
SELECT 
    name,
    birthdate,
    TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) as age,
    DATE_ADD(birthdate, 
            INTERVAL TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) YEAR) as last_birthday,
    DATE_ADD(birthdate, 
            INTERVAL TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) + 1 YEAR) as next_birthday
FROM employees;

2. 工龄分析

SELECT 
    name,
    hire_date,
    CASE 
        WHEN DATEDIFF(YEAR, hire_date, GETDATE()) < 2 THEN 'Junior'
        WHEN DATEDIFF(YEAR, hire_date, GETDATE()) < 5 THEN 'Intermediate'
        WHEN DATEDIFF(YEAR, hire_date, GETDATE()) < 10 THEN 'Senior'
        ELSE 'Expert'
    END as experience_level
FROM employees;

3. 薪资分析

WITH salary_stats AS (
    SELECT 
        department,
        AVG(salary) as avg_salary,
        STDDEV(salary) as salary_stddev
    FROM employees
    GROUP BY department
)
SELECT 
    e.name,
    e.department,
    e.salary,
    s.avg_salary,
    (e.salary - s.avg_salary) / s.salary_stddev as z_score,
    PERCENT_RANK() OVER (PARTITION BY e.department ORDER BY e.salary) as dept_percentile
FROM employees e
JOIN salary_stats s ON e.department = s.department;

4. 考勤分析

WITH daily_attendance AS (
    SELECT 
        employee_id,
        attendance_date,
        check_in_time,
        check_out_time,
        CASE 
            WHEN check_in_time > '09:00:00' THEN 'Late'
            WHEN check_out_time < '17:00:00' THEN 'Early Leave'
            ELSE 'Normal'
        END as attendance_status
    FROM attendance
)
SELECT 
    e.name,
    COUNT(*) as total_days,
    SUM(CASE WHEN a.attendance_status = 'Late' THEN 1 ELSE 0 END) as late_days,
    SUM(CASE WHEN a.attendance_status = 'Early Leave' THEN 1 ELSE 0 END) as early_leave_days,
    FORMAT(COUNT(*) * 1.0 / 
           (SELECT COUNT(DISTINCT attendance_date) FROM attendance), 'P') as attendance_rate
FROM employees e
JOIN daily_attendance a ON e.id = a.employee_id
GROUP BY e.name;

5. 销售分析

WITH monthly_sales AS (
    SELECT 
        YEAR(sale_date) as year,
        MONTH(sale_date) as month,
        SUM(amount) as total_sales,
        COUNT(DISTINCT customer_id) as customer_count
    FROM sales
    GROUP BY YEAR(sale_date), MONTH(sale_date)
)
SELECT 
    year,
    month,
    total_sales,
    customer_count,
    total_sales / customer_count as avg_customer_value,
    LAG(total_sales) OVER (ORDER BY year, month) as prev_month_sales,
    total_sales - LAG(total_sales) OVER (ORDER BY year, month) as sales_growth,
    FORMAT((total_sales - LAG(total_sales) OVER (ORDER BY year, month)) / 
           LAG(total_sales) OVER (ORDER BY year, month), 'P') as growth_rate
FROM monthly_sales;