SQL-聚合函数-550. 游戏玩法分析

预备知识：

1. date_add函数是一个用于在日期上添加指定时间间隔的函数，它的一般语法如下：

DATE_ADD(date, INTERVAL expression unit)

• date 是指定的日期。
• expression 是一个表示要添加的值的表达式。
• unit 是时间单位，例如 YEAR、MONTH、DAY、HOUR、MINUTE、SECOND 等。

例如：

SELECT DATE_ADD('2023-01-01', INTERVAL 7 DAY);

SELECT DATE_ADD('2023-01-15', INTERVAL -10 DAY);

解题思路：

题目要求连续两天登录的玩家的占比，那我们可以首先求出用户首次登录的第二天的时间，方法是查询出Activity表中每个用户的第一天时间，并加上1。

select player_id, DATE_ADD(MIN(event_date), INTERVAL 1 DAY) as second_date
from Activity
group by player_id

我们将此表命名为Expected。然后我们要从Activity中查询event_date与Expected.second_date重叠的部分，注意此判定要限定在用户相同的前提下。这部分用户即为在首次登录后第二天也登录了的用户。

select Activity.player_id as player_id
  from (
    select player_id, DATE_ADD(MIN(event_date), INTERVAL 1 DAY) as second_date
    from Activity
    group by player_id
  ) as Expected, Activity
  where Activity.event_date = Expected.second_date and Activity.player_id = Expected.player_id

请注意，此处的“as Expected, Activity”是一个隐式的连接，这种写法实际上等价于用cross join连接两个表。

FROM (SELECT player_id, DATE_ADD(MIN(event_date), INTERVAL 1 DAY) as second_date
      FROM Activity
      GROUP BY player_id) as Expected
JOIN Activity

inner join基于连接条件从两个或者多个表中选择匹配的行，返回在连接条件下存在于两个表中的行，而不包括任何在其他表中没有匹配项的行。当使用“JOIN”关键字但是不指定连接类型时，默认是inner join，连接条件通常使用“ON”来指定。

将此表命名为Result，随后我们只需要得到Result表中的用户数量，以及Activity表中的用户数量，相除保留两位小数。

select IFNULL(round(count(distinct(Result.player_id)) / count(distinct(Activity.player_id)), 2), 0) as fraction

select IFNULL(round(count(distinct(Result.player_id)) / count(distinct(Activity.player_id)), 2), 0) as fraction
from (
  select Activity.player_id as player_id
  from (
    select player_id, DATE_ADD(MIN(event_date), INTERVAL 1 DAY) as second_date
    from Activity
    group by player_id
  ) as Expected, Activity
  where Activity.event_date = Expected.second_date and Activity.player_id = Expected.player_id
) as Result, Activity

Cross join和Inner join：

Cross join：

SELECT 
    Movies.CustomerID, Movies.Movie, Customers.Age, 
    Customers.Gender, Customers.[Education Level], 
    Customers.[Internet Connection], Customers.[Marital Status], 
FROM   
    Customers 
CROSS JOIN 
    Movies

Inner join：

SELECT 
    Movies.CustomerID, Movies.Movie, Customers.Age, 
    Customers.Gender, Customers.[Education Level], 
    Customers.[Internet Connection], Customers.[Marital Status]
FROM   
    Customers 
INNER JOIN 
    Movies ON Customers.CustomerID = Movies.CustomerID

二者的区别是什么呢？

Cross join会产生表格的所有可能组合，例如，100行的table1和100行的table2将产生10000条记录。通俗的讲，下边两者等价：

x CROSS JOIN y

x INNER JOIN y ON 1=1

仅显示两个连接表中具有匹配项的行的连接称为inner join，cross join + where <=> inner join + on：

回到之前的答案，我们可能会疑惑，为什么“count(distinct(Result.player_id)”这里要加distinct呢？答案很简单，因为最后一行执行的是cross join。我们可以更改一下代码让输出结果更直观：

我们可以看到，result的player_id输出了很多次，这是因为cross join做笛卡尔积，使之前result的答案（只有一行“1”）和Activity进行笛卡尔积，结果就为图上所示。因此，必须使用distinct进行去重，否则将会输出错误的结果。牢记，先进行join再select哟！