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

预备知识：

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,
  Result.player_id
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