參考文章： 【PostgreSQL 如何實現upsert與新舊數據自動分離】
成都網站建設、成都網站制作的關注點不是能為您做些什么網站，而是怎么做網站，有沒有做好網站，給創(chuàng)新互聯一個展示的機會來證明自己，這并不會花費您太多時間，或許會給您帶來新的靈感和驚喜。面向用戶友好，注重用戶體驗，一切以用戶為中心。
很多業(yè)務也行有這樣的需求，新的數據會不斷的插入，并且可能會有更新。 對于更新的數據，需要記錄更新前的記錄到歷史表。 這個需求有點類似于審計需求，即需要對記錄變更前后做審計。 本文的目的并不是審計，而且也可能不期望使用觸發(fā)器。
還有什么方法呢？
PostgreSQL 這么高大上，當然有，而且還能在一句SQL里面完成，看法寶。
創(chuàng)建一張當前狀態(tài)表，一張歷史記錄表。
插入一條不存在的記錄，不會觸發(fā)插入歷史表的行為。
注意替代變量
插入一條不存在的記錄，不會觸發(fā)插入歷史表的行為。
插入一條已存在的記錄，并且有數據的變更，觸發(fā)數據插入歷史表的行為。
插入一條已存在的記錄，并且已存在的記錄值和老值一樣，不會觸發(fā)將數據插入歷史表的行為。
執(zhí)行計劃
## 在PostgreSQL 和 Hive中生成日期序列
### Postgresql實現日期序列
在postgresql中，有 generate_series(start_date, end_date, interval)函數來生成日期序列
```
select date(day) as day
from generate_series('2020-05-22'::timestamp, current_date, '1 day'::interval) as day
```
### Hive實現指定指定開始結束日期的日期序列
在Hive中，可以借助 posexplode(list)、datediff(end_date, start_date)來實現。
首先創(chuàng)建一個表名為calender，字段為day，類型為date，存入一個日期數值作為開始日期，比如2014-01-01。
```
CREATE TABLE default.calender (day DATE);
INSERT INTO TABLE default.calender VALUES(to_date('2014-01-01T00:00'));
```
借助 `datediff(end_date, start_date)`、`space(int_count)`、`split(list,seperator)`、`posexplode(list) `生成n個空格，然后split成list，posexplode將行轉多列，同時返回index和value。
```
select date_add(day,idx) as new_day from default.calender
lateral view posexplode( split( space( datediff( current_date, to_date('2014-01-01T00:00:00') ) ), ' ')? ) tt as idx, v;
```
中間過程解釋：
比如：
```
select datediff('2020-06-30','2020-05-1'); -- 60
select split(space(datediff('2020-06-30','2020-05-1')),' ') -- 生成60個空格，然后split成list
```
index | value
--- | ---
0 | ' '
1 | ' '
2 | ' '
... | ...
59| ' '
#### Hive在日期序列表添加星期幾
新增一列存放星期幾
```
ALTER TABLE default.calender ADD COLUMNS(weekday STRING);
```
借助函數`datediff`，`pmod`就可以實現
datediff 是兩個日期相減的函數
語法：`datediff(string enddate, string startdate)`
返回值: int
說明: 返回兩個時間參數的相差天數。
pmod 是正取余函數
語法: `pmod(int a, int b),pmod(double a, double b)`
返回值: int double
說明: 返回正的a除以b的余數
選取一個日期為星期日的日期作為參照日期，這里我選取了2013-12-29
`pmod(datediff( date, '2012-01-01'), 7)? `
返回值：int 0-6
0-6分別表示星期日-星期六
```
INSERT OVERWRITE TABLE default.calender
select date_add(day,idx) as `date`,
-- 0-6 分別代表星期日-星期六
case pmod(datediff(date_add(day,idx), to_date('2013-12-29T00:00:00')), 7)
? ? when 0 then '星期日'
? ? when 1 then '星期一'
? ? when 2 then '星期二'
? ? when 3 then '星期三'
? ? when 4 then '星期四'
? ? when 5 then '星期五'
? ? when 6 then '星期六'
END as weekday
from default.calender
lateral view posexplode( split( space( datediff( to_date('2030-01-01T00:00:00') , to_date('2014-01-01T00:00:00') ) ), ' ')? ) tt as idx, v;
```
### 補充：Hive實現缺失日期的補全
在統計一些daily的metrics的時候，通常使用group by，往往會存在某些日期沒有數據從而導致最后的結果表的日期其實不是連續(xù)的齊全序列。
比如：
store_id | date | count
---- | ---- | -----
1 | 2020-04-02 | 45
2 | 2020-04-02 | 10
2 | 2020-04-03 | 10
1 | 2020-04-05 | 50
2 | 2020-04-06 | 10
1 | 2020-04-08 | 50
... | ...... | ....
針對這種情況，需要進行以下步驟拆解：
1. 按照store_id進行聚合，找出最小、最大日期
2. 此時，基于步驟1的CTE表進行基于每個store_id的最小、最大日期的日期序列補全
```
select t.store_id, date_add(t.min_date, idx) as `date`
from store_with_min_max_usage_date t
lateral view posexplode(split(space(datediff(t.max_date, t.min_date)),' ')) pe as idx, v
```
3. 將步驟2的結果與之前的agg聚合結果表進行`left join`，對`NULL`用`COALESCE(v, 0)`進行缺失值替換。
PostgreSQL自帶有一個簡易的全文檢索引擎，可以實現小規(guī)模數據量的全文檢索功能。本文我們將引導介紹一下這個功能，對于小數據量的搜索這個功能是足夠使用的，而無需搭建額外的ES等重量級的全文檢索服務器。
PG的全文檢索操作符是 @@ ，當一個 tsvector (文檔)和 tsquery (條件)匹配時返回 true ，并且前后順序無影響:
和普通的SQL查詢一樣，只要在 WHERE 條件中使用這個符號就代表使用全文檢索條件篩選文檔了。如:
@@ 操作符支持隱式轉換，對于 text 類型可以無需強類型轉換( ::tsvector 或 to_tsvector(config_name, text) )，所以這個操作符實際支持的參數類型是這樣的:
tsquery 查詢條件并不是簡單的正則，而是一組搜索術語，使用并且使用布爾操作符 （AND）、 | （OR）和 ! （NOT）來組合它們，還有短語搜索操作符 - （FOLLOWED BY）。更詳細的語法參見 此文檔 。
此外，PostgreSQL還提供了兩個相對簡化的版本 plainto_tsquery 和 phraseto_tsquery 。
plainto_tsquery ( plainto_tsquery([ config regconfig, ] querytext text) returns tsquery )用戶將未格式化的 text 經過分詞之后，插入 符號轉為 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為全文檢索提供了兩個可選的排序函數 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 ，以便實現基于 權重 的排序。
此外，對于PostgreSQL 9.6以上的版本還可以使用 RUM index 排序。(注意，這個是擴展，默認不包含)。
PostgreSQL默認的分詞字典中并不包含中文分詞字典，因此我們必須手工引入。目前一個比較好的項目是 zhparser ，同時這個插件也是阿里云的RDS默認包含的。安裝和啟用沒什么好說的。值得一提的是分詞配置參數。
在 CREATE EXTENSION 之后，必須配置分詞參數才能正確進行分詞和查找，否則什么都查不到。官方文檔提供的一個配置策略是:
n,v,a,i,e,l 這幾個字母分別表示一種token策略，只啟用了這幾種token mapping，其余則被屏蔽。具體支持的參數和含義可以用 \dFp+ zhparser 顯示:
WITH simple 表示詞典使用的是內置的simple詞典，即僅做小寫轉換。根據需要可以靈活定義詞典和token映射，以實現屏蔽詞和同義詞歸并等功能。
比如我們看下面這個例子:
可以看到 江淮 這個詞組在查詢的時候被忽略了，我們啟用 j (abbreviation,簡稱)再看看結果:
所以實際使用中要設置合理的token types，過少將導致搜索結果不準確，過多將導致性能下降。此外，還有一些諸如 短詞復合: zhparser.multi_short = f 這一類的控制分詞結果的選項，根據實際使用酌情開啟。
重慶分公司
重慶分公司
