[1日3.SQL]Leetcode-Mediums&Hards
.jpg)
[https://leetcode.com/problemset/database/]180. Consecutive Numbers
質問する
Write an SQL query to find all numbers that appear at least three times consecutively.
3回以上連続して現れる数字を探しています.
Return the result table in any order.
私の答え
正直解けないと思ってたけど
ずっと頭を働かせてnumpyのconcat方式で簡単に解けるようになったのですが、どうやって実現すればいいのでしょうか?悩みました.
したがって、次の行から最後の行を作成し、接続します(left join)
次の行から最後まで行を作成し、接続します.
単純な3つの値は同じ条件です.(where構文)
select distinct A.num as ConsecutiveNums -- A.id, A.num, next_1.id, next_1.num, next_2.id, next_2.num
from Logs A
left join (
select B.id, B.num
from Logs B
limit 1,100000 -- 2번째 행부터 갖고옴
) next_1
on A.id = next_1.id -1
left join (
select C.id, C.num
from Logs C
limit 2,100000 -- 3번째 행부터 갖고옴
) next_2
on A.id = next_2.id -2
where 1=1
and next_2.id is not null
and next_1.id is not null -- 사실 이건 필요없음
and A.num = next_1.num
and next_1.num = next_2.num解の差は多くないようですか.
SELECT T.Num as ConsecutiveNums
FROM
(SELECT DISTINCT A.Num
FROM Logs A
LEFT JOIN Logs B
on A.Id = B.Id-1
LEFT JOIN Logs C
on A.Id = C.Id-2
WHERE A.Num = B.Num
AND A.Num = C.Num) T直感的なので良く見えます.これはもっと使いやすいように見えます
mysqlのlagとlead windowの崩壊を知りたいです.
[https://mizykk.tistory.com/121]
SELECT distinct num ConsecutiveNums
FROM
(SELECT id, num,
lag(num) over (order by id) as before,
lead(num) over (order by id) as after
FROM logs) next_prev
WHERE num=before and before =afterWrite an SQL query to find employees who have the highest salary in each of the departments.
Return the result table in any order.
私の答え
悩んでみたら解けるのは難しくない…!
悩みの部分
最初に思いついたのはrankなのでrankで問題を解きたいです
各部門の順位をつけたいと思っていました.しかし
もし100の部門があったら?100個のrankを作成できません.
だからrankじゃないみたい
別の方法でgroupbyかと思いましたが、これではないようです.
... 結局
100部門あっても、最大値という基準値があれば大丈夫です.
各部門の最大値テーブルを作成します.
参考のためにサインします.
各部門の最大値に基づいてフィルタリングを行った.
select dpt.Department
, A.name as Employee
, (A.Salary) --
-- , max_per_dpt.MAX
from Employee A
left join (
select B.id, B.name as Department
from Department B
) dpt
on A.departmentId = dpt.id
left join (
select departmentId, max(salary) as MAX
from Employee
group by departmentId
order by salary desc,departmentId desc
) max_per_dpt
on A.departmentId = max_per_dpt.departmentId
where (A.Salary) >= max_per_dpt.MAXウィンドウがクラッシュ...!
パーティションがあったんですね.
with top_sal as (
select d.Name as Department
,e.name as Employee
,e.salary as Salary
,rank() over (partition by departmentid order by salary desc) top
from employee e, department d
where e. departmentid = d.id
)
select Department as "Department"
,Employee as "Employee"
,Salary as "Salary"
from top_sal
where top = 1
order by 1 descA company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department.
Write an SQL query to find the employees who are high earners in each of the departments.
Return the result table in any order.
私の答え
問題を見ると密集したrankを使うべきだと思いますが、
前の問題の他の人の答えのようにpartitionbyを書くような気がします.
# Write your MySQL query statement below
with top_sal as (
select d.Name as Department
,e.name as Employee
,e.salary as Salary
,dense_rank() over (partition by departmentid order by salary desc) top
from employee e, department d
where e. departmentid = d.id
)
select Department as "Department"
,Employee as "Employee"
,Salary as "Salary"
from top_sal
where top <= 3
order by 1 desc
```Reference
この問題について([1日3.SQL]Leetcode-Mediums&Hards), 我々は、より多くの情報をここで見つけました https://velog.io/@soonwoo2003/SQL-leetcode-Mediumsテキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol