sql - 如何使用 sql 中的单个查询获取项目计数和总计数？

Question

我有一个表，用于存储糖果店在所有地点销售的商品的信息。出售糕点、华夫饼、冰淇淋、糖果等。

我需要从表中获取此信息。

Which item was sold the most per location along with total items sold in those locations.

下面是表格的结构

     Column     |            Type             | Modifiers 
----------------+-----------------------------+-----------
 id             | integer                     | 
 date           | timestamp without time zone | 
 item           | character varying(15)       | 
 location       | character varying(25)       | 

现在我将其分为两个单独的问题

1. 每个地点哪种商品销量最高
2. 每个地点销售的商品总数。

这些是我的疑问:

查询#1:

select location, item, count(item) as count_ 
from sweet_shop 
group by location, item;

查询#2:

select location, count(item) as count_ 
from sweet_shop 
group by location;

如您所见，我进行了两个单独的查询，然后需要将这两个信息映射到正确的位置并获得最终输出。我正在寻找如下所示的输出

  location     |  item             | item_count    total_count 
---------------+-------------------+-------------|----------
 BA2           | candies           |   400       |     550
 BA4           | pastries          |   320       |     610 
 BA3           | waffles           |   250       |     400 
 BA7           | ice creams        |   180       |     500

但我想在单个查询中获取此信息，而不是进行两个单独的查询。如何做到这一点？

注意:我使用的是 Postgres 9.2

Answer 1

我将按位置和项目进行聚合，使用 COUNT() 作为分析函数来生成每个位置的总计数。此外，还可以使用 ROW_NUMBER 来识别每个位置表现最好的项目。

WITH cte AS (
    SELECT location, item, COUNT(*) AS item_count,
           SUM(COUNT(*)) OVER (PARTITION BY location) AS total_count,
           ROW_NUMBER() OVER (PARTITION BY location
                              ORDER BY COUNT(*) DESC) rn
    FROM sweet_shop
    GROUP BY location, item
)

SELECT location, item, item_count, total_count
FROM cte
WHERE rn = 1;