PostgreSQL自帶有一個簡易的全文檢索引擎，可以實現(xiàn)小規(guī)模數(shù)據(jù)量的全文檢索功能。本文我們將引導介紹一下這個功能，對于小數(shù)據(jù)量的搜索這個功能是足夠使用的，而無需搭建額外的ES等重量級的全文檢索服務器。
專注于為中小企業(yè)提供網(wǎng)站建設(shè)、成都網(wǎng)站建設(shè)服務,電腦端+手機端+微信端的三站合一,更高效的管理,為中小企業(yè)陸良免費做網(wǎng)站提供優(yōu)質(zhì)的服務。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動了上1000家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設(shè)實現(xiàn)規(guī)模擴充和轉(zhuǎn)變。
PG的全文檢索操作符是 @@ ，當一個 tsvector (文檔)和 tsquery (條件)匹配時返回 true ，并且前后順序無影響:
和普通的SQL查詢一樣，只要在 WHERE 條件中使用這個符號就代表使用全文檢索條件篩選文檔了。如:
@@ 操作符支持隱式轉(zhuǎn)換，對于 text 類型可以無需強類型轉(zhuǎn)換( ::tsvector 或 to_tsvector(config_name, text) )，所以這個操作符實際支持的參數(shù)類型是這樣的:
tsquery 查詢條件并不是簡單的正則，而是一組搜索術(shù)語，使用并且使用布爾操作符 （AND）、 | （OR）和 ! （NOT）來組合它們，還有短語搜索操作符 - （FOLLOWED BY）。更詳細的語法參見 此文檔 。
此外，PostgreSQL還提供了兩個相對簡化的版本 plainto_tsquery 和 phraseto_tsquery 。
plainto_tsquery ( plainto_tsquery([ config regconfig, ] querytext text) returns tsquery )用戶將未格式化的 text 經(jīng)過分詞之后，插入 符號轉(zhuǎn)為 tsquery :
phraseto_tsquery ( phraseto_tsquery([ config regconfig, ] querytext text) returns tsquery )行為和 plainto_tsquery 行為類似，但是分詞之后不是插入 而是 - (FOLLOWED BY):
使用索引可以加快全文檢索的速度。對于全文檢索來說，可選的索引類型是 GIN (通用倒排索引)和 GIST (通用搜索樹)，官方文檔更推薦使用 GIN索引 。創(chuàng)建一個 GIN 索引的范例:
也可以是一個連接列:
還可以單獨創(chuàng)建一個 tsvector 列，為這個列創(chuàng)建索引:
除了普通的 ORDER BY 條件之外，PostgreSQL為全文檢索提供了兩個可選的排序函數(shù) ts_rank([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) returns float4 和 ts_rank_cd([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) returns float4 ，以便實現(xiàn)基于 權(quán)重 的排序。
此外，對于PostgreSQL 9.6以上的版本還可以使用 RUM index 排序。(注意，這個是擴展，默認不包含)。
PostgreSQL默認的分詞字典中并不包含中文分詞字典，因此我們必須手工引入。目前一個比較好的項目是 zhparser ，同時這個插件也是阿里云的RDS默認包含的。安裝和啟用沒什么好說的。值得一提的是分詞配置參數(shù)。
在 CREATE EXTENSION 之后，必須配置分詞參數(shù)才能正確進行分詞和查找，否則什么都查不到。官方文檔提供的一個配置策略是:
n,v,a,i,e,l 這幾個字母分別表示一種token策略，只啟用了這幾種token mapping，其余則被屏蔽。具體支持的參數(shù)和含義可以用 \dFp+ zhparser 顯示:
WITH simple 表示詞典使用的是內(nèi)置的simple詞典，即僅做小寫轉(zhuǎn)換。根據(jù)需要可以靈活定義詞典和token映射，以實現(xiàn)屏蔽詞和同義詞歸并等功能。
比如我們看下面這個例子:
可以看到 江淮 這個詞組在查詢的時候被忽略了，我們啟用 j (abbreviation,簡稱)再看看結(jié)果:
所以實際使用中要設(shè)置合理的token types，過少將導致搜索結(jié)果不準確，過多將導致性能下降。此外，還有一些諸如 短詞復合: zhparser.multi_short = f 這一類的控制分詞結(jié)果的選項，根據(jù)實際使用酌情開啟。
這是因為沒有卸載干凈，卸載之后建議將安裝目錄刪除，將%appdata%中postgresql相關(guān)的文件夾刪除，將我的文檔中的postgresql相關(guān)的文件夾刪除。
我最常用的 Linux 發(fā)行版為 CentOS。為了擁有一個“干凈”的系統(tǒng)，盡量減少不必要的軟件安裝，我一般都會采用 minimal 版本安裝。但安裝完成后并不能滿足我的基本需求；對我來說，還需要安裝一些常用的（對我來說）軟件包。而我需要有很多軟件，在CentOS 自帶的yum 源中并沒有。所以就促使我去尋找其他的可用 yum 軟件源。
企業(yè)級 Linux 擴展軟件包（Extra Packages for Enterprise Linux 即：EPEL）是一個Fedora特殊興趣小組為企業(yè)級 Linux創(chuàng)建、維護和管理的高質(zhì)量的附加軟件包，企業(yè)級Linux 包括但不限于 Red Hat Enterprise Linux(RHEL)，CentOS，Scientific Linux（SL）和 Oracle Linux（OL）。
我一般是在 fedora 官網(wǎng)獲取 epel-release 軟件： 。
我在 CentOS 6 系統(tǒng)下會使用如下指令安裝 epel-release
Remi repository 是包含最新版本 PHP 和 MySQL 包的 Linux 源，由 Remi 提供維護。有個這個源之后，使用 YUM 安裝或更新 PHP、MySQL、phpMyAdmin 等服務器相關(guān)程序的時候就非常方便了。
它依賴 EPEL 軟件包。
我一般是在其官網(wǎng)獲取 remi-release 軟件： Remi's RPM repository
我在 CentOS 6 系統(tǒng)下會使用如下指令安裝 remi-relsase:
RepoForge 的前身是 RPMForge 。
RepoForge項目維護Red Hat Enterprise Linux（RHEL），CentOS和Scientific Linux的RPM軟件包。它提供了一組與各種RHEL派生版本兼容的存儲庫。
我一般是在其官網(wǎng)獲取 RepoForge （rpmforge-release）軟件： RepoForge
我在 CentOS 6 (x86)系統(tǒng)下會使用如下指令安裝 rpmforge-release:
以上三個 Linux 軟件源已經(jīng)夠我當前使用了，如果以后在Linux使用過程中有用到其他的軟件源，我會在此文件下做補充。
很多時候在CentOS系統(tǒng)下默認的Postgresql版本并不能滿足需求。如我安裝GitLab時就要求Postgresql在9.1以上，但CentOS 系統(tǒng) 6.9 版本僅提供了Postgresql 的 8.4.20 版本，而CentOS 7 默認也無法安裝Postgresql 9 及以上版本。
當然你可以使用源碼安裝，但對于大多數(shù)人來說應該會選擇一個更簡單的方法。那就去給你的系統(tǒng)安裝一個postgresql的yum源吧。
網(wǎng)址：
進到首頁你應該可以看到以下內(nèi)容：
選一個Postgresql的發(fā)行版本點進去，會看到如下內(nèi)容：
對應于不同的發(fā)行版本，針對不同的操作系統(tǒng)有著不同的yum源。你可以根據(jù)你的操作系統(tǒng)進行選擇。
比如我選擇了 Postgresql 9.6 版本，而我的操作系統(tǒng)是 CentOS 6.9 ，x86 架構(gòu)。我只需要執(zhí)行以下命令：
然后在 /etc/yum.repos.d 目錄下會多出一個 文件 pgdg-96-centos.repo 。
至此，你就可以使用命令 yum install postgresql96 來安裝postgresql 9.6 版本最新的發(fā)行版了軟件了。
在CentOS6系統(tǒng)中，默認能安裝的MySQL最高版本為5.5.x；而到CentOS7中由于Oracle收購MySQL，因擔心版權(quán)問題，CentOS7放棄了MySQL而采用了Mariadb，最高支持版本為5.5.x。對于用習慣了MySQL的，并且想使用高版本MySQL的我來說，系統(tǒng)默認支持已經(jīng)滿足不了需求。
可以從 這里 選擇一個你所需要的yum源進行安裝。
然后：
然后你就可以執(zhí)行以下命令安裝 MySQL 5.7了
在MongoDB的官網(wǎng)上可以找到MongoDB的yum安裝源配置方式。
MongoDB 官網(wǎng)YUM源配置方式
然后在打開的頁面中會有如下信息：
你需要根據(jù)自己的需求調(diào)整版本號，然后創(chuàng)建一個/etc/yum.repos.d/mongodb-org-y.x.repo文件，把上面的配置寫進去。
完成以上步驟后，就可以使用以下命令安裝MongoDB了。
PostgreSQL安裝：
一、windows下安裝過程
安裝介質(zhì)：postgresql-9.6.2-1-windows-x64(166M)，安裝過程非常簡單，過程如下：
1、開始安裝：
2、選擇程序安裝目錄：
注：安裝 PostgreSQL 的分區(qū)最好是 NTFS 格式的。PostgreSQL 首要任務是要保證數(shù)據(jù)的完整性，而 FAT 和 FAT32 文件系統(tǒng)不能提供這樣的可靠性保障，而且 FAT 文件系統(tǒng)缺乏安全性保障，無法保證原始數(shù)據(jù)在未經(jīng)授權(quán)的情況下被更改。此外，PostgreSQL 所使用的"多分點"功能完成表空間的這一特征在FAT文件系統(tǒng)下無法實現(xiàn)。
然而，在某些系統(tǒng)中，只有一種 FAT 分區(qū)，這種情況下，可以正常安裝 PostgreSQL，但不要進行數(shù)據(jù)庫的初始化工作。安裝完成后，在 FAT 分區(qū)上手動執(zhí)行 initdb.exe 程序即可，但不能保證其安全性和可靠性，并且建立表空間也會失敗。
3、選擇數(shù)據(jù)存放目錄：
4、輸入數(shù)據(jù)庫超級用戶和創(chuàng)建的OS用戶的密碼
注：數(shù)據(jù)庫超級用戶是一個非管理員賬戶，這是為了減少黑客利用在 PostgreSQL 發(fā)現(xiàn)的缺陷對系統(tǒng)造成損害，因此需要對數(shù)據(jù)庫超級用戶設(shè)置密碼，如下圖所示，安裝程序自動建立的服務用戶的用戶名默認為 postgres。
5、設(shè)置服務監(jiān)聽端口，默認為5432
6、選擇運行時語言環(huán)境
注：選擇數(shù)據(jù)庫存儲區(qū)域的運行時語言環(huán)境（字符編碼格式）。
在選擇語言環(huán)境時，若選擇"default locale"會導致安裝不正確；同時，PostgreSQL 不支持 GBK 和 GB18030 作為字符集，如果選擇其它四個中文字符集：中文繁體 香港（Chinese[Traditional], Hong Kong S.A.R.）、中文簡體 新加坡（Chinese[Simplified], Singapore）、中文繁體 臺灣（Chinese[Traditional], Taiwan）和中文繁體 澳門（Chinese[Traditional], Marco S.A.R.），會導致查詢結(jié)果和排序效果不正確。建議選擇"C"，即不使用區(qū)域。
----我選擇了default localt，安裝正確；建議選擇default localt。
7、安裝過程（2分鐘）
8、安裝完成
安裝完成后，從開始文件夾可以看到：
在安裝目錄可以看到：
其中：data存放數(shù)據(jù)文件、日志文件、控制文件、配置文件等。
uninstall-postgresql.exe用于卸載已安裝的數(shù)據(jù)庫管理系統(tǒng)。
pg_env.bat里配置了數(shù)據(jù)庫的幾個環(huán)境變量，內(nèi)容如下：
執(zhí)行以下命令安裝PostgreSQL
$ apt-get install postgresql
安裝好之后，PostgreSQL會創(chuàng)建一個名為postgres的系統(tǒng)用戶，以及一個同樣名字的數(shù)據(jù)庫管理員角色，以及一個同樣名字的數(shù)據(jù)庫。
Debian下配置PostgreSQL
通過cat /etc/passwd列出系統(tǒng)中所有的用戶，可以找到剛剛PostgreSQL為我們創(chuàng)建的postgres用戶：
$ cat /etc/passwd
...
postgres:x:122:129:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash
使用su命令切換到postgres用戶：
$ su postgres
使用passwd命令修改當前系統(tǒng)用戶密碼（可選）：
$ passwd
輸入新的 UNIX 密碼：
重新輸入新的 UNIX 密碼：
passwd：已成功更新密碼
使用createdb命令創(chuàng)建新的數(shù)據(jù)庫：
$ createdb mydb
使用psql命令進入PostgreSQL命令行。psql后面可以接數(shù)據(jù)庫名稱，表示進入特定的數(shù)據(jù)庫。如果不提供數(shù)據(jù)庫名稱，則默認進入postgres數(shù)據(jù)庫。
$ psql
psql (9.1.24)
輸入 "help" 來獲取幫助信息
前面?zhèn)z個章節(jié)我們介紹了窗口函數(shù)、滑動窗口函數(shù)的概念，接下來我們介紹一下PG支持的原生通用窗口函數(shù)，總共11個(9.6版本, 中國社區(qū)官網(wǎng)文檔地址 ）
1、row_number 函數(shù)
row_number函數(shù)可以給每隔數(shù)據(jù)行返回一個虛擬的自增ID,也就是相當于給行分配一個編號，這些編號不會出現(xiàn)重復，即使over()里面沒有按照字段排序字段也能正常工作，
2、rank函數(shù)
rank的官方解釋是：帶間隙的當前行排名； 與該行的第一個同等行的row_number相同
從上面的的結(jié)果我們可以看出 rank函數(shù)和row_number一樣可以將行編號，但是號碼可能重復，比如我們按照年份排序，年份相同的話rank值相同，2017年的數(shù)據(jù)rank直接跳到了7，這就相當于上學的時候考試，用rank計算排名的話，如果同年級出現(xiàn)三個并列的第一名的話，那么計算的結(jié)果將是三個人的rank值都是第一，但是實際上的第二高的分數(shù)的同學會被rank排名為第四名，如果我們想第二高的分數(shù)的排名為2,我們可以使用dense_rank函數(shù)；
3、dense_rank函數(shù)
從上面的結(jié)果我們可以看出dense_rank函數(shù)會把編號弄得更加緊密，中間不會出現(xiàn)像rank那樣的斷層編碼。
4、percent_rank函數(shù)
官方文檔解釋： 當前行的相對排名=(rank- 1) / (總行數(shù) - 1) ,
??? 排名和rank值成正相關(guān) ，rank值相同的行號?percent_rank獲取的結(jié)果也一樣，返回的 結(jié)果是個小數(shù)范圍在[0,1]之間 ，可以等于0或者1
5、cume_dist函數(shù)
官方文檔解釋： 當前行的相對排名=(rank- 1) / (總行數(shù) - 1) ，
??? 排名和rank值成正相關(guān) ，rank值相同的行號?percent_rank獲取的結(jié)果也一樣，返回的 結(jié)果是個小數(shù)范圍在[0,1]之間 ，可以等于0或者1
6、ntile函數(shù)
官方文檔解釋：從1到參數(shù)值的整數(shù)范圍，盡可能等分分區(qū)，
ntile(num_buckets)，num_buckets的值表示將結(jié)果集分成num_buckets組，有限填滿前面的組，最后一組可能出現(xiàn)個數(shù)不足(非等分)情況，實際上就是把每隔行分個組號。
7、lag函數(shù)
官方文檔解釋：lag( value ?anyelement?[,? offset ?integer?[,? default ?anyelement?]])，返回 value ， 它在分區(qū)內(nèi)當前行的之前 offset 個位置的行上計算；如果沒有這樣的行，返回 default 替代。 (作為 value 必須是相同類型)。? offset 和 default 都是根據(jù)當前行計算的結(jié)果。如果忽略它們，則 offset 默認是1， default 默認是空值
? ? 官方文檔的解釋很晦澀難懂，我們直接使用用例執(zhí)行一下看一下數(shù)據(jù)分布就好了
從上圖可以知道當前行的lag值是當前行的前offset行的值，沒有的話就返回default,default不想存在的話就返回null,從數(shù)據(jù)姐過再去看官方文檔的解釋的話可能清晰很多，lag函數(shù)可以在結(jié)果集的行內(nèi)移動，經(jīng)常使用到的場景是計算今年和全年的年產(chǎn)量的差值，
8、lead函數(shù)
官方文檔解釋：lead( value ?anyelement?[,? offset ?integer?[,? default ?anyelement?]])?返回 value ，它在分區(qū)內(nèi)當前行的之后 offset 個位置的行上計算；如果沒有這樣的行，返回 default 替代。(作為 value 必須是相同類型)。 offset 和 default 都是根據(jù)當前行計算的結(jié)果。如果忽略它們，則 offset 默認是1， default 默認是空值
其實lead函數(shù)和lag函數(shù)的作用是相同的，如果lead的offset參數(shù)值為-N,那么lag的offset的參數(shù)為N的話計算結(jié)果是相同的，lag(gdp,-1)是lead(gdp,1)的替代
9、first_value、last_value、nth_value函數(shù)較為簡單不做介紹
至此我們講完了幾乎所有的窗口函數(shù)，希望這五篇關(guān)于PostgreSQL的文章能對大家在平時的開發(fā)中有所幫助
重慶分公司
重慶分公司
