PostgreSQL 務實應用常用表達

在實際應用中，對於具體的數據計算我們會找相應的函數來實現。而計算需求不同的表達，往往會使得我們使用不同的函數或方式來實現。或者也可以說，同一計算可以使用多種不同的表達方式實現。

PostgreSQL 提供非常豐富的類型及函數，具體可參與相關幫助文檔。

以下我們通過實例來展示幾個常用的函數表達場景

首先，我們創建示例數據如下

CREATE TABLE table_test (
    id int PRIMARY KEY,
    test_name varchar(10),  -- 名稱
    group_code varchar(10), -- 組號
    create_at timestamp,    -- 創建時間
    status bool,            -- 狀態
    test_desc varchar(100)  -- 描述
);

-- 插入以下示例數據
INSERT INTO table_test (id, test_name, group_code, create_at, status, test_desc)
 VALUES
(1, '一號', '01', now(), true, '評爲A'),
(2, '一號', '01', now(), true, '評爲A'),
(3, '二號', '02', now(), true, '評爲B'),
(4, '三號', '01', now(), null, '評爲AC'),
(5, '四號', '03', now(), true, '評爲C'),
(6, '五號', '03', now(), true, '評爲AB');

NULL 相關

當查詢條件中相關字段存在 NULL 值時，如果直接使用 WHERE 字段 = @參數值，則怎麼都匹配不了爲 NULL 值的記錄，因爲 NULL = 任何值，包括 NULL=NULL 本身都不會返回 true，只會仍然是 NULL。

此時，可以將 NULL 進行轉換，當字段值爲 NULL 時，轉換爲一個默認值。

使用 coalesce() 可以表達此功能，它的功能是返回參數列表（支持不限定數量的參數）中，第一個非空值。

那如果我們需要根據 status （包含 NULL 值，NULL 時默認 false）來查詢，參數值可能是 true/false，則可以表達爲 coalesce(status, false)，當 status 爲 NULL 時，自然返回的就是後邊的 false。

此時：coalesce(status, false) = (case when status is null then false else status end)

-- status 中存在 NULL 值，以下參數可能爲 true/false
SELECT * FROM table_test
 WHERE coalesce(status, false) = ?;

SELECT * FROM table_test
 WHERE (case when status is null then false else status end) = ?;

時間相關

（1）當前時間及時間格式化輸出

函數 now() 取得當前系統時間，等同於系統變量 CURRENT_TIMESTAMP

使用 to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS US') 可以格式化時間，格式符含義如下：

YYYY 年，MM 月，DD 日，HH24 二十四小時制的時，MI 分，SS 秒， US 微秒

SELECT now(), CURRENT_TIMESTAMP, to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS US')
-- 輸入結果
"2019-04-21 23:24:45.276729+08" 
"2019-04-21 23:24:45.276729+08" 
"2019-04-21 23:24:45 276729"

（2）時間差

兩個時間相差的秒數使用：

extract(epoch FROM ( 時間 1 - 時間 2))

通過該函數，我們可以製作一個用於表示時間已過去多久的函數。

CREATE OR REPLACE FUNCTION public.f_time_span(
    t timestamp without time zone)
    RETURNS character varying
    LANGUAGE 'plpgsql' 
AS $$

DECLARE
    lv_span_double double precision;
    lv_span int8;
BEGIN
    -- 取得當前時間與參數時間 相差的秒數
    SELECT extract(epoch FROM (now() - t )) into lv_span_double;
    -- 轉換爲整型
    lv_span := cast(lv_span_double as int8);

    IF lv_span < 11 THEN
       RETURN '幾秒前';
    END IF;

    IF lv_span < 60 then
       RETURN lv_span || ' 秒前';
    END IF;

    IF lv_span < 3600 THEN
       RETURN (lv_span / 60) || ' 分鐘前';
    END IF;

    IF lv_span < 86400 THEN
       RETURN (lv_span / 3600) || ' 小時前';
    END IF;

    IF lv_span < 30 * 86400 THEN
       RETURN (lv_span / 86400) || ' 天前';
    END IF;

    IF lv_span < 365 * 86400 THEN
       RETURN (lv_span / (30*86400)) || ' 月前';
    END IF;

    RETURN (lv_span / (365*86400)) || ' 年前';
END 
$$;

我們應用於查詢中，如作爲創建時間隔現在多久的一個描述

SELECT test_name, create_at, f_time_span(create_at) span_desc FROM table_test

UUID 相關

使用 UUID，需要安裝擴展 uuid-ossp，擴展安裝成功以後，就可以通過 uuid_generate_v4() 或 uuid_generate_v1() 取得 UUID。

CREATE EXTENSION "uuid-ossp";

SELECT uuid_generate_v4()
-- 輸出 "da28ce8a-ca9b-483f-918e-dce96fe7137f"

聚合相關

PostgreSQL 提供了除 SUM, COUNT, MAX, MIN, AVG 等之外更多的便捷的聚合函數。

string_agg(表達式, '分隔符') -- 按聚合的表達式值用分隔符連接成一串文本

array_agg(表達式) -- 將聚合的表達式值形成一個數組

如示例數據中，我們希望返回每一個組號內的名稱列表，則可以：

SELECT GROUP_CODE,
      string_agg(test_name, ','),
      array_agg(test_name)
  FROM table_test GROUP BY GROUP_CODE

得到結果如下圖

其它補充

（1）MD5 加密

在用戶信息表中，密碼信息通常不明文存儲，有一種方式是使用密碼的 MD5 進行單向加密存儲從而保密性更強，PostgreSQL 想人之所想，提供了直接的 md5 函數，md5(text) 直接得到 32 位的 MD5 加密結果。

（2）正則相關

PostgreSQL 支持正則表達式，這爲查詢提供了極大的靈活性與表達空間。

語法格式爲：'字符串' ~ '正則表達式'，含義類似 IsMatch('字符串' ,'正則表達式')

~* 則表示忽略大小寫，!~ 或 !~* 表示否定

也可以使用 not '字符串' ~ '正則表達式' 表示否定

-- test_desc 包含 B 或 C 的
SELECT * FROM table_test  WHERE test_desc ~ 'B|C';
-- test_desc 包含ABC中兩個字母相鄰的
SELECT * FROM table_test  WHERE test_desc ~ '[A-C]{2}';
-- test_desc A結尾的
SELECT * FROM table_test  WHERE test_desc ~ 'A$';
-- test_desc 不是A結尾的
SELECT * FROM table_test  WHERE test_desc !~ 'A$';
本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/dJ7B5pemBDZV3AiFZ807jg

猜你喜歡