我有一个简单的问题，但我想知道是否有人能告诉我窗口函数中DISTINCT和GROUP BY背后的机制.
我已经读了几篇帖子，但没有得到任何与我的怀疑有关的帖子.
我的问题是:
关于以下输入/输出: enter image description here个
这是通过以下SQL查询得出的:
select
player_id
, first_value(event_date) over(partition by player_id order by event_date) as first_login
from Activity
group by player_id
我知道它可以通过简单的聚合来解决，不要误会我的意思. 我只是想知道为什么‘group by’在这个问题上似乎不起作用，而‘DISTINCT’可以起作用并通过测试.提前谢谢您.
select
DISTINCT player_id
, first_value(event_date) over(partition by player_id order by event_date) as first_login
from Activity
如果我使用cuit或子查询，则‘group by’起作用:
select
*
from
(select
player_id
, first_value(event_date) over(partition by player_id order by event_date) as first_login
from Activity) as cte
group by player_id, first_login
虚拟数据
WITH
Activity AS (
SELECT 1 AS player_id, 2 AS device_id, '2016-03-01' AS event_date, 5 AS games_played UNION ALL
SELECT 1, 2, '2016-05-02', 6 UNION ALL
SELECT 1, 3, '2015-06-25', 1 UNION ALL
SELECT 3, 1, '2016-03-02', 0 UNION ALL
SELECT 3, 4, '2016-02-03', 5 )