【阿里巴巴】计算订单日志链路平均时间差
JOIN 连接聚合函数日期函数漏斗分析面试真题
题目描述
来源：阿里巴巴 SQL 面试真题
某公司需要分析支付订单的客户端日志链路。order_log 记录玩家创建订单的日志，select_log 记录玩家选择支付方式的日志。请计算同一订单在两类日志之间的平均采集时间差。
数据表
order_log 创建订单日志表
| 字段 | 类型 | 说明 |
|---|---|---|
| order_id | VARCHAR | 订单号 |
| uid | VARCHAR | 用户 ID |
| logtime | DATETIME | 日志采集时间 |
| time | DATETIME | 客户端记录时间 |
| product_id | VARCHAR | 商品 ID |
| pay_method | VARCHAR | 支付方式；创建订单时可能为空 |
select_log 选择支付方式日志表
| 字段 | 类型 | 说明 |
|---|---|---|
| order_id | VARCHAR | 订单号 |
| uid | VARCHAR | 用户 ID |
| logtime | DATETIME | 日志采集时间 |
| time | DATETIME | 客户端记录时间 |
| product_id | VARCHAR | 商品 ID |
| pay_method | VARCHAR | 用户选择的支付方式 |
题目要求
- 从订单链路出发，按 order_id 关联 order_log 和 select_log。
- 使用 logtime 计算两类日志之间的秒数差，不使用客户端记录时间 time。
- logtime 可能局部乱序，因此需要对时间差取绝对值。
- 对所有匹配订单的绝对时间差求平均值，四舍五入后以整数形式返回。
- logtime 无需考虑 NULL。
期望输出列
| 列名 | 说明 |
|---|---|
| gap | 两类订单日志平均绝对时间差，单位为秒 |
数据样例
| order_idPKVARCHAR(50) | uidVARCHAR(50) | logtimeDATETIME | timeDATETIME | product_idVARCHAR(50) | pay_methodVARCHAR(50) |
|---|---|---|---|---|---|
| aaaa | user_0001 | 2021-01-01 10:00:00 | 2021-01-01 10:00:00 | p599 | NULL |
| bbbb | user_0006 | 2022-01-01 09:59:58 | 2021-01-01 09:59:58 | p599 | NULL |
| cccc | user_0006 | 2022-01-01 09:59:58 | 2021-01-01 09:59:58 | p599 | NULL |
输入数据显示 3 / 3 行
SQL 编辑器正在保存草稿...
Ctrl + Enter 运行
正在加载 SQL 编辑器...
运行你的 SQL 查询后，结果将显示在这里