13518219792
序列数可以增加postgresql数据表的检索速度，同时降低数据查询时的资源消耗。那么如何在postgresql中创建序列数并且应用呢？下面我给大家分享一下。
公司主营业务：成都做网站、成都网站建设、移动网站开发等业务。帮助企业客户真正实现互联网宣传，提高企业的竞争能力。创新互联公司是一支青春激扬、勤奋敬业、活力青春激扬、勤奋敬业、活力澎湃、和谐高效的团队。公司秉承以“开放、自由、严谨、自律”为核心的企业文化，感谢他们对我们的高要求，感谢他们从不同领域给我们带来的挑战，让我们激情的团队有机会用头脑与智慧不断的给客户带来惊喜。创新互联公司推出龙城免费做网站回馈大家。
工具/材料
pgAdmin4
创建序列数
首先打开pgAdmin4，展开postgresql数据库，找到模式下面的public选项，如下图所示
接下来在public下面右键单击序列，然后点击Create下面的sequence选项，如下图所示
在弹出的创建Sequence界面中首先给序列数起一个名字，如下图所示，注意都用英文
然后切换到Definition页卡，定义一下序列的增加量，如下图所示，其中maximum根据自己的需要进行设置
最后回到数据库主界面，你会看到序列下面多出了一个项，这就是我们创建的序列数了，如下图所示
在数据表中应用序列数
首先选中一个数据表，点击右侧的编辑按钮，如下图所示
在弹出的编辑界面中切换到Columns页卡，点击ID签名的编辑按钮，如下图所示
最后在字段的编辑界面中切换到Variables选项卡，然后在Value列中通过nextval函数带入刚才定义的序列数即可，如下图所示
Services Management”即“服务”中停止相应的PostgreSQL服务 方式二：通过命令行“NET STOPpostgresql-x64-9.1”停止相应的PostgreSQL服务Step 2:修改注册表对应键值位置在"HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services \postgresql-x64-9。
postgresql中一个序列对象通常用于为行或者表生成唯一的标识符。查看序列：psql
的
\d
命令输出一个数据库对象，包括
Sequence，表，视图和索引。你还可以使用
\ds
命令只查看当前数据库的所有序列。例如：pigdb-#
\ds
List
of
relations
Schema
|
Name
|
Type
|
Owner--------+-----------------------+----------+--------
public
|
author_ids
|
sequence
|
ichexw
public
|
shipments_ship_id_seq
|
sequence
|
ichexw(2
rows)
在oracle中sequence就是所谓的序列号，每次取的时候它会自动增加，一般用在需要按序列号排序的地方。 1、Create Sequence 你首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE权限， CREATE SEQUENCE emp_sequence INCREMENT BY 1 -- 每次加几个 START WITH 1 -- 从1开始计数 NOMAXVALUE -- 不设置最大值 NOCYCLE -- 一直累加，不循环 CACHE 10; 一旦定义了emp_sequence，你就可以用CURRVAL，NEXTVAL CURRVAL=返回 sequence的当前值 NEXTVAL=增加sequence的值，然后返回 sequence 值 比如： emp_sequence.CURRVAL emp_sequence.NEXTVAL 可以使用sequence的地方： - 不包含子查询、snapshot、VIEW的 SELECT 语句 - INSERT语句的子查询中 - NSERT语句的VALUES中 - UPDATE 的 SET中 可以看如下例子： INSERT INTO emp VALUES (empseq.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20); SELECT empseq.currval FROM DUAL; 但是要注意的是： - 第一次NEXTVAL返回的是初始值；随后的NEXTVAL会自动增加你定义的INCREMENT BY值，然后返回增加后的值。CURRVAL 总是返回当前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。一次NEXTVAL会增加一次SEQUENCE的值，所以如果你在同一个语句里面使用多个NEXTVAL，其值就是不一样的。明白？ - 如果指定CACHE值，ORACLE就可以预先在内存里面放置一些sequence，这样存取的快些。cache里面的取完后，oracle自动再取一组到cache。 使用cache或许会跳号， 比如数据库突然不正常down掉（shutdown abort),cache中的sequence就会丢失. 所以可以在create sequence的时候用nocache防止这种情况。 2、Alter Sequence 你或者是该sequence的owner，或者有ALTER ANY SEQUENCE 权限才能改动sequence. 可以alter除start至以外的所有sequence参数.如果想要改变start值，必须 drop sequence 再 re-create . Alter sequence 的例子 ALTER SEQUENCE emp_sequence INCREMENT BY 10 MAXVALUE 10000 CYCLE -- 到10000后从头开始 NOCACHE ; 影响Sequence的初始化参数： SEQUENCE_CACHE_ENTRIES =设置能同时被cache的sequence数目。 可以很简单的Drop Sequence DROP SEQUENCE order_seq; 示例Sequence： CREATE SEQUENCE SCOTT.DMIFPOSTID START WITH 261 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCYCLE CACHE 20 NOORDER 3、如何使用 第一种方法：一般来说需要新建一个触发器（TRIGGER），使得在插入数据之前先运行Sequence生成自增号。示例Trigger -- Create table create table TEST ( SEQ INTEGER not null, NAME VARCHAR2(20), PWD VARCHAR2(20) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); -- Create/Recreate primary, unique and foreign key constraints alter table TEST add constraint PK_TEST primary key (SEQ) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); create or replace trigger TI_TEST before insert on test for each row declare -- local variables here begin SELECT SEQ_TEST.NEXTVAL INTO :NEW.SEQ FROM DUAL; end TI_TEST; 插入语句 insert into test values('aa','aa'); 第二种方法：可以在插入数据时直接调用。 insert into table(id,name) values(seq_name.nextval,'名字');
## 在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)`进行缺失值替换。
