Oracle SQL：如何在OVER子句PARTITION BY后加条件统计±5分钟影片数

要统计每个影片类别中，时长与当前影片相差±5分钟的影片数量，不能直接在PARTITION BY后加条件，得用以下几种方式实现：

方法一：使用FILTER子句（适用于PostgreSQL等支持该语法的数据库） #

直接在窗口函数里通过FILTER筛选符合时长范围的记录：

SELECT 
    film_id, 
    title, 
    category_name, 
    length AS LEN, 
    COUNT(film_id) OVER (
        PARTITION BY category_name
        FILTER (WHERE length BETWEEN LEN - 5 AND LEN + 5)
    ) AS same_length_range_count
FROM film 
INNER JOIN film_category USING (film_id) 
INNER JOIN category USING (category_id)
ORDER BY category_name, length;

方法二：条件计数+SUM窗口函数（通用多数数据库，比如MySQL） #

用CASE标记符合条件的记录，再通过SUM在同类别内统计数量：

SELECT 
    film_id, 
    title, 
    category_name, 
    length AS LEN, 
    SUM(
        CASE WHEN length BETWEEN LEN - 5 AND LEN + 5 THEN 1 ELSE 0 END
    ) OVER (PARTITION BY category_name) AS same_length_range_count
FROM film 
INNER JOIN film_category USING (film_id) 
INNER JOIN category USING (category_id)
ORDER BY category_name, length;

方法三：RANGE框架窗口函数（部分数据库支持，比如PostgreSQL） #

配合ORDER BY length定义时长的数值范围框架，统计范围内的记录数：

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

你之前用RANGE没效果的原因 #

RANGE在窗口函数里必须配合ORDER BY使用，用来基于排序字段定义数值范围；另外不同数据库对RANGE的支持逻辑有差异，比如MySQL的RANGE仅支持部分聚合函数，且范围逻辑是累计式的，可能不符合你的需求。

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