In the following case, I just want to count films of each category with a length plus 5 or minus 5 minutes (respective the current length LEN)? How can I add this condition to the statement after PARTITION BY?

SELECT film_id, title, category_name, length AS LEN, 
       Count(film_id) OVER (PARTITION BY category_name)  
FROM film INNER JOIN film_category USING (film_id) 
          INNER JOIN category USING (category_id)
ORDER BY name, length;

I've tried with RANGE but it does not have the effect I want.

SELECT film_id, title, category_name, length AS LEN, 
       Count(film_id) OVER (PARTITION BY category_name)  
FROM film INNER JOIN film_category USING (film_id) 
          INNER JOIN category USING (category_id)
ORDER BY name, length;

CodePudding user response：

I'm afraid a simple windowing function cannot accomplish what you are trying to do. I would, instead, put your base query into a Common Table Expression (CTE), then cross all films of each category, allowing you to easily compare the lengths of each.

With basequery AS (
    SELECT film_id, title, category_name, length AS LEN, 
    FROM film INNER JOIN film_category USING (film_id) 
      INNER JOIN category USING (category_id)
)
Select film_id, title, category_name, count(*) as nbr_similar
From basequery A Inner Join basequery B
    On A.category_name=B.category_name
      AND B.LEN between A.LEN-5 and A.LEN 5
Group By film_id, title, category_name
Order By A.category_name, A.Len

Note that this counts the film itself in the counts of those fitting the interval. If you want to exclude that one and just count all others, you'll need to add

AND A.film_id<>B.film_id

CodePudding user response：

Use RANGE window in the analytic function:

SELECT film_id,
       title,
       category_name,
       length AS LEN, 
       Count(film_id) OVER (
         PARTITION BY category_id
         ORDER BY length
         RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING
       ) AS count_similar
FROM   film
       INNER JOIN film_category USING (film_id) 
       INNER JOIN category USING (category_id)
ORDER BY title, length;

Which, for the sample data:

CREATE TABLE film ( film_id, title, length ) AS
  SELECT LEVEL,
         CASE WHEN LEVEL <= 26 THEN CHR(64 LEVEL) ELSE CHR(70 LEVEL) END,
         LEVEL
  FROM   DUAL
  CONNECT BY LEVEL <= 52;

CREATE TABLE film_category ( film_id, category_id ) AS
  SELECT film_id, CEIL(film_id/26) FROM film;

CREATE TABLE category (category_id, category_name) AS
  SELECT LEVEL, CHR(64 LEVEL) FROM DUAL CONNECT BY LEVEL <= 2;

Outputs:

fiddle