Leetcode_512_游戏玩法分析2_SQL

最新推荐文章于 2025-08-15 22:48:16 发布

原创最新推荐文章于 2025-08-15 22:48:16 发布 · 2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#leetcode #游戏 #sql

daily_algorithm 同时被 2 个专栏收录

230 篇文章

订阅专栏

SQL

27 篇文章

订阅专栏

本文探讨了如何通过SQL查询分析运动员的活动数据，包括按玩家ID获取最早活动日期、设备ID关联、历史事件比较以及实时排名。核心内容涉及数据筛选、排名算法和设备跟踪技术在体育数据分析中的应用。

with t as (
select player_id, min(event_date) as d
from activity
group by player_id
)

select a.player_id, a.device_id
from activity as a, t
where a.event_date = t.d and a.player_id = t.player_id

select a1.player_id, a1.device_id
from activity as a1
where a1.event_date <= ALL(
    select a2.event_date
    from activity as a2
    where a1.player_id = a2.player_id
)

select player_id, device_id from (
	select
		player_id,
		device_id,
		dense_rank() over(partition by player_id order by event_date) as 排名
	from activity
) as temp where temp.排名 = 1;