在 PostgreSQL 中显示一行中某一列的多个值

发布于 2024-12-11 18:28:01 字数 619 浏览 0 评论 0原文

我有一个这样的查询：

select to_date(to_char(registime, 'YYYY-MM'),'YYYY-MM') as dt,count(id) as total_call  
from all_info 
where alarm_id is null
group by dt   
order by dt

结果就像这样：

dt          total_call      
2011-03-01     45
2011-04-01     61
2011-05-01     62
2011-06-01     41
2011-07-01     48
2011-08-01     42
2011-09-01     28
2011-10-01     39

我想要像下面演示中那样的结果，表格形式：

2011-03-01   2011-04-01  2011-05-01  2011-06-01  ..........
   45            61          62          41

我想使用 crosstab，但它似乎不起作用？

原文

I have a query like this:

select to_date(to_char(registime, 'YYYY-MM'),'YYYY-MM') as dt,count(id) as total_call  
from all_info 
where alarm_id is null
group by dt   
order by dt

And the result just like this:

dt          total_call      
2011-03-01     45
2011-04-01     61
2011-05-01     62
2011-06-01     41
2011-07-01     48
2011-08-01     42
2011-09-01     28
2011-10-01     39

I want the result like in the demo below, a table form:

2011-03-01   2011-04-01  2011-05-01  2011-06-01  ..........
   45            61          62          41

I want to use crosstab, but it doesn't seem to work?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

寻找我们的幸福 2024-12-18 18:28:01

查看 contrib 模块 tablefunc。它提供的正是您正在寻找的数据透视表功能。
请参阅此处的手册。

请按照此处的安装说明或 @Paul Tomblin 建议的文章进行操作他上面的评论。

那么你的函数可能看起来像这样：

SELECT *
  FROM crosstab($
SELECT 'total_calls'::text AS col_name
      ,to_char(registime, '"x"YYYY_MM_01') as dt
      ,count(id) as total_call  
FROM   all_info 
WHERE  alarm_id is null
GROUP  BY dt   
ORDER  BY dt
$)
AS ct(
 call_day text
,x2011_03_01 int8
,x2011_04_01 int8
,x2011_05_01 int8
,x2011_06_01 int8
,x2011_07_01 int8
,x2011_08_01 int8
,x2011_09_01 int8
,x2011_10_01 int8);

输出：

  call_day   | x2011_03_01 | x2011_04_01 | x2011_05_01 | x2011_06_01 | x2011_07_01 | x2011_08_01 | x2011_09_01 | x2011_10_01
-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------
 total_calls |           1 |           4 |           4 |           2 |           1 |           5 |           1 |           1

列名不能以数字开头（或者你必须用双引号），这就是为什么我在日期前加上 x 前缀。我还简化了您的查询。
您可以将其包装在视图或函数中以供重复使用。
也许是一个动态调整列名和 EXECUTE 的 plpgsql 函数。

此相关答案中有更多详细信息、说明和链接。

Look into the contrib module tablefunc. It provides exactly the kind of pivot table functionality you are looking for.
See the manual here.

Follow the installation instructions here or in the article @Paul Tomblin suggested in his comment above.

Then your function could look like this:

SELECT *
  FROM crosstab($
SELECT 'total_calls'::text AS col_name
      ,to_char(registime, '"x"YYYY_MM_01') as dt
      ,count(id) as total_call  
FROM   all_info 
WHERE  alarm_id is null
GROUP  BY dt   
ORDER  BY dt
$)
AS ct(
 call_day text
,x2011_03_01 int8
,x2011_04_01 int8
,x2011_05_01 int8
,x2011_06_01 int8
,x2011_07_01 int8
,x2011_08_01 int8
,x2011_09_01 int8
,x2011_10_01 int8);

Output:

  call_day   | x2011_03_01 | x2011_04_01 | x2011_05_01 | x2011_06_01 | x2011_07_01 | x2011_08_01 | x2011_09_01 | x2011_10_01
-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------
 total_calls |           1 |           4 |           4 |           2 |           1 |           5 |           1 |           1

Column names can't start with a digit (or you have to double-quote), that's why I prefixed the date with x. I also simplified your query.
You could wrap this in a view or function for repeated use.
Maybe a plpgsql function that dynamically adjusts the column names and EXECUTEs.

More details, explanation and links in this related answer.

回复收藏 0 原文

~没有更多了~