實(shí)戰(zhàn)：從字符串到JSON與窗口函數(shù)完全指南)
1. 前言PostgreSQL 的常用函數(shù)說實(shí)話是很多人從 MySQL 遷過來之后第一個要重新適應(yīng)的東西。語法有些相似但細(xì)節(jié)差異很大尤其是類型轉(zhuǎn)換、字符串處理、JSON 操作這幾個方向。這篇文章不打算照搬官方文檔而是按我平時寫 SQL 的實(shí)際習(xí)慣把最常用的函數(shù)過一遍順便把那些容易踩坑的地方點(diǎn)出來。我從 MySQL 轉(zhuǎn)到 PostgreSQL 大概兩三年期間做過不少數(shù)據(jù)遷移和報表開發(fā)。剛開始確實(shí)不適應(yīng)比如字符串拼接的寫法、類型轉(zhuǎn)換的語法、數(shù)組和 JSON 的處理方式都跟 MySQL 不太一樣。但用熟了之后反而覺得 PostgreSQL 的函數(shù)生態(tài)更強(qiáng)尤其適合做復(fù)雜查詢和數(shù)據(jù)分析。這篇文章適合正在學(xué) PostgreSQL 的開發(fā)者、從 MySQL 遷過來的同學(xué)以及平時要寫報表 SQL 的數(shù)據(jù)分析師。我會按功能分類講每類給一些直接能用的示例也會說明哪些地方容易出錯。2. 字符串函數(shù)寫業(yè)務(wù) SQL 最離不開的模塊字符串處理在業(yè)務(wù)查詢里極其常見無論是格式化輸出、拼接字段還是清洗數(shù)據(jù)都會用到。PostgreSQL 的字符串函數(shù)比 MySQL 豐富但寫法上有不少需要注意的差異。2.1 拼接、截取、替換日常操作PostgreSQL 里拼接字符串有兩種方式一種是直接用||運(yùn)算符另一種是用concat函數(shù)。兩種我都在用但推薦優(yōu)先用concat因?yàn)橛龅?NULL 值時不會整個變成 NULL。這一點(diǎn)很多從 MySQL 過來的人會踩坑因?yàn)?MySQL 的CONCAT函數(shù)遇到 NULL 會返回 NULL而 PostgreSQL 的concat會把 NULL 當(dāng)作空字符串。-- MySQL 習(xí)慣的寫法可能這樣 SELECT Hello || , || World; -- 返回 Hello, World -- 但如果有 NULL 參與 SELECT Hello || NULL; -- 返回 NULL注意 -- 用 concat 則安全很多 SELECT concat(Hello, , , World); -- Hello, World SELECT concat(Hello, NULL); -- Hello截取字符串用substringPostgreSQL 支持三種用法標(biāo)準(zhǔn) SQL 的substring(str from start for count)風(fēng)格也有substr(str, start, count)風(fēng)格。我平時用substr更多函數(shù)名短參數(shù)直接。注意兩個函數(shù)的起始位置都是從 1 開始不是從 0 開始這點(diǎn)跟很多編程語言不一樣。SELECT substring(PostgreSQL from 1 for 6); -- Postgr SELECT substr(PostgreSQL, 1, 6); -- Postgr替換用replace(str, from, to)這個跟 MySQL 一致沒什么坑。但要注意它是全局替換不是只替換第一個匹配項。SELECT replace(hello world hello, hello, hi); -- 結(jié)果: hi world hi2.2 大小寫轉(zhuǎn)換與填充格式化場景常用比較常見的是upper、lower、initcap這三個函數(shù)。initcap會把每個單詞的首字母大寫在生成報告展示名稱時很好用。SELECT upper(postgresql); -- POSTGRESQL SELECT lower(POSTGRESQL); -- postgresql SELECT initcap(hello world); -- Hello World還有l(wèi)pad、rpad填充函數(shù)常用于把數(shù)字補(bǔ)成固定位數(shù)比如訂單號補(bǔ)零。SELECT lpad(42, 5, 0); -- 00042 SELECT rpad(abc, 5, *); -- abc**這里有個細(xì)節(jié)如果原字符串長度已經(jīng)超過目標(biāo)長度不會截斷而是直接返回原字符串。如果你需要固定長度并截斷要先substr再lpad兩個函數(shù)疊加使用。2.3 正則表達(dá)式與字符串分割PostgreSQL 的正則支持是它的一大優(yōu)勢。regexp_replace、regexp_matches、regexp_split_to_table這組函數(shù)在數(shù)據(jù)清洗時非常實(shí)用。-- 正則替換把連續(xù)的多個空格替換成單個 SELECT regexp_replace(a b c, \s, , g); -- 結(jié)果: a b c -- 正則分割把逗號分隔的字符串拆成多行 SELECT regexp_split_to_table(apple,banana,orange, ,); -- 結(jié)果三行: apple / banana / orange -- 提取匹配片段 SELECT regexp_matches(我的手機(jī)號是13812345678, [0-9]{11});三個點(diǎn)要提醒一下第一regexp_replace的第四個參數(shù)是 flags常用的g表示全局替換不加的話只替換第一個匹配項這個跟編程語言里的正則習(xí)慣一致。第二regexp_split_to_table返回的是集合不止能用一行而string_to_array返回的是數(shù)組類型。兩者用途不同regexp_split_to_table適合直接用FROM引用的位置string_to_array需要配合unnest才能展開成行。第三正則里反斜杠的轉(zhuǎn)義\s在字符串里需要用\s而不是\\s因?yàn)?PostgreSQL 字符串本身也支持反斜杠轉(zhuǎn)義。很多人在這一步犯迷糊在普通 SQL 中建議直接寫成\s。split_part也是個實(shí)用函數(shù)按分隔符分割后取指定位置的片段。SELECT split_part(2025-06-15, -, 2); -- 06它比regexp_split_to_array更輕量只要不是復(fù)雜正則優(yōu)先用split_part。3. 數(shù)值與類型轉(zhuǎn)換報表統(tǒng)計繞不開的基礎(chǔ)工作數(shù)值計算和類型轉(zhuǎn)換在統(tǒng)計類 SQL 里出現(xiàn)頻率極高。PostgreSQL 的類型系統(tǒng)比 MySQL 嚴(yán)格好處是出錯早、數(shù)據(jù)可信度高壞處是很多 MySQL 里能模糊處理的寫法在這里直接報錯。3.1 四舍五入與取整round、ceil、floor以及trunc這四個函數(shù)是處理數(shù)值精度的主力。SELECT round(42.438, 2); -- 42.44 SELECT round(42.4); -- 42注意返回類型 SELECT ceil(42.1); -- 43 SELECT floor(42.9); -- 42 SELECT trunc(42.438, 2); -- 42.43直接截斷不舍入round和trunc的第二個參數(shù)表示保留的小數(shù)位。不傳第二個參數(shù)時round返回 numeric 類型trunc對 numeric 和double precision的表現(xiàn)略有差異大表聚合時要留意精度問題。一個很容易忽略的點(diǎn)round對numeric類型是“四舍五入”但由于二進(jìn)制浮點(diǎn)數(shù)的表示誤差double precision類型可能出現(xiàn)意外的結(jié)果。做財務(wù)報表時建議把值先轉(zhuǎn)成numeric再計算。SELECT round(2.675::double precision, 2); -- 意外可能得到 2.67 SELECT round(2.675::numeric, 2); -- 正常得到 2.683.2 類型轉(zhuǎn)換三套寫法都要會用PostgreSQL 類型轉(zhuǎn)換有幾種方式我按優(yōu)先順序排列推薦使用value::type格式可讀性最好也可以使用CAST(value AS type)符合標(biāo)準(zhǔn) SQL某些場景還可以用函數(shù)式轉(zhuǎn)換比如to_char、to_numberSELECT 123::int; -- 123 SELECT CAST(123 AS int); -- 123 SELECT 12.3::numeric; -- 12.3 SELECT 2025-06-15::date; -- 2025-06-15 SELECT 42::text; -- 42類型轉(zhuǎn)換最常見的坑是格式錯誤導(dǎo)致報錯。比如把空字符串轉(zhuǎn) int 會報錯把包含逗號的數(shù)字字符串轉(zhuǎn) numeric 也會報錯。這種時候可以用CASE或regexp先做清洗或者用to_number函數(shù)指定格式SELECT to_number(1,234.56, 9,999.99); -- 1234.563.3 生成序號與隨機(jī)數(shù)generate_series是 PostgreSQL 獨(dú)有的利器可以生成連續(xù)數(shù)字序列或時間序列。造測試數(shù)據(jù)、補(bǔ)全日期維度、生成序列編號時極其好用。-- 生成 1 到 5 的連續(xù)數(shù)字 SELECT generate_series(1, 5); -- 結(jié)果: 1, 2, 3, 4, 5 -- 生成帶步長的序列 SELECT generate_series(1, 10, 3); -- 結(jié)果: 1, 4, 7, 10 -- 生成時間序列比如每間隔 2 小時 SELECT generate_series(2025-01-01 00:00::timestamp, 2025-01-01 06:00::timestamp, 2 hours::interval);random()函數(shù)返回 0 到 1 之間的隨機(jī)浮點(diǎn)數(shù)。要在區(qū)間內(nèi)取整數(shù)需要配合floor和運(yùn)算-- 生成 1 到 100 的隨機(jī)整數(shù) SELECT floor(random() * 100)::int 1;4. 日期時間函數(shù)數(shù)據(jù)分析里最常出問題的地方日期處理函數(shù)是每個做數(shù)據(jù)分析的人都繞不開的環(huán)節(jié)。PostgreSQL 的日期類型很豐富但函數(shù)寫法和 MySQL 差異最大也是遷移時抱怨最多的地方。4.1 獲取當(dāng)前時間now()、current_timestamp、current_date、current_time這組函數(shù)要分清楚。SELECT now(); -- 2025-06-15 14:30:22.12308 SELECT current_timestamp; -- 同上now() 是它的別名 SELECT current_date; -- 2025-06-15 SELECT current_time; -- 14:30:22.12308 SELECT clock_timestamp(); -- 實(shí)時時間不同于 now() SELECT statement_timestamp(); -- 當(dāng)前語句開始時間now()返回的是事務(wù)開始時間不是每條語句的實(shí)際執(zhí)行時間。同一事務(wù)內(nèi)多次調(diào)用now()返回一樣的結(jié)果。需要獲取每條語句真實(shí)執(zhí)行時刻用clock_timestamp()。在長事務(wù)里做時間記錄、日志對比時這個區(qū)別很關(guān)鍵。4.2 日期加減與間隔計算日期加減是使用頻率最高的操作比如查詢最近 7 天訂單、上個月數(shù)據(jù)、本年度累計等。-- 日期加減 SELECT current_date 1; -- 明天 SELECT current_date - 7; -- 7天前 SELECT now() interval 3 hours; -- 3小時后 SELECT now() - interval 1 day; -- 1天前關(guān)于interval的寫法PostgreSQL 支持比較寬松的格式。常見幾種寫法都可以正常工作SELECT now() interval 1 day; SELECT now() interval 1 day; -- 這種也合法 SELECT now() 1 day::interval; -- 推薦類型顯式轉(zhuǎn)換兩個日期之間的間隔直接用減法得到的是天數(shù)。但兩個 timestamp 相減得到的是 interval 類型要注意區(qū)分SELECT date 2025-06-15 - date 2025-06-01; -- 14天數(shù)整數(shù) SELECT 2025-06-15 12:00::timestamp - 2025-06-15 10:00::timestamp; -- 結(jié)果: 02:00:00interval 類型要從 interval 里取天數(shù)或小時用extractSELECT extract(epoch FROM interval 2 hours); -- 7200秒數(shù) SELECT extract(day FROM interval 3 days 4 hours); -- 3extract也可以從日期時間中提取年、月、日、星期等SELECT extract(year FROM now()); -- 2025 SELECT extract(month FROM now()); -- 6 SELECT extract(dow FROM now()); -- 星期幾0是周日1是周一 SELECT extract(isodow FROM now()); -- 星期幾1是周一7是周日dow和isodow的區(qū)別經(jīng)常把人繞暈。簡單說報表按周分組時優(yōu)先用isodow它的星期順序符合國際標(biāo)準(zhǔn)慣例周一到周日對應(yīng) 1 到 7dow從周日算起對應(yīng) 0 到 6。我的習(xí)慣是統(tǒng)一用isodow做按周統(tǒng)計。4.3 日期格式化函數(shù)to_char是日期轉(zhuǎn)字符串的核心函數(shù)追溯整個 PostgreSQL 的日期格式化它提供的模板模式非常豐富。SELECT to_char(now(), YYYY-MM-DD HH24:MI:SS); -- 2025-06-15 14:30:22 SELECT to_char(now(), YYYY-MM-DD); -- 2025-06-15 SELECT to_char(now(), YYYY年MM月DD日); -- 2025年06月15日 SELECT to_char(now(), Dy, DD Mon YYYY); -- Sun, 15 Jun 2025對應(yīng)的to_date和to_timestamp把字符串轉(zhuǎn)成日期時間SELECT to_date(2025-06-15, YYYY-MM-DD); SELECT to_timestamp(2025-06-15 14:30:00, YYYY-MM-DD HH24:MI:SS);格式化模板里的坑主要集中在HH24才是 24 小時制HH是 12 小時制用錯會導(dǎo)致早晚顛倒MM是月MI是分鐘大小寫不同含義完全不同。我見過有人把MI寫成MM導(dǎo)致輸出的分鐘變成了月份這種錯誤光用肉眼看數(shù)據(jù)不容易發(fā)現(xiàn)做時間排序時才會暴露。4.4 時間與日期截斷date_trunc函數(shù)可以把時間截斷到指定精度做報表聚合時尤其好用。SELECT date_trunc(month, now()); -- 2025-06-01 00:00:00 SELECT date_trunc(day, now()); -- 2025-06-15 00:00:00 SELECT date_trunc(hour, now()); -- 2025-06-15 14:00:00 SELECT date_trunc(week, now()); -- 2025-06-09 00:00:00周一注意date_trunc的 week 模式默認(rèn)從周一開始與isodow的語義一致。按周聚合時這個細(xì)節(jié)非常重要。age函數(shù)可以計算兩個日期之間的年齡或時間差返回的是 interval 類型人類可讀性很好。SELECT age(timestamp 2025-06-15, timestamp 2020-01-01); -- 結(jié)果: 5 years 5 mons 14 days SELECT age(timestamp 1988-03-22); -- 距今多少年月日5. JSON 與數(shù)組函數(shù)PostgreSQL 區(qū)別于傳統(tǒng)關(guān)系庫的特色功能一般數(shù)據(jù)庫頂多支持 JSON 存儲但 PostgreSQL 的 JSON 處理能力更像是一門獨(dú)立的編程語言。配合數(shù)組函數(shù)可以做很多本來要放在應(yīng)用層處理的邏輯。5.1 JSONB 基礎(chǔ)操作jsonb類型與json類型最大的區(qū)別在于存儲格式j(luò)sonb是解析后的二進(jìn)制格式查詢性能和索引支持都更好。正常情況下直接使用jsonb。-- JSON 取值用箭頭操作符 SELECT {name: 張三, age: 30}::jsonb - name; -- 結(jié)果: 張三保留 JSON 引號 SELECT {name: 張三, age: 30}::jsonb - name; -- 結(jié)果: 張三純文本 -- 嵌套取值用 # 和 # SELECT {info: {city: 上海}}::jsonb # {info, city}; -- 結(jié)果: 上海 SELECT {info: {city: 上海}}::jsonb # {info, city}; -- 結(jié)果: 上海四個操作符看起來差不多但用途分得很清楚-返回 JSON 類型-返回文本類型#和#用于嵌套路徑。判斷哪個該用就看后面的操作要再繼續(xù)取下一級字段用-要跟文本比較或直接展示用-。判斷鍵是否存在、是否存在某值用?和?|、?SELECT {a: 1, b: 2}::jsonb ? a; -- true SELECT {a: 1, b: 2}::jsonb ?| array[a, c]; -- true只要存在一個 SELECT {a: 1, b: 2}::jsonb ? array[a, b]; -- true必須全部存在JSON 數(shù)據(jù)聚合為一行用jsonb_agg。反過來行轉(zhuǎn) JSON 對象用jsonb_object_agg這兩個函數(shù)在報表里非常實(shí)用-- 把多行數(shù)據(jù)聚合為 JSON 數(shù)組 SELECT jsonb_agg(name) FROM users; -- [[張三, 李四, 王五]] -- 把兩列數(shù)據(jù)轉(zhuǎn)成 JSON 對象 SELECT jsonb_object_agg(id, name) FROM users; -- {1: 張三, 2: 李四}我用 JSONB 函數(shù)做動態(tài)配置存儲比較多。比如給每個訂單存擴(kuò)展字段查詢時直接取出里面的某個值參與過濾和計算不需要改表結(jié)構(gòu)靈活度很高。5.2 數(shù)組函數(shù)與unnestPostgreSQL 的數(shù)組類型本身就很好用。array_agg可以把一列的值聚合成數(shù)組unnest則把數(shù)組拆散成多行。-- 把成績列聚合成數(shù)組 SELECT array_agg(score ORDER BY score) FROM student_scores; -- {60, 70, 80, 90} -- 把數(shù)組拆成多行 SELECT unnest(array[11, 22, 33]); -- 輸出三行: 11 / 22 / 33數(shù)組與行的互相轉(zhuǎn)換配合string_to_array、array_to_string可以處理很多 CSV 類的需求。SELECT array_to_string(array[a, b, c], ,); -- a,b,c SELECT string_to_array(a,b,c, ,); -- {a,b,c} -- 檢查元素是否在數(shù)組里 SELECT b ANY(array[a, b, c]); -- true關(guān)于unnest的一個非常優(yōu)秀的用法在 SQL 里做笛卡爾積式展開。比如某個訂單包含多個商品商品數(shù)量存的是數(shù)組用unnest配合WITH ORDINALITY可以同時拿到下標(biāo)和數(shù)據(jù)SELECT * FROM unnest(array[蘋果, 香蕉, 橙子]) WITH ORDINALITY AS t(fruit, ord); -- 蘋果 1 -- 香蕉 2 -- 橙子 3下標(biāo)序號在還原原始數(shù)據(jù)順序時非常重要比如頁面提交的選項順序、JSON 里保存的列表順序。6. 聚合函數(shù)與窗口函數(shù)分析場景的必備武器統(tǒng)計報表離不開聚合函數(shù)而窗口函數(shù)則是 PostgreSQL 實(shí)現(xiàn)復(fù)雜分析場景的殺手锏。這一節(jié)把兩組函數(shù)放在一起講因?yàn)樗鼈兘?jīng)常配合使用。6.1 常用聚合函數(shù)與 FILTER 子句聚合函數(shù)總體跟 MySQL 差異不大sum、avg、count、max、min是最基本的組合但count的幾個寫法需要特別注意SELECT count(*) FROM orders; -- 統(tǒng)計所有行 SELECT count(1) FROM orders; -- 同上性能差異可忽略 SELECT count(order_id) FROM orders; -- 統(tǒng)計非 NULL 的行 SELECT count(DISTINCT user_id) FROM orders; -- 去重統(tǒng)計FILTER子句是 PostgreSQL 特有的聚合擴(kuò)展比CASE WHEN嵌套更簡潔也不需要多處冗余-- 統(tǒng)計各分類的匯總值和滿足條件的值 SELECT category, sum(amount) AS total_amount, sum(amount) FILTER (WHERE status paid) AS paid_amount, count(*) FILTER (WHERE status paid) AS paid_count FROM orders GROUP BY category;等價寫法是用CASE WHEN但代碼明顯更長FILTER在可讀性上優(yōu)勢很大。我個人的項目里凡是統(tǒng)計口徑比較多的報表都優(yōu)先用FILTER。array_agg作為聚合函數(shù)也值得提一下它可以把分組內(nèi)的值拼成數(shù)組再配合array_to_string或string_agg輸出。6.2 窗口函數(shù)分組內(nèi)排序與累計計算窗口函數(shù)在報表場景中基本是頂梁柱。常用的四類排名類、累計類、偏移類、分組聚合類。排名類函數(shù)是最典型的窗口函數(shù)SELECT product_id, sales_amount, row_number() OVER (ORDER BY sales_amount DESC) AS row_num, rank() OVER (ORDER BY sales_amount DESC) AS rank, dense_rank() OVER (ORDER BY sales_amount DESC) AS dense_rank FROM product_sales;row_number和rank的區(qū)別在于處理并列row_number對并列數(shù)據(jù)強(qiáng)行編號不重復(fù)rank產(chǎn)生并列間隔如 1, 1, 3dense_rank產(chǎn)生并列連續(xù)如 1, 1, 2。取 Top N 時如果希望并列都進(jìn)榜單用dense_rank如果嚴(yán)格取前 N 條記錄用row_number。偏移類窗口函數(shù)比如取前一行或后一行的值SELECT day, sales_amount, lag(sales_amount, 1) OVER (ORDER BY day) AS prev_day_sales, lead(sales_amount, 1) OVER (ORDER BY day) AS next_day_sales FROM daily_sales;這種寫法在做環(huán)比、同比分析時非常直接。比子查詢關(guān)聯(lián)的性能好很多代碼也短。累計類窗口函數(shù)可以直接計算累計值比如每天累計銷售額SELECT day, sales_amount, sum(sales_amount) OVER (ORDER BY day) AS cumulative_sales FROM daily_sales;窗口函數(shù)搭配PARTITION BY是分組統(tǒng)計的常態(tài)SELECT category, product_id, sales_amount, avg(sales_amount) OVER (PARTITION BY category) AS avg_in_category FROM product_sales;這是“每個分類的平均銷售額”和“每條記錄的銷售額”同時展示一行數(shù)據(jù)包含當(dāng)前記錄和所在分組的上下文信息在報表導(dǎo)出時特別有用。7. 條件邏輯與空值處理讓數(shù)據(jù)查詢更健壯條件函數(shù)、空值函數(shù)是 SQL 的防御性編程利器。實(shí)際生產(chǎn)環(huán)境里臟數(shù)據(jù)、缺省值幾乎是常態(tài)寫 SQL 時不處理 NULL后面做應(yīng)用層就會很痛苦。7.1 CASE WHEN 的靈活用法CASE WHEN是條件分支的標(biāo)準(zhǔn)寫法能在 SELECT 列表、WHERE 子句、ORDER BY 里使用靈活度非常高。SELECT user_id, CASE WHEN order_count 10 THEN VIP WHEN order_count 1 THEN 普通用戶 ELSE 新用戶 END AS user_level FROM user_stats;注意CASE WHEN是表達(dá)式不是語句?？梢栽诰酆虾瘮?shù)里套用計算滿足條件的值SELECT sum(CASE WHEN status paid THEN amount ELSE 0 END) AS paid_sum FROM orders;7.2 COALESCE 與 NULLIF 的坑COALESCE是最常用的空值處理函數(shù)返回參數(shù)列表中的第一個非 NULL 值SELECT coalesce(NULL, NULL, default); -- default SELECT coalesce(username, email, phone, 未知) FROM users; -- 依次取第一個非空值NULLIF的作用正好相反當(dāng)兩個參數(shù)相等時返回 NULLSELECT nullif(0, 0); -- NULL SELECT nullif(5, 0); -- 5NULLIF最常見的用途是防止除零錯誤SELECT total_amount / nullif(order_count, 0) AS avg_amount FROM user_stats;如果order_count是 0直接除會報錯用NULLIF轉(zhuǎn)成 NULL整體結(jié)果就是 NULL不會中斷查詢。還有一個GREATEST和LEAST在比較多個值時很有用SELECT greatest(1, 2, 3); -- 3 SELECT least(1, 2, 3); -- 1但注意它們的邏輯跟 MySQL 不完全一致遇到 NULL 會返回 NULL這點(diǎn)比 MySQL 更嚴(yán)格。7.3 DISTINCT ON 的獨(dú)特價值DISTINCT ON是 PostgreSQL 特有的語法非常實(shí)用。它可以根據(jù)指定字段去重同時返回該分組內(nèi)的任意一條記錄配合ORDER BY可以精準(zhǔn)控制取哪一條-- 取每個用戶最新的一條訂單 SELECT DISTINCT ON (user_id) user_id, order_id, order_time FROM orders ORDER BY user_id, order_time DESC;這個寫法比窗口函數(shù)更簡潔也不需要子查詢。它內(nèi)部處理邏輯是先按ORDER BY排序再對DISTINCT ON的字段去重保留每個組的第一條記錄。注意ORDER BY里DISTINCT ON指定的字段必須排在最前面否則會報錯。我經(jīng)常用它來取最新價格、最新狀態(tài)、每個用戶最近一次登錄時間等場景性能在索引合理的情況下相當(dāng)不錯。8. 常見問題與排查技巧實(shí)錄這一節(jié)整理幾個我在實(shí)際工作中踩過、也幫別人排查過的典型問題都是查文檔不一定能快速找到答案的細(xì)節(jié)。8.1 PostgreSQL 與 MySQL 函數(shù)習(xí)慣沖突最常見的是字符串截取和拼接MySQL 的LEFT(str, n)、RIGHT(str, n)在 PostgreSQL 里也有但參數(shù)含義一致SUBSTRING則完全不同。PostgreSQL 支持標(biāo)準(zhǔn) SQL 的substring(str from n for m)也支持substr(str, n, m)。從 MySQL 遷過來的人往往對substring的第一印象是“怎么這么別扭”所以我一般建議直接用substr。類型轉(zhuǎn)換也是重災(zāi)區(qū)。MySQL 里123 0就能把字符串轉(zhuǎn)成數(shù)字PostgreSQL 里必須用123::int或CAST寫錯一個字直接報錯。這種嚴(yán)格反而讓數(shù)據(jù)更可信因?yàn)轭愋湾e誤在 SQL 執(zhí)行階段就暴露出來了。8.2 時區(qū)問題的排查實(shí)錄曾經(jīng)接了一個項目數(shù)據(jù)庫存儲的 timestamp 不帶時區(qū)應(yīng)用層傳入的時間卻是北京時間。取出來的數(shù)據(jù)做日期分組時凌晨的數(shù)據(jù)被歸到前一天報表數(shù)據(jù)始終對不上。排查思路是先用current_time確認(rèn)服務(wù)器時區(qū)然后檢查連接字符串是否指定了timezone參數(shù)。后來統(tǒng)一約定數(shù)據(jù)庫統(tǒng)一存 UTC所有取數(shù)操作在 SQL 里用AT TIME ZONE做轉(zhuǎn)換。-- UTC 轉(zhuǎn)北京時間 SELECT now() AT TIME ZONE UTC AT TIME ZONE Asia/Shanghai;8.3 隱式類型轉(zhuǎn)換引發(fā)的索引失效還有一個比較隱蔽的性能問題在 timestamp 列上做字符串比較比如WHERE created_at 2025-06-15。表面看沒問題但 PostgreSQL 的隱式類型轉(zhuǎn)換可能導(dǎo)致索引無法使用。解決方法是顯式轉(zhuǎn)換-- 不推薦可能無法走索引 SELECT * FROM orders WHERE created_at 2025-06-15; -- 推薦讓類型匹配 SELECT * FROM orders WHERE created_at 2025-06-15::date AND created_at (2025-06-15::date 1);這個例子也順便說明了一個日常經(jīng)驗(yàn)日期范圍查詢最好寫成左閉右開區(qū)間既覆蓋了整天數(shù)據(jù)又保證了索引最優(yōu)使用。8.4 常見函數(shù)速查表整理一份我貼在工位上的速查表都是日常出現(xiàn)頻率最高的功能推薦寫法示例字符串拼接concat()concat(a, b)正則提取regexp_matches()regexp_matches(str, pattern)字符串分割取片段split_part()split_part(a-b-c, -, 2)防止除零NULLIFa / NULLIF(b, 0)空值兜底coalesce()coalesce(col, 默認(rèn)值)日期格式化to_char()to_char(now(), YYYY-MM-DD)時間截斷date_trunc()date_trunc(month, now())時間差秒extract(epoch from ...)extract(epoch FROM now() - start_time)JSON 取值-jsonb_col - field分組內(nèi)取最新DISTINCT ONDISTINCT ON (user_id) ... ORDER BY user_id, time DESC累計求和sum() OVER ()sum(amount) OVER (ORDER BY day)9. 結(jié)尾的話從 MySQL 遷到 PostgreSQL剛開始會覺得函數(shù)寫法別扭尤其是類型轉(zhuǎn)換和字符串截取。但用久了就會發(fā)現(xiàn)PostgreSQL 的函數(shù)設(shè)計更貼近標(biāo)準(zhǔn) SQL正則、JSON、窗口函數(shù)這塊的能力是實(shí)打?qū)嵉膹?qiáng)。我在實(shí)際項目中體會最深的一點(diǎn)遇到不確定行為時直接用SELECT不帶表跑一下函數(shù)幾秒鐘就能驗(yàn)證結(jié)果。PostgreSQL 允許這種純表達(dá)式查詢調(diào)試起來比 MySQL 舒服很多。最后分享一個我常用的組合技巧regexp_split_to_table加上unnest配合WITH ORDINALITY可以把很多復(fù)雜字符串解析問題拆成簡單的行級操作。把數(shù)據(jù)展開成行之后再配合窗口函數(shù)和FILTER做統(tǒng)計分析整個處理流程會非常順滑。這篇文章列出的函數(shù)基本覆蓋了日常開發(fā)中九成以上的需求。實(shí)際操作中我對每類函數(shù)都建議在自己環(huán)境的數(shù)據(jù)庫里敲一遍觀察返回類型和結(jié)果。函數(shù)返回值的數(shù)據(jù)類型決定了下一步能調(diào)哪些函數(shù)這是文檔里不太會強(qiáng)調(diào)、但實(shí)際寫 SQL 時非常關(guān)鍵的一點(diǎn)。 PostgreSQL常用函數(shù)實(shí)戰(zhàn)：從字符串到JSON與窗口函數(shù)完全指南
1. 前言PostgreSQL 的常用函數(shù)說實(shí)話是很多人從 MySQL 遷過來之后第一個要重新適應(yīng)的東西。語法有些相似但細(xì)節(jié)差異很大尤其是類型轉(zhuǎn)換、字符串處理、JSON 操作這幾個方向。這篇文章不打算照搬官方文檔而是按我平時寫 SQL 的實(shí)際習(xí)慣把最常用的函數(shù)過一遍順便把那些容易踩坑的地方點(diǎn)出來。我從 MySQL 轉(zhuǎn)到 PostgreSQL 大概兩三年期間做過不少數(shù)據(jù)遷移和報表開發(fā)。剛開始確實(shí)不適應(yīng)比如字符串拼接的寫法、類型轉(zhuǎn)換的語法、數(shù)組和 JSON 的處理方式都跟 MySQL 不太一樣。但用熟了之后反而覺得 PostgreSQL 的函數(shù)生態(tài)更強(qiáng)尤其適合做復(fù)雜查詢和數(shù)據(jù)分析。這篇文章適合正在學(xué) PostgreSQL 的開發(fā)者、從 MySQL 遷過來的同學(xué)以及平時要寫報表 SQL 的數(shù)據(jù)分析師。我會按功能分類講每類給一些直接能用的示例也會說明哪些地方容易出錯。2. 字符串函數(shù)寫業(yè)務(wù) SQL 最離不開的模塊字符串處理在業(yè)務(wù)查詢里極其常見無論是格式化輸出、拼接字段還是清洗數(shù)據(jù)都會用到。PostgreSQL 的字符串函數(shù)比 MySQL 豐富但寫法上有不少需要注意的差異。2.1 拼接、截取、替換日常操作PostgreSQL 里拼接字符串有兩種方式一種是直接用||運(yùn)算符另一種是用concat函數(shù)。兩種我都在用但推薦優(yōu)先用concat因?yàn)橛龅?NULL 值時不會整個變成 NULL。這一點(diǎn)很多從 MySQL 過來的人會踩坑因?yàn)?MySQL 的CONCAT函數(shù)遇到 NULL 會返回 NULL而 PostgreSQL 的concat會把 NULL 當(dāng)作空字符串。-- MySQL 習(xí)慣的寫法可能這樣 SELECT Hello || , || World; -- 返回 Hello, World -- 但如果有 NULL 參與 SELECT Hello || NULL; -- 返回 NULL注意 -- 用 concat 則安全很多 SELECT concat(Hello, , , World); -- Hello, World SELECT concat(Hello, NULL); -- Hello截取字符串用substringPostgreSQL 支持三種用法標(biāo)準(zhǔn) SQL 的substring(str from start for count)風(fēng)格也有substr(str, start, count)風(fēng)格。我平時用substr更多函數(shù)名短參數(shù)直接。注意兩個函數(shù)的起始位置都是從 1 開始不是從 0 開始這點(diǎn)跟很多編程語言不一樣。SELECT substring(PostgreSQL from 1 for 6); -- Postgr SELECT substr(PostgreSQL, 1, 6); -- Postgr替換用replace(str, from, to)這個跟 MySQL 一致沒什么坑。但要注意它是全局替換不是只替換第一個匹配項。SELECT replace(hello world hello, hello, hi); -- 結(jié)果: hi world hi2.2 大小寫轉(zhuǎn)換與填充格式化場景常用比較常見的是upper、lower、initcap這三個函數(shù)。initcap會把每個單詞的首字母大寫在生成報告展示名稱時很好用。SELECT upper(postgresql); -- POSTGRESQL SELECT lower(POSTGRESQL); -- postgresql SELECT initcap(hello world); -- Hello World還有l(wèi)pad、rpad填充函數(shù)常用于把數(shù)字補(bǔ)成固定位數(shù)比如訂單號補(bǔ)零。SELECT lpad(42, 5, 0); -- 00042 SELECT rpad(abc, 5, *); -- abc**這里有個細(xì)節(jié)如果原字符串長度已經(jīng)超過目標(biāo)長度不會截斷而是直接返回原字符串。如果你需要固定長度并截斷要先substr再lpad兩個函數(shù)疊加使用。2.3 正則表達(dá)式與字符串分割PostgreSQL 的正則支持是它的一大優(yōu)勢。regexp_replace、regexp_matches、regexp_split_to_table這組函數(shù)在數(shù)據(jù)清洗時非常實(shí)用。-- 正則替換把連續(xù)的多個空格替換成單個 SELECT regexp_replace(a b c, \s, , g); -- 結(jié)果: a b c -- 正則分割把逗號分隔的字符串拆成多行 SELECT regexp_split_to_table(apple,banana,orange, ,); -- 結(jié)果三行: apple / banana / orange -- 提取匹配片段 SELECT regexp_matches(我的手機(jī)號是13812345678, [0-9]{11});三個點(diǎn)要提醒一下第一regexp_replace的第四個參數(shù)是 flags常用的g表示全局替換不加的話只替換第一個匹配項這個跟編程語言里的正則習(xí)慣一致。第二regexp_split_to_table返回的是集合不止能用一行而string_to_array返回的是數(shù)組類型。兩者用途不同regexp_split_to_table適合直接用FROM引用的位置string_to_array需要配合unnest才能展開成行。第三正則里反斜杠的轉(zhuǎn)義\s在字符串里需要用\s而不是\\s因?yàn)?PostgreSQL 字符串本身也支持反斜杠轉(zhuǎn)義。很多人在這一步犯迷糊在普通 SQL 中建議直接寫成\s。split_part也是個實(shí)用函數(shù)按分隔符分割后取指定位置的片段。SELECT split_part(2025-06-15, -, 2); -- 06它比regexp_split_to_array更輕量只要不是復(fù)雜正則優(yōu)先用split_part。3. 數(shù)值與類型轉(zhuǎn)換報表統(tǒng)計繞不開的基礎(chǔ)工作數(shù)值計算和類型轉(zhuǎn)換在統(tǒng)計類 SQL 里出現(xiàn)頻率極高。PostgreSQL 的類型系統(tǒng)比 MySQL 嚴(yán)格好處是出錯早、數(shù)據(jù)可信度高壞處是很多 MySQL 里能模糊處理的寫法在這里直接報錯。3.1 四舍五入與取整round、ceil、floor以及trunc這四個函數(shù)是處理數(shù)值精度的主力。SELECT round(42.438, 2); -- 42.44 SELECT round(42.4); -- 42注意返回類型 SELECT ceil(42.1); -- 43 SELECT floor(42.9); -- 42 SELECT trunc(42.438, 2); -- 42.43直接截斷不舍入round和trunc的第二個參數(shù)表示保留的小數(shù)位。不傳第二個參數(shù)時round返回 numeric 類型trunc對 numeric 和double precision的表現(xiàn)略有差異大表聚合時要留意精度問題。一個很容易忽略的點(diǎn)round對numeric類型是“四舍五入”但由于二進(jìn)制浮點(diǎn)數(shù)的表示誤差double precision類型可能出現(xiàn)意外的結(jié)果。做財務(wù)報表時建議把值先轉(zhuǎn)成numeric再計算。SELECT round(2.675::double precision, 2); -- 意外可能得到 2.67 SELECT round(2.675::numeric, 2); -- 正常得到 2.683.2 類型轉(zhuǎn)換三套寫法都要會用PostgreSQL 類型轉(zhuǎn)換有幾種方式我按優(yōu)先順序排列推薦使用value::type格式可讀性最好也可以使用CAST(value AS type)符合標(biāo)準(zhǔn) SQL某些場景還可以用函數(shù)式轉(zhuǎn)換比如to_char、to_numberSELECT 123::int; -- 123 SELECT CAST(123 AS int); -- 123 SELECT 12.3::numeric; -- 12.3 SELECT 2025-06-15::date; -- 2025-06-15 SELECT 42::text; -- 42類型轉(zhuǎn)換最常見的坑是格式錯誤導(dǎo)致報錯。比如把空字符串轉(zhuǎn) int 會報錯把包含逗號的數(shù)字字符串轉(zhuǎn) numeric 也會報錯。這種時候可以用CASE或regexp先做清洗或者用to_number函數(shù)指定格式SELECT to_number(1,234.56, 9,999.99); -- 1234.563.3 生成序號與隨機(jī)數(shù)generate_series是 PostgreSQL 獨(dú)有的利器可以生成連續(xù)數(shù)字序列或時間序列。造測試數(shù)據(jù)、補(bǔ)全日期維度、生成序列編號時極其好用。-- 生成 1 到 5 的連續(xù)數(shù)字 SELECT generate_series(1, 5); -- 結(jié)果: 1, 2, 3, 4, 5 -- 生成帶步長的序列 SELECT generate_series(1, 10, 3); -- 結(jié)果: 1, 4, 7, 10 -- 生成時間序列比如每間隔 2 小時 SELECT generate_series(2025-01-01 00:00::timestamp, 2025-01-01 06:00::timestamp, 2 hours::interval);random()函數(shù)返回 0 到 1 之間的隨機(jī)浮點(diǎn)數(shù)。要在區(qū)間內(nèi)取整數(shù)需要配合floor和運(yùn)算-- 生成 1 到 100 的隨機(jī)整數(shù) SELECT floor(random() * 100)::int 1;4. 日期時間函數(shù)數(shù)據(jù)分析里最常出問題的地方日期處理函數(shù)是每個做數(shù)據(jù)分析的人都繞不開的環(huán)節(jié)。PostgreSQL 的日期類型很豐富但函數(shù)寫法和 MySQL 差異最大也是遷移時抱怨最多的地方。4.1 獲取當(dāng)前時間now()、current_timestamp、current_date、current_time這組函數(shù)要分清楚。SELECT now(); -- 2025-06-15 14:30:22.12308 SELECT current_timestamp; -- 同上now() 是它的別名 SELECT current_date; -- 2025-06-15 SELECT current_time; -- 14:30:22.12308 SELECT clock_timestamp(); -- 實(shí)時時間不同于 now() SELECT statement_timestamp(); -- 當(dāng)前語句開始時間now()返回的是事務(wù)開始時間不是每條語句的實(shí)際執(zhí)行時間。同一事務(wù)內(nèi)多次調(diào)用now()返回一樣的結(jié)果。需要獲取每條語句真實(shí)執(zhí)行時刻用clock_timestamp()。在長事務(wù)里做時間記錄、日志對比時這個區(qū)別很關(guān)鍵。4.2 日期加減與間隔計算日期加減是使用頻率最高的操作比如查詢最近 7 天訂單、上個月數(shù)據(jù)、本年度累計等。-- 日期加減 SELECT current_date 1; -- 明天 SELECT current_date - 7; -- 7天前 SELECT now() interval 3 hours; -- 3小時后 SELECT now() - interval 1 day; -- 1天前關(guān)于interval的寫法PostgreSQL 支持比較寬松的格式。常見幾種寫法都可以正常工作SELECT now() interval 1 day; SELECT now() interval 1 day; -- 這種也合法 SELECT now() 1 day::interval; -- 推薦類型顯式轉(zhuǎn)換兩個日期之間的間隔直接用減法得到的是天數(shù)。但兩個 timestamp 相減得到的是 interval 類型要注意區(qū)分SELECT date 2025-06-15 - date 2025-06-01; -- 14天數(shù)整數(shù) SELECT 2025-06-15 12:00::timestamp - 2025-06-15 10:00::timestamp; -- 結(jié)果: 02:00:00interval 類型要從 interval 里取天數(shù)或小時用extractSELECT extract(epoch FROM interval 2 hours); -- 7200秒數(shù) SELECT extract(day FROM interval 3 days 4 hours); -- 3extract也可以從日期時間中提取年、月、日、星期等SELECT extract(year FROM now()); -- 2025 SELECT extract(month FROM now()); -- 6 SELECT extract(dow FROM now()); -- 星期幾0是周日1是周一 SELECT extract(isodow FROM now()); -- 星期幾1是周一7是周日dow和isodow的區(qū)別經(jīng)常把人繞暈。簡單說報表按周分組時優(yōu)先用isodow它的星期順序符合國際標(biāo)準(zhǔn)慣例周一到周日對應(yīng) 1 到 7dow從周日算起對應(yīng) 0 到 6。我的習(xí)慣是統(tǒng)一用isodow做按周統(tǒng)計。4.3 日期格式化函數(shù)to_char是日期轉(zhuǎn)字符串的核心函數(shù)追溯整個 PostgreSQL 的日期格式化它提供的模板模式非常豐富。SELECT to_char(now(), YYYY-MM-DD HH24:MI:SS); -- 2025-06-15 14:30:22 SELECT to_char(now(), YYYY-MM-DD); -- 2025-06-15 SELECT to_char(now(), YYYY年MM月DD日); -- 2025年06月15日 SELECT to_char(now(), Dy, DD Mon YYYY); -- Sun, 15 Jun 2025對應(yīng)的to_date和to_timestamp把字符串轉(zhuǎn)成日期時間SELECT to_date(2025-06-15, YYYY-MM-DD); SELECT to_timestamp(2025-06-15 14:30:00, YYYY-MM-DD HH24:MI:SS);格式化模板里的坑主要集中在HH24才是 24 小時制HH是 12 小時制用錯會導(dǎo)致早晚顛倒MM是月MI是分鐘大小寫不同含義完全不同。我見過有人把MI寫成MM導(dǎo)致輸出的分鐘變成了月份這種錯誤光用肉眼看數(shù)據(jù)不容易發(fā)現(xiàn)做時間排序時才會暴露。4.4 時間與日期截斷date_trunc函數(shù)可以把時間截斷到指定精度做報表聚合時尤其好用。SELECT date_trunc(month, now()); -- 2025-06-01 00:00:00 SELECT date_trunc(day, now()); -- 2025-06-15 00:00:00 SELECT date_trunc(hour, now()); -- 2025-06-15 14:00:00 SELECT date_trunc(week, now()); -- 2025-06-09 00:00:00周一注意date_trunc的 week 模式默認(rèn)從周一開始與isodow的語義一致。按周聚合時這個細(xì)節(jié)非常重要。age函數(shù)可以計算兩個日期之間的年齡或時間差返回的是 interval 類型人類可讀性很好。SELECT age(timestamp 2025-06-15, timestamp 2020-01-01); -- 結(jié)果: 5 years 5 mons 14 days SELECT age(timestamp 1988-03-22); -- 距今多少年月日5. JSON 與數(shù)組函數(shù)PostgreSQL 區(qū)別于傳統(tǒng)關(guān)系庫的特色功能一般數(shù)據(jù)庫頂多支持 JSON 存儲但 PostgreSQL 的 JSON 處理能力更像是一門獨(dú)立的編程語言。配合數(shù)組函數(shù)可以做很多本來要放在應(yīng)用層處理的邏輯。5.1 JSONB 基礎(chǔ)操作jsonb類型與json類型最大的區(qū)別在于存儲格式j(luò)sonb是解析后的二進(jìn)制格式查詢性能和索引支持都更好。正常情況下直接使用jsonb。-- JSON 取值用箭頭操作符 SELECT {name: 張三, age: 30}::jsonb - name; -- 結(jié)果: 張三保留 JSON 引號 SELECT {name: 張三, age: 30}::jsonb - name; -- 結(jié)果: 張三純文本 -- 嵌套取值用 # 和 # SELECT {info: {city: 上海}}::jsonb # {info, city}; -- 結(jié)果: 上海 SELECT {info: {city: 上海}}::jsonb # {info, city}; -- 結(jié)果: 上海四個操作符看起來差不多但用途分得很清楚-返回 JSON 類型-返回文本類型#和#用于嵌套路徑。判斷哪個該用就看后面的操作要再繼續(xù)取下一級字段用-要跟文本比較或直接展示用-。判斷鍵是否存在、是否存在某值用?和?|、?SELECT {a: 1, b: 2}::jsonb ? a; -- true SELECT {a: 1, b: 2}::jsonb ?| array[a, c]; -- true只要存在一個 SELECT {a: 1, b: 2}::jsonb ? array[a, b]; -- true必須全部存在JSON 數(shù)據(jù)聚合為一行用jsonb_agg。反過來行轉(zhuǎn) JSON 對象用jsonb_object_agg這兩個函數(shù)在報表里非常實(shí)用-- 把多行數(shù)據(jù)聚合為 JSON 數(shù)組 SELECT jsonb_agg(name) FROM users; -- [[張三, 李四, 王五]] -- 把兩列數(shù)據(jù)轉(zhuǎn)成 JSON 對象 SELECT jsonb_object_agg(id, name) FROM users; -- {1: 張三, 2: 李四}我用 JSONB 函數(shù)做動態(tài)配置存儲比較多。比如給每個訂單存擴(kuò)展字段查詢時直接取出里面的某個值參與過濾和計算不需要改表結(jié)構(gòu)靈活度很高。5.2 數(shù)組函數(shù)與unnestPostgreSQL 的數(shù)組類型本身就很好用。array_agg可以把一列的值聚合成數(shù)組unnest則把數(shù)組拆散成多行。-- 把成績列聚合成數(shù)組 SELECT array_agg(score ORDER BY score) FROM student_scores; -- {60, 70, 80, 90} -- 把數(shù)組拆成多行 SELECT unnest(array[11, 22, 33]); -- 輸出三行: 11 / 22 / 33數(shù)組與行的互相轉(zhuǎn)換配合string_to_array、array_to_string可以處理很多 CSV 類的需求。SELECT array_to_string(array[a, b, c], ,); -- a,b,c SELECT string_to_array(a,b,c, ,); -- {a,b,c} -- 檢查元素是否在數(shù)組里 SELECT b ANY(array[a, b, c]); -- true關(guān)于unnest的一個非常優(yōu)秀的用法在 SQL 里做笛卡爾積式展開。比如某個訂單包含多個商品商品數(shù)量存的是數(shù)組用unnest配合WITH ORDINALITY可以同時拿到下標(biāo)和數(shù)據(jù)SELECT * FROM unnest(array[蘋果, 香蕉, 橙子]) WITH ORDINALITY AS t(fruit, ord); -- 蘋果 1 -- 香蕉 2 -- 橙子 3下標(biāo)序號在還原原始數(shù)據(jù)順序時非常重要比如頁面提交的選項順序、JSON 里保存的列表順序。6. 聚合函數(shù)與窗口函數(shù)分析場景的必備武器統(tǒng)計報表離不開聚合函數(shù)而窗口函數(shù)則是 PostgreSQL 實(shí)現(xiàn)復(fù)雜分析場景的殺手锏。這一節(jié)把兩組函數(shù)放在一起講因?yàn)樗鼈兘?jīng)常配合使用。6.1 常用聚合函數(shù)與 FILTER 子句聚合函數(shù)總體跟 MySQL 差異不大sum、avg、count、max、min是最基本的組合但count的幾個寫法需要特別注意SELECT count(*) FROM orders; -- 統(tǒng)計所有行 SELECT count(1) FROM orders; -- 同上性能差異可忽略 SELECT count(order_id) FROM orders; -- 統(tǒng)計非 NULL 的行 SELECT count(DISTINCT user_id) FROM orders; -- 去重統(tǒng)計FILTER子句是 PostgreSQL 特有的聚合擴(kuò)展比CASE WHEN嵌套更簡潔也不需要多處冗余-- 統(tǒng)計各分類的匯總值和滿足條件的值 SELECT category, sum(amount) AS total_amount, sum(amount) FILTER (WHERE status paid) AS paid_amount, count(*) FILTER (WHERE status paid) AS paid_count FROM orders GROUP BY category;等價寫法是用CASE WHEN但代碼明顯更長FILTER在可讀性上優(yōu)勢很大。我個人的項目里凡是統(tǒng)計口徑比較多的報表都優(yōu)先用FILTER。array_agg作為聚合函數(shù)也值得提一下它可以把分組內(nèi)的值拼成數(shù)組再配合array_to_string或string_agg輸出。6.2 窗口函數(shù)分組內(nèi)排序與累計計算窗口函數(shù)在報表場景中基本是頂梁柱。常用的四類排名類、累計類、偏移類、分組聚合類。排名類函數(shù)是最典型的窗口函數(shù)SELECT product_id, sales_amount, row_number() OVER (ORDER BY sales_amount DESC) AS row_num, rank() OVER (ORDER BY sales_amount DESC) AS rank, dense_rank() OVER (ORDER BY sales_amount DESC) AS dense_rank FROM product_sales;row_number和rank的區(qū)別在于處理并列row_number對并列數(shù)據(jù)強(qiáng)行編號不重復(fù)rank產(chǎn)生并列間隔如 1, 1, 3dense_rank產(chǎn)生并列連續(xù)如 1, 1, 2。取 Top N 時如果希望并列都進(jìn)榜單用dense_rank如果嚴(yán)格取前 N 條記錄用row_number。偏移類窗口函數(shù)比如取前一行或后一行的值SELECT day, sales_amount, lag(sales_amount, 1) OVER (ORDER BY day) AS prev_day_sales, lead(sales_amount, 1) OVER (ORDER BY day) AS next_day_sales FROM daily_sales;這種寫法在做環(huán)比、同比分析時非常直接。比子查詢關(guān)聯(lián)的性能好很多代碼也短。累計類窗口函數(shù)可以直接計算累計值比如每天累計銷售額SELECT day, sales_amount, sum(sales_amount) OVER (ORDER BY day) AS cumulative_sales FROM daily_sales;窗口函數(shù)搭配PARTITION BY是分組統(tǒng)計的常態(tài)SELECT category, product_id, sales_amount, avg(sales_amount) OVER (PARTITION BY category) AS avg_in_category FROM product_sales;這是“每個分類的平均銷售額”和“每條記錄的銷售額”同時展示一行數(shù)據(jù)包含當(dāng)前記錄和所在分組的上下文信息在報表導(dǎo)出時特別有用。7. 條件邏輯與空值處理讓數(shù)據(jù)查詢更健壯條件函數(shù)、空值函數(shù)是 SQL 的防御性編程利器。實(shí)際生產(chǎn)環(huán)境里臟數(shù)據(jù)、缺省值幾乎是常態(tài)寫 SQL 時不處理 NULL后面做應(yīng)用層就會很痛苦。7.1 CASE WHEN 的靈活用法CASE WHEN是條件分支的標(biāo)準(zhǔn)寫法能在 SELECT 列表、WHERE 子句、ORDER BY 里使用靈活度非常高。SELECT user_id, CASE WHEN order_count 10 THEN VIP WHEN order_count 1 THEN 普通用戶 ELSE 新用戶 END AS user_level FROM user_stats;注意CASE WHEN是表達(dá)式不是語句?？梢栽诰酆虾瘮?shù)里套用計算滿足條件的值SELECT sum(CASE WHEN status paid THEN amount ELSE 0 END) AS paid_sum FROM orders;7.2 COALESCE 與 NULLIF 的坑COALESCE是最常用的空值處理函數(shù)返回參數(shù)列表中的第一個非 NULL 值SELECT coalesce(NULL, NULL, default); -- default SELECT coalesce(username, email, phone, 未知) FROM users; -- 依次取第一個非空值NULLIF的作用正好相反當(dāng)兩個參數(shù)相等時返回 NULLSELECT nullif(0, 0); -- NULL SELECT nullif(5, 0); -- 5NULLIF最常見的用途是防止除零錯誤SELECT total_amount / nullif(order_count, 0) AS avg_amount FROM user_stats;如果order_count是 0直接除會報錯用NULLIF轉(zhuǎn)成 NULL整體結(jié)果就是 NULL不會中斷查詢。還有一個GREATEST和LEAST在比較多個值時很有用SELECT greatest(1, 2, 3); -- 3 SELECT least(1, 2, 3); -- 1但注意它們的邏輯跟 MySQL 不完全一致遇到 NULL 會返回 NULL這點(diǎn)比 MySQL 更嚴(yán)格。7.3 DISTINCT ON 的獨(dú)特價值DISTINCT ON是 PostgreSQL 特有的語法非常實(shí)用。它可以根據(jù)指定字段去重同時返回該分組內(nèi)的任意一條記錄配合ORDER BY可以精準(zhǔn)控制取哪一條-- 取每個用戶最新的一條訂單 SELECT DISTINCT ON (user_id) user_id, order_id, order_time FROM orders ORDER BY user_id, order_time DESC;這個寫法比窗口函數(shù)更簡潔也不需要子查詢。它內(nèi)部處理邏輯是先按ORDER BY排序再對DISTINCT ON的字段去重保留每個組的第一條記錄。注意ORDER BY里DISTINCT ON指定的字段必須排在最前面否則會報錯。我經(jīng)常用它來取最新價格、最新狀態(tài)、每個用戶最近一次登錄時間等場景性能在索引合理的情況下相當(dāng)不錯。8. 常見問題與排查技巧實(shí)錄這一節(jié)整理幾個我在實(shí)際工作中踩過、也幫別人排查過的典型問題都是查文檔不一定能快速找到答案的細(xì)節(jié)。8.1 PostgreSQL 與 MySQL 函數(shù)習(xí)慣沖突最常見的是字符串截取和拼接MySQL 的LEFT(str, n)、RIGHT(str, n)在 PostgreSQL 里也有但參數(shù)含義一致SUBSTRING則完全不同。PostgreSQL 支持標(biāo)準(zhǔn) SQL 的substring(str from n for m)也支持substr(str, n, m)。從 MySQL 遷過來的人往往對substring的第一印象是“怎么這么別扭”所以我一般建議直接用substr。類型轉(zhuǎn)換也是重災(zāi)區(qū)。MySQL 里123 0就能把字符串轉(zhuǎn)成數(shù)字PostgreSQL 里必須用123::int或CAST寫錯一個字直接報錯。這種嚴(yán)格反而讓數(shù)據(jù)更可信因?yàn)轭愋湾e誤在 SQL 執(zhí)行階段就暴露出來了。8.2 時區(qū)問題的排查實(shí)錄曾經(jīng)接了一個項目數(shù)據(jù)庫存儲的 timestamp 不帶時區(qū)應(yīng)用層傳入的時間卻是北京時間。取出來的數(shù)據(jù)做日期分組時凌晨的數(shù)據(jù)被歸到前一天報表數(shù)據(jù)始終對不上。排查思路是先用current_time確認(rèn)服務(wù)器時區(qū)然后檢查連接字符串是否指定了timezone參數(shù)。后來統(tǒng)一約定數(shù)據(jù)庫統(tǒng)一存 UTC所有取數(shù)操作在 SQL 里用AT TIME ZONE做轉(zhuǎn)換。-- UTC 轉(zhuǎn)北京時間 SELECT now() AT TIME ZONE UTC AT TIME ZONE Asia/Shanghai;8.3 隱式類型轉(zhuǎn)換引發(fā)的索引失效還有一個比較隱蔽的性能問題在 timestamp 列上做字符串比較比如WHERE created_at 2025-06-15。表面看沒問題但 PostgreSQL 的隱式類型轉(zhuǎn)換可能導(dǎo)致索引無法使用。解決方法是顯式轉(zhuǎn)換-- 不推薦可能無法走索引 SELECT * FROM orders WHERE created_at 2025-06-15; -- 推薦讓類型匹配 SELECT * FROM orders WHERE created_at 2025-06-15::date AND created_at (2025-06-15::date 1);這個例子也順便說明了一個日常經(jīng)驗(yàn)日期范圍查詢最好寫成左閉右開區(qū)間既覆蓋了整天數(shù)據(jù)又保證了索引最優(yōu)使用。8.4 常見函數(shù)速查表整理一份我貼在工位上的速查表都是日常出現(xiàn)頻率最高的功能推薦寫法示例字符串拼接concat()concat(a, b)正則提取regexp_matches()regexp_matches(str, pattern)字符串分割取片段split_part()split_part(a-b-c, -, 2)防止除零NULLIFa / NULLIF(b, 0)空值兜底coalesce()coalesce(col, 默認(rèn)值)日期格式化to_char()to_char(now(), YYYY-MM-DD)時間截斷date_trunc()date_trunc(month, now())時間差秒extract(epoch from ...)extract(epoch FROM now() - start_time)JSON 取值-jsonb_col - field分組內(nèi)取最新DISTINCT ONDISTINCT ON (user_id) ... ORDER BY user_id, time DESC累計求和sum() OVER ()sum(amount) OVER (ORDER BY day)9. 結(jié)尾的話從 MySQL 遷到 PostgreSQL剛開始會覺得函數(shù)寫法別扭尤其是類型轉(zhuǎn)換和字符串截取。但用久了就會發(fā)現(xiàn)PostgreSQL 的函數(shù)設(shè)計更貼近標(biāo)準(zhǔn) SQL正則、JSON、窗口函數(shù)這塊的能力是實(shí)打?qū)嵉膹?qiáng)。我在實(shí)際項目中體會最深的一點(diǎn)遇到不確定行為時直接用SELECT不帶表跑一下函數(shù)幾秒鐘就能驗(yàn)證結(jié)果。PostgreSQL 允許這種純表達(dá)式查詢調(diào)試起來比 MySQL 舒服很多。最后分享一個我常用的組合技巧regexp_split_to_table加上unnest配合WITH ORDINALITY可以把很多復(fù)雜字符串解析問題拆成簡單的行級操作。把數(shù)據(jù)展開成行之后再配合窗口函數(shù)和FILTER做統(tǒng)計分析整個處理流程會非常順滑。這篇文章列出的函數(shù)基本覆蓋了日常開發(fā)中九成以上的需求。實(shí)際操作中我對每類函數(shù)都建議在自己環(huán)境的數(shù)據(jù)庫里敲一遍觀察返回類型和結(jié)果。函數(shù)返回值的數(shù)據(jù)類型決定了下一步能調(diào)哪些函數(shù)這是文檔里不太會強(qiáng)調(diào)、但實(shí)際寫 SQL 時非常關(guān)鍵的一點(diǎn)。