PostgreSQL 的内置函数_pgsql 包含函数
赞
踩
PostgreSQL 提供了大量的内置函数，这些函数可以在查询中用于处理数据、进行计算和转换。以下是一些常用的 PostgreSQL 函数：
-
字符串函数：
-
concat(string1, string2, ...): 连接两个或多个字符串。SELECT concat(first_name, ' ', last_name) FROM users;注意：如果任何参数为 null，结果也将为 null。可以使用
coalesce函数避免 null 值。 -
length(string): 返回字符串的长度。SELECT length(email) FROM users WHERE length(email) > 30;注意：对于多字节字符集，如 UTF-8，
length可能不会返回实际的字符数，而是字节数。可以使用char_length获取字符数。 -
substring(string from start for length): 提取字符串的子串。SELECT substring(email from '@' for char_length(email)) FROM users;注意：PostgreSQL 中的字符串索引从 1 开始。如果
start是负数，则从字符串末尾开始计数。 -
upper(string): 将字符串转换为大写。SELECT upper(first_name) FROM users;注意：对于非 ASCII 字符，这可能不适用。可以考虑使用
unaccent函数去除变音符号。 -
lower(string): 将字符串转换为小写。SELECT lower(first_name) FROM users;注意：同
upper，对于非 ASCII 字符，可能需要额外的处理。
-
-
数值函数：
-
round(numeric, decimals): 将数值四舍五入到指定的小数位数。SELECT round(price, 2) FROM products;注意：
round可能会进行银行家舍入（即四舍六入五成双）。 -
ceil(numeric): 返回大于或等于数值的最小整数。SELECT ceil(3.14);注意：
ceil只接受正数参数。 -
floor(numeric): 返回小于或等于数值的最大整数。SELECT floor(3.14);注意：
floor只接受正数参数。 -
sqrt(numeric): 返回数值的平方根。SELECT sqrt(16);注意：如果参数是负数，将返回错误。
-
-
日期和时间函数：
-
current_date: 返回当前的日期。SELECT current_date;注意：这个函数返回的是不带时区的日期。
-
current_time: 返回当前的时间。SELECT current_time;注意：这个函数返回的是不带时区的时间。
-
current_timestamp: 返回当前的日期和时间。SELECT current_timestamp;注意：这个函数返回的是带时区的日期和时间。
-
extract(field from source): 从日期/时间源中提取特定的字段。SELECT extract(year from created_at) FROM posts;注意：
field可以是年、月、日、小时、分钟等。 -
age(date): 返回从一个日期到现在的年龄。SELECT age(birthdate) FROM users;注意：这个函数假设当前日期是今天的日期。
-
now(): 返回当前的日期和时间（带时区）。SELECT now();注意：
now()返回的是带时区的时间戳。
-
-
聚合函数：
-
count(*): 计算记录数。SELECT count(*) FROM users;注意：使用
count(*)而不是count(column)可以避免排除 null 值。 -
sum(expression): 计算数值列的总和。SELECT sum(price) FROM orders;注意：如果列中有 null 值，它们将被忽略。
-
avg(expression): 计算数值列的平均值。SELECT avg(score) FROM games;注意：如果列中有 null 值，它们将被忽略。
-
min(expression): 找出数值列的最小值。SELECT min(score) FROM quizzes;注意：
min可以用于文本列，在这种情况下，它将返回字典顺序最小的值。 -
max(expression): 找出数值列的最大值。SELECT max(score) FROM quizzes;注意：
max也可以用于文本列，在这种情况下它将返回字典顺序最大的值。
-
-
窗口函数（又名分析函数）：
-
lag(expression, offset, default): 访问结果集中当前行之前的行的值。- SELECT score, lag(score, 1) OVER (ORDER BY id) as prev_score
- FROM scores;
注意：
lag()可以用来比较当前行与前行的值。 -
lead(expression, offset, default): 访问结果集中当前行之后的行的值。- SELECT score, lead(score, 1) OVER (ORDER BY id) as next_score
- FROM scores;
注意：
lead()可以用来比较当前行与后续行的值。 -
dense_rank(): 与rank()类似，但排名中没有间隔。- SELECT dense_rank() OVER (ORDER BY score DESC) as rank, name, score
- FROM players;
注意：
dense_rank()在遇到相同值时不会跳过排名，排名是连续的。 -
rank(): 为结果集中的每一行分配一个唯一的排名。- SELECT rank() OVER (ORDER BY score DESC) as rank, name, score
- FROM players;
注意：
rank()在遇到相同值时会分配相同的排名，并跳过后面的排名。 -
row_number(): 为结果集中的每一行分配一个唯一的连续整数。- SELECT row_number() OVER (ORDER BY score DESC) as rank, name, score
- FROM players;
注意：
row_number()不考虑分组，它在整个结果集上分配唯一的行号。
-
-
条件表达式：
-
nullif(expression, value): 如果表达式等于值，则返回 null。SELECT nullif(score, 0) as valid_score FROM results;注意：
nullif用于将特定的值（如 0）转换为 null。 -
coalesce(expression, ...): 返回参数列表中的第一个非空表达式。SELECT coalesce(email, phone) as contact FROM users;注意：
coalesce可以接受多个参数，如果所有参数都是 null，结果将是 null。 -
case when condition then result else alternative end: 根据条件返回不同的结果。- SELECT name, age,
- case
- when age < 18 then 'Minor'
- when age >= 18 and age < 65 then 'Adult'
- else 'Senior'
- end as category
- FROM users;
注意：
case表达式可以用于多个条件的复杂逻辑。
-
-
JSON和数组函数：
-
array_to_string(array, delimiter): 将数组的所有元素连接成一个字符串。SELECT array_to_string(ingredients, ', ') FROM recipes;注意：这个函数对于将数组转换为可读的字符串非常有用。
-
array_length(array, dimension): 返回数组的长度。SELECT array_length(tags, 1) FROM articles;注意：
dimension参数通常为 1，因为 PostgreSQL 数组只有一个维度。 -
json_array_elements_text(json): 将 JSON 数组扩展为一系列文本元素。SELECT json_array_elements_text(likes) FROM posts;注意：这个函数适用于 JSON 数组，它会为每个数组元素返回一行。
-
json_object_keys(json): 返回 JSON 对象中的所有键。SELECT json_object_keys(metadata) FROM datasets;注意：这个函数只适用于 JSON 对象，不适用于数组。
-
使用这些函数时，需要注意以下几点：
- 确保你使用的函数适用于你的数据类型和业务逻辑。
- 考虑函数的性能影响，尤其是在处理大型数据集时。
- 对于涉及日期和时间的函数，要注意时区的影响。
- 在使用聚合函数和窗口函数时，要注意它们的分组和排序规则。
- 在使用条件表达式时，要确保逻辑覆盖了所有可能的情况。
- 对于 JSON 和数组函数，要熟悉 JSON 和数组在 PostgreSQL 中的特殊处理。
在实际应用中，你可能需要根据具体的业务需求和数据结构来选择合适的函数，并对其进行适当的调整。
