使用PostgreSQL的交叉表查询中的计数和总和无效 [英] Invalid count and sum in cross tab query using PostgreSQL
问题描述
我正在使用PostgreSQL 9.3版本数据库。
I am using PostgreSQL 9.3 version database.
我遇到一种情况,我想统计产品销售数量和产品总和,也想
I have a situation where I want to count the number of products sales and sum the amount of product and also want to show the cities in a column where the product have sale.
设置 >
create table products (
name varchar(20),
price integer,
city varchar(20)
);
insert into products values
('P1',1200,'London'),
('P1',100,'Melborun'),
('P1',1400,'Moscow'),
('P2',1560,'Munich'),
('P2',2300,'Shunghai'),
('P2',3000,'Dubai');
交叉表查询:
select * from crosstab (
'select name,count(*),sum(price),city,count(city)
from products
group by name,city
order by name,city
'
,
'select distinct city from products order by 1'
)
as tb (
name varchar(20),TotalSales bigint,TotalAmount bigint,London bigint,Melborun bigint,Moscow bigint,Munich bigint,Shunghai bigint,Dubai bigint
);
输出
name totalsales totalamount london melborun moscow munich shunghai dubai
---------------------------------------------------------------------------------------------------------
P1 1 1200 1 1 1
P2 1 3000 1 1 1
预期产量:
name totalsales totalamount london melborun moscow munich shunghai dubai
---------------------------------------------------------------------------------------------------------
P1 3 2700 1 1 1
P2 3 6860 1 1 1
推荐答案
您的第一个错误似乎很简单。根据 crosstab()
函数的第二个参数,'Dubai'
必须作为第一个城市(按城市排序) )。详细信息:
Your first mistake seems to be simple. According to the 2nd parameter of the crosstab()
function, 'Dubai'
must come as first city (sorted by city). Details:
- PostgreSQL Crosstab Query
总销售额
的意外值和总计
代表每个名称
组第一行的值。 额外列的处理方式如下。详细信息:
The unexpected values for totalsales
and totalamount
represent values from the first row for each name
group. "Extra" columns are treated like that. Details:
- Pivot on Multiple Columns using Tablefunc
获取每个名称
,在集合函数上运行窗口函数。详细信息:
To get sums per name
, run window functions over your aggregate functions. Details:
- Get the distinct sum of a joined table column
select * from crosstab (
'select name
,sum(count(*)) OVER (PARTITION BY name)
,sum(sum(price)) OVER (PARTITION BY name)
,city
,count(city)
from products
group by name,city
order by name,city
'
-- ,'select distinct city from products order by 1' -- replaced
,$$SELECT unnest('{Dubai,London,Melborun
,Moscow,Munich,Shunghai}'::varchar[])$$
) AS tb (
name varchar(20), TotalSales bigint, TotalAmount bigint
,Dubai bigint
,London bigint
,Melborun bigint
,Moscow bigint
,Munich bigint
,Shunghai bigint
);
更好的是,提供一个静态集合作为第二个参数。输出列是硬编码的,因此动态生成数据列可能不可靠。如果您在另一行中有一个新的城市,则此行将中断。
这样,您还可以根据需要对列进行排序。只需保持输出列和第二个参数同步即可。
Better yet, provide a static set as 2nd parameter. Output columns are hard coded, it may be unreliable to generate data columns dynamically. If you a another row with a new city, this would break.
This way you can also order your columns as you like. Just keep output columns and 2nd parameter in sync.
这篇关于使用PostgreSQL的交叉表查询中的计数和总和无效的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!