데이터 분석 공부일기

https://leetcode.com/problems/find-users-with-valid-e-mails/description/?envType=study-plan-v2&envId=top-sql-50 유효한 이메일을 찾는 문제로, 유효한 이메일의 기준은 아래와 같다.The prefix name is a string that may contain letters (upper or lower case), digits, underscore '_', period '.', and/or dash '-'.이메일 아이디는 글자, 숫자, '_', '.', '-'를 포함할 수 있다.The prefix name must start with a letter.이메일은 반드시 알파벳으로 시작해야 한다.The domain is '@..

https://leetcode.com/problems/delete-duplicate-emails/description/?envType=study-plan-v2&envId=top-sql-50    Write a solution to delete all duplicate emails, keeping only one unique email with the smallest id.중복되는 이메일을 제거해라. 단, 가작 작은 수의 ID를 가진 이메일만 남겨놓고.* For SQL users, please note that you are supposed to write a DELETE statement and not a SELECT one.  [정답]DELETE p1FROM person p1, person p2WHER..

1251. Average Selling Price  product의 가격 테이블과 판매된 수량에 대한 테이블이 있는 것을 조인해서 '(판매가격*수량) / 판매수량'으로 평균 판매 가격을 구하는 문제였다. 단, 신경써야 할 부분이 2가지 였는데,1. 정답 쿼리는 단 한개도 판매되지 않은 상품까지도 결과에 조회가 되야 한다는 것 (IFNULL 사용)2. 같은 상품이라도 판매한 날짜에 따라 가격이 다르다는 것 (JOIN 조건 추가) [정답]SELECT product_id , IFNULL(ROUND(SUM(total) / SUM(units),2),0) AS average_price -- 만일 unitssold의 값이 없어서 null이라면 0을 반환FROM ( SELECT p.product_id --..

SUM(~) OVER (ORDER BY ~)엘레베이터에 차례(Turn)대로 탔을 때 1000kg가 넘지 않는 조건에서 누구까지 탈 수 있는지 구하는 문제였다. [정답]SELECT person_nameFROM (SELECT person_name, weight, turn, SUM(weight) over (order by turn) as totalFROM Queue)aWHERE total  SUM, AVG 뒤에 OVER(ORDER BY ~)를 붙이면 누적 합계, 누적 평균을 행 단위로 구할 수 있다.  AVG/SUM(~) OVER (ORDER BY ~ ROWS BETWEEN n PRECEDING AND CURRENT ROW)오늘 날짜 기준으로 지난 7일간의 매출 합계, 평균 구..

180. Consecutive Numbers lags 테이블에서 연속으로 3번 이상 언급된 숫자를 추출하는 문제였다.  [정답]-- LEAD를 사용한 풀이SELECT DISTINCT sub.Num AS ConsecutiveNumsFROM (SELECT Num, LEAD(Num, 1) OVER (ORDER BY Id) AS next, LEAD(Num, 2) OVER (ORDER BY Id) AS after_next FROM Logs) subWHERE sub.Num = sub.next AND sub.next = sub.after_next-- LAG를 이용한 풀이SELECT DISTINCT sub.Num AS ConsecutiveNumsFROM (SELECT Num, ..

GROUP BY > 입양시각 구하기(2)  ANIMAL_OUTS : 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블SEX_UPON_OUTCOME : 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부 정보 테이블  0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성하는 문제였다.그런데 그냥 group by를 해서 결과를 조회하면 0시부터 23시까지 나오지 않고, 입양이 발생한 시간만 조회되는 문제가 있어 0시부터 23시까지의 시간 테이블을 생성해야 하는 것이 관건이었다. 이 때 사용되는 것이 SQL에서 반복문처럼 사용할 수 있는 WITH RECURSIVE 함수/** [Example] 0시부터 24시까지 시간 컬럼 만들기 **/WITH recursiv..

1280. Students and Examinations 각 학생들의 시험과목별 응시횟수 조회하기 [내 답안]WITH exams AS ( -- 학생별 시험별 응시횟수SELECT e.student_id, s.student_name, e.subject_name, COUNT(*) AS attended_examsFROM examinations eJOIN students s ON e.student_id = s.student_idGROUP BY e.student_id, s.student_name, e.subject_nameORDER BY e.student_id)SELECT e1.student_id, e3.student_name, e1.subject_name, ..

INDEX인덱스를 설정하면 쿼리 실행 속도가 빨라진다. > 생산성 향상 클러스터형 인덱스영어 사전과 같은 형태로 데이터를 실제로 재정렬하여 저장PRIMARY KEY 컬럼이 있을 경우, 자동 생성 보조 인덱스데이터를 디스크에 재정렬하지 않고, 보조 인덱스를 설정하여 각 데이터의 위치만 빠르게 조회 인덱스는 키-필드만 갖고 있고, 나머지 세부 테이블 컬럼 정보는 가지고 있지 않음 INDEX 확인방법SHOW INDEX FROM 테이블명; 을 실행했을 때 key_name = PRIMARY KEY라면 클러스터형 인덱스 테이블 변경FOREIGN KEY도 INDEX가 생긴다! (보조 인덱스)테이블에 특정 컬럼에 duplicate 값이 나오지 않도록 제약조건을 추가하기-- ALTER TABLE 테이블이름 ADD [CO..

서브쿼리MySQL에서는 ‘FROM 테이블명’에 서브쿼리를 입력하게 되면 반드시 alias(이름)을 지정해주어야 한다./* rental 테이블에서 고객별 평균 대여 횟수보다 많은 대여를 한고객들의 이름( first_name , last_name )을 찾아주세요. */SELECT first_name, last_nameFROM customerWHERE customer_id IN ( -- JOIN쿼리 SELECT customer_id FROM rental GROUP BY customer_id HAVING COUNT(*) > ( SELECT AVG(rental_count) -- 전체 평균값(각 customer_id별 rental 횟수/customer_id 수) FROM (SELECT..

문자열 함수LENGTH(string) : 컬럼 값의 문자열 길이 반환UPPER(string): 컬럼 값을 대문자로 변환CONCAT(string1, string2, …): 두개 이상의 컬럼 값을 하나로 합침SUBSTRING(string, start, length): 컬럼 값에서 부분 문자열 추출 *length가 string 길이보다 길면 아무것도 반환하지 않음LOCATE(찾는 값, string): 찾는 값이 string에 몇번째에 위치해 있는지 확인 *찾는 문자가 없다면 0을 반환RIGHT(string, 숫자), LEFT(string, 숫자): 엑셀의 right, left와 동일한 기능REPLACE(string, 기존 값, 변경할 값) : string 내의 기존 값을 변경할 값으로 변경 날짜/시간 함수NO..