sql - 如何在分组集查询中仅显示前 2 个和前 1 个值？

Question

我有这个PostgreSQL表格和数据:

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;

This fiddle展示了它的样子。

现在我只想选择每个品牌的前 2 个分割市场，以及每个品牌/分割市场的前 1 个名称。所以结果应该是这样的:

<表类="s-表"><头>品牌片段名字总计<正文>丰田15丰田 buggy 6丰田 buggy 汉兰达3丰田轿车5丰田轿车花冠3

我尝试过使用窗口函数，但结果不是我所期望的

Answer 1

尝试使用 ROW_NUMBER 函数，如下所示:

WITH get_grouping_set AS
(
  SELECT brand, segment, name, count(1) AS total
  FROM info
  GROUP BY GROUPING SETS 
  (
    (brand),
    (brand, segment),
    (brand, segment, name)
  )
), 
brand_segment_order AS
(
  SELECT brand, segment,
    ROW_NUMBER() OVER (PARTITION BY brand ORDER BY total DESC) rn_seg
    FROM get_grouping_set
  WHERE segment IS NOT NULL AND name IS NULL
), 
joined_data AS
(
  SELECT T.*,
    ROW_NUMBER() OVER (PARTITION BY T.brand, T.segment ORDER BY T.total DESC) rn 
  FROM get_grouping_set T JOIN brand_segment_order T2
  ON T.brand = T2.brand AND T.segment = T2.segment OR T.segment IS NULL
  WHERE T2.rn_seg <= 2
 )
SELECT brand, segment, name, total  
FROM joined_data 
WHERE (rn = 1 AND segment IS NULL ) OR (rn <= 2 AND segment IS NOT NULL)
ORDER BY brand, MAX(Total) OVER (PARTITION BY brand, segment) DESC, 
         Total DESC, segment NULLS FIRST, name NULLS FIRST

See demo

另一种解决方案。您可以使用按每个品牌分割组的 max(count) 排序的 dense_rank 函数，如下所示:

WITH get_grouping_set AS
(
  SELECT brand, segment, name, count(1) AS total, 
    MAX(count(*)) over (PARTITION BY brand, segment) max_brand_segment
  FROM info
  GROUP BY GROUPING SETS 
  (
    (brand),
    (brand, segment),
    (brand, segment, name)
  )
),
brand_segment_order AS
(
  SELECT *,
    DENSE_RANK() OVER (PARTITION BY brand ORDER BY max_brand_segment DESC) segment_rank,
    DENSE_RANK() OVER (PARTITION BY brand, segment ORDER BY Total DESC) name_rank
    FROM get_grouping_set
)
SELECT brand, segment, name, total 
FROM brand_segment_order
WHERE segment_rank <= 3 AND name_rank <= 2
ORDER BY brand, max_brand_segment DESC, 
         Total DESC, segment NULLS FIRST, name NULLS FIRST

WHERE segment_rank <= 3这将为每个品牌检索两个分割市场，加上一个，因为包含基本品牌(分割市场为空)。

AND name_rank <= 2这将为每个段检索一个名称，加一是因为包含了基本段(名称为空)。

dense_rank 函数的用途是在出现并列的情况下获取所有(段、名称)，即当有多个段/名称具有相同的最大值(计数)时。