第一部分：解决产品型公司提出的SQL问题

冷不防 2024-04-03 09:05 43阅读 0赞

第一部分：解决产品型公司提出的SQL问题 问题

Facebook提出的平均会话时间

拥有坚实的基础和关于SQL的知识将有助于你更有效地破解数据科学面试。这篇文章将讨论一个问题的陈述和它的解决方案，使用不止一种方法。如果你想让你的SQL概念和知识更加强大，那么你一定要阅读这篇文章。它一定会提高你的概念和解决问题的态度。

这是关于产品类公司面试时提出的SQL问题系列的第一篇文章。在这篇文章中，我使用StrataScratch平台来获取产品型公司中提出的问题。我将使用Postgres SQL数据库来解决这个问题。

这是Facebook在一次面试中提出的中等水平的问题。你可以在这里查看这个问题。问题的名称是Users By Average Session Time。

以下是该问题的细节。

面谈问题日期:2021年7月

问题说明。

计算每个用户的平均会话时间。一个会话被定义为页面\_加载和页面\_退出之间的时间差。为了简单起见，假设一个用户每天只有1个会话，如果当天有多个相同的事件，只考虑最近的`page_load`和最早的`page_exit`。输出`user_id`和他们的平均会话时间。

这是对数据的预览。表的名称是`facebook_web_log`，其中包含3列`int`类型的`user_id`，`dateTime`类型的`timestamp`，`varchar`类型的`action`。

正如你所看到的数据。每个用户的page\_load和page\_exit的时间都是多天的。对于每一天，一个用户可以有不止一个page\_load和page\_exit时间。正如问题陈述中所给出的，对于每一天，我们必须考虑最新的page\_load和最早的page\_exit时间。然后我们要计算每个用户的平均会话时间。

我强烈建议你先去看问题链接，并尝试解决这个问题。在思考了问题的陈述后，你可以继续。我们将用两种方法解决这个问题。

方法一：使用`Join`

在一个查询中选择每个用户每天的最大页面加载时间。

select user_id, 
    max(timestamp) as l_time , 
    action from facebook_web_log 
    where action in ('page_load')
    group by (user_id, action, date(timestamp) )

2.然后在另一个查询中对每一个用户的每一天的最小退出时间进行查询。

select user_id, 
    min(timestamp) as e_time , 
    action from facebook_web_log 
    where action in ( 'page_exit') 
    group by (user_id, action, date(timestamp))

1.  然后将上述两个查询加入到 `user_id`中，通过取平均会话时间，即（加载时间-退出时间），并使用 `user_id`， `date(timestamp)`的组来找到平均会话时间。

整个查询。

SELECT A.user_id, Avg(B.e_time — A.l_time) from
    (select user_id, max(timestamp) as l_time , action from facebook_web_log where action in (‘page_load’) group by (user_id, action, date(timestamp) )) as A 
    
    join
    (select user_id, min(timestamp) as e_time , action from    facebook_web_log where action in (‘page_exit’) group by (user_id, action, date(timestamp))) as B
    on A.user_id = B.user_id
    group by A.user_id;

按`A.user_id`分组。

输出将看起来像这样 方法2：使用子查询和案例

使用case子句在单行中 为每一个用户\_id选择每一天的最大页面加载时间和最小页面退出时间。

select user_id,
    max(case when action = ‘page_load’ then timestamp end) as l_time,    min(case when action = ‘page_exit’ then timestamp end) as e_time from facebook_web_log
    group by user_id, date(timestamp)

1.  把它作为一个子查询，然后应用group by函数来寻找平均值。

整个查询。

select user_id, avg(e_time - l_time) as avg_session_time from 
    (select user_id,
    max(case when action = 'page_load' then timestamp end) as l_time, min(case when action = 'page_exit' then timestamp end) as e_time from facebook_web_log

按用户ID、日期（时间戳）分组）作为A 按用户ID分组 有

avg(e_time - l_time)

不为空。 注意：date(timestamp)是一个函数调用，将从时间戳字段返回日期。

使用不同的方法来解决同一个问题，这确实是非常重要的。这种做法会让你对同一个问题有不同的思考方式，并想出更有效的解决问题的方法。

本文由[ mdnice ][mdnice]多平台发布

[mdnice]: https://mdnice.com/?platform=4