Mysql 공부 (Leetcode SQL50)

1 : 단순 조회
2 : 어느 컬럼이 NULL이거나 2가 아닌 경우의 튜플을 조회
3 :
두 가지 컬럼으로 where 조회.
두 컬럼에 복합 인덱스 없는 경우 성능 낮을 수 있어 단일 조건 조회 이후 UNION 하는 편이 나을 수 있다. UNIONALL 은 중복을 허용하니 주의

4 :
자기 글 본 사람 id 조회. 중복될 수 있으니 Distinct 로 필터할 수 있음, 정렬로 별칭을 대상으로 둘 수 있다.

SELECT DISTINCT author_id as id
FROM Views
WHERE author_id = viewer_id
ORDER BY id;

5 :
문자타입의 길이를 조건으로 조회 CHAR_LENGTH(STR)

SELECT tweet_id
FROM Tweets
WHERE char_length(content) > 15;

6 :
unique id 가 없는 애들은 null을 줘서 조인 테이블 출력. left, right 는 반드시 출력 보장 방향을 기준으로 한다.
현재 sql 문은 오른쪽에 Employee 가 있고 그렇기에 모든 Employee 가 출력되며 unique id 가 없다면 null 로 출력시킨다.

select eu.unique_id, e.name
from EmployeeUNI eu
right join Employees e
on eu.id = e.id;

FULL JOIN (전체 외부 조인, FULL OUTER JOIN)
• 설명: 두 테이블의 모든 데이터를 반환하며, 어느 한쪽에서 일치하는 데이터가 없으면 NULL로 채워집니다. 즉, 왼쪽과 오른쪽 테이블의 일치 여부와 상관없이 모든 데이터를 가져옵니다.

! MySQL에서는 직접적으로 FULL OUTER JOIN을 지원하지 않으며, 대신 LEFT JOIN과 RIGHT JOIN을 UNION으로 결합하여 사용할 수 있습니다.


CROSS JOIN (크로스 조인, 카티션 곱)
• 설명: 두 테이블의 모든 행을 조합하여 결과 집합을 만듭니다. 테이블 간 조건이 없기 때문에 두 테이블의 가능한 모든 조합을 반환합니다.
• 사용 예시:

SELECT a.name, b.salary
FROM Employees a
CROSS JOIN Salaries b;

7 : 단순 이너조인

8 : Null 비교는 is 로 해야합니다. = null 은 안됩니다.

select v.customer_id, count(*) as count_no_trans
from Visits v
left join Transactions t
on v.visit_id = t.visit_id
where t.visit_id is null
group by v.customer_id;

SQL 실제 처리 순서
1. FROM (테이블 및 조인 처리)
• 가장 먼저 실행되며, 데이터를 가져올 테이블이나 뷰를 선택합니다.
• JOIN 구문이 있는 경우, 이 단계에서 테이블 간의 조인도 처리됩니다.
• 데이터가 여러 테이블에 걸쳐 있을 경우, 이 절에서 조인 방식에 따라 데이터를 결합합니다.
2. WHERE (행 필터링)
• FROM 절로부터 가져온 데이터를 조건에 맞게 필터링합니다.
• 조건에 맞지 않는 행은 이 단계에서 제외됩니다.
• NULL 처리, 비교 연산자, 서브 쿼리 등이 이 절에서 작동합니다.
3. GROUP BY (그룹화)
• WHERE 절을 통해 필터링된 데이터를 특정 열이나 표현식에 따라 그룹화합니다.
• 주로 집계 함수(예: COUNT, SUM, AVG 등)를 사용하기 전에 데이터를 그룹별로 나누기 위해 사용됩니다.
4. HAVING (그룹 필터링)
• GROUP BY에 의해 그룹화된 데이터를 필터링합니다.
• WHERE와 비슷하지만, WHERE 절에서는 그룹화된 결과에 대한 필터링을 할 수 없기 때문에, HAVING 절에서 그룹화된 데이터를 조건에 따라 필터링할 수 있습니다.
• 집계 함수와 함께 사용하여 그룹화된 데이터에 대한 조건을 지정할 수 있습니다.
5. SELECT (열 선택 및 계산)
• 필터링된 데이터를 가져오고, 필요한 열을 선택하거나 표현식을 계산하여 최종 결과 집합을 생성합니다.
• DISTINCT를 사용하는 경우, 이 단계에서 중복이 제거됩니다.
6. ORDER BY (정렬)
• SELECT 절로 선택된 데이터를 지정된 열이나 표현식에 따라 정렬합니다.
• 기본적으로 오름차순(ASC), 내림차순(DESC)으로 정렬할 수 있습니다.
7. LIMIT (또는 OFFSET, FETCH) (결과 제한)
• 결과 집합에서 반환할 행의 개수를 제한합니다.
• 일부 데이터베이스에서는 LIMIT 대신 FETCH 또는 OFFSET을 사용할 수 있습니다.
• 예를 들어, 10개의 행만 가져오거나, 특정 위치부터 데이터를 가져오고 싶을 때 사용됩니다.

9 : 어제와 비교해 온도가 올란 경우 Id 를 반환하라. 같은 테이블을 별칭으로 두 개 만들어 서로 비교하며
DATEDIFF 를 통해 차이가 1일때 판단합니다.

select w1.id
from Weather w1, Weather w2
where w1.Temperature > w2.Temperature and datediff(w1.recordDate, w2.recordDate) = 1;
-- DATEDIFF(w1.recordDate, w2.recordDate) 같다면 1 반환 w1 - w2 인 셈. 
-- w1.recordDate - w2.recordDate = 1 는 하루 차이.

10 : 테이블 별칭으로 두 개 만들어 평균 구하기 round 로 소수점 반올림, avg 로 평균 구하기

select a1.machine_id, round(avg(a1.timestamp - a2. timestamp), 3) as processing_time
from Activity a1
join Activity a2
on a2.machine_id = a1.machine_id
where a1.machine_id = a2.machine_id and a1.process_id = a2.process_id and a1.activity_type = 'end' and a2.activity_type = 'start'
group by a1.machine_id;

11 :

select e.name, b.bonus
from Employee e
left join Bonus b
on e.empId = b.empId
where b.bonus is null or b.bonus < 1000;

12 : 삼단 조인에 카테시안곱의 형태, CROSS JOIN 한다고 막 펼쳐지는게 아니라 GROUP BY 로 여러 컬럼 잡아줘야 잘 펼쳐주네

select st.student_id, st.student_name, su.subject_name, COUNT(ex.student_id) AS attended_exams
from Students st
cross join Subjects su
left join Examinations ex -- 시험을 안친 애도 있지만 학생은 다 노출해야해서 LEFT JOIN
on st.student_id = ex.student_id and su.subject_name = ex.subject_name -- subject_name 도 걸어줘야한다. 안그러면 과목 구분없이 뭉탱이 취급해
group by st.student_id, su.subject_name
order by st.student_id, su.subject_name;

13 : 뭔 쿼리를 짜도 내껀 성능이 구려! 어째서!

-- select e1.name
-- from Employee e1
-- join Employee e2
-- on e1.id = e2.managerId
-- group by e1.id
-- having count(e1.id) >= 5;

select e1.name
from Employee e1
where e1.id in 
(select managerId from Employee e2
group by managerId
having count(id) >= 5)

14 : 집계함수에 조건을 넣어줄수도 있네 마치 삼항연산자같구나~ 으하하하!

# Write your MySQL query statement below
select s.user_id, round(avg(if(c.action='confirmed',1,0)), 2) as confirmation_rate
from Signups s
left join Confirmations c
on s.user_id = c.user_id
group by s.user_id

15 : 정렬이야

select * from Cinema c
where c.description != 'boring' and c.id % 2 != 0
order by c.rating desc;

16 : IFNULL 이랑 COALESCE 뭔 차이야. datetime의 dt between a and b 형식으로 사용할 수 있구나

SELECT p.product_id, IFNULL(ROUND(SUM(units*price)/SUM(units),2),0) AS average_price
FROM Prices p LEFT JOIN UnitsSold u
ON p.product_id = u.product_id AND
u.purchase_date BETWEEN start_date AND end_date
group by p.product_id

17 :
18 :
19 :
20 :
21 :