주댕이의 공부 기록

# SUBSTR()문자열에서 특정 부분을 추출한다.주어진 문자열에서 시작 위치와 길이를 기준으로 부분 문자열을 반환한다. # 기본 구문SUBSTR(string, start_position, length)string: 부분 문자열을 추출할 원본 문자열start_position: 추출을 시작할 위치. 이 값은 1부터 시작하며, 음수를 사용할 경우 문자열의 끝에서부터 역으로 셈을 한다.length: 추출할 부분 문자열의 길이. 이 값을 지정하지 않으면 start_position부터 끝까지 추출된다. # 예시## 기본 예시SELECT SUBSTR('Hello World', 1, 5) AS result;결과: 'Hello'문자열 'Hello World'에서 첫 번째 문자부터 시작하여 5개의 문자를 추출한다.## ..

# 링크solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/ https://solvesql.com/collections/advent-of-sql-2024/ solvesql.com18일차 - 펭귄 날개와 몸무게의 상관 계수:https://solvesql.com/problems/correlation-penguin/ https://solvesql.com/problems/correlation-penguin/ solvesql.com  # 풀이SELECT species, ROUND( ( COUNT(*) * SUM(flipper_length_mm * body_mass_g) - SUM(flipper_lengt..

# 링크solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/ https://solvesql.com/collections/advent-of-sql-2024/ solvesql.com17일차 - 멀티 플랫폼 게임 찾기:https://solvesql.com/problems/multiplatform-games/ https://solvesql.com/problems/multiplatform-games/ solvesql.com  # 풀이-- 2012년 이후 출시된 게임WITH after_2012 AS (SELECT g.name AS game_name, p.name AS platform_nameFROM games gJOIN..

# 링크solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/ https://solvesql.com/collections/advent-of-sql-2024/ solvesql.com16일차 - 스테디셀러 작가 찾기: https://solvesql.com/problems/find-steadyseller-writers/ https://solvesql.com/problems/find-steadyseller-writers/ solvesql.com  # 풀이WITH fictions AS ( SELECT DISTINCT author, year FROM books WHERE genre = 'Fiction'),co..

# 링크solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/ https://solvesql.com/collections/advent-of-sql-2024/ solvesql.com14일차 - 폐쇄할 따릉이 정류소 찾기 2:https://solvesql.com/problems/find-unnecessary-station-2/ https://solvesql.com/problems/find-unnecessary-station-2/ solvesql.com # 풀이 WITH usage_counts AS ( SELECT station_id, strftime('%Y-%m', usage_at) AS month, ..

# OVER()OVER 함수는 윈도우 함수(Window Function)와 함께 사용되어, 집계 또는 분석 작업을 수행할 때 특정 범위(윈도우) 내에서 연산을 처리할 수 있도록 도와준다.OVER는 데이터의 그룹화 없이 각 행에 대해 계산을 수행하고, 지정된 범위(윈도우)에 대해서만 집계 함수가 적용되도록 한다.주로 PARTITION BY, ORDER BY, 그리고 ROWS BETWEEN과 함께 사용된다. # 기본 구문SELECT column1, column2, ..., aggregate_function(column) OVER (PARTITION BY column ORDER BY column ROWS BETWEEN ... AND ...)FROM table;  # 주요 구성 요소PARTITION..

# SQL에서 이동 평균을 구하는 방법OVER 윈도우 함수와 함께 AVG() 함수를 사용하는 방법이동 평균은 보통 시간 순서대로 데이터를 평균 내는 방식으로 계산된다. # 예시: 5일 이동 평균 구하기SELECT date, value, AVG(value) OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avgFROM your_table;위 쿼리는 각 날짜별로 5일 간의 value 평균을 계산한 결과를 반환한다.쿼리 설명:AVG(value): value 컬럼에 대해 평균을 계산한다.OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW):ORDER BY..

# 링크solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/ https://solvesql.com/collections/advent-of-sql-2024/ solvesql.com14일차 - 전력 소비량 이동 평균 구하기: https://solvesql.com/problems/moving-average-of-power-consumption/ https://solvesql.com/problems/moving-average-of-power-consumption/ solvesql.com # 풀이SELECT DATETIME(measured_at, '+10 minutes') AS end_at, ROUND(AVG(zone..

# 링크solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/ https://solvesql.com/collections/advent-of-sql-2024/ solvesql.com13일차 - 게임 개발사의 주력 플랫폼 찾기: https://solvesql.com/problems/main-platform-of-game-developers/ https://solvesql.com/problems/main-platform-of-game-developers/ solvesql.com # 풀이WITH PlatformSales AS ( SELECT c.name AS developer, p.name AS platfo..

# 링크solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/ https://solvesql.com/collections/advent-of-sql-2024/ solvesql.com12일차 - 3년간 들어온 소장품 집계하기: https://solvesql.com/problems/summary-of-artworks-in-3-years/ https://solvesql.com/problems/summary-of-artworks-in-3-years/ solvesql.com # 풀이SELECT classification, COUNT(*) FILTER (WHERE strftime('%Y', acquisition_date) ..