我在解决一个问题
表：Activity
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
+--------------+---------+(player_id, event_date)是这个表的主键。这个表显示了一些游戏的玩家的活动。
每一行都是一个玩家的记录，他在某一天使用某个设备登录并玩了很多游戏（可能是0）。
编写一个SQL查询，报告在第一次登录后第二天再次登录的玩家比例，四舍五入到小数点后2位。换句话说，您需要计算从第一次登录日期开始至少连续两天登录的玩家数量，然后将该数字除以玩家总数。查询结果格式如下例所示。
输入：Activity表：
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-03-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+-----------+-----------+------------+--------------+输出：
+-----------+
| fraction |
+-----------+
| 0.33 |
+-----------+说明：
只有id为1的玩家在他登录的第一天后重新登录，所以答案是1/3 = 0.33
我写了这个问题：
select
round(count(a1.player_id) / (select count(distinct player_id) as cnt from activity a3), 2)
from
(select
activity.*,
row_number() over (partition by player_id order by event_date) as rn
from
activity) a1
join
activity a2 on a1.rn = 1
and a2.event_date - a1.event_date = 1
and a1.player_id = a2.player_id;但我得到以下错误：
第32行错误：
ORA-00937：不是单组组函数
编辑：
我实际上是用这个解决的：
with retained as (
select count(*) as ret
from
(
select activity.*, row_number() over(partition by player_id order by event_date) as rn
from activity
) a1 join activity a2
on
a1.rn = 1 and
a2.event_date - a1.event_date = 1 and
a1.player_id = a2.player_id
),
total as (
select count(distinct player_id) as cnt
from activity
)
select distinct round(ret/cnt, 2) as fraction from retained, total;
;与我最初的解决方案的唯一区别是我使用单独的语句进行了重构。但我仍然不确定为什么我的原始解决方案是错误的。
3条答案
按热度按时间tzcvj98z1#
在Oracle 12中，您可以使用
MATCH_RECOGNIZE执行逐行模式匹配，并查找玩家的第二次登录时间是第一次登录后的第二天，然后使用条件聚合来查找分数：或者，如果你想使用解析函数，那么：
其中，对于样本数据：
两个输出：
| 分数|
| --------------|
| 点三三口径|
fiddle
xdyibdwo2#
尝试使用窗口函数
count()：使用
left join而不是join将有助于在任何参与者的条件不匹配时返回预期结果。Demo here
cfh9epnr3#
您可以使用分析函数（Count（）Over（）和Lead（）Over（））来获取这两个值-玩家总数和日期差异，以查看日志是否在连续的几天（DAY_DIFF = 1）。更新：
在内部查询中添加了一个分析列，以获取第一个（和其他）日志记录。主要工作已经通过内部查询完成，结果如下：
外部选择只是计算每天连续登录的不同玩家，然后将其除以玩家总数。好吧，它在那里工作-他们只是出于某种原因不满意列名“结果”。将其更改为“分数”，如下所示：
