## 在PostgreSQL 和 Hive中生成日期序列
### Postgresql實(shí)現(xiàn)日期序列
在postgresql中疾瓮，有 generate_series(start_date, end_date, interval)函數(shù)來(lái)生成日期序列
```
select date(day) as day
from generate_series('2020-05-22'::timestamp, current_date, '1 day'::interval) as day
```
### Hive實(shí)現(xiàn)指定指定開始結(jié)束日期的日期序列
在Hive中梅誓，可以借助 posexplode(list)滞诺、datediff(end_date, start_date)來(lái)實(shí)現(xiàn)越庇。
首先創(chuàng)建一個(gè)表名為calender十艾，字段為day乾巧，類型為date，存入一個(gè)日期數(shù)值作為開始日期查剖，比如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個(gè)空格效览，然后split成list，posexplode將行轉(zhuǎn)多列荡短，同時(shí)返回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個(gè)空格，然后split成list
```
index | value
--- | ---
0 | ' '
1 | ' '
2 | ' '
... | ...
59| ' '
#### Hive在日期序列表添加星期幾
新增一列存放星期幾
```
ALTER TABLE default.calender ADD COLUMNS(weekday STRING);
```
借助函數(shù)`datediff`掘托，`pmod`就可以實(shí)現(xiàn)
datediff 是兩個(gè)日期相減的函數(shù)
語(yǔ)法：`datediff(string enddate, string startdate)`
返回值: int
說(shuō)明: 返回兩個(gè)時(shí)間參數(shù)的相差天數(shù)瘦锹。
pmod 是正取余函數(shù)
語(yǔ)法: `pmod(int a, int b),pmod(double a, double b)`
返回值: int double
說(shuō)明: 返回正的a除以b的余數(shù)
選取一個(gè)日期為星期日的日期作為參照日期，這里我選取了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;
```
### 補(bǔ)充：Hive實(shí)現(xiàn)缺失日期的補(bǔ)全
在統(tǒng)計(jì)一些daily的metrics的時(shí)候，通常使用group by弯院，往往會(huì)存在某些日期沒有數(shù)據(jù)從而導(dǎo)致最后的結(jié)果表的日期其實(shí)不是連續(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
... | ...... | ....
針對(duì)這種情況，需要進(jìn)行以下步驟拆解：
1. 按照store_id進(jìn)行聚合听绳，找出最小颂碘、最大日期
2. 此時(shí)，基于步驟1的CTE表進(jìn)行基于每個(gè)store_id的最小椅挣、最大日期的日期序列補(bǔ)全
```
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的結(jié)果與之前的agg聚合結(jié)果表進(jìn)行`left join`头岔，對(duì)`NULL`用`COALESCE(v, 0)`進(jìn)行缺失值替換。
