假设每个开始时间都有一个结束时间，可以使用row_number函数进行聚合，如下所示：

select session_id, post_id,
       max(case when event_name='view_start' then ts end) startTime,
       max(case when event_name='view_end' then ts end) endTime,
       case 
         when 
          max(case when event_name='view_end' then ts end)
        - max(case when event_name='view_start' then ts end) > 3 
          and max(view_perc) > 0.8
         then 'yes' else 'no'
       end as hasimpact
from
(
  select *,
    row_number() over (partition by session_id, post_id order by ts) rn
  from view_logs
) t
group by session_id, post_id, (rn-1)/ 2
order by session_id, post_id, min(ts)

Demo

每个带有view_start的记录和它后面带有view_end的记录可以使用self join连接，因为你没有递增的列，我们可以使用row_number()构造一个列，用作join子句的条件：

with cte as (
  select *, row_number() over( partition by session_id, post_id order by ts) as rn
  from view_logs
)
select c1.session_id, c1.post_id,
         c1.ts as start_time, c2.ts as end_time,
         case when c2.view_perc > 0.8 and c2.ts - c1.ts > 3
              then 'Yes' else 'No' end as has_impact
from cte c1
inner join cte c2 on c1.event_name = 'view_start' 
                  and c2.event_name = 'view_end'
                  and c1.session_id = c2.session_id
                  and c1.post_id = c2.post_id
                  and c2.rn = c1.rn + 1
order by c1.session_id, c1.post_id, c1.ts

Demo here

这可能无法得到您想要的结果，但它确实计算has_impact列

WITH cte
AS (
    SELECT *
        ,CASE 
            WHEN event_name = 'view_start'
                THEN ts
            END AS start_time
        ,CASE 
            WHEN event_name = 'view_end'
                THEN ts
            END AS end_time
    FROM view_logs
    )
SELECT session_id
    ,post_id
    ,start_time
    ,end_time
    ,CASE 
        WHEN end_time - lag(start_time, 1) OVER (
                ORDER BY (
                        SELECT NULL
                        )
                ) > 3
            AND view_perc > 0.8
            THEN true
        ELSE false
        END AS has_impact
FROM cte;

输出：
| 会话ID| post_id|开始时间|结束时间|有影响|
| - -----|- -----|- -----|- -----|- -----|
| m1|一千|1524600|零|假的|
| m1|一千|零|1524602|假的|
| m1|一千|1524650|零|假的|
| m1|一千|零|1524654|真的|
| m1|二千年|1524700|零|假的|
| m1|二千年|零|1524707|假的|
| m1|二千年|1524710|零|假的|
| m1|二千年|零|1524713|假的|

假设每一个结束都有一个紧接在它前面的开始，你可以在一个子查询中简单地使用窗口函数lag()一次：

SELECT session_id, post_id, start_time, ts AS end_time, view_perc
     , (ts - start_time) > 3 AND view_perc > 0.8 AS has_impact
FROM  (
   SELECT *, lag(ts) OVER (PARTITION BY session_id, post_id ORDER BY ts) AS start_time
   FROM   view_logs
   ) sub
WHERE  event_name = 'view_end'
ORDER  BY session_id, post_id, start_time;  -- optional

fiddle
应该是最快的，因为它只需要一个单一的通过表。
如果我的假设不成立，声明我们可以假设什么 * 确切 *。