题目一: sql: 计算下表的用户平均停留时长: || userid || actionType || timestamp || =============================== || a || login || 1234567891 || || a || logout || 1234567892 || || b || login || 1234567893 || || a || login || 1234567894 || || b || logout || 1234567895 || || a || logout || 1234567896 || 题目二: sql: 计算用户每人每天的最后一次登录微信的停留时长: the_date,uid,timestamp,stay_time 例子: 20210329,user123,1617012000,600
时间: 2023-08-03 16:04:25 浏览: 45
题目一:
可以使用以下 SQL 查询语句计算用户平均停留时长:
```sql
SELECT userid, AVG(stay_time) AS avg_stay_time
FROM (
SELECT userid, TIMESTAMPDIFF(SECOND, MIN(timestamp), MAX(timestamp)) AS stay_time
FROM your_table
WHERE actionType IN ('login', 'logout')
GROUP BY userid
) t
GROUP BY userid;
```
请将 "your_table" 替换为你的表名。
题目二:
可以使用以下 SQL 查询语句计算用户每人每天的最后一次登录微信的停留时长:
```sql
SELECT DATE(FROM_UNIXTIME(timestamp)) AS the_date, uid, stay_time
FROM (
SELECT uid, MAX(timestamp) AS max_timestamp
FROM your_table
WHERE stay_time > 0
GROUP BY uid, DATE(FROM_UNIXTIME(timestamp))
) t
JOIN your_table ON t.uid = your_table.uid AND t.max_timestamp = your_table.timestamp;
```
请将 "your_table" 替换为你的表名。
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![gz](https://img-home.csdnimg.cn/images/20210720083447.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)