使用交叉表功能的PostgreSQL和数据透视表 [英] PostgreSQL and pivot tables using crosstab function
问题描述
我在使用crosstab()
函数在PostgreSQL中创建数据透视表时遇到问题.它运作良好,但会为同一client_id
产生多个记录.如何避免这种情况?
I have a problem with creating a pivot table in PostgreSQL using the crosstab()
function. It works well but it produces multiple records for the same client_id
. How can I avoid this?
这是SQL:
SELECT *
FROM crosstab('SELECT client_id
,extract(year from date)
,sum(amount)
from orders
group by extract(year from date)
,client_id'
,'SELECT extract(year from date)
FROM orders
GROUP BY extract(year from date)
order by extract(year from date)')
AS orders(
row_name integer,
year_2001 text,
year_2002 text,
year_2003 text,
year_2004 text,
year_2005 text,
year_2006 text,
year_2007 text,
year_2008 text,
year_2009 text,
year_2010 text,
year_2011 text);
//编辑
Thx to Erwin现在可以使用了,但是我试图在没有内置函数的情况下做到这一点,但是没有运气.有人可以提出建议吗?我的代码只有两年了:
Thx to Erwin it works now, but I have tried to do this without built in function with no luck. Can someone suggest something on that? My code for only two years:
SELECT DISTINCT o.client_id,
CASE WHEN (extract(year from o.date)=2001)
THEN sum(o.amount) ELSE 0 END,
CASE WHEN (extract(year from o.date)=2002)
THEN sum(o.amount) ELSE 0 END
FROM orders AS o
GROUP BY 1, extract(year from o.date)
ORDER BY 1;
推荐答案
您需要相应地ORDER BY
第一个查询.我在这里使用简化语法 ORDER BY <ordinal number>
You need to ORDER BY
the first query accordingly. I use the simplified syntax ORDER BY <ordinal number>
here.
SELECT *
FROM crosstab(
'SELECT client_id
,extract(year from date)
,sum(amount)
FROM orders
GROUP BY 1,2
ORDER BY 1,2',
'SELECT extract(year from date)
FROM orders
GROUP BY 1
ORDER BY 1')
AS orders(
row_name integer,
year_2001 text,
year_2002 text,
year_2003 text,
year_2004 text,
year_2005 text,
year_2006 text,
year_2007 text,
year_2008 text,
year_2009 text,
year_2010 text,
year_2011 text);
crosstab()
函数未包含在标准PostgreSQL中,但随附了其他功能模块tablefunc .
The crosstab()
function is not included in standard PostgreSQL but comes with the additional module tablefunc.
没有crosstab()
功能的版本:
仅按client_id
分组,否则每个client_id
最终将有多行.
Version without crosstab()
function:
Only group by client_id
or you will end up with multiple rows per client_id
.
SELECT client_id
,sum(CASE WHEN extract(year from date) = 2001 THEN amount ELSE 0 END) AS year_2001
,sum(CASE WHEN extract(year from date) = 2002 THEN amount ELSE 0 END) AS year_2002
-- ...
FROM orders o
GROUP BY 1
ORDER BY 1;
这篇关于使用交叉表功能的PostgreSQL和数据透视表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!