【轉(zhuǎn)】PostgreSQL多行變一行

PGSQL多行聚合，PGSQL多行合并為一行，PGSQL多行合并

創(chuàng)新互聯(lián)是一家集網(wǎng)站建設(shè),二七企業(yè)網(wǎng)站建設(shè),二七品牌網(wǎng)站建設(shè),網(wǎng)站定制,二七網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營(yíng)銷,網(wǎng)絡(luò)優(yōu)化,二七網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競(jìng)爭(zhēng)力?？沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專業(yè)、時(shí)尚、前沿，時(shí)刻以成就客戶成長(zhǎng)自我，堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。

《PostgreSQL 多行變一行》

《PostgreSQL官方文檔-聚集函數(shù)》

array_agg(distinct(字段名)) 去重后可以獲得拼接唯一的字段

使用sqoop將hive中的數(shù)據(jù)導(dǎo)入關(guān)系型數(shù)據(jù)庫(kù)怎么去重

直接導(dǎo)入hive表

sqoop import --connect jdbc:postgresql://ip/db_name--username user_name --table table_name --hive-import -m 5

內(nèi)部執(zhí)行實(shí)際分三部，1.將數(shù)據(jù)導(dǎo)入hdfs（可在hdfs上找到相應(yīng)目錄），2.創(chuàng)建hive表名相同的表，3，將hdfs上數(shù)據(jù)傳入hive表中

sqoop根據(jù)postgresql表創(chuàng)建hive表

sqoop create-hive-table --connect jdbc:postgresql://ip/db_name --username user_name --table table_name --hive-table hive_table_name( --hive-partition-key partition_name若需要分區(qū)則加入分區(qū)名稱)

導(dǎo)入hive已經(jīng)創(chuàng)建好的表中

sqoop import --connect jdbc:postgresql://ip/db_name --username user_name --table table_name --hive-import -m 5 --hive-table hive_table_name (--hive-partition-key partition_name --hive-partition-value partititon_value);

使用query導(dǎo)入hive表

sqoop import --connect jdbc:postgresql://ip/db_name --username user_name --query "select ,* from retail_tb_order where \$CONDITIONS" --hive-import -m 5 --hive-table hive_table_name (--hive-partition-key partition_name --hive-partition-value partititon_value);

注意：$CONDITIONS條件必須有，query子句若用雙引號(hào)，則$CONDITIONS需要使用\轉(zhuǎn)義，若使用單引號(hào)，則不需要轉(zhuǎn)義。

云數(shù)據(jù)庫(kù)RDS是什么？

云數(shù)據(jù)RDS是關(guān)系型數(shù)據(jù)庫(kù)服務(wù)（Relational Database Service）的簡(jiǎn)稱，是一種即開即用、穩(wěn)定可靠、可彈性伸縮的在線數(shù)據(jù)庫(kù)服務(wù)。具有多重安全防護(hù)措施和完善的性能監(jiān)控體系，并提供專業(yè)的數(shù)據(jù)庫(kù)備份、恢復(fù)及優(yōu)化方案，使您能專注于應(yīng)用開發(fā)和業(yè)務(wù)發(fā)展。

關(guān)系模型就是指二維表格模型,因而一個(gè)關(guān)系型數(shù)據(jù)庫(kù)就是由二維表及其之間的聯(lián)系組成的一個(gè)數(shù)據(jù)組織。 當(dāng)前主流的關(guān)系型數(shù)據(jù)庫(kù)有Oracle、DB2、PostgreSQL、Microsoft SQL Server、Microsoft Access、MySQL等。

云關(guān)系型數(shù)據(jù)庫(kù)（RDS）是一種穩(wěn)定可靠、可彈性伸縮的在線數(shù)據(jù)庫(kù)服務(wù)，支持MySQL、SQL Server、PostgreSQL、PPAS（Postgre Plus Advanced Server，高度兼容Oracle數(shù)據(jù)庫(kù)）、MariaDB等引擎，并且提供了容災(zāi)、備份、恢復(fù)、監(jiān)控、遷移等方面的全套解決方案。

云數(shù)據(jù)庫(kù)的特性有：實(shí)例創(chuàng)建快速、支持只讀實(shí)例、讀寫分離、故障自動(dòng)切換、數(shù)據(jù)備份、Binlog備份、SQL審計(jì)、訪問(wèn)白名單、監(jiān)控與消息通知等。

擴(kuò)展資料：

云數(shù)據(jù)庫(kù)RDS優(yōu)勢(shì)

1、輕松部署。用戶能夠在RDS控制臺(tái)輕松的完成數(shù)據(jù)庫(kù)申請(qǐng)和創(chuàng)建，RDS實(shí)例在幾分鐘內(nèi)就可以準(zhǔn)備就緒并投入使用。用戶通過(guò)RDS提供的功能完善的控制臺(tái)，對(duì)所有實(shí)例進(jìn)行統(tǒng)一管理。

2、高可靠。云數(shù)據(jù)庫(kù)具有故障自動(dòng)單點(diǎn)切換、數(shù)據(jù)庫(kù)自動(dòng)備份等功能，保證實(shí)例高可用和數(shù)據(jù)安全。免費(fèi)提供7天數(shù)據(jù)備份，可恢復(fù)或回滾至7天內(nèi)任意備份點(diǎn)。

3、低成本。DS支付的費(fèi)用遠(yuǎn)低于自建數(shù)據(jù)庫(kù)所需的成本，用戶可以根據(jù)自己的需求選擇不同套餐，使用很低的價(jià)格得到一整套專業(yè)的數(shù)據(jù)庫(kù)支持服務(wù)。

參考資料來(lái)源：百度百科—云數(shù)據(jù)庫(kù)

mysql數(shù)據(jù)類型中blob和binary的區(qū)別？

MySQL 數(shù)據(jù)類型細(xì)分下來(lái)，大概有以下幾類：

數(shù)值，典型代表為 tinyint,int,bigint

浮點(diǎn)/定點(diǎn)，典型代表為 float,double,decimal 以及相關(guān)的同義詞

字符串，典型代表為 char,varchar

時(shí)間日期，典型代表為 date,datetime,time,timestamp

二進(jìn)制，典型代表為 binary,varbinary

位類型

枚舉類型

集合類型

大對(duì)象，比如 text,blob

json 文檔類型

一、數(shù)值類型（不是數(shù)據(jù)類型，別看錯(cuò)了）如果用來(lái)存放整數(shù)，根據(jù)范圍的不同，選擇不同的類型。

以上是幾個(gè)整數(shù)選型的例子。整數(shù)的應(yīng)用范圍最廣泛，可以用來(lái)存儲(chǔ)數(shù)字，也可以用來(lái)存儲(chǔ)時(shí)間戳，還可以用來(lái)存儲(chǔ)其他類型轉(zhuǎn)換為數(shù)字后的編碼，如 IPv4 等。示例 1用 int32 來(lái)存放 IPv4 地址，比單純用字符串節(jié)省空間。表 x1，字段 ipaddr，利用函數(shù) inet_aton，檢索的話用函數(shù) inet_ntoa。

查看磁盤空間占用，t3 占用最大，t1 占用最小。所以說(shuō)如果整數(shù)存儲(chǔ)范圍有固定上限，并且未來(lái)也沒(méi)有必要擴(kuò)容的話，建議選擇最小的類型，當(dāng)然了對(duì)其他類型也適用。root@ytt-pc:/var/lib/mysql/3305/ytt# ls -sihl總用量 3.0G3541825 861M -rw-r----- 1 mysql mysql 860M 12月 10 11:36 t1.ibd3541820 989M -rw-r----- 1 mysql mysql 988M 12月 10 11:38 t2.ibd3541823 1.2G -rw-r----- 1 mysql mysql 1.2G 12月 10 11:39 t3.ibd

二、浮點(diǎn)數(shù) / 定點(diǎn)數(shù)先說(shuō)?浮點(diǎn)數(shù)，float 和 double 都代表浮點(diǎn)數(shù)，區(qū)別簡(jiǎn)單記就是 float 默認(rèn)占 4 Byte。float(p) 中的 p 代表整數(shù)位最小精度。如果 p 24 則直接轉(zhuǎn)換為 double，占 8 Byte。p 最大值為 53，但最大值存在計(jì)算不精確的問(wèn)題。再說(shuō)?定點(diǎn)數(shù)，包括 decimal 以及同義詞 numeric，定點(diǎn)數(shù)的整數(shù)位和小數(shù)位分別存儲(chǔ)，有效精度最大不能超過(guò) 65。所以區(qū)別于 float 的在于精確存儲(chǔ)，必須需要精確存儲(chǔ)或者精確計(jì)算的最好定義為 decimal 即可。示例 3創(chuàng)建一張表 y1，分別給字段 f1,f2,f3 不同的類型。mysql-(ytt/3305)-create table y1(f1 float,f2 double,f3 decimal(10,2));Query OK, 0 rows affected (0.03 sec)

三、字符類型字符類型和整形一樣，用途也很廣。用來(lái)存儲(chǔ)字符、字符串、MySQL 所有未知的類型?？梢院?jiǎn)單說(shuō)是萬(wàn)能類型！

char(10) 代表最大支持 10 個(gè)字符存儲(chǔ)，varhar(10) 雖然和 char(10) 可存儲(chǔ)的字符數(shù)一樣多，不同的是 varchar 類型存儲(chǔ)的是實(shí)際大小，char 存儲(chǔ)的理論固定大小。具體的字節(jié)數(shù)和字符集相關(guān)。示例 4例如下面表 t4 ，兩個(gè)字段 c1,c2，分別為 char 和 varchar。mysql-(ytt/3305)-create table t4 (c1 char(20),c2 varchar(20));Query OK, 0 rows affected (0.02 sec)

所以在 char 和 varchar 選型上，要注意看是否合適的取值范圍。比如固定長(zhǎng)度的值，肯定要選擇 char；不確定的值，則選擇 varchar。

四、日期類型日期類型包含了 date,time,datetime,timestamp，以及 year。year 占 1 Byte，date 占 3 Byte?！?/p>

time,timestamp,datetime 在不包含小數(shù)位時(shí)分別占用 3 Byte,4 Byte,8 Byte；小數(shù)位部分另外計(jì)算磁盤占用，見(jiàn)下面表格。

請(qǐng)點(diǎn)擊輸入圖片描述

請(qǐng)點(diǎn)擊輸入圖片描述

請(qǐng)點(diǎn)擊輸入圖片描述

注意：timestamp 代表的時(shí)間戳是一個(gè) int32 存儲(chǔ)的整數(shù)，取值范圍為 '1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'；datetime 取值范圍為 '1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'。

綜上所述，日期這塊類型的選擇遵循以下原則：

1. 如果時(shí)間有可能超過(guò)時(shí)間戳范圍，優(yōu)先選擇 datetime。2. 如果需要單獨(dú)獲取年份值，比如按照年來(lái)分區(qū)，按照年來(lái)檢索等，最好在表中添加一個(gè) year 類型來(lái)參與。3. 如果需要單獨(dú)獲取日期或者時(shí)間，最好是單獨(dú)存放，而不是簡(jiǎn)單的用 datetime 或者 timestamp。后面檢索時(shí)，再加函數(shù)過(guò)濾，以免后期增加 SQL 編寫帶來(lái)額外消耗。

4. 如果有保存毫秒類似的需求，最好是用時(shí)間類型自己的特性，不要直接用字符類型來(lái)代替。MySQL 內(nèi)部的類型轉(zhuǎn)換對(duì)資源額外的消耗也是需要考慮的。

示例 5

建立表 t5，對(duì)這些可能需要的字段全部分離開，這樣以后寫 SQL 語(yǔ)句的時(shí)候就很容易了。

當(dāng)然了，這種情形占用額外的磁盤空間。如果想在易用性與空間占用量大這兩點(diǎn)來(lái)折中，可以用 MySQL 的虛擬列來(lái)實(shí)時(shí)計(jì)算。比如假設(shè) c5 字段不存在，想要得到 c5 的結(jié)果。mysql-(ytt/3305)-alter table t5 drop c5, add c5 year generated always as (year(c1)) virtual;Query OK, 1 row affected (2.46 sec)Records: 1 ?Duplicates: 0 ?Warnings: 0

五、二進(jìn)制類型

binary 和 varbinary 對(duì)應(yīng)了 char 和 varchar 的二進(jìn)制存儲(chǔ)，相關(guān)的特性都一樣。不同的有以下幾點(diǎn)：

binary(10)/varbinary(10) 代表的不是字符個(gè)數(shù)，而是字節(jié)數(shù)。

行結(jié)束符不一樣。char 的行結(jié)束符是 \0，binary 的行結(jié)束符是 0x00。

由于是二進(jìn)制存儲(chǔ)，所以字符編碼以及排序規(guī)則這類就直接無(wú)效了。

示例 6

來(lái)看這個(gè) binary 存取的簡(jiǎn)單示例，還是之前的變量 @a。

切記！這里要提前計(jì)算好 @a 占用的字節(jié)數(shù)，以防存儲(chǔ)溢出。

六、位類型

bit 為 MySQL 里存儲(chǔ)比特位的類型，最大支持 64 比特位， 直接以二進(jìn)制方式存儲(chǔ)，一般用來(lái)存儲(chǔ)狀態(tài)類的信息。比如，性別，真假等。具有以下特性：

1. 對(duì)于 bit(8) 如果單純存放 1 位，左邊以 0 填充 00000001。2. 查詢時(shí)可以直接十進(jìn)制來(lái)過(guò)濾數(shù)據(jù)。3. 如果此字段加上索引，MySQL 不會(huì)自己做類型轉(zhuǎn)換，只能用二進(jìn)制來(lái)過(guò)濾。

示例 7

創(chuàng)建表 c1, 字段性別定義一個(gè)比特位。mysql-(ytt/3305)-create table c1(gender bit(1));Query OK, 0 rows affected (0.02 sec)

mysql-(ytt/3305)-select cast(gender as unsigned) ?'f1' from c1;+------+| f1 ? |+------+| ? ?0 || ? ?1 |+------+2 rows in set (0.00 sec)

過(guò)濾數(shù)據(jù)也一樣，二進(jìn)制或者直接十進(jìn)制都行。mysql-(ytt/3305)-select conv(gender,16,10) as gender \???- from c1 where gender = b'1';?+--------+| gender |+--------+| 1??????|+--------+1 row in set (0.00 sec)????mysql-(ytt/3305)-select conv(gender,16,10) as gender \????- from c1 where gender = '1';+--------+| gender |+--------+| 1??????|+--------+1 row in set (0.00 sec)

其實(shí)這樣的場(chǎng)景，也可以定義為 char(0)，這也是類似于 bit 非常優(yōu)化的一種用法。

mysql-(ytt/3305)-create table c2(gender char(0));Query OK, 0 rows affected (0.03 sec)

那現(xiàn)在我給表 c1 簡(jiǎn)單的造點(diǎn)測(cè)試數(shù)據(jù)。

mysql-(ytt/3305)-select count(*) from c1;+----------+| count(*) |+----------+| 33554432 |+----------+1 row in set (1.37 sec)

把 c1 的數(shù)據(jù)全部插入 c2。

mysql-(ytt/3305)-insert into c2 select if(gender = 0,'',null) from c1;Query OK, 33554432 rows affected (2 min 18.80 sec)Records: 33554432 ?Duplicates: 0 ?Warnings: 0

兩張表的磁盤占用差不多。root@ytt-pc:/var/lib/mysql/3305/ytt# ls -sihl總用量 1.9G4085684 933M -rw-r----- 1 mysql mysql 932M 12月 11 10:16 c1.ibd4082686 917M -rw-r----- 1 mysql mysql 916M 12月 11 10:22 c2.ibd

檢索方式稍微有些不同，不過(guò)效率也差不多。所以說(shuō)，字符類型不愧為萬(wàn)能類型。

七、枚舉類型

枚舉類型，也即 enum。適合提前規(guī)劃好了所有已經(jīng)知道的值，且未來(lái)最好不要加新值的情形。枚舉類型有以下特性：

1. 最大占用 2 Byte。2. 最大支持 65535 個(gè)不同元素。3. MySQL 后臺(tái)存儲(chǔ)以下標(biāo)的方式，也就是 tinyint 或者 smallint 的方式，下標(biāo)從 1 開始。4. 排序時(shí)按照下標(biāo)排序，而不是按照里面元素的數(shù)據(jù)類型。所以這點(diǎn)要格外注意。

示例 8

創(chuàng)建表 t7。mysql-(ytt/3305)-create table t7(c1 enum('mysql','oracle','dble','postgresql','mongodb','redis','db2','sql server'));Query OK, 0 rows affected (0.03 sec)

八、集合類型

集合類型 SET 和枚舉類似，也是得提前知道有多少個(gè)元素。SET 有以下特點(diǎn)：

1. 最大占用 8 Byte，int64。2. 內(nèi)部以二進(jìn)制位的方式存儲(chǔ)，對(duì)應(yīng)的下標(biāo)如果以十進(jìn)制來(lái)看，就分別為 1,2,4,8，...，pow(2,63)。3. 最大支持 64 個(gè)不同的元素，重復(fù)元素的插入，取出來(lái)直接去重。4. 元素之間可以組合插入，比如下標(biāo)為 1 和 2 的可以一起插入，直接插入 3 即可。

示例 9

定義表 c7 字段 c1 為 set 類型，包含了 8 個(gè)值，也就是下表最大為 pow(2,7)。

mysql-(ytt/3305)-create table c7(c1 set('mysql','oracle','dble','postgresql','mongodb','redis','db2','sql server'));Query OK, 0 rows affected (0.02 sec)

插入 1 到 128 的所有組合。

mysql-(ytt/3305)-INSERT INTO c7WITH RECURSIVE ytt_number (cnt) AS ( ? ? ? ?SELECT 1 AS cnt ? ? ? ?UNION ALL ? ? ? ?SELECT cnt + 1 ? ? ? ?FROM ytt_number ? ? ? ?WHERE cnt pow(2, 7) ? ?)SELECT *FROM ytt_number;Query OK, 128 rows affected (0.01 sec)Records: 128 ?Duplicates: 0 ?Warnings: 0

九、數(shù)據(jù)類型在存儲(chǔ)函數(shù)中的用法

函數(shù)里除了顯式聲明的變量外，默認(rèn) session 變量的數(shù)據(jù)類型很弱，隨著給定值的不同隨意轉(zhuǎn)換。

示例 10

定義一個(gè)函數(shù)，返回兩個(gè)給定參數(shù)的乘積。定義里有兩個(gè)變量，一個(gè)是 v_tmp 顯式定義為 int64，另外一個(gè) @vresult 隨著給定值的類型隨意變換類型。

簡(jiǎn)單調(diào)用下。

mysql-(ytt/3305)-select ytt_sample_data_type(1111,222) 'result';+--------------------------+| result ? ? ? ? ? ? ? ? ? |+--------------------------+| The result is: '246642'. |+--------------------------+1 row in set (0.00 sec)

總結(jié)

本篇把 MySQL 基本的數(shù)據(jù)類型做了簡(jiǎn)單的介紹，并且用了一些容易理解的示例來(lái)梳理這些類型。我們?cè)趯?shí)際場(chǎng)景中，建議選擇適合最合適的類型，不建議所有數(shù)據(jù)類型簡(jiǎn)單的最大化原則。比如能用 varchar(100)，不用 varchar(1000)。

數(shù)據(jù)分析校招sql50題-1

1.查詢" 01 "課程比" 02 "課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)

第一步 先在分?jǐn)?shù)表中把課程1和課程2的數(shù)據(jù)分別篩選出之后對(duì)比課程1比課程2高的學(xué)生id，

第二步 結(jié)果再與學(xué)生表關(guān)聯(lián)

結(jié)果：

ps：

2.查詢平均成績(jī)大于等于 60 分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)

'''

select sname, student.sid , r.avg_score from student right join

(select sid, avg(score) as avg_score from sc group by sid having avg(score) 60) r

on student.sid = r.sid

PS：

第一步 從sc表中將sid去重查出后與student表關(guān)聯(lián)

第二部 將符合第一步結(jié)果的數(shù)據(jù)從student表中展示出來(lái)

結(jié)果：

4.查詢所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名、選課總數(shù)、所有課程的成績(jī)總和

第一步 將sc表中學(xué)生按sid分組，并聚合計(jì)算數(shù)量、總分。同時(shí)注意子查詢中聚合函數(shù)要有別名( as cnum、as sum_score)

第二步 將第一步中結(jié)果與student表關(guān)聯(lián)

結(jié)果：

第一步 從teacher表中找到張三老師tid

第二步 用tid關(guān)聯(lián)course表找到張三老師教授課程的cid

第三步 用cid關(guān)聯(lián)score表找到上過(guò)張三老師課程的學(xué)生的sid

第四步 用第三步的sid關(guān)聯(lián)student表查詢出具體符合條件的學(xué)生的詳細(xì)信息

太笨了，多表聯(lián)合查詢：

結(jié)果：

排除學(xué)全所有課程之外的學(xué)生id（not in語(yǔ)法），再與student表關(guān)聯(lián)

結(jié)果：

8.查詢至少有一門課與學(xué)號(hào)為" 01 "的同學(xué)所學(xué)相同的同學(xué)的信息

第一步 將學(xué)號(hào)為01的學(xué)生所學(xué)課程id從sc表中篩選出來(lái)

第二步 查詢sc表中所學(xué)課程id在上面得到的結(jié)果中的學(xué)生id（in語(yǔ)法），去重

第三步 查詢student表中學(xué)生id在上面得到結(jié)果中的學(xué)生信息（in語(yǔ)法）

mysql有g(shù)roup_concat函數(shù) 參考

postgresql稍微麻煩點(diǎn)。

---ARRAY_AGG()函數(shù)是一個(gè)聚合函數(shù)，它接受一組值并返回一個(gè)數(shù)組，其中將輸入集中的每個(gè)值分配給該數(shù)組的元素。

----unnest(anyarray)

返回值:setof anyelement(可以理解為一個(gè)(臨時(shí))表)

說(shuō)明:unnest函數(shù)將輸入的數(shù)組轉(zhuǎn)換成一個(gè)表,這個(gè)表的每一列都代表相應(yīng)的一個(gè)數(shù)組中的元素。如果unnest與其他字段一起出現(xiàn)在select中，就相當(dāng)于其他字段進(jìn)行了一次join。

----array_to_string("數(shù)組",",") 即把數(shù)組轉(zhuǎn)化為字符串，并用“,”連接(使用提供的分隔符連接數(shù)組元素)

結(jié)果:

第一步 查出張三老師教授的課程id

第二步 在sc表中查出上過(guò)張三老師課程的學(xué)生id

第三步 將結(jié)果的非（not in）去篩選student表

文章標(biāo)題：關(guān)于postgresql取重的信息
分享URL：http://www.rwnh.cn/article16/dsdhhdg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供App開發(fā)、外貿(mào)網(wǎng)站建設(shè)、標(biāo)簽優(yōu)化、移動(dòng)網(wǎng)站建設(shè)、網(wǎng)站內(nèi)鏈、App設(shè)計(jì)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)