postgres 交叉表，错误:提供的 SQL 必须返回 3 列 [英] postgres crosstab, ERROR: The provided SQL must return 3 columns

问题描述

您好，我创建了一个视图，但想要旋转它.

Hello I have created a view, but want to pivot it.

旋转前的输出:

   tag1  |  qmonth1  |  qmonth2  |  sum1
 --------+-----------+-----------+--------
 name1   |  18-05   |  MAY      |  -166
 name2   |  18-05   |  MAY      |  -86
 name3   |  18-05   |  MAY      |  35
 name1   |  18-06   |  JUN      |  -102
 name2   |  18-06   |  JUN      |  -32
 name3   |  18-06   |  JUN      |  -75
 name1   |  18-09   |  AVG      |  -135
 name2   |  18-09   |  AVG      |  -52
 name3   |  18-09   |  AVG      |  -17

预期输出:

 qmonth2 | name1 | name2 | name3
 --------+-------+-------+-------
  MAY    | -166  | -86  |  35
  JUN    | -102  | -32  | -75
  AVG    | -135  | -52  | -17

我的完整查询:

SELECT tag1,qmonth2,sum1 FROM crosstab 
('SELECT tag1::text,qmonth1,qmonth2::text,sum1::numeric 
FROM public."chartdata_chart3"') 
AS ct ( "tag1" TEXT,"qmonth2" TEXT,"sum1" NUMERIC);

我收到此错误但无法解决:

I getting this error and unable to resolve:

ERROR:  invalid source data SQL statement
DETAIL:  The provided SQL must return 3 columns: rowid, category, and values.
SQL state: 22023

推荐答案

作为参数传递给 crosstab() 函数的 SQL 语句必须返回一个 row_name 列、一个类别列和一个值列.在您的情况下，这是 qmonth2、tag1 和 sum1.

SQL statement passed as paremeter to crosstab() function must return one row_name column, one category column, and one value column. This in your case is qmonth2, tag1 and sum1.

这是您的查询，考虑到 sum1 是一个整数，qmonth2 和 tag1 是文本:

This is your query considering sum1 is an integer, qmonth2 and tag1 are text:

select *
from crosstab(
  'select qmonth2, tag1, sum1
  from public."chartdata_chart3"
  ') AS ct(qmonth2 text, name1 int, name2 int, name3 int;

输出:

 qmonth2 | name1 | name2 | name3
---------+-------+-------+-------
 MAY     | -166  | -86   | 35
 JUN     | -102  | -32   | -75
 AVG     | -135  | -52   | -17

有关如何手册的更多信息和示例，请参阅手册代码>交叉表有效.

Refer to manual for more information and samples on how crosstab works.

另外，请记住，用引号命名您的列/表会使编写每个 SQL 查询变得更加复杂，因此您最好不要这样做:)

Also, keep in mind that naming your columns/tables with quotes makes it more complex to write every SQL query so you are better off without doing that :)

这篇关于postgres 交叉表，错误:提供的 SQL 必须返回 3 列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！