如何根据另一个事件的时间戳查找序列中最近的事件

zpjtge22 于 2021-06-24 发布在 Hive

关注(0)|答案(2)|浏览(283)

我有一个应用程序事件数据表。每一行都有一个用户标识、一个时间戳、一个页面标识、一个事件名称和其他字段。我现在关心的事件是页面打开和按钮点击事件，但是在这两者之间可能会发生10种其他事件类型，比如滚动。用户可能会多次打开同一页，但每隔一段时间只能单击该页上的某个按钮，如下面的示例所示。

user_id  timestamp   page_id  event_name
-------  ----------  -------  --------------
     71  12:00:34        307  page_open
     88  13:01:44        307  page_open
     71  13:02:09        307  page_open
     71  13:02:11        307  scroll
     71  13:04:41        307  page_open
     71  13:04:42        307  scroll
     71  13:04:45        307  button_click_a
     71  13:08:30        307  page_open
     88  13:09:01        307  button_click_b

对于每个用户的每个页面打开事件，我需要一个额外的列来告诉我是否最终单击了按钮。我没有要使用的页面“会话”，所以我必须查找按钮单击时间戳之前出现的最大页面打开时间戳。换言之，我想把上表转换成下表

user_id  timestamp   page_id  event_name  button_event
-------  ----------  -------  ----------  --------------
     71  12:00:34        307  page_open   NULL
     88  13:01:44        307  page_open   button_click_b
     71  13:02:09        307  page_open   NULL
     71  13:04:41        307  page_open   button_click_a
     71  13:08:30        307  page_open   NULL

我试着把页面打开和按钮点击事件分为两个表，然后做一个 LEFT JOIN 在用户id和页面id上，如下面所示，但这当然不起作用，因为它将按钮单击与该页面id打开的所有页面匹配。我只想将按钮单击与相应的页面打开事件匹配。

SELECT
    a.user_id,
    a.timestamp,
    a.page_id,
    a.event_name,
    b.event_name AS button_event
FROM
    (SELECT * FROM events WHERE event_name = 'page_open') a
LEFT JOIN
    (SELECT * FROM events WHERE event_name = 'button_click_a' OR event_name = 'button_click_b') b
ON
    a.user_id = b.user_id AND
    a.page_id = b.page_id
;

我对处理这样的事件数据还不熟悉。你能提供的任何帮助都会很棒。解决这个问题的正确方法是什么？

sql Hive window-functions hql gaps-and-islands

来源：https://stackoverflow.com/questions/63818724/how-do-i-find-the-most-recent-event-in-sequence-based-on-the-timestamp-of-anothe

2条答案

按热度按时间

nimxete21#

这是一个缺口和孤岛问题。您需要定义以“页面打开”事件开头的相邻记录组；我建议进行窗口计数：

select *
from (
    select 
        t.*,
        max(case when event_name <> 'page_open' then event_name end) 
            over(partition by page_id, user_id, grp) button_event
    from (
        select 
            t.*,
            sum(case when event_name = 'page_open' then 1 else 0 end) 
                over(partition by page_id, user_id order by timestamp) grp
        from mytable t
        where event_name = 'page_open' or event_name like 'button_click_%'
    ) t
) t
where event_name = 'page_open'

您没有告诉您正在运行哪个数据库。这使用标准的窗口函数语法，并且应该适用于所有支持窗口函数的数据库。
db小提琴演示：

user_id | timestamp | page_id | event_name | grp | button_event  
------: | :-------- | ------: | :--------- | --: | :-------------
     71 | 12:00:34  |     307 | page_open  |   1 | null          
     88 | 13:01:44  |     307 | page_open  |   1 | button_click_b
     71 | 13:02:09  |     307 | page_open  |   2 | null          
     71 | 13:04:41  |     307 | page_open  |   3 | button_click_a
     71 | 13:08:30  |     307 | page_open  |   4 | null

赞(0）回复(0）举报 2021-06-24

umuewwlo2#

查找按钮单击时间戳之前出现的最大页面打开时间戳。
重新措辞以查看下一行是否是按钮单击。
您没有标记dbms，但大多数系统支持滞后/超前：

with cte as 
 (
   select
      user_id,
      timestamp,
      page_id,
      event_name,
      -- find the next non-'page_open' event
      lead(case when event_name <> 'page_open'
               then event_name
          end)
      over (partition by user_id
            order by timestamp) AS button_event
   from mytable
   where event_name in ('page_open','button_click_a','button_click_b')
 )
select *
from cte 
where event_name = 'page_open' -- remove click rows

看小提琴

赞(0）回复(0）举报 2021-06-24

我来回答

如何根据另一个事件的时间戳查找序列中最近的事件

2条答案

相关问题

热门标签

最新问答