建站
咨詢
售后
建站咨詢
新聞中心
如何生成一個(gè)虛擬表，生成日期在PostgreSQL的序列
日期一覽表 使用generate_series函數(shù)來獲得，你可以添加到一個(gè)日期，以便獲得日期列表號(hào)碼列表：SELECT CURRENT_DATE + s.a AS dates
十載的鄒城網(wǎng)站建設(shè)經(jīng)驗(yàn)，針對(duì)設(shè)計(jì)、前端、開發(fā)、售后、文案、推廣等六對(duì)一服務(wù)，響應(yīng)快，48小時(shí)及時(shí)工作處理。全網(wǎng)整合營銷推廣的優(yōu)勢是能夠根據(jù)用戶設(shè)備顯示端的尺寸不同，自動(dòng)調(diào)整鄒城建站的顯示方式，使網(wǎng)站能夠適用不同顯示終端，在瀏覽器中調(diào)整網(wǎng)站的寬度，無論在任何一種瀏覽器上瀏覽網(wǎng)站，都能展現(xiàn)優(yōu)雅布局與設(shè)計(jì)，從而大程度地提升瀏覽體驗(yàn)。成都創(chuàng)新互聯(lián)公司從事“鄒城網(wǎng)站設(shè)計(jì)”,“鄒城網(wǎng)站推廣”以來，每個(gè)客戶項(xiàng)目都認(rèn)真落實(shí)執(zhí)行。
FROM generate_series(0,14,7) as s(a);
結(jié)果：dates
------------
2004-02-05
2004-02-12
2004-02-19
旋轉(zhuǎn) 轉(zhuǎn)換行數(shù)據(jù)為柱狀的數(shù)據(jù)-你的問題涉及旋轉(zhuǎn)結(jié)果集的后半部分。 PIVOT和UNPIVOT是ANSI，但在PostgreSQL目前我沒有看到他們。最persistent樞軸查詢的是aggregation函數(shù)： SELECT t.account,
SUM(CASE WHEN t.date = '2010-01-01' THEN t.amount END) AS '2010-01-01',
SUM(CASE WHEN t.date = '2010-01-02' THEN t.amount END) AS '2010-01-02',
SUM(CASE WHEN t.date = '2010-01-03' THEN t.amount END) AS '2010-01-03',
SUM(t.amount) AS Balance
FROM (SELECT CURRENT_DATE + s.a AS dates
FROM generate_series(0,14,7) as s(a)) x
LEFT JOIN TRANSACTIONS y ON y.date = x.date
GROUP BY t.account
動(dòng)態(tài)列 ...指動(dòng)態(tài)SQL。
借助Postgresql生成熱力圖
熱力圖，在空間數(shù)據(jù)可視化場景中是一個(gè)非常常見的需求。首先看下一個(gè)完整的熱力圖效果。
前端實(shí)現(xiàn)熱力圖的原理可以看該 博文 。本文不是探究熱力圖前端實(shí)現(xiàn)原理的，是來探討由于熱力圖一次性加載的點(diǎn)過多，產(chǎn)生卡頓的問題。
前面的 博文 也提出了自己的解決辦法。但是若數(shù)據(jù)居多（10w+）,光數(shù)據(jù)傳輸就頭疼。現(xiàn)有的熱力圖的前端組件有很多，可以用openlayer、高德地圖js、百度地圖js、mapbox等。但是大多數(shù)的數(shù)據(jù)結(jié)構(gòu)的都是下面這種形式。
所以需要在后臺(tái)將下面策略實(shí)現(xiàn)，給前臺(tái)頁面?zhèn)鬏敽线m的數(shù)據(jù)就行。
借助postgresql的width_bucket和postgis，僅用0.4s將20W的數(shù)據(jù)壓縮到2500，這就大大降低了數(shù)據(jù)傳輸和渲染的壓力。同時(shí)也不需要對(duì)已有的GIS前端熱力圖組件進(jìn)行修改。
德哥的文章
數(shù)據(jù)可視化：淺談熱力圖如何在前端實(shí)現(xiàn)
如何使用navicat for postgresql建立數(shù)據(jù)庫
第一步，打開桌面上的Navicat快捷方式
第二步，進(jìn)入Navicat主界面
第三步，點(diǎn)擊左上角的文件
第四步，下拉列表中選中新建連接
第五步，選擇PostgreSQL
第六步，彈出新建連接對(duì)話框
第七步，根據(jù)實(shí)際情況填寫相應(yīng)信息，點(diǎn)擊確定
第八步，成功生成連接PostgreSQL數(shù)據(jù)庫
在PostgreSQL 和 Hive中生成日期序列
## 在PostgreSQL 和 Hive中生成日期序列
### Postgresql實(shí)現(xiàn)日期序列
在postgresql中，有 generate_series(start_date, end_date, interval)函數(shù)來生成日期序列
```
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)來實(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ù)
語法：`datediff(string enddate, string startdate)`
返回值: int
說明: 返回兩個(gè)時(shí)間參數(shù)的相差天數(shù)。
pmod 是正取余函數(shù)
語法: `pmod(int a, int b),pmod(double a, double b)`
返回值: int double
說明: 返回正的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)行缺失值替換。
如何在Postgresql中創(chuàng)建序列數(shù)并應(yīng)用在數(shù)據(jù)表
序列數(shù)可以增加postgresql數(shù)據(jù)表的檢索速度，同時(shí)降低數(shù)據(jù)查詢時(shí)的資源消耗。那么如何在postgresql中創(chuàng)建序列數(shù)并且應(yīng)用呢？下面我給大家分享一下。
工具/材料
pgAdmin4
創(chuàng)建序列數(shù)
首先打開pgAdmin4，展開postgresql數(shù)據(jù)庫，找到模式下面的public選項(xiàng)，如下圖所示
接下來在public下面右鍵單擊序列，然后點(diǎn)擊Create下面的sequence選項(xiàng)，如下圖所示
在彈出的創(chuàng)建Sequence界面中首先給序列數(shù)起一個(gè)名字，如下圖所示，注意都用英文
然后切換到Definition頁卡，定義一下序列的增加量，如下圖所示，其中maximum根據(jù)自己的需要進(jìn)行設(shè)置
最后回到數(shù)據(jù)庫主界面，你會(huì)看到序列下面多出了一個(gè)項(xiàng)，這就是我們創(chuàng)建的序列數(shù)了，如下圖所示
在數(shù)據(jù)表中應(yīng)用序列數(shù)
首先選中一個(gè)數(shù)據(jù)表，點(diǎn)擊右側(cè)的編輯按鈕，如下圖所示
在彈出的編輯界面中切換到Columns頁卡，點(diǎn)擊ID簽名的編輯按鈕，如下圖所示
最后在字段的編輯界面中切換到Variables選項(xiàng)卡，然后在Value列中通過nextval函數(shù)帶入剛才定義的序列數(shù)即可，如下圖所示
當(dāng)前名稱：生成postgresql的簡單介紹
本文網(wǎng)址：http://ef60e0e.cn/article/dsccdog.html
