east-wind 님의 블로그

WITH flags AS ( SELECT d.ID, d.EMAIL, MAX(s.CATEGORY = 'Front End') AS has_fe, MAX(s.NAME = 'Python') AS has_py, MAX(s.NAME = 'C#') AS has_csharp FROM DEVELOPERS d LEFT JOIN SKILLCODES s ON (s.CODE & d.SKILL_CODE) = s.CODE GROUP BY d.ID, d.EMAIL)# SELECT * FROM FLAGS;SELECT CASE WHEN has_fe = 1 AND has_py = 1 THEN 'A' WHEN has_csharp = 1 ..

SELECT DISTINCT d.ID as ID, d.EMAIL as EMAIL, d.FIRST_NAME as FIRST_NAME,d.LAST_NAME as LAST_NAMEFROM DEVELOPERS as d LEFT JOIN SKILLCODES as s ON s.CODE & d.SKILL_CODE = s.CODEWHERE s.CATEGORY = 'Front End'ORDER BY ID;# SELECT *# FROM DEVELOPERS as d LEFT JOIN SKILLCODES as s ON s.CODE & d.SKILL_CODE = s.CODE# ORDER BY ID; join 상태 모르겠으면 한번 출력해보자 DISTINCT는 SELECT 바로 뒤에만 붙는다.

#IFNULL로 결측치 채우기WITH F2 AS( SELECT ID, FISH_TYPE,IFNULL(LENGTH, 10) as LENGTH, TIME FROM FISH_INFO)SELECT COUNT(*) as FISH_COUNT, MAX(LENGTH) as MAX_LENGTH, FISH_TYPEFROM F2GROUP BY FISH_TYPEHAVING AVG(LENGTH) >= 33ORDER BY FISH_TYPE ASC; 결측치 채워야할 경우에는 WITH으로 채운 테이블 만들기IFNULL(COL, 채울값)

SELECT COUNT(*) AS FISH_COUNT, MONTH(TIME) as 'MONTH'FROM FISH_INFOGROUP BY MONTH(TIME)ORDER BY MONTH; SQL에서 대표적인 날짜/시간 타입DATE → 연/월/일 (예: 2025-09-12)TIME → 시/분/초 (예: 18:23:45)DATETIME / TIMESTAMP → 날짜+시간 (예: 2025-09-12 18:23:45)1. 날짜 전체 추출 / 변환DATE(expr)DATETIME이나 TIMESTAMP에서 날짜 부분(YYYY-MM-DD) 만 추출예: DATE('2025-09-12 18:23:45') → '2025-09-12'TIME(expr)DATETIME에서 시간 부분(HH:MM:SS) 만 추출예: TIME('202..

SELECT ID,CASEWHEN SIZE_OF_COLONY BETWEEN 0 AND 100 THEN 'LOW'WHEN SIZE_OF_COLONY BETWEEN 101 AND 1000 THEN 'MEDIUM'ELSE 'HIGH'END AS 'SIZE'FROM ECOLI_DATAORDER BY ID ASC; 1.CASE columnWHEN value THEN ~ELSE ~END AS~ 2.CASEWHEN column BETWEEN 0 AND 100 THEN ~ELSE ~END AS~

WITH RECURSIVE T AS( SELECT ID, PARENT_ID, 1 as depth FROM ECOLI_DATA as e WHERE PARENT_ID IS NULL UNION ALL SELECT e.ID, e.PARENT_ID, t.depth+1 as depth FROM T as t JOIN ECOLI_DATA as e ON t.ID = e.PARENT_ID)# SELECT * FROM T;SELECT COUNT(*) as 'COUNT', DEPTH as GENERATIONFROM (SELECT t.DEPTHFROM T as tWHERE NOT EXISTS (SELECT 1 FROM T as t2 WHERE t.ID=t2.PARENT_ID)) a..

SELECT a.IDFROM ECOLI_DATA as a LEFT JOIN ECOLI_DATA as b ON (a.PARENT_ID = b.ID)LEFT JOIN ECOLI_DATA as c ON (b.PARENT_ID = c.ID)WHERE b.ID IS NOT NULL AND c.ID IS NOT NULL AND c.PARENT_ID IS NULLORDER BY a.ID recursive 활용 - union all 전에 초기, 이후 ~WITH RECURSIVE ancestry AS ( SELECT id, parent_id, 1 AS depth FROM ecoli_data WHERE parent_id IS NULL ..

WITH NT AS ( SELECT ID, NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC) AS CHML FROM ECOLI_DATA)SELECT ID, CASE CHML WHEN 1 THEN 'CRITICAL' WHEN 2 THEN 'HIGH' WHEN 3 THEN 'MEDIUM' WHEN 4 THEN 'LOW' ELSE '?' END AS COLONY_NAMEFROM NTORDER BY ID ASC; select ntile (n) over (order by col) as ~select case col when ~ then ~ else ~ end as col..

NTILE(n) OVER (ORDER BY column) 전체 행(row)을 순서대로 정렬한 후 → n개 그룹으로 나눔각 행에 **그룹 번호(1 ~ n)**를 부여그룹 크기는 가능한 한 균등하게 분배됨(딱 안 나눠떨어지면 앞쪽 그룹이 한 개 더 많음)순위 기반 등분: 데이터를 사분위(4분위), 십분위(10분위) 등으로 나눌 때 WITH - CTE(Common Table Expression, 공통 테이블 식)WITH 임시테이블명 AS ( SELECT ...)SELECT ...FROM 임시테이블명; WITH 구문은 **임시로 이름 붙인 결과 집합(가상의 테이블)**을 만드는 문법마치 **서브쿼리(subquery)**를 FROM 안에 넣는 것과 비슷하지만,가독성이 좋고재사용이 가능하다는 차이

부모 형질을 전부 포함한다 >> and 연산 했을때 부모 형질과 같아야함#트리 구조인 느낌이면 join 이용해서 합치기#비트연산 &, |으로 가능select c.ID as ID, c.GENOTYPE as GENOTYPE, p.GENOTYPE as PARENT_GENOTYPEfrom ECOLI_DATA as c JOIN ECOLI_DATA as pon (c.PARENT_ID = p.ID)WHERE (c.GENOTYPE & p.GENOTYPE) = p.GENOTYPEORDER BY c.ID;