PostgreSQL常用函数用法

最新推荐文章于 2024-09-04 15:15:01 发布

TechCraft

最新推荐文章于 2024-09-04 15:15:01 发布

阅读量1.3k

点赞数 33

文章标签： postgresql 数据库 PostgreSQL 运维大数据

本文链接：https://blog.csdn.net/jinhope/article/details/141845515

版权

在PostgreSQL中，函数是处理和操作数据的强大工具。以下是一些常用函数的用法示例。

1. 字符串函数

字符串函数用于操作和处理文本数据，常见操作包括字符串连接、截取、替换、转换大小写等。

LENGTH: 返回字符串的长度。

-- 查询语句
SELECT LENGTH('PostgreSQL') AS string_length;

-- 结果
string_length 
--------------
10

CONCAT: 将多个字符串连接为一个字符串。

-- 查询语句
SELECT CONCAT('Postgre', 'SQL') AS full_string;

-- 结果
full_string 
-------------
PostgreSQL

SUBSTRING: 从指定字符串中提取子字符串。

-- 查询语句
SELECT SUBSTRING('PostgreSQL', 1, 6) AS short_string;

-- 结果
short_string 
--------------
Postgre

REPLACE: 将字符串中的子字符串替换为另一个字符串。

-- 查询语句
SELECT REPLACE('PostgreSQL', 'SQL', 'DB') AS replaced_string;

-- 结果
replaced_string 
-----------------
PostgreDB

UPPER: 将字符串转换为大写。

-- 查询语句
SELECT UPPER('postgresql') AS upper_string;

-- 结果
upper_string 
--------------
POSTGRESQL

LOWER: 将字符串转换为小写。

-- 查询语句
SELECT LOWER('PostgreSQL') AS lower_string;

-- 结果
lower_string 
--------------
postgresql

TRIM: 删除字符串开头和结尾的空白或指定字符。

-- 查询语句
SELECT TRIM(BOTH ' ' FROM '  PostgreSQL  ') AS trimmed_string;

-- 结果
trimmed_string 
----------------
PostgreSQL

POSITION: 查找子字符串在另一个字符串中的位置（起始位置）。

-- 查询语句
SELECT POSITION('SQL' IN 'PostgreSQL') AS substring_position;

-- 结果
substring_position 
--------------------
8

2. 日期和时间函数

日期和时间函数用于处理日期、时间和时间戳数据，支持时间差计算、格式化输出、提取特定时间部分等。

CURRENT_DATE: 返回当前日期。

-- 查询语句
SELECT CURRENT_DATE AS today;

-- 结果
today
------------
2024-09-03

CURRENT_TIME: 返回当前时间（不包含日期）。

-- 查询语句
SELECT CURRENT_TIME AS now_time;

-- 结果
now_time  
-------------
14:30:00

CURRENT_TIMESTAMP: 返回当前日期和时间（带有时区信息）。

-- 查询语句
SELECT CURRENT_TIMESTAMP AS current_datetime;

-- 结果
current_datetime
-----------------------
2024-09-03 14:30:00+00

DATE_TRUNC: 截断日期或时间戳，保留指定的时间单位（如年、月、日）。

-- 查询语句
SELECT DATE_TRUNC('month', NOW()) AS month_start;

-- 结果
month_start
----------------------
2024-09-01 00:00:00

EXTRACT: 从日期或时间戳中提取特定部分（如年、月、日、小时等）。

-- 查询语句
SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS current_year;

-- 结果
current_year 
--------------
2024

AGE: 计算两个时间戳之间的差值，并以年、月、日的形式显示。

-- 查询语句
SELECT AGE('2020-01-01') AS time_diff;

-- 结果
time_diff
------------------
4 years 8 mons

TO_CHAR: 将日期或数字转换为指定格式的字符串。

-- 查询语句
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD') AS formatted_date;

-- 结果
formatted_date
----------------
2024-09-03

DATE_PART: 类似于EXTRACT，用于提取日期或时间戳的特定部分，以浮点数形式返回。

-- 查询语句
SELECT DATE_PART('hour', CURRENT_TIMESTAMP) AS current_hour;

-- 结果
current_hour
--------------
14

3. 数学函数

数学函数用于数值计算，包括四舍五入、取整、求平方根等操作，适用于统计分析、财务计算等场景。

ROUND: 对数字进行四舍五入，保留指定的小数位数。

-- 查询语句
SELECT ROUND(123.456, 2) AS rounded_value;

-- 结果
rounded_value 
---------------
123.46

FLOOR: 返回小于或等于给定数值的最大整数。

-- 查询语句
SELECT FLOOR(123.456) AS floor_value;

-- 结果
floor_value 
-------------
123

CEIL: 返回大于或等于给定数值的最小整数。

-- 查询语句
SELECT CEIL(123.456) AS ceil_value;

-- 结果
ceil_value 
------------
124

ABS: 返回数值的绝对值。

-- 查询语句
SELECT ABS(-123.456) AS absolute_value;

-- 结果
absolute_value 
----------------
123.456

MOD: 返回两个数相除的余数。

-- 查询语句
SELECT MOD(10, 3) AS remainder;

-- 结果
remainder 
-----------
1

POWER: 返回指定数值的指定次幂。

-- 查询语句
SELECT POWER(2, 3) AS result;

-- 结果
result 
--------
8

SQRT: 返回数值的平方根。

-- 查询语句
SELECT SQRT(16) AS square_root;

-- 结果
square_root 
-------------
4

EXP: 返回e的指定次幂。

-- 查询语句
SELECT EXP(1) AS e_value;

-- 结果
e_value 
---------
2.71828

LN: 返回数值的自然对数（底数为e）。

-- 查询语句
SELECT LN(2.71828) AS natural_log;

-- 结果
natural_log 
-------------
1.0000067

LOG: 返回指定基数的对数。

-- 查询语句
SELECT LOG(10, 100) AS log_value;

-- 结果
log_value 
-----------
2

SIGN: 返回数值的符号，正数返回1，负数返回-1，零返回0。

-- 查询语句
SELECT SIGN(-123.456) AS sign_value;

-- 结果
sign_value 
------------
-1

4. 控制流函数

控制流函数用于处理NULL值，选择最大值或最小值等。这些函数在处理数据不确定性或条件判断时非常有用。

COALESCE: 返回第一个非NULL的值，如果所有值都是NULL，则返回NULL。

-- 查询语句
SELECT COALESCE(NULL, NULL, 'default') AS result_value;

-- 结果
result_value 
--------------
default

GREATEST: 返回提供的多个值中的最大值。

-- 查询语句
SELECT GREATEST(10, 20, 30, 15) AS max_value;

-- 结果
max_value 
-----------
30

LEAST: 返回提供的多个值中的最小值。

-- 查询语句
SELECT LEAST(10, 20, 30, 15) AS min_value;

-- 结果
min_value 
-----------
10

5. 随机数与序列函数

随机数与序列函数用于生成随机数、自动递增序列等操作，适用于数据采样、模拟测试等场景。

RANDOM: 生成一个0到1之间的随机浮点数。

-- 查询语句
SELECT RANDOM() AS random_value;

-- 结果
random_value 
--------------
0.345678

SERIAL: 用于生成自动递增的整数序列，通常用于主键字段。

-- 查询语句
CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

-- 插入数据
INSERT INTO test_table (name) VALUES ('Alice'), ('Bob');

-- 查询结果
SELECT * FROM test_table;

-- 结果
id | name 
----+------
1  | Alice
2  | Bob

GENERATE_SERIES: 生成一个指定范围的整数序列。

-- 查询语句
SELECT * FROM GENERATE_SERIES(1, 5);

-- 结果
generate_series 
----------------
1
2
3
4
5

继续对PostgreSQL中的常用函数进行分类讲解，以下是更多的函数类别以及对应的函数用法示例。每个示例都包含了SQL查询语句和预期的查询结果，帮助您更直观地理解这些函数的作用和用法。

6. 聚合函数

聚合函数用于对一组值执行计算并返回单一的结果，常用于统计和数据分析。例如，计算总和、平均值、计数、最大值和最小值等。这些函数在报告生成、数据汇总等场景中非常实用。

COUNT: 计算行数或非NULL值的数量。

-- 查询语句：计算表中员工的总数
SELECT COUNT(*) AS total_employees FROM employees;

-- 结果
total_employees
----------------
50

SUM: 计算数值列的总和。

-- 查询语句：计算所有员工的工资总和
SELECT SUM(salary) AS total_salary FROM employees;

-- 结果
total_salary
-------------
500000

AVG: 计算数值列的平均值。

-- 查询语句：计算员工工资的平均值
SELECT AVG(salary) AS average_salary FROM employees;

-- 结果
average_salary
---------------
10000

MAX: 返回数值列中的最大值。

-- 查询语句：获取最高工资
SELECT MAX(salary) AS highest_salary FROM employees;

-- 结果
highest_salary
---------------
20000

MIN: 返回数值列中的最小值。

-- 查询语句：获取最低工资
SELECT MIN(salary) AS lowest_salary FROM employees;

-- 结果
lowest_salary
--------------
5000

STRING_AGG: 将字符串值连接成一个字符串，并使用指定的分隔符。

-- 查询语句：将所有部门名称连接成一个字符串
SELECT STRING_AGG(department_name, ', ') AS departments FROM departments;

-- 结果
departments
-------------------------------
Sales, Marketing, HR, IT, Finance

ARRAY_AGG: 将输入值收集到一个数组中。

-- 查询语句：将所有员工的姓名收集到一个数组中
SELECT ARRAY_AGG(employee_name) AS employee_names FROM employees;

-- 结果
employee_names
--------------------------------------------
{Alice,Bob,Charlie,David,Eva,...}

7. JSON函数

JSON函数用于在PostgreSQL中处理JSON数据。随着NoSQL和JSON数据格式的流行，PostgreSQL提供了丰富的函数来存储、查询和操作JSON数据，适用于处理复杂和嵌套的数据结构。

TO_JSON: 将任意行或数组转换为JSON格式。

-- 查询语句：将文本转换为JSON格式
SELECT TO_JSON('PostgreSQL') AS json_value;

-- 结果
json_value
------------
"PostgreSQL"

TO_JSONB: 将任意行或数组转换为二进制JSON（JSONB）格式，具有更好的性能。

-- 查询语句：将数组转换为JSONB格式
SELECT TO_JSONB(ARRAY[1, 2, 3]) AS jsonb_value;

-- 结果
jsonb_value
-------------
[1, 2, 3]

JSON_BUILD_OBJECT: 构建一个JSON对象。

-- 查询语句：创建一个JSON对象表示员工信息
SELECT JSON_BUILD_OBJECT(
    'id', 1,
    'name', 'Alice',
    'department', 'HR'
) AS employee_json;

-- 结果
employee_json
-------------------------------------------
{"id": 1, "name": "Alice", "department": "HR"}

JSON_EXTRACT_PATH_TEXT: 从JSON对象中提取指定路径的文本值。

-- 查询语句：从JSON数据中提取员工姓名
SELECT JSON_EXTRACT_PATH_TEXT('{"id":1,"name":"Alice"}', 'name') AS employee_name;

-- 结果
employee_name
--------------
Alice

JSONB_AGG: 将多行数据聚合为JSONB数组。

-- 查询语句：将所有员工信息聚合为JSONB数组
SELECT JSONB_AGG(
    JSONB_BUILD_OBJECT(
        'id', id,
        'name', name,
        'department', department
    )
) AS employees_json
FROM employees;

-- 结果
employees_json
-----------------------------------------------------------------------
[
  {"id": 1, "name": "Alice", "department": "HR"},
  {"id": 2, "name": "Bob", "department": "IT"},
  ...
]

JSONB_SET: 更新JSONB对象中的指定键值对。

-- 查询语句：更新员工JSON对象中的部门信息
SELECT JSONB_SET('{"id":1,"name":"Alice","department":"HR"}', '{department}', '"Finance"') AS updated_json;

-- 结果
updated_json
-----------------------------------------------
{"id": 1, "name": "Alice", "department": "Finance"}

8. 数组函数

数组函数用于创建、处理和查询数组类型的数据。在PostgreSQL中，数组是一等公民，可以直接存储和操作多值数据，适用于需要存储列表或集合的场景。

ARRAY: 创建一个数组。

-- 查询语句：创建一个整数数组
SELECT ARRAY[1, 2, 3, 4, 5] AS number_array;

-- 结果
number_array
----------------
{1,2,3,4,5}

ARRAY_APPEND: 向数组末尾添加元素。

-- 查询语句：在数组末尾添加一个元素
SELECT ARRAY_APPEND(ARRAY[1, 2, 3], 4) AS updated_array;

-- 结果
updated_array
---------------
{1,2,3,4}

ARRAY_PREPEND: 向数组开头添加元素。

-- 查询语句：在数组开头添加一个元素
SELECT ARRAY_PREPEND(0, ARRAY[1, 2, 3]) AS updated_array;

-- 结果
updated_array
---------------
{0,1,2,3}

UNNEST: 将数组展开为多行。

-- 查询语句：将数组元素展开为多行
SELECT UNNEST(ARRAY['Alice', 'Bob', 'Charlie']) AS employee_name;

-- 结果
employee_name
--------------
Alice
Bob
Charlie

ARRAY_AGG: 将多行数据聚合为数组。

-- 查询语句：将所有部门名称聚合为一个数组
SELECT ARRAY_AGG(department_name) AS departments_array FROM departments;

-- 结果
departments_array
-------------------------------
{Sales,Marketing,HR,IT,Finance}

CARDINALITY: 返回数组的元素数量。

-- 查询语句：获取数组的元素数量
SELECT CARDINALITY(ARRAY[1, 2, 3, 4, 5]) AS array_size;

-- 结果
array_size
-----------
5

ARRAY_CONTAINS: 检查数组是否包含指定元素。

-- 查询语句：检查数组是否包含元素3
SELECT 3 = ANY(ARRAY[1, 2, 3, 4, 5]) AS contains_element;

-- 结果
contains_element
----------------
t

9. 类型转换函数

类型转换函数用于在不同数据类型之间进行转换，确保数据在存储和处理时具有正确的类型。这些函数在数据清洗和预处理过程中非常重要。

CAST: 将一个值显式转换为指定类型。

-- 查询语句：将字符串转换为整数
SELECT CAST('123' AS INTEGER) AS integer_value;

-- 结果
integer_value
--------------
123

::（类型转换运算符）: 另一种类型转换的简写形式。

-- 查询语句：将字符串转换为日期
SELECT '2024-09-03'::DATE AS date_value;

-- 结果
date_value
------------
2024-09-03

TO_CHAR: 将数字或日期转换为字符串，支持格式化。

-- 查询语句：将数字格式化为带有千位分隔符的字符串
SELECT TO_CHAR(1234567.89, 'FM9,999,999.00') AS formatted_number;

-- 结果
formatted_number
-----------------
1,234,567.89

TO_NUMBER: 将字符串转换为数字，支持解析格式化的字符串。

-- 查询语句：将格式化字符串转换为数字
SELECT TO_NUMBER('1,234,567.89', 'FM9,999,999.00') AS numeric_value;

-- 结果
numeric_value
--------------
1234567.89

TO_TIMESTAMP: 将字符串转换为时间戳，支持指定格式。

-- 查询语句：将字符串转换为时间戳
SELECT TO_TIMESTAMP('2024-09-03 14:30:00', 'YYYY-MM-DD HH24:MI:SS') AS timestamp_value;

-- 结果
timestamp_value
------------------------
2024-09-03 14:30:00+00

TO_DATE: 将字符串转换为日期，支持指定格式。

-- 查询语句：将字符串转换为日期
SELECT TO_DATE('03-09-2024', 'DD-MM-YYYY') AS date_value;

-- 结果
date_value
------------
2024-09-03

10. 条件函数

条件函数用于在SQL查询中实现条件逻辑，类似于编程语言中的if-else结构。这些函数可以根据不同的条件返回不同的结果，增强查询的灵活性和智能性。

CASE WHEN: 实现条件判断，根据条件返回不同的值。

-- 查询语句：根据员工工资等级返回相应的级别描述
SELECT
  employee_name,
  salary,
  CASE
    WHEN salary >= 15000 THEN 'High'
    WHEN salary >= 10000 THEN 'Medium'
    ELSE 'Low'
  END AS salary_level
FROM employees;

-- 结果
employee_name | salary | salary_level
--------------+--------+--------------
Alice         | 16000  | High
Bob           | 11000  | Medium
Charlie       | 8000   | Low

NULLIF: 如果两个参数相等，返回NULL，否则返回第一个参数。

-- 查询语句：如果销售额为0，返回NULL，否则返回销售额
SELECT NULLIF(sales, 0) AS adjusted_sales FROM sales_data;

-- 结果
adjusted_sales
---------------
NULL
5000
10000

COALESCE: 返回参数列表中第一个非NULL的值。

-- 查询语句：获取员工的联系邮箱，如果没有则使用默认邮箱
SELECT COALESCE(personal_email, work_email, 'noemail@example.com') AS contact_email FROM employees;

-- 结果
contact_email
----------------------
alice@personal.com
bob@work.com
noemail@example.com

GREATEST: 返回参数列表中的最大值。

-- 查询语句：获取每个员工的最高评分
SELECT employee_name, GREATEST(score1, score2, score3) AS highest_score FROM performance_reviews;

-- 结果
employee_name | highest_score
--------------+---------------
Alice         | 95
Bob           | 88
Charlie       | 92

LEAST: 返回参数列表中的最小值。

-- 查询语句：获取每个员工的最低评分
SELECT employee_name, LEAST(score1, score2, score3) AS lowest_score FROM performance_reviews;

-- 结果
employee_name | lowest_score
--------------+--------------
Alice         | 85
Bob           | 78
Charlie       | 80