我有一个sql视图说emp_table
,如下所示:
+----------+----------+------+
| actor_id | movie_id | year |
+----------+----------+------+
| 2 | 280088 | 2002 |
| 2 | 396232 | 2000 |
| 3 | 376687 | 2000 |
| 4 | 336265 | 2001 |
| 5 | 135644 | 1953 |
| 6 | 12083 | 1996 |
| 7 | 252053 | 1993 |
| 7 | 402635 | 1992 |
| 7 | 409592 | 1995 |
| 8 | 101866 | 2000 |
| 9 | 336265 | 2001 |
| 10 | 12148 | 2000 |
| 11 | 80189 | 2001 |
| 12 | 12148 | 2000 |
| 13 | 80189 | 2001 |
| 14 | 70079 | 1982 |
| 15 | 12148 | 2000 |
| 16 | 242675 | 1991 |
| 17 | 105231 | 1993 |
| 17 | 242453 | 1988 |
+----------+----------+------+
。等等。 我需要找到所有从未有过超过3年职业差距的actor_id
。这意味着我需要计算所有演员,如果我计算他们在电影中表演的独特年数,然后对其进行排序,那么一年之间的最大连续差异永远不会超过 3 年。请帮助我处理此sql查询。我已经尝试过sql自我加入,但无法考虑更多。
所有 SQL 代码仅适用于 MySQL。
注意:您可以认为只有一种组合actor_id和movie_id。
预期成果
+----------+----------+
| actor_id | max_gap |
+----------+----------+
| 2 | 2 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
| 7 | 2 |
| . | . |
| . | . |
| . | . |
| 17 | 5 |
+----------+----------+
等等
注2:很抱歉输出中有这么多变化。这是最终版本,在此之后不再更改。
使用 MySQL 8 和 MariaDB 10.2,您可以使用窗口函数LEAD()
来获取演员的下一个连续播放年份(或最后一个连续播放年份的LAG()
(。然后,您只需要在外部查询中获取最大差异。
with tmp as (
select
actor_id,
year,
lead(year) over (partition by actor_id order by year) as year_lead
from emp_table e
)
select actor_id, coalesce(max(year_lead - year), 0) as max_gap
from tmp
group by actor_id
having max_gap <= 3;
演示:https://www.db-fiddle.com/f/cWChT2TqLuRT8bW1zcM9G2/0
我最初从反连接方法开始,但在看到您对最大间隙的要求后对其进行了更改。
下面的方法从一个子查询开始,子查询本身使用相关的子查询来计算每个参与者记录和年份的前瞻性年差。 然后,它按演员聚合,并断言从未发生过大于 3 年的差距。
SELECT actor_id, MAX(gap) AS max_gap
FROM
(
SELECT
e1.actor_id,
ABS(e1.year - COALESCE((SELECT e2.year FROM emp_table e2
WHERE e2.actor_id = e1.actor_id AND e2.year > e1.year
ORDER BY e2.year LIMIT 1), e1.year)) AS gap
FROM emp_table e1
) t
GROUP BY
actor_id
HAVING
MAX(gap) <= 3;
请注意,调用COALESCE
是非常必要的,因为演员最近一年的边缘情况。 在这种情况下,没有前瞻性的年份,但我们想打折今年。
和组的自连接actor_id:
select
e1.actor_id, max(coalesce(e2.year, e1.year) - e1.year) max_gap
from emp_table e1 left join emp_table e2
on
e2.actor_id = e1.actor_id
and
e2.year = (
select min(year) from emp_table where actor_id = e1.actor_id and year > e1.year
)
group by e1.actor_id
having max_gap <= 3
观看演示