postgresql行转列、列转行 清疚 2023-09-27 12:11 79阅读 0赞 ### 列转行 ### postgresql列转行的思路主要是利用`string_to_array`进行数组转换,然后用`unnest`进行行拆分 ![在这里插入图片描述][e9313b602c924c5ba2d5ed02b334f3a0.png] select t.bid_unit,unit_id from unit t where t.unit_id=1947; result=> 中国信息通信研究院;北京市海淀区学院 -- by zhengkai.blog.csdn.net ![在这里插入图片描述][48a6719dc6824a51be71d62b2f1006e9.png] select unnest(string_to_array(t.bid_unit,';')),unit_id from unit t where t.unit_id=1947; result=> 中国信息通信研究院 北京市海淀区学院 -- by zhengkai.blog.csdn.net [pgsql官方对functions-array的解释][pgsql_functions-array] <table> <thead> <tr> <th>Function</th> <th>Return Type</th> <th>Description</th> <th>Example</th> <th>Result</th> </tr> </thead> <tbody> <tr> <td>string_to_array(text, text [, text])</td> <td>text[]</td> <td>splits string into array elements using supplied delimiter and optional null string (使用提供的分隔符和可选的空字符串将字符串分割为数组元素)</td> <td>string_to_array(‘xx<sub>^</sub>yy<sub>^</sub>zz’, ‘<sub>^</sub>’, ‘yy’)</td> <td>{xx,NULL,zz}</td> </tr> <tr> <td>unnest(anyarray)</td> <td>setof anyelement</td> <td>expand an array to a set of rows(将数组展开到一组行)</td> <td>unnest(ARRAY[1,2])</td> <td>1 2 (2 rows)</td> </tr> </tbody> </table> ### 行转列 ### 用postgresql的`crosstab`交叉函数 -- by zhengkai.blog.csdn.net create table sales(year int, month int, qty int); insert into sales values(2022, 1, 1000); insert into sales values(2022, 2, 1500); insert into sales values(2022, 7, 500); insert into sales values(2022, 11, 1500); insert into sales values(2022, 12, 2000); insert into sales values(2023, 1, 1200); select * from crosstab( 'select year, month, qty from sales order by 1', 'select m from generate_series(1,12) m' ) as ( year int, "Jan" int, "Feb" int, "Mar" int, "Apr" int, "May" int, "Jun" int, "Jul" int, "Aug" int, "Sep" int, "Oct" int, "Nov" int, "Dec" int ); year | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec ------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------ 2022 | 1000 | 1500 | | | | | 500 | | | | 1500 | 2000 2023 | 1200 | | | | | | | | | | | (2 rows) [可以参考pgsql官方的tablefunc实用说明][pgsql_tablefunc] <table> <thead> <tr> <th>Function</th> <th>Returns</th> <th>Description</th> </tr> </thead> <tbody> <tr> <td>normal_rand(int numvals, float8 mean, float8 stddev)</td> <td>setof float8</td> <td>Produces a set of normally distributed random values(产生一组正态分布的随机值)</td> </tr> <tr> <td>crosstab(text sql)</td> <td>setof record</td> <td>Produces a “pivot table” containing row names plus N value columns, where N is determined by the row type specified in the calling query(生成一个包含行名和N个值列的“数据透视表”,其中N个由调用查询中指定的行类型决定)</td> </tr> <tr> <td>crosstabN(text sql)</td> <td>setof table_crosstab_N</td> <td>Produces a “pivot table” containing row names plus N value columns. crosstab2, crosstab3, and crosstab4 are predefined, but you can create additional crosstabN functions as described below(生成一个包含行名和N个值列的“数据透视表”。交叉表2、交叉表3和交叉表4都是预定义的,但是您可以创建额外的跨表n函数,如下面所述)</td> </tr> <tr> <td>crosstab(text source_sql, text category_sql)</td> <td>setof record</td> <td>Produces a “pivot table” with the value columns specified by a second query(生成具有由第二个查询指定的值列的“数据透视表”)</td> </tr> <tr> <td>crosstab(text sql, int N)</td> <td>setof record</td> <td>Obsolete version of crosstab(text). The parameter N is now ignored, since the number of value columns is always determined by the calling query(过时版本的交叉表(文本)。参数N现在被忽略,因为值列的数量总是由调用查询决定)</td> </tr> <tr> <td>connectby(text relname, text keyid_fld, text parent_keyid_fld [, text orderby_fld ], text start_with, int max_depth [, text branch_delim ])</td> <td>setof record</td> <td>Produces a representation of a hierarchical tree structure(生成层次树结构的表示)</td> </tr> </tbody> </table> [e9313b602c924c5ba2d5ed02b334f3a0.png]: https://img-blog.csdnimg.cn/e9313b602c924c5ba2d5ed02b334f3a0.png [48a6719dc6824a51be71d62b2f1006e9.png]: https://img-blog.csdnimg.cn/48a6719dc6824a51be71d62b2f1006e9.png [pgsql_functions-array]: https://www.postgresql.org/docs/9.1/functions-array.html [pgsql_tablefunc]: https://www.postgresql.org/docs/9.1/tablefunc.html
还没有评论,来说两句吧...