postgresql行转列、列转行

清疚 2023-09-27 12:11 138阅读 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