题目一： 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

题目一的SQL查询语句可以如下所示：

SELECT userid, AVG(stay_time) AS avg_stay_time
FROM (
  SELECT userid, timestamp AS login_time,
    LEAD(timestamp) OVER (PARTITION BY userid ORDER BY timestamp) AS logout_time,
    LEAD(timestamp) OVER (PARTITION BY userid ORDER BY timestamp) - timestamp AS stay_time
  FROM your_table
  WHERE actionType = 'login'
) subquery
WHERE logout_time IS NOT NULL
GROUP BY userid;

注意替换 `your_table` 为你的表名。

题目二的SQL查询语句可以如下所示：

SELECT the_date, uid, stay_time
FROM (
  SELECT the_date, uid, timestamp, stay_time,
    ROW_NUMBER() OVER (PARTITION BY the_date, uid ORDER BY timestamp DESC) AS rn
  FROM your_table
) subquery
WHERE rn = 1;

注意替换 `your_table` 为你的表名。