sql函数笔记分享

最近在练习sql的一些语句，分享几个常用的函数。

1、字符串函数

CONCAT 函数：用于拼接字符串。比如在用户信息表中，有姓（first_name）和名（last_name）两个字段，要生成完整的姓名，可以使用CONCAT函数。假设表名为users，SQL 语句如下：

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM users;

SUBSTRING 函数：能够从字符串中提取子字符串。例如在地址信息中，假设地址字段（address）格式为 “省份 - 城市 - 区县 - 详细地址”，我们想提取城市名称。表名为addresses，SQL 语句可以这样写：

SELECT SUBSTRING(address, 3, LOCATE('-', address, 3) - 3) AS city
FROM addresses;

LENGTH 函数：返回字符串的长度。在处理用户昵称时，若要统计昵称长度，假设表名为users，昵称字段为nickname，SQL 语句如下：

SELECT nickname, LENGTH(nickname) AS length_of_nickname
FROM users;

UPPER 和 LOWER 函数：分别用于将字符串转换为大写和小写。在处理邮箱地址时，为了统一格式，将所有邮箱地址转换为小写，假设表名为users，邮箱字段为email，SQL 语句如下：

SELECT LOWER(email) AS lower_email
FROM users;

2、日期和时间函数

NOW 函数：返回当前的日期和时间。在记录操作日志时，需要记录操作发生的具体时间，假设日志表名为operation_log，有operation_id（操作 ID）、user_id（用户 ID）和operation_time（操作时间）字段，插入操作记录的 SQL 语句如下：

INSERT INTO operation_log (operation_id, user_id, operation_time)
VALUES (1, 101, NOW());

CURDATE 函数：只返回当前日期。在电商系统中记录订单下单日期，假设订单表名为orders，有order_id（订单 ID）、customer_id（客户 ID）和order_date（订单日期）字段，插入订单记录时可使用CURDATE函数，SQL 语句如下：

INSERT INTO orders (order_id, customer_id, order_date)
VALUES (1001, 501, CURDATE());

DATE_ADD 函数：用于在日期上添加指定的时间间隔。在项目管理系统中，已知项目开始日期，要计算项目预计结束日期（假设项目周期为 30 天），假设项目表名为projects，有project_id（项目 ID）、project_name（项目名称）、start_date（开始日期）和end_date（结束日期）字段，更新结束日期的 SQL 语句如下：

UPDATE projects
SET end_date = DATE_ADD(start_date, INTERVAL 30 DAY)
WHERE project_id = 1;

DATEDIFF 函数：计算两个日期之间的差值。在人力资源系统中计算员工的在职天数，假设员工表名为employees，有employee_id（员工 ID）、name（姓名）、hire_date（入职日期）字段，SQL 语句如下：

SELECT employee_id, name, DATEDIFF(CURDATE(), hire_date) AS days_worked
FROM employees;

YEAR 函数：提取日期中的年份。在统计销售数据时，按年份统计销售额，假设销售表名为sales，有sale_id（销售 ID）、sale_date（销售日期）、amount（销售金额）字段，SQL 语句如下：

SELECT YEAR(sale_date) AS sale_year, SUM(amount) AS total_amount
FROM sales
GROUP BY YEAR(sale_date);

3、聚合函数

COUNT 函数：用于计算行数。在员工表中统计员工数量，假设员工表名为employees，SQL 语句如下：

SELECT COUNT(*) AS employee_count
FROM employees;

如果要统计某个部门的员工数量，假设部门字段为department，统计销售部门的员工数量，SQL 语句如下：

SELECT COUNT(*) AS sales_department_count
FROM employees
WHERE department = '销售部';

SUM 函数：用于计算数值列的总和。在销售记录表中计算总销售额，假设销售记录表名为sales，销售额字段为amount，SQL 语句如下：

SELECT SUM(amount) AS total_amount
FROM sales;

AVG 函数：用于计算数值列的平均值。计算员工的平均薪资，假设员工表名为employees，薪资字段为salary，SQL 语句如下：

SELECT AVG(salary) AS average_salary
FROM employees;

MAX 函数：返回数值列的最大值。找出员工中的最高薪资，假设员工表名为employees，薪资字段为salary，SQL 语句如下：

SELECT MAX(salary) AS max_salary
FROM employees;

MIN 函数：返回数值列的最小值。找出员工中的最低薪资，假设员工表名为employees，薪资字段为salary，SQL 语句如下：

SELECT MIN(salary) AS min_salary
FROM employees;

4、窗口函数

窗口函数是 SQL 中的高级特性，能在不改变查询结果集行数的情况下，对每一行执行聚合计算或其他复杂计算。它通常与 OVER () 子句一起使用，可定义窗口或分区，并在其上执行计算 。

语法结构
<窗口函数> OVER (
    [PARTITION BY <分组列>]
    [ORDER BY <排序列>]
    [ROWS 或 RANGE <窗口框架定义>]
)

PARTITION BY 子句：用于将数据分成不同分区，窗口函数在每个分区内执行，类似 GROUP BY，但不合并行。
ORDER BY 子句：定义数据排序方式，决定窗口函数计算顺序。
ROWS BETWEEN 子句：指定窗口范围，可以是行数、区间等。
常用窗口函数示例
假设我们有员工表employees，包含字段：employee_id（员工 ID）、name（姓名）、department_name（部门名称）、salary（薪资） 。

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(255),
    department_name VARCHAR(255),
    salary DECIMAL(10,2)
);

INSERT INTO employees (employee_id, department_name, name, salary) VALUES
(1, '财务部', '张三',30000),
(2, '财务部', '李四',25000),
(3, '市场部', '王五',40000),
(4, '市场部', '赵六',35000),
(5, '市场部', '孙七',50000),
(6, '技术部', '周八',45000),
(7, '技术部', '钱九',60000),
(8, '技术部', '吴十',55000);

聚合窗口函数：计算每个部门的薪资总和、平均薪资、员工数量、最高薪资和最低薪资。

SELECT
    employee_id,
    name,
    department_name,
    salary,
    SUM(salary) OVER (PARTITION BY department_name) AS total_salary,
    AVG(salary) OVER (PARTITION BY department_name) AS average_salary,
    COUNT(*) OVER (PARTITION BY department_name) AS employee_count,
    MAX(salary) OVER (PARTITION BY department_name) AS max_salary,
    MIN(salary) OVER (PARTITION BY department_name) AS min_salary
FROM employees;

排名窗口函数
ROW_NUMBER()：为窗口内每一行分配唯一序号，序号连续且不重复。为每个部门的员工按薪资降序分配唯一行号。

SELECT
    employee_id,
    name,
    department_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_name ORDER BY salary DESC) AS salary_rank
FROM employees;

RANK()：排名函数，允许并列名次，名次后面会出现空位。
DENSE_RANK()：排名函数，允许并列名次，名次后面不会空出位置，即序号连续。
假设插入一些薪资相同的数据：

INSERT INTO employees (employee_id, name, department_name, salary) VALUES
(9, 'Alice', '财务部',70000),
(10, 'Bob', '财务部',60000),
(11, 'Charlie', '财务部',60000), 
(12, 'David', '市场部',80000),
(13, 'Eve', '市场部',80000), 
(14, 'Frank', '市场部',50000);

执行包含RANK()和DENSE_RANK()函数的查询：

SELECT
    employee_id,
    name,
    department_name,
    salary,
    RANK() OVER (PARTITION BY department_name ORDER BY salary DESC) AS salary_rank,
    DENSE_RANK() OVER (PARTITION BY department_name ORDER BY salary DESC) AS dense_salary_rank
FROM employees
ORDER BY department_name, salary DESC;

分组窗口函数：NTILE()将窗口内行分为指定数量组，每组行数尽可能相等。将每个部门员工按薪资分为 4 组。

SELECT
    employee_id,
    name,
    department_name,
    salary,
    NTILE(4) OVER (PARTITION BY department_name ORDER BY salary) AS salary_group
FROM employees;

窗口函数在处理复杂数据分析任务时非常强大，比如统计分析、报表生成、排名、分组等场景都能发挥重要作用 。