limhm's blog

DAY 10 / [solvesql] Advent of SQL 2024 (세션 유지 시간을 10분으로 재정의하기)

세션 유지 시간을 10분으로 재정의하기-- 세션을 종료하는 기준을 사용자가 10분 이상 행동하지 않을 때로 수정하여,-- 사용자 'a8Xu9GO6TB’의 세션을 재정의해 세션 ID를 계산하는 쿼리 작성WITH filtered AS ( SELECT event_timestamp_kst , TIMEDIFF(event_timestamp_kst, LAG(event_timestamp_kst, 1) OVER(ORDER BY event_timestamp_kst)) AS diff_prev , user_pseudo_id , event_name , ga_session_id FROM ga WHERE user_pseudo_id = 'a8Xu9GO6TB' ORDER BY even..

DAY 9 / [solvesql] Advent of SQL 2024 2문제 (친구 수 집계하기, 세 명이 서로 친구인 관계 찾기)

친구 수 집계하기WITH tmp AS (SELECT user_a_id AS user_id , user_b_id AS friend_idFROM edgesUNION ALLSELECT user_b_id AS user_id , user_a_id AS friend_idFROM edges)SELECT u.user_id , COUNT(DISTINCT t.friend_id) AS num_friendsFROM users AS uLEFT JOIN tmp AS t ON u.user_id = t.user_idGROUP BY u.user_idORDER BY num_friends DESC , user_id 작성 순서1. edges 테이블에서 1행의 데이터는 하나의 관계를 나타내지만, 이는 2명의 친구 정보를..

중앙값의 경우 DBMS에 내장되어 있는 함수가 있다면 쉽게 바로 구할 수 있다. 그러나 중앙값을 구하는 내장 함수가 없는 경우, 직접 계산하는 쿼리를 작성해야 한다. (BigQuery, Oracle 은 중앙값을 구하는 내장 함수 있음) 중앙값이란?• 자료를 크기순으로 나열했을 때 제일 가운데에 위치하는 값• 아래 또는 위로 세어봐도 같은 중앙 위치• 중앙값 = 중간값 = 중위수 = 50 백분위수 = 2 사분위수 중앙값 구하는 쿼리 작성하기STEP 1️⃣ 정렬하여 순서매기기SELECT math, ROW_NUMBER() OVER (ORDER BY math) AS rn, COUNT(*) OVER () AS total_rowsFROM a_class; 데이터가 홀수인 경우mathrntot..

전국 카페 주소 데이터 정제하기SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(address, ' ', 2), ' ', 1) AS sido , SUBSTRING_INDEX(SUBSTRING_INDEX(address, ' ', 2), ' ', -1) AS sigungu , COUNT(DISTINCT cafe_id) AS cntFROM cafesGROUP BY SUBSTRING_INDEX(SUBSTRING_INDEX(address, ' ', 2), ' ', 1) , SUBSTRING_INDEX(SUBSTRING_INDEX(address, ' ', 2), ' ', -1)ORDER BY cnt DESC 예를 들어 주소(address)에 이런 데이터가 있다고 하자...

COALESCE ( ) 정의- COALESCE는 '합치다' 라는 뜻을 가진 단어다.- SQL에서도 COALESCE() 함수는 입력받은 데이터를 하나로 합치는 기능을 한다.- 합치는 기준은 'NULL'이다. 왼쪽부터 NULL이 아닌 첫번째 값을 반환함으로써 합친다. 즉, 왼쪽부터 'NULL'인지 아닌지를 따져서 NULL을 제외하고 처음 나오는 특정 값을 반환해 버리는 것이다. 단, 만약 모두 NULL 이라면 그냥 NULL을 내보낸다. (Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.) 사용 예시1. COALESCE(value, ...) -- (1)SELECT COALESCE(NULL, 1); --..

1. 이미지명령어 설명docker pull 이미지이름도커허브에서 이미지 다운로드docker images로컬에 있는 이미지 목록 보기docker rmi 이미지ID이미지 삭제docker build -t 이미지이름 .Dockerfile로 이미지 생성 2. 컨테이너컨테이너 실행/종료명령어설명docker run -it 이미지이름 bash컨테이너 실행 + bash로 진입docker run -d 이미지이름백그라운드 실행 (-d = detached)docker start 컨테이너이름중지된 컨테이너 다시 실행docker stop 컨테이너이름컨테이너 중지docker restart 컨테이너이름컨테이너 재시작docker rm 컨테이너이름중지된 컨테이너 삭제 컨테이너 확인 및 진입명령어 설명docker ps현재 실행 중인..

스테디셀러 작가 찾기 -- 5년 이상 연속으로 베스트셀러 작품 목록에 이름을 올린 소설 작가와 연도 정보를 출력하는 쿼리WITH cal AS ( SELECT * , LAG(year, 4) OVER(PARTITION BY author ORDER BY year) AS prev4 FROM ( SELECT author , year , COUNT(*) AS cnt_books FROM books WHERE genre = 'Fiction' GROUP BY author, year ) AS tmp)SELECT author , MAX(year) AS "year" , MAX(year) - MIN..