PostgreSQL 的内置函数

PostgreSQL 提供了大量的内置函数，这些函数可以在查询中用于处理数据、进行计算和转换。以下是一些常用的 PostgreSQL 函数：

1. 字符串函数：

   • 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 字符，可能需要额外的处理。

2. 数值函数：

   • 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);
     

     注意：如果参数是负数，将返回错误。

3. 日期和时间函数：

   • 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() 返回的是带时区的时间戳。

4. 聚合函数：

   • 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 也可以用于文本列，在这种情况下它将返回字典顺序最大的值。

5. 窗口函数（又名分析函数）：

   • lag(expression, offset, default): 访问结果集中当前行之前的行的值。

     1. SELECT score, lag(score, 1) OVER (ORDER BY id) as prev_score

     2. FROM scores;

     注意：lag() 可以用来比较当前行与前行的值。

   • lead(expression, offset, default): 访问结果集中当前行之后的行的值。

     1. SELECT score, lead(score, 1) OVER (ORDER BY id) as next_score

     2. FROM scores;

     注意：lead() 可以用来比较当前行与后续行的值。

   • dense_rank(): 与 rank() 类似，但排名中没有间隔。

     1. SELECT dense_rank() OVER (ORDER BY score DESC) as rank, name, score

     2. FROM players;

     注意：dense_rank() 在遇到相同值时不会跳过排名，排名是连续的。

   • rank(): 为结果集中的每一行分配一个唯一的排名。

     1. SELECT rank() OVER (ORDER BY score DESC) as rank, name, score

     2. FROM players;

     注意：rank() 在遇到相同值时会分配相同的排名，并跳过后面的排名。

   • row_number(): 为结果集中的每一行分配一个唯一的连续整数。

     1. SELECT row_number() OVER (ORDER BY score DESC) as rank, name, score

     2. FROM players;

     注意：row_number() 不考虑分组，它在整个结果集上分配唯一的行号。

6. 条件表达式：

   • 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: 根据条件返回不同的结果。

     1. SELECT name, age,

     2. case

     3. when age < 18 then 'Minor'

     4. when age >= 18 and age < 65 then 'Adult'

     5. else 'Senior'

     6. end as category

     7. FROM users;

     注意：case 表达式可以用于多个条件的复杂逻辑。

7. 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 中的特殊处理。

在实际应用中，你可能需要根据具体的业务需求和数据结构来选择合适的函数，并对其进行适当的调整。