postgresql – 如何从hstore数据创建数据透视表?
发布时间:2020-12-13 16:07:15 所属栏目:百科 来源:网络整理
导读:想象一下,我有一辆带有现场数据的桌面车: CARSname | datacar 1 | { "doors" = "5","engine" = "1.1" }car 2 | { "doors" = "3","engine" = "1.1","air_conditioning" = "true" }car 3 | { "doors" = "5","engine" = "1.4" } 假设数据键是动态的(可以添加更
想象一下,我有一辆带有现场数据的桌面车:
CARS name | data car 1 | { "doors" => "5","engine" => "1.1" } car 2 | { "doors" => "3","engine" => "1.1","air_conditioning" => "true" } car 3 | { "doors" => "5","engine" => "1.4" } 假设数据键是动态的(可以添加更多),我如何从这些数据创建一个数据透视表,如下所示: CROSSTAB name | doors | engine | air_conditioning car 1 | 5 | 1.1 | car 2 | 3 | 1.1 | "true" car 3 | 5 | 1.4 | 解决方法
以下是如何获得您要求的结果:
CREATE TABLE hstore_test (id bigserial primary key,title text,doors integer,engine text,air_conditioning boolean) INSERT INTO hstore_test (title,doors,engine,air_conditioning) VALUES ('Car1',2,'1.1',false),('Car2',4,'1.2',true),('Car3',3,'1.3',('Car4',5,'1.4',null); DROP TABLE IF EXISTS hstore_persist; CREATE TABLE hstore_persist AS SELECT hstore(t) car_data FROM hstore_test AS t; SELECT car_data->'title' "name",car_data->'doors' doors,car_data->'engine' engine,car_data->'air_conditioning' air_conditioning FROM hstore_persist 这将导致表格 name | doors | engine | air_conditioning Car1 | 2 | 1.1 | f Car2 | 4 | 1.2 | t Car3 | 3 | 1.3 | f Car4 | 5 | 1.4 | 但是,它没有任何“交叉”.这只是使用hstore的访问器方法以您在示例中显示的方式显示数据. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |