SQL问题：按用户类型计算会话之间的平均天数

Question

假设我有以下两个表：

表1：用户角色 | 用户ID | 类型 | | ------ | ---------- | | 1 | 管理员 | | 2 | EE | | 3 | 承包商 | | 5 | 管理员 |

表2：用户会话 | 用户ID | 会话ID | 会话创建时间 | | ------ | ------ | ------------ | | 1 | 8 | 2021-01-01 | | 1 | 9 | 2021-01-02 | | 3 | 10 | 2021-01-03 | | 5 | 11 | 2021-01-04 | | 5 | 12 | 2021-01-05 |

期望结果： | 用户类型 | 平均登录天数 | | ---------- | ------------ | | 管理员 | 50 | | EE | 35 | | 承包商 | 100 | | Accountant | 10 |

请问有人能帮我按用户类型获取平均登录天数吗？

- RWARD2019

---

会计怎么可能有10，当会话没有他的ID时，顺便问一下使用的是哪个MySQL版本？5还是8？ - nbk

嗨，感谢提醒修复了理论表格。使用最新版本的https://docs.aws.amazon.com/redshift/latest/mgmt/welcome.html。 - RWARD2019

如果您使用的是MySQL 8，则可以使用窗口函数lag来获取两个日期之间的差异，而您不能在链接中使用它。另外，这里禁止使用，可以创建一个fiddle。 - nbk

Answer 1

在 MySQL 8 中，您可以使用窗口函数 LAG 来获取两个登录日期之间的差异。

CREATE TABLE User_roles (
  `User_ID` INTEGER,
  `Type` VARCHAR(10)
);

INSERT INTO User_roles
  (`User_ID`, `Type`)
VALUES
  ('1', 'Admin'),
  ('2', 'EE'),
  ('3', 'Contractor'),
  ('5', 'Admin');

CREATE TABLE User_Sessions (
  `User_ID` INTEGER,
  `Session_ID` INTEGER,
  `Session_created_at` VARCHAR(10)
);

INSERT INTO User_Sessions
  (`User_ID`, `Session_ID`, `Session_created_at`)
VALUES
  ('1', '8', '2021-01-01'),
  ('1', '9', '2021-01-02')
  ,
  ('1', '18', '2021-01-20'),
  ('3', '10', '2021-01-03'),
  ('5', '11', '2021-01-04'),
  ('5', '12', '2021-01-05')
  ,
  ('5', '13', '2021-01-10');

SELECT
`Type` as 'User Type',
AVG(diff_days ) As 'AVG days between login'
FROM (SELECT ur.`User_ID`, ur.`Type`,
timestampdiff(DAY, lag(us.`Session_created_at`, 1) OVER (PARTITION BY us.`User_ID` ORDER BY us.`Session_ID` ASC), us.`Session_created_at`) as diff_days 
FROM 
User_Sessions us JOIN User_roles ur ON us.User_ID = ur.User_ID) t1
WHERE diff_days IS NOT NULL
GROUP BY `User_ID`,`Type`
ORDER BY `User_ID` ASC

User Type | AVG days between login
:-------- | ---------------------:
Admin     |                 9.5000
Admin     |                 3.0000

db<>fiddle here

db<>fiddle {{链接1：这里}}

- nbk

Answer 2

您可以使用自我join来查找每个user_id的登录/注销会话，然后将您的角色表join回结果，同时在角色类型上进行分组计算平均值：

with cte(id, sid, sc1, sc2) as (
   select s.user_id, s.session_id, s.session_created_at, min(s1.session_created_at) from sessions s 
   left join sessions s1 on s.user_id = s1.user_id and s1.session_created_at > s.session_created_at
   group by s.user_id, s.session_id, s.session_created_at
)
select r.`type`, avg(c.sc2 - c.sc1) from cte c 
left join roles r on c.id = r.user_id where c.sc1 is not null group by r.`type`

- Ajax1234

Answer 3

有一些可能会考虑的方法：

1. 从 user_sessions 表中选择，按照 user_id 升序和 session_created_at 降序排序，其中创建列是在 user_id 上分区的行号。我们称这个表为 ordered_sessions。
2. 将表 #1 自连接，使得 row_num = row_num+1 AND user_id = user_id。以获取一个包含 user_session 和另一个对应用户前一天的列的表。
3. 使用一些 datediff 函数（例如 link）来比较表 #2 中的两个 session_created_at 列，以获取天数差异。
4. 将表 #3 与 user_roles 表 ON user_id 进行连接。
5. 按 user_type.Type 对 #3 中的列进行平均分组。

- kamster