在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数据表的检索速度，同时降低数据查询时的资源消耗。那么如何在postgresql中创建序列数并且应用呢？下面我给大家分享一下。

工具/材料

pgAdmin4

创建序列数

首先打开pgAdmin4，展开postgresql数据库，找到模式下面的public选项，如下图所示

接下来在public下面右键单击序列，然后点击Create下面的sequence选项，如下图所示

在弹出的创建Sequence界面中首先给序列数起一个名字，如下图所示，注意都用英文

然后切换到Definition页卡，定义一下序列的增加量，如下图所示，其中maximum根据自己的需要进行设置

最后回到数据库主界面，你会看到序列下面多出了一个项，这就是我们创建的序列数了，如下图所示

在数据表中应用序列数

首先选中一个数据表，点击右侧的编辑按钮，如下图所示

在弹出的编辑界面中切换到Columns页卡，点击ID签名的编辑按钮，如下图所示

最后在字段的编辑界面中切换到Variables选项卡，然后在Value列中通过nextval函数带入刚才定义的序列数即可，如下图所示

postgresql数据库怎么查询所有的序列名

1、在查询的时候一般使用*查询代表查询所有字段信息，但表太大就不建议使用这种方式。

2、在查询的时候也可以指定字段进行查询，如下图只查询表中的一部分字段信息。

3、不过最经常使用的查询是指定特殊的条件来进行查询，以便查询结果更为精确。

4、在查询的时候有时也需要对查询结果进行适当的排序，这样可以快速定位要查询数据的结果。

5、在查询的时候也可以指定特殊的查询范围，根据指定的查询范围来查询出特定结果。

文章名称：包含postgresql序列的词条
路径分享：http://kswjz.com/article/dsdghei.html