MySQL刷题记录 day1

NULL是需要单独判断一下的！

SELECT name
FROM customer
WHERE referee_id <> 2
OR referee_id IS NULL;

SELECT c.Name AS Customers 
FROM Customers AS c
LEFT JOIN Orders AS o ON c.Id = o.CustomerId
WHERE o.Id IS NULL;

SELECT customers.name AS 'Customers'
FROM customers
WHERE customers.id NOT IN
(
    SELECT customerid FROM orders
);

SELECT player_id, min(event_date) as first_login
FROM Activity
GROUP BY player_id;

SELECT a.player_id, a.device_id 
FROM Activity AS a,
(
    SELECT player_id, MIN(event_date) AS login_first
    FROM Activity
    GROUP BY player_id
) AS temp
WHERE a.player_id = temp.player_id
AND a.event_date = temp.login_first;

SELECT player_id, event_date, 
    SUM(games_played) 
    OVER(partition by player_id order by event_date) as games_played_so_far
FROM Activity;

over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。
其参数：over（partition by columnname1 order by columnname2）
含义：按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。
例如：employees表中，有两个部门的记录：department_id ＝10和20
select department_id，rank（） over（partition by department_id order by salary) from employees就是指在部门10中进行薪水的排名，在部门20中进行薪水排名。如果是partition by org_id，则是在整个公司内进行排名。\

• datediff(event_date, MIN(event_date) OVER(partition BY player_id))
• lead()：取后N行
• round(x,d)：x指要处理的数，d是指保留几位小数
• Lag()就是取当前顺序的上一行记录。结合over就是分组统计数据的。

--方法一
SELECT ROUND(
    (
        SELECT COUNT(player_id)
        FROM (
            SELECT player_id, event_date, 
            LAG(event_date) OVER(PARTITION BY player_id ORDER BY event_date) AS next_date,
            RANK() OVER(PARTITION BY player_id ORDER BY event_date) AS login_times 
            FROM Activity
         ) AS temp 
     WHERE datediff(event_date, next_date) = 1 and login_times = 2
    )
/
(
    SELECT COUNT(player_id)
    FROM(
        SELECT player_id FROM Activity GROUP BY player_id
    ) AS temp
)
, 2) AS fraction;
--方法二
SELECT ROUND((
    (SELECT COUNT(player_id) 
        FROM(
        SELECT player_id,
        datediff(event_date, MIN(event_date) OVER(partition BY player_id)) AS diff 
        FROM Activity
    ) AS temp WHERE diff = 1)
/
    (SELECT COUNT(distinct player_id) FROM Activity)
), 2) AS fraction;
--方法三
WITH temp AS (
	SELECT player_id,
               datediff(event_date, min(event_date) over(partition by player_id)) AS diff
	FROM Activity
) 

SELECT ROUND(
	SUM(case diff when 1 then 1 else 0 end) /
	COUNT(distinct player_id),
2) AS fraction FROM temp;
-- 方法四
SELECT ROUND(
    AVG(event_date is not null), 2) AS fraction 
    FROM (
	SELECT player_id, MIN(event_date) AS first_login 
        FROM Activity
	GROUP BY player_id
) temp LEFT JOIN Activity
ON temp.player_id = activity.player_id
AND datediff(event_date, first_login) = 1;

• WITH AS如果第一个SQL能查出结果，则返回结果，否则，执行第二条SQL，返回第二条SQL的结果。
• case diff when 1 then 1 else 0 end