.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방식으로 쉽게 풀 수 있겠는데 어떻게 구현하면 좋을까? 고민 했다.
그래서 다음 행부터 끝까지인 rows를 하나 만들어서 이어 붙이고 ( left join )
다음 다음 행부터 끝까진 rows를 하나 만들어서 이어 붙인 다음
단순히 이어진 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 윈도우 펑션을 알아봐야겠다.
[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 =after184. Department Highest Salary
Write 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로 풀어보려 했는데,
각 부서별 rank 를 만들려고 했었다. 그런데
부서가 100개라면? 100개의 rank를 만들 순 없었다.
그래서 rank 는 아닌 것 같고
다른 방법으로 group by 인가 했는데 이것도 아닌거 같고
... 결국
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 desc185. Department Top Three Salaries
A 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.
내 답안
문제를 보자마자 dense rank 를 써야함을 느꼈고,
이전 문제의 다른 사람 답안처럼 partition by를 써야함을 느꼈다.
# 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
```