使用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的交叉表查询中的计数和总和无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！