力扣sql会员sql题

力扣会员sql题

使用MySQL 8.0窗口函数解决 对于排序问题，常用的有三种窗口函数，以数值4,5,5,6为例:
ROW_NUMBER(): 求行数，结果为1,2,3,4
RANK(): 有间隔的分级，结果为1,2,2,4
DENSE_RANK(): 无间隔的分级，结果为1,2,2,3
使用这些窗口函数时，要用over设定窗口，用法为:

function OVER windowName
WINDOW windowName AS (PARTITION BY a_col ORDER BY b_col)

这两行代码含义为设定一个对a_col分组后对每组的b_col排序的窗口，对这个窗口应用function。

在本题中，要找到每位玩家第一次登录的设备id，则需要对玩家id进行分组，每组求ROW_NUMBER，使用ROW_NUMBER而不使用其他窗口函数的原因是ROW_NUMBER结果是不重复的，适合本题的场景。
代码如下:

SELECT
    player_id, 
    device_id
FROM
    (SELECT
        player_id,
        device_id,
        ROW_NUMBER() OVER(PARTITION BY player_id ORDER BY event_date) AS rn
    FROM
        Activity) AS a
WHERE 
    rn = 1;

534. 游戏玩法分析 III

思路和心得：
1.内连
条件比较重要：题目其实是让求前缀和
2.分组
按照最后想要的结果分组
3.排序
为了让结果和示例一样，排个序
（好像不排序，也ac了）

select a1.player_id as 'player_id',
    a2.event_date as 'event_date',
    sum(a1.games_played) as 'games_played_so_far'   #累计的量，前缀和
from
    Activity as a1
    inner join  Activity as a2                      #内连
where a1.player_id = a2.player_id                   #同一个人
    and a1.event_date <= a2.event_date              #当前前缀和的下标
group by a1.player_id, a2.event_date
order by a1.player_id, a2.event_date
;

550. 游戏玩法分析 IV

编写一个 SQL 查询，报告在首次登录的第二天再次登录的玩家的比率，四舍五入到小数点后两位。换句话说，您需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。

话不多说，我们来看一下每一步思路：

所有玩家首次登录的时间及ID，这就是游戏玩法分析Ⅰ的答案

#1.所有玩家首次登录的时间及ID
SELECT player_id, MIN(event_date) event_date
FROM Activity
GROUP BY player_id;
有了第一步的答案，就很好求出第一天登录，第二天也登陆的玩家数量，直接内连接，注意一下连接条件即可

#2.首次登陆之后第二天也登录的玩家数量
SELECT COUNT(*) replay_num
FROM Activity a
JOIN (
    SELECT player_id, MIN(event_date) event_date
    FROM Activity
    GROUP BY player_id
) b ON a.player_id=b.player_id AND DATEDIFF(a.event_date,b.event_date)=1
求出玩家总数，没啥说的，统计player_id注意去重就行

#3.玩家总数
SELECT COUNT(DISTINCT player_id) total_num
FROM Activity
本来还纠结了一下怎么样写的比较优雅，算了，直接简单粗暴把第2、3步结果通过笛卡尔乘积，拼一下然后求解就行了。

#4. 笛卡尔拼接
SELECT ROUND(replay_num/total_num, 2) fraction
FROM (
    SELECT COUNT(*) replay_num
    FROM Activity a
    JOIN (
        SELECT player_id, MIN(event_date) event_date
        FROM Activity
        GROUP BY player_id
    ) b ON a.player_id=b.player_id AND DATEDIFF(a.event_date,b.event_date)=1
) re CROSS JOIN (
    SELECT COUNT(DISTINCT player_id) total_num
    FROM Activity
) tot

• 在数学中，笛卡尔乘积是指两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员

假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
MySQL中笛卡尔积相当于我们学习数学中集合的概念，数据库中运用此思想，产生了很多满足我们实际需要的SQL语句。

语法：
SELECT * FROM [TABLE 1] CROSS JOIN [TABLE 2]
OR

SELECT * FROM [TABLE 1], [TABLE 2]  //重点,平时写法要注意啊

员工薪水中位数

怕说真话，官方给的答案没看明白，因为对SQL的变量使用不熟悉；case也是粗浅了解。
相比之下，row_number和count搭配over partition by就很香了。

逻辑直接了当 - 既然要按每个公司去算salary中位数，那就按每个公司partition by，先给公司里所有salary排序编号，这里用row number是为了给重复出现的salary也独一无二的编号；然后在count over partition by计算出每个公司里的salary总数，这两个新添加的competed columns也是解题的关键。

之后就直接选取需要的col，筛选条件就是salary在公司里排序的编号是奇数总数的中间位，或者偶数总数的中间两位。

select id, Company, Salary from
(
    select 
    id, 
    row_number() over (partition by Company Order by Salary asc) companyRank, 
    count(id) over (partition by Company) companyCount,
    Company, 
    Salary 
    from Employee
) tmp
where tmp.company Rank in (floor((tmp.companyCount + 1)/2), floor((tmp.companyCount + 2)/2));
;

• round()遵循四舍五入把原值转化为指定小数位数。
  如：round(1.45,0) = 1;round(1.55,0)=2

• floor()向下舍入为指定小数位数.
  如：floor(1.45,0)= 1;floor(1.55,0) = 1

• ceiling()向上舍入为指定小数位数.
  如：ceiling(1.45,0) = 2;ceiling(1.55,0)=2