Newcode-SQL

最新推荐文章于 2022-10-24 13:12:59 发布
最新推荐文章于 2022-10-24 13:12:59 发布
阅读量292 收藏
点赞数
文章标签： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/GLORYc/article/details/110456859
版权

SQL

牛客SQL练习

SQL知识点

1、join

  • inner join（default）:找出左右两表均匹配的记录
  • left join： 左连接，以左表为准，逐条去右表找可匹配字段，如果有多条会逐次列出，如果没有找到则是NULL；
  • right join：右连接，以右表为准，逐条去左表找可匹配字段，如果有多条会逐次列出，如果没有找到则是NULL；
  • full outer join: 全连接，包含两个表的连接结果，如果左表缺失或者右表缺失的数据会填充NULL。

**join 之前要确保关联键是否去重，是不是刻意保留非去重结果。

2、union & Union All

  • union和union all 均基于列合并多张表的数据，所合并的列格式必须完全一致。
  • union的过程中会去重并降低效率，union all 直接追加数据。
  • union 前后是两段select 语句而非结果集。

3、Where & Having & Group by

  • group by 是分类聚合
  • where必须在group by之前
  • having的筛选针对类，where针对字段

4、事务

  • 事务就是用户定义的一系列执行SQL语句的操作, 这些操作要么完全地执行，要么完全地都不执行， 它是一个不可分割的工作执行单元。
  • 事务的四大特性ACID原子性、一致性、隔离性、持久性事务是对表中数据进行更新的单位
原子性一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作
一致性数据库总是从一个一致性的状态转换到另一个一致性的状态
隔离性通常来说，一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的。
持久性一旦事务提交，则其所做的修改会永久保存到数据库
  • 事务开始语句(DML语句包括INSERT、DELETE、UPDATE)
  • 事务结束语句(COMMIT 或者 ROLLBACK);
    a) COMMIT —— 提交处理
    COMMIT是提交事务包含的全部更新处理的结束指令，相当于文件处理中的覆盖保存。
    一旦提交，就无法恢复到事务开始前的状态了。
    b) ROLLBACK —— 取消处理
    ROLLBACK是取消事务包含的全部更新处理的结束指令，相当于文件处理中的放弃保存。
    一旦回滚，数据库就会恢复到事务开始之前的状态。

5、窗口函数Rank() over(partition by XX order by XX)

  • rank：
  • dense_rank：
  • row_number：

6、时间函数

常用的日期提取函数包括 year()/month()/day()/hour()/minute()/second()
日期运算函数包括datediff(enddate,stratdate) 计算两个时间的时间差（day)
date_sub(startdate,days) 返回开始日期startdate减少days天后的日期
date_add(startdate,days) 返回开始日期startdate增加days天后的日期
获取日期 CURRENT_DATE
获取时间 CURRENT_TIME
获取当前的日期和时间 CURRENT_TIMESTAMP
截取日期元素 EXTRACT

7.distributeby 、sort by 、cluster by 、order by 区别？

1). order by 只有一个reduce负责对所有的数据进行排序，若大数据量，则需要较长的时间。建议在小的数据集中使用order by 进行排序。

2). order by 可以通过设置hive.mapred.mode参数控制执行方式，若选择strict ，则order by 则需要指定limit（若有分区还有指定哪个分区）；若为nostrict ，则与关系型数据库差不多。

3). sort by 基本上不受hive.mapred.mode影响，可以通过mapred.reduce.task 指定reduce个数，查询后的数据被分发到相关的reduce中。

4). sort by 的数据在进入reduce前就完成排序，如果要使用sort by 是行排序，并且设置map.reduce.tasks>1,则sort by 才能保证每个reducer输出有序，不能保证全局数据有序。

5). distributeby 采集hash算法，在map 端将查询的结果中hash值相同的结果分发到对应的reduce文件中。

6). distributeby 可以使用length方***根据string 类型的长度划分到不同的reduce中，最终输出到不同的文件中。 length 是内建函数，也可以指定其他的函数或这使用自定义函数。

7). cluster by 除了distribute by 的功能外，还会对该字段进行排序，但是排序只能是升序排序，不能指定排序规则为ASC 或者DESC，所以cluster by = distribute by +sort by 。

8. 连接字段值

group_concat(x,分隔符 [默认,])

9. sql 的 执行顺序(来自某篇文章，忘记哪一篇了，对不起><)

from -> where -> group by ->having -> select -> order by -> limit

3.问：（1 ）两条语句的执行结果是否一样？为什么？
（2 ）假设，数据量很大的情况下，您会选择哪种语句执行？也可以自行开发

--sql 语句 1
select
t1.id,t1.xxx,t2.xxx
from t1 left join t2
on t1.id = t2.id and t1.id < 10

```sql
#--sql 语句 2#
select
t1.id,t1.xxx,t2.xxx
from t1 left join t2
on t1.id = t2.id
where t1.id < 10

答：1）由于left join,right join,full join的特殊性，不管on上的条件是否为真都会返回left 或right表中的记录，full则具有left 和right的特性的并集。

sql 语句 1 中 采用的是left join,所以 on 里的 t1.id <10 对左表 t1 不起作用，结果还是会返回t1 表的所有数据
sql 2 则是先 通过 on 上的条件，将两表关联，在最终关联好的表上，在进行过滤，所以只会返回t1.id < 10 的所有数据

  1. 当数据量很大的情况下，基于上述情况我会选择 sql 2 ，但是性能不高,可以采用以下查询
select
tmp_t1.id,tmp_t1.xxx,t2.xxx
from
(select * from t1 where t1.id < 10) tmp_t1 left join t2
on tmp_t1.id = t2.id

4.count(distinct user_id) 和 group by user_id 之后 求 count 两者有什么区别？

首先要清楚，count(distinct) 的原理机制，首先他是将数据通过map 端发往一个reduce，之后reduce接收到数据之后，会将数据放入到 hashset 中去重，之后cleanUp() 方法，在执行最后的逻辑，比如:计算hashset 的size等。

这里就出现了一些问题，

1）数据都发往一个reduce会造成数据倾斜，
2）程序从分布式变成单机程序，影响效率
3）程序执行过程中，只产生一个job

但也不是绝对的，当数据量很小的时候，此时我们并不需要采分布式执行，一个job运行足矣，但是，当数据量比较大的时候，这时count(distinct) 就暴露除了大大的弊端，所以，此时，不应该采用此法来实现去重

group() by count() 当数据量比较大的时候，采用此法，先分组，这时已经在map端实现了去重机制，之后数据发往reduce 数据量已经变得很小了，并且此法涉及到shuffle ，所以reduce的压力不会集中在某个上，并且会产生多个job 。

  1. group() by count() 一定比count(distinct) 性能要好吗？

不一定，当数据量比较大的时候采用group() by count() 会比count(distinct) 要好，但是在数据量比较小的时候，一个 job就可以处理，没必要用两个job ，也没必要shuffle，所以调优看情况而定

1. 查找最晚入职员工的所有信息

为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天(sqlite里面的注释为–,mysql为comment)
CREATE TABLE employees (
emp_no int(11) NOT NULL, – ‘员工编号’
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));

select * from employees order by hire_date desc limit 1
##员工入职日期含重复值
select * from employees 
 where hire_date in (select max(hire_date) from employees)

选出最晚入职日期max(hire_date)，用where筛选入职日期等于最晚入职日期的员工，最后套一层select * from employee

2. 查找入职员工时间排名倒数第三的员工所有信息

为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));

select * from employees 
where hire_date = (select distinct(hire_date) from employees order by hire_date desc limit 2,1)

3.查找各个部门当前(dept_manager.to_date=‘9999-01-01’)领导当前(salaries.to_date=‘9999-01-01’)薪水详情以及其对应部门编号dept_no

(注:输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列)
CREATE TABLE salaries (
emp_no int(11) NOT NULL, – ‘员工编号’,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));
CREATE TABLE dept_manager (
dept_no char(4) NOT NULL, – ‘部门编号’
emp_no int(11) NOT NULL, – ‘员工编号’
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));

## 考察inner join

select a.* , b.dept_no
from salaries a join dept_manager b 
on a.emp_no=b.emp_no
where a.to_date='9999-01-01' and b.to_date='9999-01-01'
order by a.emp_no

INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。
LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据。

4.查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括暂时没有分配具体部门的员工(请注意输出描述里各个列的前后顺序)

CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));

##考察right join

select b.last_name,b.first_name,a.dept_no
from dept_emp a right join employees b
on a.emp_no =b.emp_no

on是在生成临时表时使用的条件,不管on中的条件是否为真，都会返回左边表中的记录。
where条件是在临时表生成好后，再对临时表进行过滤的条件

5.查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t

CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));

group by , having聚组条件；where是聚行条件

select emp_no,count(from_date) t
from salaries group by emp_no having t>15

6.获取所有员工当前的(dept_manager.to_date=‘9999-01-01’)manager，如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。

CREATE TABLE dept_emp (
emp_no int(11) NOT NULL, – ‘所有的员工编号’
dept_no char(4) NOT NULL, – ‘部门编号’
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE dept_manager (
dept_no char(4) NOT NULL, – ‘部门编号’
emp_no int(11) NOT NULL, – ‘经理编号’
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));

1、用 INNER JOIN 连接两张表，因为要输出自己的经理，得知自己与经理的部门要相同，故有限制条件 de.dept_no = dm.dept_no
2、再用 WHERE 限制当前员工与当前经理的条件，即 dm.to_date = ‘9999-01-01’ 、de.to_date = ‘9999-01-01’ 、 de.emp_no不等于 dm.emp_no

SELECT de.emp_no, dm.emp_no AS manager_no 
FROM dept_emp AS de INNER JOIN dept_manager AS dm
ON de.dept_no = dm.dept_no 
WHERE dm.to_date = '9999-01-01' AND de.to_date = '9999-01-01' 
AND de.emp_no <> dm.emp_no

7.获取所有部门中当前(dept_emp.to_date = ‘9999-01-01’)员工当前(salaries.to_date=‘9999-01-01’)薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列。

CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));

1、连接工资表和员工表，筛选当前日期的信息，作为c表
2、对C表用dense_rank partition by 部门编号 order by salary，排名记为rk，选出C表中员工编号、部门编号、工资和rk，记为D表
3、对D表用where 筛选rk=1，再选出所需字段

select d.dept_no,d.emp_no,d.salary
from 
(select c.dept_no,c.emp_no,c.salary,
       dense_rank() over (partition by c.dept_no order by c.salary desc) as rk
     from
        (select a.dept_no,a.emp_no,b.salary 
           from dept_emp a join salaries b on a.emp_no = b.emp_no
           where a.to_date = '9999-01-01' and b.to_date = '9999-01-01') c
) d
where d.rk=1 order by d.dept_no

8.查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成吗

CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));

select c.emp_no,c.salary,c.last_name,c.first_name
from 
(select a.*,b.salary, dense_rank() over (order by b.salary desc) rk
      from employees a join salaries b 
      on a.emp_no=b.emp_no
      where b.to_date='9999-01-01') c
where c.rk=2

9.查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

（注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的to_date!=‘9999-01-01’，这样的数据不显示在查找结果里面）
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL, – ‘入职时间’
PRIMARY KEY (emp_no));
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL, – ‘一条薪水记录开始时间’
to_date date NOT NULL, – ‘一条薪水记录结束时间’
PRIMARY KEY (emp_no,from_date));

1、将employee表与salaries a表连接，基于员工编号和hire_date=from_date，得到入职时薪水
2、与salaries b表连接，基于员工编号，where筛选to_date=9999-01-01，得到当前薪水
3、growth=b.salary-a.salary ,

select e.emp_no,(b.salary-a.salary) growth
from salaries a join employees e
on e.emp_no=a.emp_no and e.hire_date=a.from_date
join salaries b 
on e.emp_no=b.emp_no where b.to_date='9999-01-01'
order by growth

10.获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date=‘9999-01-01’,

结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE dept_manager (
dept_no char(4) NOT NULL,
emp_no int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));

1、员工表和经历表连接，分别将员工表和经理表与工资表s1，s2连接
2、用where筛选日期和s1的工资>s2的工资
3、select所需字段

SELECT e.emp_no,m.emp_no AS manager_no,sa1.salary AS emp_salary,sa2.salary AS manager_salary   
FROM dept_emp e 
LEFT JOIN dept_manager m ON e.dept_no=m.dept_no 
LEFT JOIN salaries sa1 ON sa1.emp_no = e.emp_no 
LEFT JOIN salaries sa2 ON sa2.emp_no = m.emp_no
WHERE e.to_date='9999-01-01' AND m.to_date='9999-01-01' 
AND sa1.to_date='9999-01-01' AND sa2.to_date='9999-01-01'
AND sa1.salary > sa2.salary;

11.汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的当前(dept_emp.to_date = ‘9999-01-01’)员工的当前(titles.to_date = ‘9999-01-01’)title以及该类型title对应的数目count，结果按照dept_no升序排序

(注：因为员工可能有离职，所有dept_emp里面to_date不为’9999-01-01’就已经离职了，不计入统计，而且员工可能有晋升，所以如果titles.to_date 不为 ‘9999-01-01’，那么这个可能是员工之前的职位信息，也不计入统计)
CREATE TABLE departments (
dept_no char(4) NOT NULL,
dept_name varchar(40) NOT NULL,
PRIMARY KEY (dept_no));
CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE IF NOT EXISTS titles (
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

1、inner join 三表
2、where筛选日期
3、group by 同时对部门编号和title分组，count（title），select 其他字段，order by

select b.dept_no,a.dept_name,c.title,count(c.title) count
from 
departments a join dept_emp b on a.dept_no=b.dept_no
join titles c on b.emp_no=c.emp_no
where b.to_date = '9999-01-01' and c.to_date = '9999-01-01'
group by b.dept_no,c.title
order by b.dept_no

12.给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。

提示：在sqlite中获取datetime时间对应的年份函数为strftime(’%Y’, to_date)
(数据保证每个员工的每条薪水记录to_date-from_date=1年，而且同一员工的下一条薪水记录from_data=上一条薪水记录的to_data)

CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));
如：插入
INSERT INTO salaries VALUES(10001,52117,‘1986-06-26’,‘1987-06-26’);
INSERT INTO salaries VALUES(10001,62102,‘1987-06-26’,‘1988-06-25’);
INSERT INTO salaries VALUES(10002,72527,‘1996-08-03’,‘1997-08-03’);
INSERT INTO salaries VALUES(10002,72527,‘1997-08-03’,‘1998-08-03’);
INSERT INTO salaries VALUES(10002,72527,‘1998-08-03’,‘1999-08-03’);
INSERT INTO salaries VALUES(10003,43616,‘1996-12-02’,‘1997-12-02’);
INSERT INTO salaries VALUES(10003,43466,‘1997-12-02’,‘1998-12-02’);

select s1.emp_no,s1.from_date,(s1.salary-s2.salary) salary_growth
from salaries as s1 inner join salaries as s2
on s1.emp_no = s2.emp_no 
and s1.from_date=s2.to_date
where (s1.salary-s2.salary) > 5000
order by salary_growth desc

13.获取有奖金的员工相关信息。

给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date=‘9999-01-01’

CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));
CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
create table emp_bonus(
emp_no int not null,
received datetime not null,
btype smallint not null);
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL, PRIMARY KEY (emp_no,from_date));

1、连接inner join三表，where筛选日期
2、case字段 when 值1 then 结果1……else 值n
end
3、select 字段

select a.emp_no, a.first_name,a.last_name, b.btype,c.salary,
(case b.btype 
 when 1 then c.salary*0.1 
 when 2 then c.salary*0.2
else c.salary*0.3
end) bonus
from employees a join emp_bonus b on a.emp_no=b.emp_no
join salaries c  on b.emp_no=c.emp_no
where c.to_date='9999-01-01'

14.现在有一个需求，让你统计正常用户发送给正常用户邮件失败的概率:每一个日期里面，正常用户发送给正常用户邮件失败的概率是多少，结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排序

有一个邮件(email)表，id为主键， type是枚举类型，枚举成员为(completed，no_completed)，completed代表邮件发送是成功的，no_completed代表邮件是发送失败的。简况如下:
在这里插入图片描述
下面是一个用户(user)表，id为主键，is_blacklist为0代表为正常用户，is_blacklist为1代表为黑名单用户，简况如下:
在这里插入图片描述

select date,
round(sum(case e.type when 'no_completed' 
          then 1 else 0 end)*1.0/count(e.type),3) p 
from email e
where send_id in (select id from user where is_blacklist = 0)
and receive_id in (select id from user where is_blacklist = 0)
group by date;

15.牛客每天有很多人登录，请你统计一下牛客新登录用户的次日成功的留存率，保存小数点后面3位(3位之后的四舍五入)

有一个登录(login)记录表，简况如下:
在这里插入图片描述
第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备第一次新登录了牛客网

1、选出所有用户ID作为分母
2、用date函数找出次日登陆的用户，用where筛选出其ID，count ID作为分子

select 
round(count(distinct user_id)*1.0/(select count(distinct user_id) from login) ,3)
from login
where (user_id,date) in 
(select user_id,date(min(date),'+1 day') from login group by user_id)

16.牛客每天有很多人登录，请你统计一下牛客每个日期登录新用户个数，请你写出一个sql语句查询每个日期登录新用户个数，并且查询结果按照日期升序排序，

有一个登录(login)记录表，简况如下:
在这里插入图片描述
第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网，因为是第1次登录，所以是新用户

对login表用窗口函数rownumber对每个用户的登录日期排序，排名记为rk,
select date,rk 记为表a，对表a根据date分组，对rk=1的日期数求和

select a.date, sum(case rk when 1 then 1 else 0 end) new
from 
(select date, row_number() over(partition by user_id order by date) rk from login) a
group by date

17.牛客每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率。

在这里插入图片描述
请你写出一个sql语句查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序，上面的例子查询结果如下:
在这里插入图片描述

select a.date, round(count(b.user_id) * 1.0/count(a.user_id),3) p
from 
(select user_id, min(date) as date from login group by user_id) a
left join login b 
on a.user_id = b.user_id and b.date = date(a.date, '+1 day')
group by a.date
union
select date, 0.000  p from login 
where date not in (select min(date) from login group by user_id)
order by date

18.牛客每天有很多人登录，请你统计一下牛客每个用户查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题。 不存在没有登录却刷题的情况，但是存在登录了没刷题的情况，不会存在刷题表里面，有提交代码没有通过的情况，但是会记录在刷题表里，只不过通过数目是0。

有一个登录(login)记录表，简况如下:
在这里插入图片描述
有一个刷题（passing_number)表，简况如下:
在这里插入图片描述
还有一个用户(user)表，简况如下:
在这里插入图片描述

1、对刷题表用窗口函数，选出相应字段作为表a
2、将user表连接表a，排序

select user.name as u_n, a.date, a.ps_num
from user inner join 
(select user_id, date, sum(number) over (partition by user_id order by date asc) ps_num
 from passing_number) a
on user.id=a.user_id
order by date asc, u_n asc

19.查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序

在这里插入图片描述

select id,t1.job,score,rank 
from
((select *,dense_rank() over(partition by job order by score desc) as rank from grade)t1
left join 
(select job,(count(id)+1)/2 as start,
((count(id)+1)/2 + case when count(id)%2=1 then 0 else 1 end)end
from grade group by job)t2
on t1.job=t2.job)
where rank<=end and rank>=start
order by id

20.现有交易数据表user_sales_table如下，查询支付金额在前20%的用户。

user_name 用户名 pay_amount 用户支付额度
输出要求如下：
user_name 用户名（前10%的用户）

利用窗口函数ntile将每个用户和对应的支付金额分成5组（这样每组就有1/5），取分组排名第一的用户组即前支付金额在前20%的用户。（注意这里是求前20%的用户而不是求支付排在前20的用户）

select b.user_name from 
(select user_name, ntile(5) over(order by sum(pay_amount) desc) as level
from user_sales_table group by user_name ) b 
where b.level = 1

21.现有用户登陆表user_login_table如下，查询连续7天都登陆平台的重要用户

user_name 用户名 date 用户登陆时间

输出要求如下：
user_name 用户名（连续7天都登陆的用户数）

首先利用偏移窗口函数lead求得每个用户在每个登陆时间向后偏移7行的登陆时间，再计算每个用户在每个登陆时间滞后7天的登陆时间，如果每个用户向后偏移7行的登陆时间正好等于滞后7天的时间，说明该用户连续登陆了7天。


select b.user_name from
(select user_name, date,
 lead(date,7) over(partition by user_name order by date desc) as date_7
 from user_login_table) b 
where b.date is not null
and date_sub(cast(b.date as date,7)) = cast(b.date_7 as date)
GLORYc
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
newcode-day1
wwhssb的博客
05-06 136
用verilog实现两个串联的异步复位的T触发器的逻辑，结构如图： 信号示意图： 波形示意图： 输入描述： 输入信号 data, clk, rst，类型 wire，在testbench中，clk为周期5ns的时钟，rst为低电平复位 输出描述： 输出信号 q ，类型 reg T触发器功能： q*=q^T,其中q是现态，q*是次态，T是输入，在这儿是data。 两级级联T触发器真值表： 代码： `timescale 1ns/1ns module Tff_2 ( input wire data, c
使用PowerShell的Invoke-SqlCmd
culuo4781的博客
07-26 1937
PowerShell features many one-line commands for working with SQL Server, one of which is Invoke-SqlCmd. This tool can be useful in many development contexts where we need to quickly execute script...
NewCode
weixin_33688840的博客
12-23 146
1.【数论】给你N，求不大于N的最大完全平方数。 #include<bits/stdc++.h> #define FOR(i,a,b) for(int i=(a),_b=(b);i<=_b;i++) #define DOR(i,a,b) for(int i=(a),_b=(b);i>=_b;i--) using namespace std; #define...
Newcode #1
qq_45651829的博客
11-22 409
一道关于素数除法的题，涉及到venn图，有个小坑 题目； 这道题的数字范围太大了，如果暴力搜索必然会超时，只能用巧法来减少运算的次数。 首先一个数如果要可以整除一个素数，其自身必然是这个素数的整数倍，则给出一个数，用这个数去除以某个素数，就可以得到在这个给出的数里面有个素数倍数，就有几个可以整除素数的数，以10和3为例子，10/3=3,10的范围里面只有3，6，9可以整除3，则猜想正确。 但是...
NewCode----数串
Betterc5的博客
07-31 327
题目描述： 设有n个正整数，将他们连接成一排，组成一个最大的多位整数。 如:n=3时，3个整数13,312,343,连成的最大整数为34331213。 如:n=4时,4个整数7,13,4,246连接成的最大整数为7424613。 输入描述: 有多组测试样例，每组测试样例包含两行，第一行为一个整数N（N&amp;lt;=100），第二行包含N个数(每个数不超过1000，空格分开)。 输出描述...
NewCode02
qq_45111347的博客
11-12 306
1.在 Java 中，存放字符串常量的对象属于（ ）类对象。 Character 字符封装类 String 常量池中字符串 StringBuffer 存放字符数组 Vector 数组 2. 如果类的方法没有返回值，该方法的返回值类型应当是abstract。（） 如果类的方法没有返回值，该方法的返回值类型应当是void。 被abstract修饰的类是抽象类，抽象类不能被实例化，但是可以被继承，也可以继承。 3.在java中，已定义两个接口B和C，要定义一个实现这两个接口的类，以
SQL语句(T-SQL汇总) 用T-SQL画出这些图形
09-11
T-SQL，全称Transact-SQL，是SQL的一个扩展，主要用于Microsoft SQL Server数据库管理系统中。它是SQL标准与Microsoft特定扩展的结合，提供了更丰富的数据处理能力，包括数据查询、更新、事务处理以及数据库对象的...
[T-SQL] T-SQL 2008 程序员高级开发技术指南 (英文版)
03-03
Fully functioning examples and downloadable source code bring Coles’ technically accurate and engaging treatment of Transact–SQL into your own hands. Step–by–step explanations ensure clarity, and...
[T-SQL] T-SQL 2012 程序员高级开发技术指南 (英文版)
03-03
Fully functioning examples and downloadable source code bring technically accurate and engaging treatment of Transact–SQL into your own hands. Step–by–step explanations ensure clarity, and an ...
【NewCode】Sivan的刷题日记
weixin_62633072的博客
05-02 786
没事干就来刷题~~~
NewCode----句子反转
Betterc5的博客
07-31 336
题目描述 给定一个句子（只包含字母和空格）， 将句子中的单词位置反转，单词用空格分割, 单词之间只有一个空格，前后没有空格。 比如： （1） “hello xiao mi”-&amp;amp;gt; “mi xiao hello” 输入描述: 输入数据有多组，每组占一行，包含一个句子(句子长度小于1000个字符) 输出描述: 对于每个测试示例，要求输出句子中单词反转后形成的句子 示例1 ...
C/C++：newcode经典例子
猴子居士的博客
03-19 512
题目描述 给定一个 nowcoder.txt文件，其中有3列信息，如下实例，编写一个sheel脚本来检查文件第二列是否有重复，且有几个重复，并提取出重复的行的第二列信息： 实例： 20201001 python 99 20201002 go 80 20201002 c++ 88 20201003 php 77 20201001 go 88 20201005 shell 89 20201006 java 70 20201008 c 100 20201007 java 88 20201006 go 97 结果
NewCode00 分钟计算
萌德的博客
04-08 653
NewCode C/C++语言基础编程 分钟计算 链接：https://ac.nowcoder.com/acm/problem/21994 来源：牛客网 题目描述 给你一天里的两个时间(24小时制),求这两个时间内有多少分钟,保证第一个时间在第二个时间之前 输入描述: 输入两行,每行包含两个整数表示小时与分钟 输出描述: 输出分钟数 示例1 输入 10 10 11 05 输出 55 #includ...
开刷NewCode华为机试---第一期
肖晨曦的博客
10-24 237
输入一个 int 型的正整数，计算出该 int 型数据在内存中存储时 1 的个数。数据范围：保证在 32 位整型数字范围内
开刷NewCode华为机试---第二期
最新发布
肖晨曦的博客
10-24 474
输入第一行为一个正整数n(1≤n≤1000),下面n行为n个字符串(字符串长度≤100),字符串中只含有大小写字母。数据范围： 1
LeetCode和NewCode常见错误（持续更新）
山雨的博客
05-18 5695
use of undeclared identifier 使用了未定义的标识符 解决： 可能是没有在结构体内定义导致的 comparison of integers of different signs: ‘int’ and ‘size_type’ 错误信息： int 和 size_t 两种不同类型的整数在进行比较 错误实例： int i = 0; for(i = 0 ; i < strin...
根据凸多边形顶点坐标来计算面积算法与实现
热门推荐
Han的小站
01-03 1万+
根据凸多边形顶点坐标来计算面积算法与实现
nowcoder 笔试题
slowlight93的专栏
03-13 2043
昨天做了下nowcode的内推笔试题，最后剩三分钟时惊险地AK了。。 都要求O(n)解法，空间O(1)，很考思维啊。。最大的LeftMax与rightMax之差绝对值题意：给个数列，然后把它划分成两部分，不可为空。求两部分最大值的差的绝对值的最大值。。 思路： 我们可以想象一个数列中的最大值可以“覆盖”这个数列的全部范围。 所以，我们只要枚举划分的边界点就行了。按照左右半区的方式重新组合单链
Oracle SQL Developer快速入门与操作教程
最后，codesnippets工具栏提供了代码片段库，可以节省时间，提高编码效率，尤其在编写重复性代码时。 通过学习这个教程，无论是管理现有Oracle数据库还是开发新的数据库项目，用户都可以熟练地使用Oracle SQL ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值