sql - 在第一次引导事件发生之前查找平均观看次数

Question

create table events (
fk_user integer,
event varchar(40),
time integer
);


Insert into events (fk_user, event, time)
VALUES
('1', 'view', '1'),
('1', 'view', '3'),
('1', 'view', '4'),
('1', 'lead', '5'),
('1', 'view', '6'),
('1', 'view', '7'),
('1', 'lead', '9'),
('2', 'view', '1'),
('2', 'lead', '2'),
('2', 'lead', '3'),
('2', 'view', '6'),
('2', 'view', '7'),
('2', 'view', '8'),
('5', 'view', '1'),
('5', 'view', '2'),
('2', 'view', '4'),
('2', 'lead', '5'),
('2', 'view', '9');  

我试图找到的是:在从顶部出现“引导”之前有 3 个“ View ”。我想取前三次出现的“时间”的平均值。是否可以用窗口函数来做？
预期输出应为:(1+3+4)/3 = 2.666(如果取整数则为 3)

Answer 1

您可以使用 min 窗口函数和 case 语句来查找每个 fkuser 的 first_lead_time 和然后使用派生表获取 first_lead_time

之前的 views 行的平均值

select fkuser, avg(time) from (
    select * , 
        min (case when event = 'lead' then time end) over (partition by fkuser) as first_lead_time
    from events
) t where time < first_lead_time
and event = 'view'
group by fkuser

另一种方式

select e.fk_user, avg(e.time)
from events e
join (
    select min(time) first_lead_time, fkuser
    from events
    where event = 'lead'
    group by fkuser
) t on t.fkuser = e.fkuser
where e.time < t.first_lead_time
group by e.fkuser