In postgresql, how do you count using window functions instead of using count( ) function-CodePudding

I'm trying to query a list of movies, classifying them into categories and the number of times each movies had been rented out. I want to use window function to count the number of times each movies had been rented out while being grouped into each categories and retaining their rows.

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;

I was able to get the results I wanted using the count function but I want to use the window function to aggregate. How do I go about it? Thank you in advance.

CodePudding user response：

You can modify & try the same query like below :

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 by <column> order by <column>) you can keep the same columns which you have in your original query

CodePudding user response：

You don't need any outer query rather need to mentioned the grouped by columns in count(*) window function's partition by clause.

        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

CodePudding user response：

Your query shows one row per film and category along with the film's rental total. A window function would not make sense here, because there is no value you want to derive from the selected rows.

If for instance you wanted to show the category's rental total, too, that would be done with a window function:

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;