力扣会员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