一、部门工资最高的员工【难度中等】开窗
方法1：
第一步：按DepartmentId分组查出Employee 表中最高工资的信息
select DepartmentId,max(Salary) from Employee group by DepartmentId
第二步：关联Department表和Employee 表 用IN 语句查询部门名字和工资的关系。
select Department.name as Department,
Employee.name as Employee,
Salary
from Employee inner join Department
on Department.id = Employee.DepartmentId
where (Department.id, Salary) in
(
select DepartmentId, max(Salary)
from Employee
group by DepartmentId
)
order by Salary desc;方法2：
第一步：把DepartmentId换成部门名字
SELECT t1.Name as Employee, t1.Salary, t2.Name as Department FROM
Employee t1 JOIN Department t2 ON t1.DepartmentId=t2.id
第二步：然后用开窗函数row_number()对上面结果的各个部门的工资排序，
SELECT Employee,
Department,
Salary,
row_number() OVER(PARTITION by Department ORDER BY Salary DESC)
FROM
(SELECT t1.Name as Employee,
t1.Salary,
t2.Name as Department
FROM
Employee t1 inner join Department t2
ON t1.DepartmentId=t2.id) q最后取row_number为1的数据即可，注意：row_number() 是分组之后对分组的数据进行排序
下面三个开窗函数区别。窗口函数原则上只能写在select子句中。
SELECT 后面跟字段,聚合函数... 这种模式的基本上必须要使用group by分组，不然语法错误
二、lag()函数，连续出现的数字【难度中等】
方法1：
logs内连接组合为三个相同的的表。命名为A，B，C
满足A的id等于B的id+1等于C的id+2。（id是同一个id，比如A的id为1，那后面B、C就是2和3）
同时还得满足三个最终id的值是相同的。即满足三个连续的id的值相同
SELECT DISTINCT A.num AS ConsecutiveNums FROM logs A, logs B, logs C WHERE A.id=B.id-1 AND B.id=C.id-1 AND A.num=B.num and B.num=C.num。。
另一种写法：
SELECT DISTINCT A.num AS ConsecutiveNums FROM logs A INNER JOIN logs B ON A.id=B.id-1 AND A.num=B.num INNER JOIN logs C ON B.id=C.id-1 AND C.num=B.num
方法2（比法1灵活一点，可以找出N个相同的）
用开窗函数row_number按照num分组之后，再根据分的组长度大于等于3并且对应的分组排序的序号与原始序号差值相等。
SELECT DISTINCT num AS ConsecutiveNums FROM
(SELECT id, num , "row_number"() OVER (PARTITION by num ORDER BY id) as rn FROM logs) L
GROUP BY L.rn-L.id, L.num HAVING count(*) >= 3
方法3：利用lag函数做（lead也行，跟lag相反，一个前几行，一个后几行）
Lag函数：lag(expression, offset, default_value)用于计算当前行和上第几(取决于offset参数)行之间的差异。Offset默认为1, default_value默认为NULL（注意：是在第几行取不到即不满足offset条件下才会显示的。可以指定字段如id）。
语法：lag(字段，后移的行数) over(....) 必须搭配over()使用，窗口函数都必须搭配over()使用
SELECT id, num, lag(num, int) OVER () previous_line_num FROM logs
如果int是1显示的是上一行的num，如果是0则显示当前行num即和原始num一样，如果是2则显示上第二行num(这时候由于第一行和第二行没有上第二行数据显示就是default_value值)。
SELECT id, num, lag(num, int) OVER (PARTITION BY num order by id) previous_line_num FROM logs
PARTITION BY 子句
PARTITION BY子句将结果集中的行划分LAG()为应用函数的分区。如果省略PARTITION BY子句，LAG()函数会将整个结果集视为单个分区。
ORDER BY 子句，排序AESC，DESC
注意：如果有PARTITION BY，则是分组之后再进行判断的上几行条件。
思路：根据num分组之后再判断当前行与上第二行id是否相差2（其实就是至少三个id连续的的条件）。也可以判断当前行与上一行相差1，上一行与上第二行相差1，绕一点。
SELECT DISTINCT(num) AS ConsecutiveNums FROM (
SELECT id, num, lag(id, 2) OVER (PARTITION by num ORDER BY id) as id_new FROM logs) A
WHERE id = id_new + 2
三、删除重复的电子邮箱【难度简单】
注意DELETE 删除的是一个数据库实实在在存在的表，不是SQL语句组合之后的表。
所以delete之后跟的是数据库的表，不能跟子句。delete from 表名 where 条件
只删：DELETE FROM Person WHERE id not in(SELECT min(id) as id FROM "Person" GROUP BY email)，保留分组之后的最小id，删除其余id。。。也可以用row_number() 根据排序的序号删除。
DELETE FROM Person WHERE id IN
(
SELECT id FROM (
SELECT id, email, row_number() OVER (PARTITION by email ORDER BY id) as rn FROM Person
) a
WHERE a.rn>1
)
四、行程和用户【难度困难】
写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。
取消率的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)
CREATE TYPE mood AS ENUM ('completed','cancelled_by_driver','cancelled_by_client');
CREATE TABLE Trips (
status mood,
request_at date DEFAULT '2000-01-01',
city_id int4,
driver_id int4 NOT NULL,
client_id int4 NOT NULL,
id int4 NOT NULL,
PRIMARY KEY (id)
)
CREATE TYPE mo AS ENUM ('Yes','No');
CREATE TYPE m AS ENUM ('client', 'driver', 'partner');
CREATE TABLE Uesrs (
banned mo,
role m,
user_id int4 NOT NULL,
PRIMARY KEY (user_id)
) navicat建表
开始这样内连接 trips INNER JOIN users ON (client_id=user_id AND users.banned='No') OR (driver_id=user_id AND users.banned='No')，这样的结果重复了很多，不对，所以应该利用两次连接满足一个条件基础上再去匹配另一个条件。
SELECT request_at, round(
CAST(sum(CASE WHEN status <> 'completed' THEN 1 ELSE 0 END) AS numeric) /
CAST(count(1) as numeric ), 2) AS "Cancellation Rate"
FROM trips
INNER JOIN users B ON client_id=B.user_id AND B.banned='No'
INNER JOIN users C on client_id=C.user_id AND C.banned='No'
WHERE request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY request_at
由于if 条件只能在存储过程中使用不能直接在SQL中使用，所以使用case when
case when (条件..) then 满足条件的值 else 不满足条件的值 end。....<>表示不等于符号
注意：case when ... 出来的数据也是一个字段，也会作为一列结果显示出来。case when 不允许嵌套调用聚合函数。
round() 函数保留小数的方式：CAST语法：cast(字段 as 需要转换的类型)
1、如果是利用除法/计算之后的结果则是直接去掉小数点，用0填充，如5/2保留2位 2.00，这不是我们想要得结果，这时可以利用cast强制装换类型，round(cast(5 as numeric) / cast(2 as numeric), 2)这样结果为2.50。
2、如果是直接数字那就是我们想要的结果，跟平时的四舍五入差不多，如：round(5.365, 2) 结果为 5.37
五、floor函数，员工薪水中位数【难度困难】
请编写SQL查询来查找每个公司的薪水中位数。
中位数：取排序之后中间的数，奇数个直接就是中间的数，偶数个取中间两个数除以2，这里取中间两条数据。
FLOOR函数：向下取整，即取不大于x的最大整数，不同于四舍五入，floor(2.65)为2，floor(-2.65)为-3。
思路：开窗函数对company进行分组按salary排序，刚好company分组之后的排序为1,2,3...，对company分组后进行统计分组的个数，利用分组的个数进行计算就可以得到序号1,2,3....之间的数据即为中位数。
SELECT id, company, salary FROM (
SELECT id, company, salary,
"row_number"() OVER (PARTITION by company ORDER BY salary) as rn,
count(1) OVER (PARTITION by company) as cnt
FROM employee
) A
WHERE rn in (floor((cnt + 1) / 2), floor((cnt + 2) / 2))
六、至少有5名直接下属的经理【难度中等】、员工奖金
选出所有 bonus < 1000 的员工的 name 及其 bonus。
SELECT emp.name, bonus.bonu FROM emp LEFT JOIN bonus ON bonus.empId=emp.empid WHERE bonus.bonu<1000 or bonus.bonu is NULL
这里左连接（以左为基准，右边没有的显示为NULL）右边没有匹配到的不会显示出来的，所以最后一个条件bonus.bonu is NULL很关键。
开始没有理解这道题，注意到经理也是员工，所以经理自己的经理有可能为null，最高领导
select c.name from
(
select a.id, a.name
from Employee a
left join Employee b
on a.id = b.managerId
group by a.id,a.name --用id，name分组防止同名情况，也可以直接按照name分组
having count(*) >= 5
) c
count(*) 这里是统计所有字段对应列的长度大于等于5的，ManagerId也包含在里面，刚好达到效果。（刚开始想的按ManagerId分组，也行但是没有这个方法优）。
七、统计各专业学生人数【难度中等】
一所大学有 2 个数据表，分别是 student 和 department ，这两个表保存着每个专业的学生数据和院系数据。
写一个查询语句，查询 department 表中每个专业的学生人数 （即使没有学生的专业也需列出）。
将你的查询结果按照学生人数降序排列。 如果有两个或两个以上专业有相同的学生数目，将这些部门按照部门名字的字典序从小到大排列。
一个是表连接:使用右连接，可以获的没有学生的专业
一个是分组，对department.dept_name进行分组后求和
一个是排序，先对学生人数降序排序，在对department.dept_name升序排序
SELECT department.dept_name, COUNT(student_id) student_number
FROM student right JOIN department ON student.dept_id=department.dept_id
GROUP BY department.dept_name
ORDER BY student_number DESC, department.dept_name ASC
注意易错点：当select 字段, 聚合函数 from ...... 这种语句一般后面会跟group by分组语句，其他情况要考虑字段和聚合函数怎么显示的问题，他们显示的是否等价。
如此题SELECT c.dept_name, count(c.dept_name)或者count(*) FROM (SELECT * FROM student right join department ON student.dept_id=department.dept_id) c
报错，count函数这里不明确，显示字段接着显示个数字？？矛盾
SELECT student_name, count(*) FROM student也是一样报错
八、换座位,MOD(id, 2)=1,求id序号为奇数的【难度中等】
小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。其中纵列的 id 是连续递增的，小美想改变相邻俩学生的座位。你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
思路就是当id为奇数时选择下一行，为偶数时选择上一行，当最后一个数是奇数的时候不执行赋值next，而赋值student。这个是基于表第一条数据的id为奇数的时候。如果为偶数则相反。
SELECT id, case when mod(id, 2)=1 AND next is not NULL THEN next
when mod(id, 2)=0 THEN previous
when next is NULL THEN student
END as student
FROM
(SELECT id, student, lag(student) over() as previous, lead(student) over() as next FROM seat) a
九、买下所有产品的客户【难度中等】
product_key 是 Customer 表的外键。product_key 是Product表的主键
1、先对顾客买的产品进行去重，
SELECT DISTINCT customer_id, product_key FROM customer
2、然后再分组求和即可得出顾客买的产品的个数
select DISTINCT customer_id, count(product_key) over (partition by customer_id) cnt from 1 步骤的表
3、最后再判断顾客买的产品的个数是否和产品表的产品总数相等，完整语句
select customer_id from
(select DISTINCT customer_id, count(product_key) over (partition by customer_id) cnt from
(SELECT DISTINCT customer_id, product_key FROM customer)A ) t
where t.cnt = (select count(*) from product)4、这里的count(product_key) over (partition by customer_id)是分组求和，区别于group by。用处多
十、平均工资：部门与公司比较【难度困难】
把两个表连接之后分组按照月份；月份和部门进行分组并求amount的平均值，然后用case when进行比较。开始想在case when之后用一个聚合函数求平均值，最好不要这样，这题体现了很方便。
嵌套比较多的语句如果如要分组请慎重使用group by，有可能尽量使用这里over(....)语句分组
SELECT DISTINCT pay_month,
department_id,
case when avg_month=avg_dept then 'same'
when avg_month<avg_dept then 'higher'
else 'lower' end as comparison
FROM
(
SELECT
to_char(pay_date, 'YYYY-MM') AS pay_month,
department_id,
avg(amount) OVER(PARTITION by pay_date) AS avg_month,
avg(amount) OVER(PARTITION by pay_date, department_id) AS avg_dept
from
salary as t1 left JOIN employee as t2
on t1.employee_id=t2.employee_id
) tt
ORDER BY tt.pay_month十一、每日新用户统计【难度中等】
该表没有主键，它可能有重复的行。activity 列是 ENUM 类型，编写一个 SQL 查询，以查询从今天起最多 90 天内，每个日期该日期首次登录的用户数。假设今天是 2019-06-30.
1、首先找出每个用户首次登陆的日期
2、在再上表的基础上筛选首次登陆日期大于2019-04-01的用户
3、再在首次登陆日期对用户计数SELECT activity_date, count(*) as user_count FROM (
SELECT user_id, min(activity_date) AS activity_date
FROM
traffic where activity='login' GROUP BY user_id
) A
WHERE activity_date>'2019-04-01' GROUP BY activity_date
思路很重要。
十二、每位学生的最高成绩【难度中等】
(student_id, course_id) 是该表的主键。编写一个 SQL 查询，查询每位学生获得的最高成绩和它所对应的科目，若科目成绩并列，取 course_id 最小的一门。查询结果需按 student_id 增序进行排序。
SELECT 字段1,聚合函数... 这种模式的基本上必须要使用group by分组，不然语法错误
SELECT student_id, course_id, grade FROM
(
SELECT student_id, course_id, grade,
"row_number"() OVER(PARTITION by student_id ORDER BY grade DESC, course_id ASC) as rn
FROM enrollments
) a
WHERE a.rn=1
十三、重新格式化部门表【难度中等】
(id, month) 是表的联合主键。这个表格有关于每个部门每月收入的信息。月份（month）可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]
如果是group by分组，case when 条件中涉及到的字段也会受到group by分组影响(必须出现在GROUP BY子句中或者在聚合函数中使用)
SELECT id,
min(case when month='Jan' THEN revenue end) as Jan_Renvenue,
min(case when month='Feb' THEN revenue end) as Feb_Renvenue,
min(case when month='Mar' THEN revenue end) as Mar_Renvenue,
min(case when month='Apr' THEN revenue end) as Apr_Renvenue,
min(case when month='May' THEN revenue end) as May_Renvenue,
min(case when month='Jun' THEN revenue end) as Jun_Renvenue,
min(case when month='Jul' THEN revenue end) as Jul_Renvenue,
min(case when month='Aug' THEN revenue end) as Aug_Renvenue,
min(case when month='Sep' THEN revenue end) as Sep_Renvenue,
min(case when month='Oct' THEN revenue end) as Oct_Renvenue,
min(case when month='Nov' THEN revenue end) as Nov_Renvenue,
min(case when month='Dec' THEN revenue end) as Dec_Renvenue
FROM department GROUP BY id ORDER BY id经典列转行解法，此题id，month是主键，所以一个id号不可能出现对应两个相同月份工资，所以聚合函数min可以随意，max、sum都行，因为只有一个值。
十四、用户购买平台【难度困难】
这张表记录了用户在一个在线购物网站的支出历史，该在线购物平台同时拥有桌面端（'desktop'）和手机端（'mobile'）的应用程序。这张表的主键是 (user_id, spend_date, platform)。平台列 platform 是一种 ENUM ，类型为（'desktop', 'mobile'）。
在2019-07-01, 用户1 同时 使用桌面端和手机端购买, 用户2 仅 使用了手机端购买，而用户3 仅 使用了桌面端购买。
在2019-07-02, 用户2 仅 使用了手机端购买, 用户3 仅 使用了桌面端购买，且没有用户 同时 使用桌面端和手机端购买。
这张表的主键是 (user_id, spend_date, platform)。这三个字段看作一个整体不能重复
意味着同一个用户同一天不可能有两次都是同一个platform，所以：以user_id和spend_date分组，如果分组长度等于2(不可能大于2)，那么就both，如果等于1，看platform是哪个就使用了哪个。再和原表进行连接找到platform，
注意：case when有个误区：case when 条件 then platform when 条件 then 'both'，会报错，如果platform为枚举类型且'both'为枚举类型的选项之一就可以，例如这里枚举为mobile和desktop不包括both，所以不行
SELECT spend_date,
platform,
sum(total_amount) total_amount,
count(*) total_users
FROM
(SELECT DISTINCT t1.user_id,
t1.spend_date,
case when count_p=1 AND t2.platform='mobile' then 'mobile'
when count_p=1 AND t2.platform='desktop' then 'desktop'
when count_p=2 then 'both' end as platform,
total_amount
FROM
(SELECT user_id,
spend_date,
count(*) as count_p,
sum(amount) as total_amount
FROM
spending GROUP BY user_id, spend_date
) t1
INNER JOIN spending t2 on t1.user_id=t2.user_id AND t1.spend_date=t2.spend_date
)a
GROUP BY spend_date, platform
没有both这个数，但是统计需要展示为0的没有考虑到，点到为止。
十五、报告的记录 【难度中等】
actions这张表没有主键，并有可能存在重复的行。
action 列的类型是 ENUM，可能的值为 ('view', 'like', 'reaction', 'comment', 'report', 'share')。
extra 列拥有一些可选信息，例如：报告理由（a reason for report）或反应类型（a type of reaction）等。
removals这张表的主键是 post_id。
这张表的每一行表示一个被移除的帖子，原因可能是由于被举报或被管理员审查。
2019-07-04 的垃圾广告移除率是 50%，因为有两张帖子被报告为垃圾广告，但只有一个得到移除。
2019-07-02 的垃圾广告移除率是 100%，因为有一张帖子被举报为垃圾广告并得到移除。
其余几天没有收到垃圾广告的举报，因此平均值为：(50 + 100) / 2 = 75%
注意，输出仅需要一个平均值即可，我们并不关注移除操作的日期。
1、找出所有的垃圾邮件，和removals连接找到post_id的垃圾邮件，用左连接，不能右连接，左连接才方便找出垃圾邮件的总数。连接后removals中的post_id可能为空，但是这是正常结果（表示这个post_id对应的没有被删除）。事实上删除的post_id都为1，因为post_id是主键。
2、按时间分组，分组后总个数就是总垃圾邮件数，用count(actions中post_id计算)
3、count(post_r) 和 count(post_a) 分组之后分别求某个字段的个数(容易想成或通常容易当做一行数据的个数)，count(字段)就是防止统计有字段值为NULL情况。刚好达到效果 2/1 和 1/1，2/1结果为0了，所以用cast转换一下。SELECT round(avg(percent), 2) * 100 AS average_daily_percent FROM (
SELECT round(cast(count(post_r) AS numeric)/cast(count(post_a) AS numeric), 2) percent
FROM
(
SELECT distinct actions.post_id as post_a,
actions.action_date,
removals.post_id as post_r
FROM actions
LEFT JOIN removals ON actions.post_id = removals.post_id
WHERE extra = 'spam'
) t GROUP BY action_date
) tt
十六、文章浏览 【难度中等】
此表无主键，因此可能会存在重复行。
此表的每一行都表示某人在某天浏览了某位作者的某篇文章。
请注意，同一人的 author_id 和 viewer_id 是相同的。
1、按照view_date和viewer_id分组，分组长度大于等于2且article_id不同。
SELECT viewer_id as id FROM
(
SELECT viewer_id, view_date, count(DISTINCT article_id) as count_article
FROM views GROUP BY viewer_id, view_date
) t
WHERE count_article>=2法2
SELECT viewer_id as id FROM views GROUP BY viewer_id, view_date HAVING count(DISTINCT article_id)>=2
不用group by分组用over分组时：over分组注意：
SELECT *, count(article_id) OVER (PARTITION by viewer_id, view_date) FROM views...........count(article_id)或者用其他聚合函数代替，如果没有这个函数是不行的。看语法：SELECT *, OVER (PARTITION by viewer_id, view_date) FROM...over()也是作为一列显示的，这里显然语法错误。
十七、查询活跃业务【难度中等】
'reviews'、 'ads' 和 'page views' 的总平均发生次数分别是 (7+3)/2=5, (11+7+6)/3=8，(3+12)/2=7.5。
id 为 1 的业务有 7 个 'reviews' 事件（大于 5）
和 11 个 'ads' 事件（大于 8），所以它是活跃业务
SELECT business_id FROM
(
SELECT event_type, round(avg(occurences), 1) as avg_type
FROM events GROUP BY event_type
) t
left join events on t.event_type=events.event_type
WHERE events.occurences>t.avg_type
GROUP BY business_id
HAVING count(events.business_id)>=2聚合函数不允许出现在WHERE中，不能WHERE events.occurences>t.avg_type and count(events.business_id)>=2
也不能WHERE events.occurences>t.avg_type HAVING count(events.business_id)>=2
having时在分组聚合计算后，对结果再一次进行过滤，having过滤的是分组数据，一般搭配group by使用这里分组求平均数也可以：AVG(occurences) OVER(PARTITION by event_type) avg_ct，在原有表基础之上再增加一列平均数，然后删选除大于平均数的即可，更简单一些
SELECT business_id
FROM
(
SELECT business_id,event_type,occurences,
AVG(occurences) OVER(PARTITION by event_type) avg_ct
FROM events
) t
WHERE occurences > avg_ct
GROUP BY business_id
HAVING COUNT(DISTINCT event_type) >= 2
十八、即时食物配送 【难度中等】
delivery_id 是表的主键。
该表保存着顾客的食物配送信息，顾客在某个日期下了订单，并指定了一个期望的配送日期（和下单日期相同或者在那之后）
1、最暴力的方法时直接求出总数(t1表)和首次订单数(t1表)，
SELECT cn1/cn FROM t1, t2，用其他方法做，只用一张表
SELECT round(cast(sum(case when order_date=customer_pref_delivery_date
then 1 else 0 end) as numeric)/
cast(count(delivery_id) as numeric)*100, 2)
as immediate_percentage
FROM delivery
一般有点暴力做法：按照顾客customer_id分组，分组之后最小的order_date
和对应的customer_pref_delivery_date比较，如果相等则是首次订单，否则是计划订单。
SELECT round(cast(sum(case when t1.order_date=t2.customer_pref_delivery_date
then 1 else 0 end) as numeric)/
cast(count(delivery_id) as numeric)*100, 2)
as immediate_percentage
FROM
(
SELECT customer_id,
min(order_date) as order_date
FROM delivery
GROUP BY customer_id
) t1
left join delivery t2
on t1.customer_id=t2.customer_id
AND t1.order_date=t2.order_date
不用左连接，直接把分组求出的min值当做一个列放到原表中就可以和其他列做比较了。这样更简单。这道题有另外一个更方便的方法：row_number()分组之后直接序列号为1的即是总订单数
(刚好当做原表的一列)，
然后直接再和customer_pref_delivery_date比较就可以得出首次订单了。
SELECT
round(cast(sum(case when t.order_date=customer_pref_delivery_date
then 1 else 0 end) as numeric)/
cast(count(t.delivery_id) as numeric)*100, 2) immediate_percentage
FROM
(
SELECT *,
row_number()over(PARTITION BY customer_id ORDER BY order_date) as rn
FROM delivery
) t
WHERE t.rn=1总结，凡是像这种类型的：求最小最大总和平均值然后又和原表的某一列相比较的类型。以前的思维总是把这些最小最大总和平均数求出来然后又和原表进行连接后再进行判断，因为基本都是group by分组之后求的，所以有的字段不能显示。所以才会再一次连接。
更简单的方法就是现在这种，分组之后求出所需的值然后当做一列放到原表中。用到窗口函数。
十九、最后一个能进入电梯的人【难度中等】
为了简化，Queue 表按 turn 列由小到大排序。
上例中 George Washington(id 5), John Adams(id 3) 和 Thomas Jefferson(id 6) 将可以进入电梯,因为他们的体重和为 250 + 350 + 400 = 1000。
Thomas Jefferson(id 6) 是最后一个体重合适并进入电梯的人
select *, sum(weight) over(order by turn) from queue;
select *, sum(weight) over() from queue
官网没找到over()函数比价完整的解释，这两语句的差别就是前者是
求当前行和前面的所有行的总和；刚好满足此题的需求
后者是表的所有行求和即整体求和。
select t.person_name from
(
select *, sum(weight) over(order by turn)as total_weight
from queue
) t
where t.total_weight <= 1000
order by turn
desc limit 1PARTITION by 和 ORDER BY一起用的时候是先分组，然后再根据分好组的组进行order by 纵向分组 。
二十、查询结果的质量和占比【难度简单】
此表没有主键，并可能有重复的行。“位置”（position）列的值为 1 到 500 。
“评分”（rating）列的值为 1 到 5 。评分小于 3 的查询被定义为质量很差的查询。
法一：一个表直接算
SELECT query_name,
round(sum(round(cast(rating as numeric) /
cast(position as numeric), 2)) / count(*), 2) as quality,
round(cast(sum(case when rating < 3 then 1 else 0 END) as numeric) /
count(*) * 100, 2) as poor_query_percentage
from queries
GROUP BY query_name
法二：先增加两列，求rate和评分少于3的个数(小于3标记为1否则标记为0)
最后再分组求值。
SELECT query_name,
round(avg(rate), 2) quality,
round(cast(sum(poor_query) as numeric) /
count(*) * 100, 2) poor_query_percentage
FROM
(
SELECT
query_name,
round(cast(rating as numeric)/cast(position as numeric), 2) rate,
case when rating < 3 then 1 else 0 end poor_query
FROM queries
)t
GROUP BY query_name二十一、查询球队积分【难度中等】
teams表：此表的主键是 team_id，表中的每一行都代表一支独立足球队
matches表：此表的主键是 match_id，表中的每一行都代表一场已结束的比赛，
比赛的主客队分别由它们自己的 id 表示，他们的进球由 host_goals 和 guest_goals 分别表示。
积分规则如下：
赢一场得三分；
平一场得一分；
输一场不得分。
写出一条SQL语句以查询每个队的 team_id，team_name 和 num_points。
结果根据 num_points 降序排序，如果有两队积分相同，那么这两队按 team_id 升序排序。
方法一思路：以host_team分组，再比较比分大小就可以得出积分，但是当比分相同
时参加比赛的两个队都要各自加一分，所以这里用union all组合两张表
SELECT team_id, team_name,
case when score is NULL then 0
else score end as num_points
FROM
teams left join
(
SELECT host_team,
sum(case when host_goals > guest_goals then 3
when host_goals = guest_goals then 1
else 0 end) as score
from
(
SELECT host_team, host_goals, guest_goals
FROM matches
union all
SELECT guest_team, guest_goals, host_goals
FROM matches
)t GROUP BY host_team
)t1
on teams.team_id = t1.host_team
ORDER BY num_points DESC, teams.team_id ASC
方法二思路：首先利用CASE WHEN 计算每个host_team和guest_team的分数，
再用union all将host_team与guest_team的分数合并在一起
SELECT team_id, team_name,
case when t1.score is NULL then 0
else t1.score end num_points
from teams left join
(
SELECT host_team, sum(score) as score
from
(
SELECT host_team,
CASE WHEN host_goals > guest_goals THEN 3
WHEN host_goals = guest_goals THEN 1
else 0 END score
FROM matches
UNION ALL
SELECT guest_team,
CASE WHEN host_goals < guest_goals THEN 3
WHEN host_goals = guest_goals THEN 1
else 0 END score
FROM matches
) t
GROUP BY host_team
)t1
on teams.team_id = t1.host_team
ORDER BY num_points DESC, teams.team_id ASC小细节：这里ORDER BY teams.num_points DESC会报错，因为原表teams中不存在num_points，num_points是新生成的字段。
union
表链接后会利用字段的顺序进行排序，以此筛选掉重复的数据行，最后再返回结果
因此，当数据量很大时效率很低
union all
相比 union，union all 在表链接时不会删除重复的数据行，直接返回表联合后的结果
因此，union all 执行效率要高很多，在不需要去重和排序时，更推荐使用 union all
二十二、平均售价【难度中等】
(product_id，start_date，end_date) 是 Prices 表的主键。
Prices 表的每一行表示的是某个产品在一段时期内的价格。
每个产品的对应时间段是不会重叠的，这也意味着同一个产品的价格时段不会出现交叉
UnitsSold 表没有主键，它可能包含重复项。
UnitsSold 表的每一行表示的是每种产品的出售日期，单位和产品 id。
直接根据product_id连接两个表，连接的另一个条件purchase_date在开始和结束日期之内
然后按照product_id分组，就可以price*units就计算出结果了。
SELECT t1.product_id,
round(cast(sum(price * units) as numeric) /
cast(sum(units) as numeric), 2) as average_price
FROM
prices t1 left join unitsold t2
on t1.product_id = t2.product_id
WHERE t2.purchase_date BETWEEN t1.start_date and t1.end_date
GROUP BY t1.product_id
ORDER BY t1.product_id二十三、页面推荐【难度中等】
Friendship table:
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 2 | 3 |
| 2 | 4 |
| 2 | 5 |
| 6 | 1 |
+----------+----------+
Likes table:
+---------+---------+
| user_id | page_id |
+---------+---------+
| 1 | 88 |
| 2 | 23 |
| 3 | 24 |
| 4 | 56 |
| 5 | 11 |
| 6 | 33 |
| 2 | 77 |
| 3 | 77 |
| 6 | 88 |
+---------+---------+
Result table:
+------------------+
| recommended_page |
+------------------+
| 23 |
| 24 |
| 56 |
| 33 |
| 77 |
+------------------+
用户1 同 用户2, 3, 4, 6 是朋友关系。
推荐页面为： 页面23 来自于 用户2, 页面24 来自于 用户3,
页面56 来自于 用户3 以及 页面33 来自于 用户6。
页面77 同时被 用户2 和 用户3 推荐。
页面88 没有被推荐，因为 用户1 已经喜欢了它
6喜欢88，但是自己已经喜欢88了，不显示
这里不用union all组合两张表再求出用户1的好友，直接case when 求
1、用连接求
SELECT DISTINCT page_id as recommended_page
FROM
(
SELECT case when user1_id = 1 then user2_id
when user2_id = 1 then user1_id
end as friends
FROM friendship
) t
inner join likes
on friends = user_id
WHERE page_id not in (SELECT page_id FROM likes WHERE user_id = 1)
2、用in和not in求
SELECT DISTINCT page_id as recommended_page
FROM likes
WHERE user_id in (SELECT case when user1_id = 1 then user2_id
when user2_id = 1 then user1_id
else 0 end
FROM friendship
)
and page_id not in (SELECT page_id FROM likes WHERE user_id = 1)
二十四、广告效果【难度简单】
(ad_id, user_id) 是该表的主键
该表的每一行包含一条广告的 ID(ad_id)，用户的 ID(user_id) 和用户对广告采取的行为 (action)
action 列是一个枚举类型 ('Clicked', 'Viewed', 'Ignored')
SELECT ad_id,
round(
cast(sum(case when action='Clicked' then 1 else 0 end)
as numeric) /
cast(count(ad_id) as numeric) * 100,
2) as ctr
FROM ads WHERE action != 'Ignored' GROUP BY ad_id
暂时没有考虑为ctr为0的情况mysql中 除0 会返回NULL，但是postgresql会报错，所以这里不考虑ctr为0。case when 不允许嵌套调用聚合函数。
二十五、向CEO汇报工作的人【难度中等】
employee_id 是这个表的主键。
这个表中每一行中，employee_id 表示职工的 ID，employee_name 表示职工的名字，manager_id 表示该职工汇报工作的直线经理。
这个公司 CEO 是 employee_id = 1 的人
用 SQL 查询出所有直接或间接向公司 CEO 汇报工作的职工的 employee_id 。
由于公司规模较小，经理之间的间接关系不超过 3 个经理。
可以以任何顺序返回的结果，不需要去重。
查找员工的领导，继续查找员工的领导，而领导也是作为一名员工，
从这样的方向去着手自连接的条件
本身表存在的一级manager_id，先查找一级对应的二级manager_id
接着在按照这样的思路，在找到二级manager_id对应的三级manager_id
SELECT A.employee_id
from employees A
LEFT JOIN employees B
on A.manager_id=B.employee_id
LEFT JOIN employees C
on B.manager_id=C.employee_id
WHERE C.manager_id=1 and A.employee_id!=1
二十六、不同国家的天气类型【难度简单】
select t2.country_name,
case when round(avg(weather_state), 2) <= 15 then 'cold'
when round(avg(weather_state), 2) >= 25 then 'hot'
else 'warm' end as weather_type
from weather t1
left join countries t2
on t1.country_id = t2.country_id
where t1.day between '2019-11-01' and '2019-11-31'
group by t1.country_id二十七、找到连续区间的开始和结束数字【难度中等】
第一种方法用lag()和lead()分别求出前后id，然后进行比较，数据求出来了，得到下面结果，后面暂时不知道怎么去掉NULL合并成想要的结果。重点看第二种方法
第二种方法：ROW_NUMBER() over() 就是对整个表进行排序。默认log_id是从小到大有序的
1、先整个表排序，排序结果当做一列显示，如果原表的数字是连续区间，
那么原表连续区间数字减对应连续区间的排序序号结果一定相等。
所以算出的结果直接进行分组，然后最小值就是开始数字，
最大值就是结束数字。此方案甚妙啊
SELECT min(log_id) as start_id,
max(log_id) as end_id
from
(
SELECT log_id,
log_id - ROW_NUMBER() OVER() AS label
FROM logs
) t
GROUP BY label二十八、求团队人数【难度中等】
根据team_id分组求和即可，用group by分组之后employee不好显示，所以
用另一种分组求。
SELECT employee_id,
count(team_id) over(PARTITION by team_id) as team_size
FROM employee
ORDER BY employee_id二十九、不同性别每日分数总计【难度中等】
注意：多这个order by day和没有这个效果完全不一样，一个是整个组求和
另一个是当前行和前面所有行求和。
SELECT gender, day,
sum(score_points) over(PARTITION by gender order by day)
as total
from scores
不能下面sql写法:
报错字段"scores.score_points"必须出现在GROUP BY子句中或者在聚合函数中使用
SELECT gender,
sum(score_points) over(ORDER BY day) as total
FROM scores GROUP BY gender
此题就比较典型了，和电梯那道题类似。
三十、餐馆营业额变化增长【难度中等】
1、用到一个时间差函数date_part()，可以计算两个日期之间相差的天数。
2、还用到一个PRECEDING可以叫滑动窗口，可以找出前N行（不足N行的有几行算几行），
后N行用FOLLOWING
思路：
1、按照日期分组分别求出每个日期的总amount，这样每一天就只有一条数据
2、继续就可以使用PRECEDING计算前6行的总数和平均数。
3、算出的是整个表所有日期的数据，最后使用date_part显示出满足7天为一个时间段的数据即可
SELECT * from
(
SELECT visited_on,
sum(day_amount)
over(ORDER BY visited_on rows between 6 PRECEDING and CURRENT row)
as amount,
round(avg(day_amount) over(ORDER BY visited_on
rows between 6 PRECEDING and CURRENT row),
2) as average_amount
from
(
SELECT visited_on,
sum(amount) as day_amount
from customer
GROUP BY visited_on
ORDER BY visited_on
)t
) tt
WHERE date_part('day', cast(visited_on as TIMESTAMP) -
cast((SELECT min(visited_on) from customer) as TIMESTAMP)) >= 6
------------------------------------------------------
SELECT min(visited_on) from customer这句一定要括号括起来
最外层的select* from () tt 是从已经算出所有日期的前6天的总值
和平均值中挑选出满足连续7天的日期。
这是核心，也可以用lag(visited_on, 6) over()做(放里面t表)
三十一、列出指定时间段内所有的下单产品【难度简单】
SELECT product_name, unit
from
(
SELECT product_id, sum(unit) as unit
FROM orders
WHERE order_date BETWEEN '2020-02-01' AND '2020-02-29'
GROUP BY product_id
HAVING sum(unit) >= 100
) t1
INNER JOIN products t2
on t1.product_id = t2.product_id三十二、报告系统状态的连续日期【难度困难】
1、跟前面有道题类似，日期 - 排序的序号，如果日期是连续的，那么
日期 - 排序的序号所得的值就是相同的。然后根据其分组就最大最小值即为起止日期
SELECT period_state,
min(fail_date) as start_date,
max(fail_date) as end_date
from
(
SELECT 'failed' period_state,
fail_date,
fail_date - cast(row_number() over(order by fail_date) as int) label_fail
from failed
WHERE fail_date BETWEEN '2019-01-01' and '2019-12-31'
union all
SELECT 'succeeded' period_state,
success_date,
success_date - cast(row_number() over(order by success_date) as int)
as label_success
from succeeded
WHERE success_date BETWEEN '2019-01-01' and '2019-12-31'
) t
GROUP BY period_state, label_fail
ORDER BY start_date
union all合并之后的表的字段名为第一个被合并的表的字段名
fail_date - row_number() over(order by fail_date) as label会报错，需要转换类型
'failed' period_state，是增加一个显示字段并且赋值failed三十三、每次访问的交易次数【难度困难】
1、transactions表中user_id和transaction_date一起分组就是transactions_count
2、当transactions_count为0时要去重，不为0时不用去重
SELECT case when transactions_count is NULL then 0
else transactions_count end as transactions_count,
case when transactions_count is NULL then count(DISTINCT t2.user_id)
else count(*) end as visit_count
FROM
(
SELECT user_id,
transaction_date,
count(*) as transactions_count
from transactions
GROUP BY user_id, transaction_date
) t1
right join visits t2
on t1.user_id=t2.user_id and t1.transaction_date=t2.visit_date
GROUP BY t1.transactions_count
三十四、每篇文章同一时刻最大在看人数(难度中等)
用户行为日志表tb_user_log
uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到
场景逻辑说明：artical_id-文章ID代表用户浏览的文章的ID，artical_id-文章ID为0表示用户在非文章内容页（比如App内的列表页、活动页等）。
问题：统计每篇文章同一时刻最大在看人数，如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，结果按最大人数降序。
输出示例：
示例数据的输出结果如下
| artical_id | max_uv |
| 9001 | 3 |
| 9002 | 2 |
解释：10点0分10秒时，有3个用户正在浏览文章9001；11点01分0秒时，有2个用户正在浏览文章9002。
(1)、将进入时间单独拎出来，同时记为1；
离开时间单独拎出来，同时记为-1，这样就聚合这两个表，
然后直接整个表按照时间排序。
意思就是：进去一个加1，离开一个减1。
(2)、然后利用窗口函数对计数（1或者-1）求累计和，
注意：同一时间有进有出的话先算进来的后算出去的，所以先按时间排序，再按计数排序！
(3)、然后再在每个分组里面去求最大的累积和就是最多同时在线的人数了！
SELECT artical_id, max(cnt) as max_uv
FROM
(
SELECT
artical_id,
sum(count_num) OVER(PARTITION by artical_id ORDER BY in_time, count_num DESC) as cnt
from
(
SELECT artical_id, 1 count_num, in_time FROM tb_user_log
UNION ALL
SELECT artical_id, -1 count_num, out_time FROM tb_user_log ORDER BY in_time
) t1
) t2
GROUP BY artical_id
ORDER BY max_uv DESC
sum(count_num) OVER(PARTITION by artical_id ORDER BY in_time, count_num DESC)
和电梯那道题类似，当前行和前面所有行进行聚合，事实上这样排出来能清楚看到所有时间点的登录登出在线人数。
4465
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
