在postgresql中，如何使用窗口函数来计数而不是使用count( )函数
在postgresql中，如何使用窗口函数来计数而不是使用count( )函数
提问于 2022-03-08 17:41:19
我试图查询一张电影列表，将它们分类，以及每部电影被出租的次数。我希望使用窗口函数来计算每部电影被租出的次数，同时将它们分组并保留它们的行。
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;我能够使用计数函数获得我想要的结果，但我想使用窗口函数进行聚合。我该怎么做？提前谢谢你。
回答 3
Stack Overflow用户
回答已采纳
发布于 2022-03-08 17:53:12
您可以修改和尝试相同的查询，如下所示：
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
.
.在(partition by <column> order by <column>)中，可以保留与原始查询中相同的列。
Stack Overflow用户
发布于 2022-03-08 18:51:27
您不需要任何外部查询，而是需要在count(*)窗口函数的分区by子句中按列进行分组。
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, 2Stack Overflow用户
发布于 2022-03-08 21:15:47
您的查询显示每张影片一行和类别，以及影片的租金总额。窗口函数在这里没有意义，因为您不想从选定的行派生值。
例如，如果您也想显示类别的租金总额，则可以使用窗口函数来完成：
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;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/71399321
复制相关文章
相似问题
腾讯云开发者
