如何在PostgreSQL分组集查询中仅展示品牌下前2细分市场及各细分市场前1车型

表结构与初始数据 #

CREATE TABLE info (
  brand VARCHAR(255),
  segment VARCHAR(255),
  name VARCHAR(255)
);

INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'SUV', 'Highlander');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'SUV', 'Highlander');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'SUV', 'Highlander');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'SUV', '4Runner');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'SUV', 'RAV4');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'SUV', 'RAV4');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'Sedan', 'Camry');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'Sedan', 'Camry');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'Sedan', 'Corolla');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'Sedan', 'Corolla');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'Sedan', 'Corolla');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'Truck', 'Tacoma');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'Truck', 'Tundra');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'Truck', 'Tacoma');
INSERT INTO info (brand, segment, name) VALUES ('Toyota', 'Van', 'Sienna');

现有分组统计查询 #

已实现基础分组集统计，按品牌、细分市场、车型的总计数排序：

SELECT
  brand,
  segment,
  name,
  count(1) as total
FROM
  info
GROUP BY
  GROUPING SETS (
    (brand),
    (brand, segment),
    (brand, segment, name)
  )
ORDER BY
 max(count(1)) over (partition by brand) desc,
 max(count(1)) over (partition by brand,segment) desc,
 count(1) desc;

需求与期望结果 #

需要筛选出：

• 每个品牌下的前2个细分市场（按该细分市场的总计数排序）
• 每个品牌+细分市场组合下的前1个车型（按该车型的总计数排序）

期望结果：

解决方案 #

通过嵌套窗口函数先对各层级统计结果排名，再筛选符合条件的记录：

WITH grouped_stats AS (
  SELECT
    brand,
    segment,
    name,
    count(1) AS total,
    -- 每个品牌下细分市场按总计数降序排名
    ROW_NUMBER() OVER (PARTITION BY brand ORDER BY COUNT(1) DESC) AS segment_rank,
    -- 每个品牌+细分市场下车型按总计数降序排名
    ROW_NUMBER() OVER (PARTITION BY brand, segment ORDER BY COUNT(1) DESC) AS name_rank,
    -- 标记是否为品牌级汇总行
    GROUPING(segment) AS is_brand_total
  FROM info
  GROUP BY
    GROUPING SETS (
      (brand),
      (brand, segment),
      (brand, segment, name)
    )
)
SELECT
  brand,
  CASE WHEN is_brand_total = 1 THEN NULL ELSE segment END AS segment,
  CASE WHEN segment IS NULL THEN NULL ELSE name END AS name,
  total
FROM grouped_stats
WHERE
  -- 保留品牌级汇总行
  is_brand_total = 1
  -- 保留每个品牌下前2的细分市场汇总行
  OR (segment IS NOT NULL AND name IS NULL AND segment_rank <= 2)
  -- 保留前2细分市场中排名第1的车型行
  OR (segment IS NOT NULL AND name IS NOT NULL AND segment_rank <= 2 AND name_rank = 1)
ORDER BY
  brand,
  total DESC,
  segment NULLS FIRST,
  name NULLS FIRST;

逻辑说明 #

1. grouped_stats 公共表表达式完成所有分组集统计，同时计算：
   • segment_rank：品牌下细分市场的排名（按细分市场总计数降序）
   • name_rank：品牌+细分市场下车型的排名（按车型总计数降序）
   • is_brand_total：标记当前行是否为品牌级汇总行（GROUPING(segment)=1 表示该列被聚合）
2. 外层查询通过WHERE条件筛选目标记录：
   • 保留品牌级汇总行
   • 保留品牌下前2的细分市场汇总行
   • 保留上述细分市场中排名第1的车型行
3. 最后按层级排序，确保结果结构清晰

内容的提问来源于stack exchange，提问作者Snow