工作中常用函数详解与示例-PostgreSQL(其他数据库可能函数不一致)

PostgreSQL 常用函数详解与示例

以下是对提供的六个 PostgreSQL 表达式的详细分解说明，包括每个函数的作用、语法和示例。
1. COALESCE 函数

作用
返回参数列表中第一个非空的值。通常用于处理可能为 NULL 的字段，并提供回退默认值。
语法

1. COALESCE(value1, value2, ..., fallback_value)

复制代码

用例解释

1. COALESCE(dim.categoryname_en, 'NOIDENTIFY') AS "Category"

复制代码

如果 dim.categoryname_en 字段的值为 NULL，则用字符串 'NOIDENTIFY' 替代，否则使用其原有值。
独立示例

1. SELECT
2.     COALESCE(NULL, '默认值'),        -- 输出: 默认值
3.     COALESCE('实际值', '默认值');     -- 输出: 实际值

复制代码

2. ROUND & COALESCE & SUM 组合运算

各函数作用

• SUM(): 对指定列的值进行求和。
  
• COALESCE(): 确保求和结果不为 NULL（如果所有值都是 NULL 或无匹配行，则返回 0）。
  
• ROUND(number, precision): 将数值四舍五入到指定的小数位数。
  

用例解释

1. ROUND(COALESCE(SUM(h.uldata + h.dldata), 0) * 8.0 / 1024 / 1024 / 1024 / 86400, 8) AS "Total Bandwidth"

复制代码

• SUM(h.uldata + h.dldata): 计算上行数据量(uldata)和下行数据量(dldata)的总和（单位：字节）。
  
• COALESCE(..., 0): 防止总和为 NULL。
  
• ... * 8.0 / 1024 / 1024 / 1024 / 86400:
  
  
  
  • * 8.0: 将字节(Byte)转换为比特(bit)。
    
  • / 1024^3: 将比特(bit)转换为千兆比特(Gb)。（因为 1024 Bytes = 1 KB, 1024 KB = 1 MB, 1024 MB = 1 GB）
    
  • / 86400: 除以一天的秒数（24 * 60 * 60），将总量转换为平均速率（Gbps，即 Gb per second）。
    
  
  
• ROUND(..., 8): 将最终结果四舍五入保留 8 位小数。
  

简化公式
总带宽 (Gbps) ≈ (总字节数 * 8) / (1024³) / 86400
独立示例

1. -- 假设某日总流量为 1 TB
2. SELECT ROUND((1073741824000 * 8.0) / 1024 / 1024 / 1024 / 86400, 8) AS sample_bandwidth_gbps;
3. -- 计算结果约为 0.099 Gbps

复制代码

3. DATE_TRUNC 函数

作用
根据指定的精度（如 'year', 'quarter', 'month', 'week', 'day' 等）“截断”时间戳，返回一个新的时间戳。常用于按时间段进行分组统计。
语法

1. DATE_TRUNC('precision', source_timestamp)

复制代码

用例解释

1. DATE_TRUNC('week', clttime) as clttime

复制代码

将 clttime 时间戳截断到它所在周的星期一（ISO 周的开始是星期一），时间部分变为 00:00:00。例如，2023-10-15 14:30:00 会被截断为 2023-10-09 00:00:00。
独立示例

1. SELECT
2.     DATE_TRUNC('hour', TIMESTAMP '2023-10-15 14:30:25'),   -- 输出: 2023-10-15 14:00:00
3.     DATE_TRUNC('day', TIMESTAMP '2023-10-15 14:30:25');    -- 输出: 2023-10-15 00:00:00

复制代码

4. SUM 简单聚合

作用
对指定列的所有值进行求和。
用例解释

1. SUM(uldata) as uldata_sum

复制代码

直接计算 uldata（上行数据量）列的总和。
独立示例

1. SELECT SUM(salary) AS total_salary FROM employees;

复制代码

5. 条件聚合：SUM + CASE WHEN

作用
实现条件求和。只对满足特定条件的行的值进行累加。
用例解释

1. SUM(CASE WHEN uldata > 500 * 1024 THEN dwulduration ELSE 0 END) as ldwulduration_sum

复制代码

对于每一行数据：

• 如果该行的上行数据量 (uldata) 大于 500 KB（即 500 * 1024 字节），则将 dwulduration 的值计入总和。
  
• 否则，计入 0。
  

这相当于只汇总“大流量上传事件”的相关时长。
独立示例

1. -- 计算销售额超过1000元的订单总额
2. SELECT SUM(CASE WHEN amount > 1000 THEN amount ELSE 0 END) AS large_order_total FROM orders;

复制代码

6. 条件去重计数：COUNT(DISTINCT ...) + CASE WHEN

作用
统计满足特定条件的唯一值的个数。
用例解释

1. COUNT(DISTINCT CASE WHEN imei IS NOT NULL AND category IS NOT NULL THEN CONCAT(imei, '_', CAST(category AS VARCHAR)) END) as ctgterminals_count

复制代码

• CASE WHEN: 筛选出 imei 和 category 均不为 NULL 的行。
  
• CONCAT(imei, '_', CAST(category AS VARCHAR)): 对于符合条件的行，将设备标识 imei 和类别 category 拼接成一个唯一的字符串（例如 "IMEI12345_1001"），以代表一个“具备类别的终端”。
  
• COUNT(DISTINCT ...): 对上一步生成的唯一字符串进行去重计数。这样就得到了拥有有效IMEI和分类的唯一终端数量。
  

独立示例

1. -- 统计有邮箱和电话的唯一用户数
2. SELECT COUNT(DISTINCT
3.     CASE WHEN email IS NOT NULL AND phone IS NOT NULL
4.          THEN CONCAT(email, '-', phone)
5.     END
6. ) AS valid_users FROM users;

复制代码

综合应用示例

将以上所有函数整合到一个实际的查询中：

1. SELECT
2.     -- 按周分组
3.     DATE_TRUNC('week', h.clttime) AS week_start,
4.     -- 处理分类名为空的情况
5.     COALESCE(dim.categoryname_en, 'NOIDENTIFY') AS "Category",
6.     -- 计算总带宽(Gbps)
7.     ROUND(COALESCE(SUM(h.uldata + h.dldata), 0) * 8.0 / 1024 / 1024 / 1024 / 86400, 8) AS "Total Bandwidth",
8.     -- 计算总上行数据量
9.     SUM(h.uldata) AS uldata_sum,
10.     -- 计算大流量上传的总持续时间
11.     SUM(CASE WHEN h.uldata > 500 * 1024 THEN h.dwulduration ELSE 0 END) AS ldwulduration_sum,
12.     -- 统计有分类的唯一终端数量
13.     COUNT(DISTINCT CASE WHEN h.imei IS NOT NULL AND h.category IS NOT NULL
14.                         THEN CONCAT(h.imei, '_', h.category::VARCHAR)
15.                    END) AS ctgterminals_count
16. FROM
17.     network_traffic_log h
18. LEFT JOIN
19.     device_dimension dim ON h.device_id = dim.id
20. GROUP BY
21.     week_start,
22.     dim.categoryname_en  -- 注意：GROUP BY 中使用的是原始列，不是COALESCE后的别名
23. ORDER BY
24.     week_start DESC;

复制代码

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！