如何在SQL(Oracle)中OVER子句的PARTITION BY之后添加条件

Question

在下面的情况下，我只想计算每个类别的电影长度加5分钟或减5分钟(分别为当前长度LEN)？如何将此条件添加到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;

我试过用RANGE，但没有达到我想要的效果。

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;

Answer 1

在分析函数中使用RANGE窗口：

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;

对于样本数据：

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;

输出：

标题1A2B3C4D5E6F7G8H9I10J11K12L13M14N15O16P17Q18R19S20T21U22V23WA3>9[/tr>24X25Y26Z27aB7>td style="ext-align:right；">6[/tr>28b29c30d31eB>td style="ext-align:right；">310[/tr>32f33g34h35i36j37k38l39m40nB401141o42p43q44r45s46t47u48v49w50x51y52z

Answer 2

恐怕一个简单的窗口函数无法完成你想要做的事情。相反，我会将你的基本查询放入一个公共表表达式(CTE)中，然后交叉每个类别的所有胶片，让你可以轻松地比较每个胶片的长度。

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

请注意，这会将胶片本身计入适合间隔的计数中。如果你想排除这一个，只计算所有其他的，你需要添加

AND A.film_id<>B.film_id