如何在PostgreSQL使用COUNT和分区时避免title字段重复行？

你遇到的重复问题根源在于：当前查询是基于每条租赁记录（rental表的每一行）输出结果，同一部电影的每次租赁都会生成一行数据。窗口函数COUNT()只是计算分组内的总租赁数，不会对行去重，因此同一个title和对应的title_mo_rentals会随每条租赁记录重复出现。

结合你的需求（允许其他字段重复，但title和title_mo_rentals仅显示一行），提供两种针对性解决方案：

方案一：保留租赁明细，仅首次显示title和统计值 #

如果你需要保留所有租赁记录的明细，仅在同一分组（店铺、年份、月份、分类、电影）的第一行显示title和title_mo_rentals，其余行置空，可以用ROW_NUMBER()窗口函数标记分组内的首行：

INSERT INTO detailed_report (
  rental_id
, store_num
, rental_year
, rental_month
, genre
, genre_mo_rentals
, title
, title_mo_rentals
, genre_ytd_rentals
)
SELECT
  r.rental_id
, i.store_id AS store_num
, DATE_PART('year', r.rental_date)::INT AS rental_year
, TO_CHAR(r.rental_date, 'Month') AS rental_month
, cat.name AS genre
, COUNT(r.rental_id) OVER (PARTITION by i.store_id, DATE_TRUNC('month', r.rental_date), cat.name) as genre_mo_rentals
-- 仅分组内首行显示title
, CASE WHEN row_num = 1 THEN f.title ELSE NULL END AS title
-- 仅分组内首行显示title_mo_rentals
, CASE WHEN row_num = 1 THEN title_mo_rentals ELSE NULL END AS title_mo_rentals
, COUNT(r.rental_id) OVER (PARTITION by i.store_id, DATE_PART('year', r.rental_date), cat.name) AS genre_ytd_rentals
FROM (
  SELECT
    r.*, i.store_id, f.title, cat.name,
    COUNT(r.rental_id) OVER (PARTITION by i.store_id, DATE_TRUNC('month', r.rental_date), cat.name, f.title) AS title_mo_rentals,
    -- 按店铺、月份、分类、电影分组，给每行编号
    ROW_NUMBER() OVER (PARTITION by i.store_id, DATE_TRUNC('month', r.rental_date), cat.name, f.title ORDER BY r.rental_id) AS row_num
  FROM rental r
  INNER JOIN inventory i ON r.inventory_id = i.inventory_id
  INNER JOIN film f ON i.film_id = f.film_id
  INNER JOIN film_category fc ON f.film_id = fc.film_id
  INNER JOIN category cat ON fc.category_id = cat.category_id
) sub;

方案二：插入汇总数据（每个title仅一行） #

如果你的实际需求是生成汇总报告而非租赁明细，应该先对数据做聚合分组，此时需要调整表结构（原表主键rental_id不适合汇总场景）：

1. 修改表结构（新增自增主键） #

DROP TABLE IF EXISTS detailed_report;
CREATE TABLE detailed_report (
  report_id SERIAL PRIMARY KEY -- 替换原rental_id为自增主键
, store_num INT
, rental_year INT
, rental_month VARCHAR(9)
, genre VARCHAR(25)
, genre_mo_rentals INT
, title VARCHAR (50)
, title_mo_rentals INT
, genre_ytd_rentals INT  
);

2. 执行汇总插入 #

INSERT INTO detailed_report (
  store_num
, rental_year
, rental_month
, genre
, genre_mo_rentals
, title
, title_mo_rentals
, genre_ytd_rentals
)
SELECT
  i.store_id AS store_num
, DATE_PART('year', DATE_TRUNC('month', r.rental_date))::INT AS rental_year
, TO_CHAR(DATE_TRUNC('month', r.rental_date), 'Month') AS rental_month
, cat.name AS genre
-- 分类当月店铺租赁数
, COUNT(r.rental_id) OVER (PARTITION by i.store_id, DATE_TRUNC('month', r.rental_date), cat.name) as genre_mo_rentals
, f.title AS title
-- 电影当月店铺分类下租赁数（聚合结果）
, COUNT(r.rental_id) AS title_mo_rentals
-- 分类当年店铺租赁数
, COUNT(r.rental_id) OVER (PARTITION by i.store_id, DATE_PART('year', r.rental_date), cat.name) AS genre_ytd_rentals
FROM rental r
INNER JOIN inventory i ON r.inventory_id = i.inventory_id
INNER JOIN film f ON i.film_id = f.film_id
INNER JOIN film_category fc ON f.film_id = fc.film_id
INNER JOIN category cat ON fc.category_id = cat.category_id
-- 按店铺、月份、分类、电影分组聚合
GROUP BY i.store_id, DATE_TRUNC('month', r.rental_date), cat.name, f.title;

说明：用DATE_TRUNC('month', r.rental_date)替代字符串格式分组，避免因Month格式带空格导致的分组错误，结果更准确。

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