PostgreSQL中实现Update前的备份骚操作

参考文章： 【PostgreSQL 如何实现upsert与新旧数据自动分离】

成都网站建设、成都网站制作的关注点不是能为您做些什么网站，而是怎么做网站，有没有做好网站，给创新互联一个展示的机会来证明自己，这并不会花费您太多时间，或许会给您带来新的灵感和惊喜。面向用户友好，注重用户体验，一切以用户为中心。

很多业务也行有这样的需求，新的数据会不断的插入，并且可能会有更新。 对于更新的数据，需要记录更新前的记录到历史表。 这个需求有点类似于审计需求，即需要对记录变更前后做审计。 本文的目的并不是审计，而且也可能不期望使用触发器。

还有什么方法呢？

PostgreSQL 这么高大上，当然有，而且还能在一句SQL里面完成，看法宝。

创建一张当前状态表，一张历史记录表。

插入一条不存在的记录，不会触发插入历史表的行为。

注意替代变量

插入一条不存在的记录，不会触发插入历史表的行为。

插入一条已存在的记录，并且有数据的变更，触发数据插入历史表的行为。

插入一条已存在的记录，并且已存在的记录值和老值一样，不会触发将数据插入历史表的行为。

执行计划

在PostgreSQL 和 Hive中生成日期序列

## 在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)来实现。

首先创建一个表名为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，往往会存在某些日期没有数据从而导致最后的结果表的日期其实不是连续的齐全序列。

比如：

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全文检索简介

PostgreSQL自带有一个简易的全文检索引擎，可以实现小规模数据量的全文检索功能。本文我们将引导介绍一下这个功能，对于小数据量的搜索这个功能是足够使用的，而无需搭建额外的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索引 。创建一个 GIN 索引的范例:

也可以是一个连接列:

还可以单独创建一个 tsvector 列，为这个列创建索引:

除了普通的 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 这一类的控制分词结果的选项，根据实际使用酌情开启。