在postgresql中，如何使用窗口函数而不是使用count()函数进行计数

Question

我正在尝试查询电影列表，将它们分类以及每部电影的出租次数。我想使用窗口函数来计算每部电影的出租次数，同时将其分组到每个类别并保留其行。

SELECT film_title, category_name, 
       COUNT (*) AS count_of_rentals
FROM 
       (SELECT f.film_id AS film_id,
              f.title AS film_title,
              c.name AS category_name,
              r.customer_id AS customer_id,
              rental_date AS rental_date
        FROM film f
        JOIN film_category fc
        ON f.film_id = fc.film_id
        JOIN category c
        ON c.category_id = fc.category_id 
        JOIN inventory i
        ON i.film_id = f.film_id
        JOIN rental r
        ON r.inventory_id = i.inventory_id
        WHERE c.name IN ('Animation', 'Children', 'Classics', 'Comedy', 'Family', 'Music')
        ORDER BY 3, 2) t1
GROUP BY 1, 2
ORDER BY 2, 1;

我能够使用计数函数获得我想要的结果，但我想使用窗口函数进行聚合。我该怎么办？先感谢您。

Answer 1

您可以修改 &尝试如下相同的查询：

SELECT film_title, 
       category_name, 
       count_of_rentals
FROM (
        SELECT f.film_id AS film_id,
              f.title AS film_title,
              c.name AS category_name,
              r.customer_id AS customer_id,
              rental_date AS rental_date,
              count(*) over (partition by <column> order by <column>) count_of_rentals
              .
              .

IN (partition byorder by)您可以保留原始查询中的相同列

Answer 2

您不需要任何外部查询，而是需要在 count(*) 窗口函数的分区子句中提及按列分组。

        SELECT f.film_id AS film_id,
              f.title AS film_title,
              c.name AS category_name,
              count(*) over (partition by f.title, c.name) count_of_rentals,
              r.customer_id AS customer_id,
              rental_date AS rental_date
        FROM film f
        JOIN film_category fc
        ON f.film_id = fc.film_id
        JOIN category c
        ON c.category_id = fc.category_id 
        JOIN inventory i
        ON i.film_id = f.film_id
        JOIN rental r
        ON r.inventory_id = i.inventory_id
        WHERE c.name IN ('Animation', 'Children', 'Classics', 'Comedy', 'Family', 'Music')
        ORDER BY 3, 2

Answer 3

您的查询显示每部电影和类别一行以及电影的租赁总额。窗口函数在这里没有意义，因为您不需要从所选行中派生任何值。

例如，如果您还想显示该类别的租金总额，则可以使用窗口函数来完成：

SELECT 
  f.title AS film_title,
  c.name AS category_name,
  COUNT(*) AS count_of_film_rentals,
  SUM(COUNT(*)) OVER (PARTITION BY c.category_id) AS count_of_category_rentals
FROM film f
JOIN film_category fc ON f.film_id = fc.film_id
JOIN category c ON c.category_id = fc.category_id 
JOIN inventory i ON i.film_id = f.film_id
JOIN rental r ON r.inventory_id = i.inventory_id
WHERE c.name IN ('Animation', 'Children', 'Classics', 'Comedy', 'Family', 'Music')
GROUP BY f.film_id, c.category_id        
ORDER BY category_name, film_title;