postgresql行轉列

標籤：cross   cte   插入   integer   oss   data   random   交叉表   不重複   

問：怎麼分頁&&按條件&&按順序&&姓名不重複查出資料？

答：其實就是行轉列，那麼，postgresql怎麼進行轉列呢，百度了下，大概有三種寫法

 

寫法1 group by + sum + case when

select name,sum(case when zbfm=‘年齡‘ then value else 0 end) as 年齡,sum(case when zbfm=‘身高‘ then value else 0 end) as 身高,sum(case when zbfm=‘體重‘ then value else 0 end) as 體重from test group by namehaving name like ‘%1‘ and length(name)=4 order by 年齡 desc

 

寫法2用postgresql的crosstab交叉函數

crosstab(unknown, unknown) does not exist

select * fromcrosstab(
‘select name,zbfm,value from test where name like ‘‘%1‘‘ and length(name)=4‘,$$values(‘年齡‘), (‘身高‘), (‘體重‘)$$)as score(name text, 年齡 int, 身高 int, 體重 int)order by 年齡 desc

 

寫法3group by + string_agg + split_part(分組，行轉列，字元切割)

select name, split_part(split_part(temp,‘,‘,1),‘:‘,2) as 年齡,split_part(split_part(temp,‘,‘,2),‘:‘,2) as 身高,split_part(split_part(temp,‘,‘,3),‘:‘,2) as 體重from(select name, string_agg(zbfm||‘:‘||value,‘,‘) as temp from testgroup by name having name like ‘%1‘ and length(name)=4) as t order by 年齡 desc

group by + string_agg

select name, string_agg(zbfm||‘:‘||value,‘,‘) from testgroup by name having name like ‘%1‘ and length(name)=4

 其他建表語句

CREATE TABLE test(  id serial NOT NULL,  value integer,  name character varying,  zbfm character varying,  CONSTRAINT pkey PRIMARY KEY (id))

插入資料(python)

import psycopg2from random import randomconn = psycopg2.connect(database="postgres", user="postgres", password="password", host="ip", port="port")cur = conn.cursor()def insertData():    names = [‘路人甲‘, ‘王尼瑪‘, ‘唐馬儒‘]    zbfms = [‘年齡‘, ‘身高‘, ‘體重‘]    for i in range(100):        sqlstr = ‘insert into test(name, zbfm, value) values‘        for j in range(100):            for name in names:                for zbfm in zbfms:                    sqlstr += "(‘%s‘,‘%s‘,%d),"%(name+str(i*100+j),zbfm,int(100*random()))        cur.execute(sqlstr[:-1])        conn.commit()        print(i)if __name__ == ‘__main__‘:    insertData()    selectData()

 

 

參考

PostgreSQL 實現交叉表（行列轉換）的五種方法

postgresql行轉列