728x90
# SQL이란?
우선 약어를 쓰는 것 자체가 마음에 안 든다. 약어가 한두 개이어야지 처음 배우는 사람에게 SQL이 뭔지 어떻게 아냐는 말이다. 특정 분야나 조직에서 일하다 보면 효율성을 위해서 약어들을 사용하는데, 막상 시스템이 점차적으로 복잡해질수록 수많은 정리되지 않은 약어들 때문에 의미도 모르고 사용되는 약어들이 수두룩 빽빽이다. 그래서 프로그래밍을 다뤄본 브로들은 SQL이 뭔지 알겠지만 SQL을 처음 본 브로들을 위해 나의 모든 포스트는 간단한 약어라도 풀어서 보여주고 시작하려고 한다.
아무튼 본론으로 돌아와서 SQL이란 Structure Query Language를 의미한다. 약어만 풀어서 보기만 해도 대충 뭘 나타내는지 훨씬 잘 보인다. 언어인데 구조적 쿼리에 대한 것이라는게 딱 보인다. 이 말 그대로 우리가 구조적 쿼리를 사용하는 데 있어 표준화한 언어라고 생각하면 된다. 그렇다면 구조적 쿼리란 무엇인가? 아재들이 늘 줄여서 부르는 디비, 즉 데이터베이스(database)에 "요청"한다라는 의미가 쿼리이고, 구조화된 질문을 디비에 하는 언어가 바로 Structure Query Language, SQL인 셈이다. (최소한 이 정도로 설명을 해줘야지 "SQL은 관계형 데이터베이스에 정보를 저장하고 처리하기 위한 프로그래밍 언어입니다."라고만 하면 도대체 무슨 말인지 어째 알란 말인지 나 같아도 짜증이 올라올 것 같다.)
이 SQL은 이따끔씩 개발자들 사이에서 프로그래밍 언어가 아니라는 둥, 무시를 당하거나 SQL를 다루는 개발자들을 무시하기도 하는데, 솔직히 나는 SQL은 정말 효율적이면서도 아름다운 언어라고 생각한다. SQL은 생각보다 오래된 언어이자, 다른 언어와 대비해서 표준화를 이룬 대중 언어의 대표적인 예라고 생각한다. 1970년에 개발이 되었으며, 1986년에 표준화가 되었다. 그러고 나서 지금까지도 데이터베이스를 검색, 요청, 삭제, 추가 등 다양한 작업들을 효율적이고 정확하게 할 수 있도록 도와주는 정말 감사한 언어라고 생각한다. 특히 AI시대에 데이터 양이 많아지고 하나의 디비가 아니라 수많은 디비들을 연결해서 사용한다고 가정한다면, 더더욱 SQL이 있어서 다행이라고 생각이 든다.
# SQL을 알아둬야 하나요?
거두절미하고 SQL 알아둬야 한다. 솔직히 AI가 빠르게 대중화되고 모든 산업에 적용될수록 데이터기반의 현재 AI는 수많은 디비들을 가지고 작업을 수행해 나간다. 그 말인즉슨, AI가 수행되는 앞단에 여러 개의 층층이 쌓인 단계별 디비들이 연속적으로 연동되고 이러한 디비들을 관리하기 사람이 관리하기 위해서는 SQL을 통해서 살펴볼 수 있어야 한다. 그렇다 보니, 디비 관리자만 주로 사용하던 SQL이, 이제는 모든 개발자가 SQL을 다룰 줄 알고 디비를 볼 줄 알아야 하는 시대로 변하고 있다고 생각한다. 실제로도 잡마켓이 어렵다고 하지만 데이터 엔지니어 포지션은 계속 생겨나고 있고 심지어 소프트웨어 엔지니어임에도 데이터를 다루는 포지션들이 정말 많다는 걸 실제로 느끼고 있다. 그러면서 별개로 왜 자바가 아직까지도 실무에서 대중 언어인지 느끼고 있다.
물론 SQL 명령어를 모조리 외워둘 필요까지는 없다. 그리고 몇번 써 보다 보면 사실 사용하는 기능이 단순하기 때문에 쉽게 익힐 수 있다. 다만 Computer science (CS) 학부를 떠나온 지 좀 되었거나 컴퓨터 관련 학부를 나오지 않았다면 생소할 수 있기 때문에 사전에 익혀두는 것이 좋다. 그래야 면접에서도 "SQL"아나요?라고 했을 때 대답을 할 수 있을 것이다.
# Tesla 자율주행 오픈코드를 기반으로 한 SQL 예제
그냥 명령어만 띡 있으면 이해도 안 가고 재미가 없다고 생각해서 실제 예제를 통해서 살펴보면 명령어를 어떻게 사용해야 하는지에 대해서 쉽게 이해할 수 있지 않을까 생각했다. 내가 학부 때를 돌이켜보면 그냥 명령어만 딱 써놓고 그냥 외우라는 식으로 되어있는 글들을 보기가 싫었었다. 그리고 요즘에는 AI가 알려주는데 굳이 명령어를 쭉 리스트로 늘어놓을 필요는 없다고 본다.
따라서 마침 공개되어있는 자료들을 기반으로 SQL 예제를 만들어보았다. 테슬라 자율주행 관련해서 공개되어 있는 자료를 기반으로 디비 라벨링과 SQL을 어떻게 사용할 수 있는지를 살펴보았다. 관심 있는 브로들은 아래의 링크를 참고하면 된다.
(본 예제의 VIN과 주행, 충전, 고장 데이터는 모두 학습용 가상 데이터입니다.)
(1) Tesla 공식 Fleet Telemetry 필드 정의 (protobuf)
-- https://github.com/teslamotors/fleet-telemetry
-- https://developer.tesla.com/docs/fleet-api/fleet-telemetry/available-data
-- → VehicleSpeed, Odometer, Soc, BatteryLevel, PackVoltage, PackCurrent,
-- Gear, BrakePedal, EstBatteryRange, Location, SelfDrivingMilesSinceReset
(2) TeslaMate 오픈소스 로거의 PostgreSQL 스키마 (실사용 구조)
-- https://docs.teslamate.org / https://github.com/teslamate-org/teslamate
-- → cars, drives, positions, charges, charging_processes, addresses, geofences
(3) nuScenes 자율주행 데이터셋의 관계형 스키마 (라벨링/어노테이션)
-- https://github.com/nutonomy/nuscenes-devkit/blob/master/docs/schema_nuscenes.md
-- → category, instance, sample, sample_annotation, ego_pose (token PK + FK)# Database(DB)를 살펴보기
우선 디비를 이해하는 시간을 가져야한다. 디비 안에는 우리가 테이블 (tables)이라고 부르는 데이터를 저장해 두는 행과 열이 존재한다. 그냥 쉽게 생각해서 엑셀을 생각하면 된다. 그 테이블들이 한두 개면 그냥 직접 찾아서 특정 데이터를 살펴보면 그만이지만 테이블들이 너무 많다는 게 문제이다. 그래서 원하는 테이블 안의 데이터값을 찾아내기 위해서는 SQL을 사용한다. 마치 도서관을 쭉 둘러보면서 우리가 좋아하는 책 코너가 어디인지를 파악하는 셈이다.
내가 생각하는 디비를 검색해서 살펴보는 방법은 SQL에서 크게 네가지로 살펴볼 수 있다고 생각한다. 아래에 있는 코드블록에서 User tables, user constraints, DESC, DUAL 등을 이유와 예제를 살펴볼 수 있다.
■ USER_TABLES / USER_TAB_COLUMNS (데이터 딕셔너리 뷰)
-- [왜] 실무의 첫 작업은 쿼리 작성이 아니라 "테이블과 컬럼이 뭐가 있는지" 파악임.
-- 오라클은 스키마 정보를 딕셔너리 뷰로 제공하므로 SELECT만으로 구조 조회가 됨.
-- [어떻게] USER_* 는 내 소유 객체, ALL_* 는 접근 가능한 객체 전체를 조회함.
SELECT table_name FROM user_tables ORDER BY table_name;
SELECT column_name, data_type, data_length, nullable
FROM user_tab_columns
WHERE table_name = 'TELEMETRY_LOGS' -- 딕셔너리에는 객체명이 대문자로 저장됨
ORDER BY column_id;
■ USER_CONSTRAINTS / USER_INDEXES
-- [왜] 조인 키(PK/FK)와 인덱스 존재 여부를 모르면 쿼리 성능 판단이 불가능함.
-- [어떻게] constraint_type : P(기본키), R(외래키), U(유니크), C(체크)
SELECT constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name IN ('VEHICLES', 'TELEMETRY_LOGS');
SELECT index_name, table_name, uniqueness FROM user_indexes;
■ DESC (SQL*Plus / SQL Developer 클라이언트 명령)
-- [왜] 위 딕셔너리 조회의 한 줄 단축형. 단, SQL 문법이 아니라 클라이언트 명령이라
-- 자바(JDBC) 코드에서는 사용할 수 없음. 코드에서는 딕셔너리 뷰를 써야 함.
-- [어떻게] DESC TELEMETRY_LOGS
■ DUAL (오라클 전용 1행 더미 테이블)
-- [왜] 오라클의 SELECT는 FROM 절이 필수임. 테이블 없이 함수와 연산만 테스트하려면
-- 1행 1열짜리 더미 테이블 DUAL이 필요함.
SELECT SYSDATE, SUBSTR('5YJ3E1EA1TF000001', 1, 3) FROM dual;# 테이블(Tables) 만들기 (DROP / CREATE / ALTER / INDEX / SEQUENCE / VIEW)
테이블을 다루는데 있어서는 크게 6가지의 명령어를 사용할 수 있다.
점점 많아져서 되게 복잡해 보이고 길어 보이는데, 그 원인은 내가 테슬라 오픈코드를 가져다가 예시로 만들어서 그런 거니, 불필요한 내용은 배제하고 기능들만 살펴봐도 무방하다. 그리고 추후 브로들이 필요하다면 내가 정리해 둔 AI용 SQL 스크립트를 md 파일 형식으로 공유가 가능하니, 필요한 브로들은 댓글을 남겨주길 바란다.
■ DROP TABLE (재실행 대비 초기화)
-- [왜] 스크립트를 반복 실행하려면 기존 객체를 먼저 제거해야 함. FK로 참조되는
-- 부모 테이블은 CASCADE CONSTRAINTS 옵션이 있어야 지워짐.
-- [어떻게] 최초 실행 시 발생하는 ORA-00942(객체 없음)는 무시하면 됨.
DROP TABLE dtc_logs_backup CASCADE CONSTRAINTS;
DROP TABLE vehicle_last_state CASCADE CONSTRAINTS;
DROP TABLE dtc_logs CASCADE CONSTRAINTS;
DROP TABLE charging_processes CASCADE CONSTRAINTS;
DROP TABLE telemetry_logs CASCADE CONSTRAINTS;
DROP TABLE ns_sample_annotation CASCADE CONSTRAINTS;
DROP TABLE ns_instance CASCADE CONSTRAINTS;
DROP TABLE ns_category CASCADE CONSTRAINTS;
DROP TABLE vehicles CASCADE CONSTRAINTS;
DROP SEQUENCE seq_event_id;
■ CREATE TABLE + 제약조건 (PK / FK / NOT NULL / UNIQUE / CHECK / DEFAULT)
-- [왜] 제약조건은 잘못된 데이터가 아예 못 들어오게 DB 계층에서 막는 장치임.
-- 자바 단 검증은 우회 경로가 생길 수 있지만 제약조건은 모든 경로에서 강제됨.
-- [어떻게] 컬럼 레벨로 붙이거나, 테이블 레벨에서 CONSTRAINT 이름 유형으로 선언함.
-- (1) 차량 마스터 : TeslaMate의 cars 테이블 컬럼(model, name, efficiency)이 근거
CREATE TABLE vehicles (
car_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- 12c+ 자동 증가
vin VARCHAR2(17) NOT NULL UNIQUE, -- VIN 국제 표준 17자리
model VARCHAR2(30) NOT NULL,
name VARCHAR2(50),
efficiency NUMBER(6,4) -- kWh/km. 미상이면 NULL 허용
);
-- 11g 이하 : IDENTITY 미지원. CREATE SEQUENCE + seq.NEXTVAL 로 대체함.
-- (2) 주행 텔레메트리 : Tesla Fleet Telemetry 공식 필드명이 근거
CREATE TABLE telemetry_logs (
log_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
vin VARCHAR2(17) NOT NULL,
ts TIMESTAMP NOT NULL, -- 수신 시각
vehicle_speed NUMBER(5,1), -- 필드 VehicleSpeed
odometer NUMBER(10,1), -- 필드 Odometer
soc NUMBER(5,2), -- 필드 Soc
battery_level NUMBER(3) CHECK (battery_level BETWEEN 0 AND 100), -- BatteryLevel
pack_voltage NUMBER(6,1), -- 필드 PackVoltage (예제에선 미수집 NULL)
pack_current NUMBER(7,2), -- 필드 PackCurrent (예제에선 미수집 NULL)
est_battery_range NUMBER(6,1), -- 필드 EstBatteryRange
gear VARCHAR2(1) CHECK (gear IN ('P','R','N','D')), -- Gear
brake_pedal NUMBER(1), -- 필드 BrakePedal (0/1)
latitude NUMBER(9,6), -- 필드 Location 분해 (TeslaMate positions.latitude)
longitude NUMBER(9,6), -- 필드 Location 분해 (TeslaMate positions.longitude)
CONSTRAINT fk_tel_vin FOREIGN KEY (vin) REFERENCES vehicles (vin)
);
-- (3) 충전 프로세스 : TeslaMate charging_processes 테이블/컬럼명이 근거
CREATE TABLE charging_processes (
id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
car_id NUMBER NOT NULL,
start_date TIMESTAMP NOT NULL,
end_date TIMESTAMP,
charge_energy_added NUMBER(8,2), -- 충전으로 추가된 에너지(kWh)
start_battery_level NUMBER(3),
end_battery_level NUMBER(3),
duration_min NUMBER(6),
cost NUMBER(10,2),
CONSTRAINT fk_cp_car FOREIGN KEY (car_id) REFERENCES vehicles (car_id)
);
-- (4) 결함 코드 로그 : 이 테이블만 가상 설계임 (DTC 공개 스키마 없음)
CREATE TABLE dtc_logs (
log_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
vin VARCHAR2(17) NOT NULL REFERENCES vehicles (vin),
error_code VARCHAR2(10) NOT NULL,
ecu_id VARCHAR2(4), -- 숫자처럼 보이는 문자 컬럼. PART 12 형변환 함정 실습용
ts TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
);
-- (5)~(7) nuScenes 라벨링 스키마 : schema_nuscenes.md 정의가 근거
-- 실제 token은 32자리 16진수 문자열이며, prev/next 필드로 시간축 연결 리스트를 구성함.
-- (prev/next는 예약어 충돌을 피해 prev_token/next_token 으로 매핑함)
CREATE TABLE ns_category (
token VARCHAR2(32) PRIMARY KEY,
name VARCHAR2(50) NOT NULL, -- 예 : vehicle.car, human.pedestrian.adult
description VARCHAR2(200)
);
CREATE TABLE ns_instance (
token VARCHAR2(32) PRIMARY KEY,
category_token VARCHAR2(32) NOT NULL REFERENCES ns_category (token),
nbr_annotations NUMBER(5) -- 이 객체가 등장한 어노테이션 수
);
CREATE TABLE ns_sample_annotation (
token VARCHAR2(32) PRIMARY KEY,
sample_token VARCHAR2(32) NOT NULL, -- 어느 키프레임(sample)의 라벨인지
instance_token VARCHAR2(32) NOT NULL REFERENCES ns_instance (token),
num_lidar_pts NUMBER(6), -- 박스 안 라이다 포인트 수
num_radar_pts NUMBER(6),
prev_token VARCHAR2(32), -- 직전 어노테이션 (첫 프레임이면 NULL)
next_token VARCHAR2(32) -- 다음 어노테이션 (마지막이면 NULL)
);
■ ALTER TABLE (운영 중 구조 변경)
-- [왜] 요구사항 변화는 테이블 재생성이 아니라 ALTER로 반영함. 실제로 Fleet
-- Telemetry에 2025-12 추가된 자율주행 필드를 그대로 반영해 보는 예제임.
-- [어떻게] ADD(컬럼 추가) / MODIFY(자료형 변경) / DROP COLUMN(삭제)
ALTER TABLE telemetry_logs ADD self_driving_miles_since_reset NUMBER(10,1); -- 필드 SelfDrivingMilesSinceReset
ALTER TABLE telemetry_logs ADD miles_since_reset NUMBER(10,1); -- 필드 MilesSinceReset
ALTER TABLE telemetry_logs MODIFY vehicle_speed NUMBER(6,2);
ALTER TABLE telemetry_logs DROP COLUMN miles_since_reset;
■ DELETE vs TRUNCATE vs DROP (면접 단골 3종 비교)
-- [왜] 셋 다 "지운다"지만 동작 계층이 다름. 잘못 고르면 복구 불가 사고가 남.
-- DELETE : DML. 행 단위 삭제, WHERE 가능, ROLLBACK 가능, UNDO 기록 때문에 느림.
-- TRUNCATE : DDL. 전체 행 즉시 삭제, WHERE 불가, ROLLBACK 불가, 빠름.
-- DROP : DDL. 데이터에 더해 테이블 구조 자체를 제거함.
-- [어떻게] (실행하면 뒤 예제가 깨지므로 주석으로만 둠)
-- DELETE FROM dtc_logs WHERE ts < SYSTIMESTAMP - INTERVAL '90' DAY;
-- TRUNCATE TABLE dtc_logs;
-- DROP TABLE dtc_logs;
■ CREATE INDEX (일반 / 복합 / 함수 기반)
-- [왜] 인덱스 없는 대용량 로그 조회는 항상 FULL SCAN임. WHERE와 JOIN에 쓰는 컬럼이
-- 인덱스 후보이고, 함수로 가공해서 조회하는 컬럼은 함수 기반 인덱스가 필요함.
CREATE INDEX ix_tel_vin_ts ON telemetry_logs (vin, ts); -- 복합 : 차량별 시계열 조회용
CREATE INDEX ix_dtc_code ON dtc_logs (error_code);
CREATE INDEX ix_tel_wmi ON telemetry_logs (SUBSTR(vin, 1, 3)); -- 함수 기반 : WMI 필터 대응
■ CREATE SEQUENCE (11g 호환 자동 번호)
-- [왜] IDENTITY가 없는 11g 이하에서 PK 번호를 만드는 표준 방법임.
-- [어떻게] seq.NEXTVAL(다음 값 발급), seq.CURRVAL(현재 세션의 마지막 발급 값)
CREATE SEQUENCE seq_event_id START WITH 1 INCREMENT BY 1 NOCACHE;
SELECT seq_event_id.NEXTVAL FROM dual;
■ CREATE OR REPLACE VIEW
-- [왜] 자주 쓰는 조인/필터를 뷰로 저장하면 자바 쪽 SQL이 짧아지고, 원본 테이블
-- 권한 없이 필요한 컬럼만 노출할 수 있어 보안에도 유리함.
CREATE OR REPLACE VIEW v_low_battery AS
SELECT vin, ts, battery_level
FROM telemetry_logs
WHERE battery_level <= 20;
■ COMMENT ON (딕셔너리에 남기는 주석 메타데이터)
-- [왜] 컬럼 의미와 라벨 출처를 딕셔너리에 남겨야 다음 사람이 근거를 추적할 수 있음.
COMMENT ON COLUMN telemetry_logs.soc IS 'Tesla Fleet Telemetry 필드 Soc';
COMMENT ON TABLE ns_sample_annotation IS 'nuScenes sample_annotation 스키마 기반';# 데이터 트랜잭션 (INSERT / UPDATE / DELETE / MERGE / COMMIT)
데이터를 저장하고 트랜잭션, 즉 업데이트한다고 했을때, 총 5가지의 명령어를 통해서 가능하다.
-- ■ INSERT (단건)
-- [왜] 가장 기본적인 적재. 컬럼 목록을 생략하면 테이블 정의 순서에 묶여 깨지기
-- 쉬우므로, 실무 원칙은 컬럼 목록 명시임.
-- [어떻게] IDENTITY 컬럼(car_id)은 목록에서 빼면 자동 발급됨(1부터 시작 가정).
INSERT INTO vehicles (vin, model, name, efficiency)
VALUES ('5YJ3E1EA1TF000001', 'Model 3', 'M3-Alpha', 0.1450);
INSERT INTO vehicles (vin, model, name, efficiency)
VALUES ('5YJSA1E2XTF000002', 'Model S', 'MS-Beta', 0.1860);
INSERT INTO vehicles (vin, model, name, efficiency)
VALUES ('KMHL14JA5TA000003', 'Sonata', 'SN-Gamma', NULL); -- efficiency 미상 : NULL 실습용
-- ■ INSERT ALL (오라클 다중 행 삽입)
-- [왜] 오라클엔 다중 VALUES 표준 문법이 없어서, 수집기 배치 적재를 한 문장으로
-- 흉내내려면 INSERT ALL을 씀. 마지막의 SELECT 1 FROM dual 은 필수 형식임.
INSERT ALL
INTO telemetry_logs (vin, ts, vehicle_speed, odometer, soc, battery_level, gear, brake_pedal, latitude, longitude)
VALUES ('5YJ3E1EA1TF000001', TO_TIMESTAMP('2026-08-01 09:00:00','YYYY-MM-DD HH24:MI:SS'), 62.0, 15230.5, 81.50, 81, 'D', 0, 37.566500, 126.978000)
INTO telemetry_logs (vin, ts, vehicle_speed, odometer, soc, battery_level, gear, brake_pedal, latitude, longitude)
VALUES ('5YJ3E1EA1TF000001', TO_TIMESTAMP('2026-08-01 09:05:00','YYYY-MM-DD HH24:MI:SS'), 88.5, 15236.2, 80.20, 80, 'D', 0, 37.570100, 126.982400)
INTO telemetry_logs (vin, ts, vehicle_speed, odometer, soc, battery_level, gear, brake_pedal, latitude, longitude)
VALUES ('5YJ3E1EA1TF000001', TO_TIMESTAMP('2026-08-01 09:10:00','YYYY-MM-DD HH24:MI:SS'), 105.3, 15243.9, 78.90, 78, 'D', 0, 37.574800, 126.986900)
INTO telemetry_logs (vin, ts, vehicle_speed, odometer, soc, battery_level, gear, brake_pedal, latitude, longitude)
VALUES ('5YJSA1E2XTF000002', TO_TIMESTAMP('2026-08-01 09:00:00','YYYY-MM-DD HH24:MI:SS'), 120.0, 40110.0, 65.00, 65, 'D', 0, 37.394900, 127.111100)
INTO telemetry_logs (vin, ts, vehicle_speed, odometer, soc, battery_level, gear, brake_pedal, latitude, longitude)
VALUES ('5YJSA1E2XTF000002', TO_TIMESTAMP('2026-08-01 09:05:00','YYYY-MM-DD HH24:MI:SS'), 95.4, 40118.3, 63.80, 63, 'D', 1, 37.398800, 127.115600)
INTO telemetry_logs (vin, ts, vehicle_speed, odometer, soc, battery_level, gear, brake_pedal, latitude, longitude)
VALUES ('5YJSA1E2XTF000002', TO_TIMESTAMP('2026-08-01 09:10:00','YYYY-MM-DD HH24:MI:SS'), 130.2, 40126.0, 62.10, 62, 'D', 0, 37.402500, 127.120300)
INTO telemetry_logs (vin, ts, vehicle_speed, odometer, soc, battery_level, gear, brake_pedal, latitude, longitude)
VALUES ('KMHL14JA5TA000003', TO_TIMESTAMP('2026-08-01 10:00:00','YYYY-MM-DD HH24:MI:SS'), 55.0, 80500.0, NULL, NULL, 'D', 0, 37.497900, 127.027600)
INTO telemetry_logs (vin, ts, vehicle_speed, odometer, soc, battery_level, gear, brake_pedal, latitude, longitude)
VALUES ('KMHL14JA5TA000003', TO_TIMESTAMP('2026-08-01 10:05:00','YYYY-MM-DD HH24:MI:SS'), 0.0, 80500.4, NULL, NULL, 'P', 1, 37.499300, 127.029500)
SELECT 1 FROM dual;
-- 충전 프로세스 시드 : car_id 1, 2만 충전 이력 보유 (3번 Sonata는 없음 → 아우터 조인 실습용)
INSERT INTO charging_processes (car_id, start_date, end_date, charge_energy_added, start_battery_level, end_battery_level, duration_min, cost)
VALUES (1, TO_TIMESTAMP('2026-07-30 22:00:00','YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2026-07-30 23:30:00','YYYY-MM-DD HH24:MI:SS'), 21.40, 35, 80, 90, 12.50);
INSERT INTO charging_processes (car_id, start_date, end_date, charge_energy_added, start_battery_level, end_battery_level, duration_min, cost)
VALUES (1, TO_TIMESTAMP('2026-08-01 21:00:00','YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2026-08-01 22:00:00','YYYY-MM-DD HH24:MI:SS'), 12.10, 55, 78, 60, 7.20);
INSERT INTO charging_processes (car_id, start_date, end_date, charge_energy_added, start_battery_level, end_battery_level, duration_min, cost)
VALUES (2, TO_TIMESTAMP('2026-07-29 13:00:00','YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2026-07-29 13:40:00','YYYY-MM-DD HH24:MI:SS'), 30.00, 20, 60, 40, 18.90);
INSERT INTO charging_processes (car_id, start_date, end_date, charge_energy_added, start_battery_level, end_battery_level, duration_min, cost)
VALUES (2, TO_TIMESTAMP('2026-08-01 08:00:00','YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2026-08-01 08:25:00','YYYY-MM-DD HH24:MI:SS'), 18.50, 41, 64, 25, 11.60);
-- DTC 시드 : P0A80(배터리 교체 알림)은 ECU '0012', U0100(통신 두절)은 ECU '0034'
INSERT INTO dtc_logs (vin, error_code, ecu_id, ts) VALUES ('5YJ3E1EA1TF000001', 'P0A80', '0012', TO_TIMESTAMP('2026-07-28 10:00:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO dtc_logs (vin, error_code, ecu_id, ts) VALUES ('5YJ3E1EA1TF000001', 'P0A80', '0012', TO_TIMESTAMP('2026-07-30 11:00:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO dtc_logs (vin, error_code, ecu_id, ts) VALUES ('5YJ3E1EA1TF000001', 'P0A80', '0012', TO_TIMESTAMP('2026-08-01 09:30:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO dtc_logs (vin, error_code, ecu_id, ts) VALUES ('5YJ3E1EA1TF000001', 'U0100', '0034', TO_TIMESTAMP('2026-07-29 15:00:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO dtc_logs (vin, error_code, ecu_id, ts) VALUES ('5YJSA1E2XTF000002', 'P0A80', '0012', TO_TIMESTAMP('2026-07-31 08:00:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO dtc_logs (vin, error_code, ecu_id, ts) VALUES ('5YJSA1E2XTF000002', 'P0A80', '0012', TO_TIMESTAMP('2026-08-01 14:00:00','YYYY-MM-DD HH24:MI:SS'));
-- nuScenes 시드 : 트래킹 사슬 2개 (차량 a01→a02→a03, 보행자 b01→b02)
INSERT INTO ns_category (token, name, description) VALUES ('c01', 'vehicle.car', '승용차');
INSERT INTO ns_category (token, name, description) VALUES ('c02', 'human.pedestrian.adult', '성인 보행자');
INSERT INTO ns_instance (token, category_token, nbr_annotations) VALUES ('i01', 'c01', 3);
INSERT INTO ns_instance (token, category_token, nbr_annotations) VALUES ('i02', 'c02', 2);
INSERT INTO ns_sample_annotation (token, sample_token, instance_token, num_lidar_pts, num_radar_pts, prev_token, next_token)
VALUES ('a01', 's01', 'i01', 152, 4, NULL, 'a02');
INSERT INTO ns_sample_annotation (token, sample_token, instance_token, num_lidar_pts, num_radar_pts, prev_token, next_token)
VALUES ('a02', 's02', 'i01', 120, 3, 'a01', 'a03');
INSERT INTO ns_sample_annotation (token, sample_token, instance_token, num_lidar_pts, num_radar_pts, prev_token, next_token)
VALUES ('a03', 's03', 'i01', 98, 2, 'a02', NULL);
INSERT INTO ns_sample_annotation (token, sample_token, instance_token, num_lidar_pts, num_radar_pts, prev_token, next_token)
VALUES ('b01', 's01', 'i02', 45, 1, NULL, 'b02');
INSERT INTO ns_sample_annotation (token, sample_token, instance_token, num_lidar_pts, num_radar_pts, prev_token, next_token)
VALUES ('b02', 's02', 'i02', 30, 0, 'b01', NULL);
COMMIT;
-- ■ CREATE TABLE AS SELECT (CTAS) + INSERT ... SELECT
-- [왜] 백업 테이블과 집계 테이블은 애플리케이션 왕복 없이 DB 안에서 복사해 만듦.
-- [주의] CREATE 같은 DDL은 묵시적 COMMIT을 발생시켜 직전 DML까지 확정함 (면접 포인트).
CREATE TABLE dtc_logs_backup AS SELECT * FROM dtc_logs;
INSERT INTO dtc_logs_backup SELECT * FROM dtc_logs WHERE error_code = 'P0A80';
-- ■ UPDATE
-- [왜] 이미 적재된 행 수정. WHERE를 빠뜨리면 전 행이 바뀌므로, 실무 습관은
-- 같은 WHERE로 SELECT 먼저 실행해 대상 확인 후 UPDATE 실행임.
UPDATE vehicles SET name = 'M3-Alpha-R' WHERE vin = '5YJ3E1EA1TF000001';
-- ■ DELETE
DELETE FROM dtc_logs_backup WHERE error_code <> 'P0A80';
-- ■ MERGE (UPSERT : 있으면 UPDATE, 없으면 INSERT)
-- [왜] "차량별 최신 상태" 테이블 갱신이 대표 사례임. SELECT로 존재 확인 후 분기하면
-- 왕복 2회에 동시성 구멍이 생기지만, MERGE는 이 분기를 원자적 1문장으로 처리함.
-- [어떻게] MERGE INTO 대상 USING 소스 ON (키) WHEN MATCHED ... WHEN NOT MATCHED ...
CREATE TABLE vehicle_last_state (
vin VARCHAR2(17) PRIMARY KEY,
last_soc NUMBER(5,2),
updated_at TIMESTAMP
);
MERGE INTO vehicle_last_state t
USING (SELECT '5YJ3E1EA1TF000001' AS vin, 78.90 AS soc FROM dual) s
ON (t.vin = s.vin)
WHEN MATCHED THEN
UPDATE SET t.last_soc = s.soc, t.updated_at = SYSTIMESTAMP
WHEN NOT MATCHED THEN
INSERT (vin, last_soc, updated_at) VALUES (s.vin, s.soc, SYSTIMESTAMP);
-- ■ COMMIT / ROLLBACK / SAVEPOINT (TCL)
-- [왜] DML은 COMMIT 전까지 내 세션에서만 보임. 자바(JDBC)는 기본 auto-commit이라서
-- 배치 적재는 setAutoCommit(false)로 묶고, 실패 시 rollback() 하는 게 정석임.
SAVEPOINT before_cleanup;
DELETE FROM dtc_logs_backup;
ROLLBACK TO before_cleanup; -- 세이브포인트 이후 작업만 되돌림
COMMIT; -- 여기까지 전체 확정# SELECT 기본 구조
SQL의 시작을 담당하는 SELECT를 어떻게 다룰 수 있냐에 따라서 확실히 디비 사용이 편리해진다. 그래서인지 실제로도 면접에서 이 SELECT에 대해서 기본구조를 물어보는 질문은 꽤 나왔었다.
새로운 내용이 아니라 복습할 겸 중요한 포인트를 다시 정리해 둔 파트이다.
-- ■ 논리적 실행 순서 (면접 최다 빈출)
-- [왜] "SELECT 별칭을 왜 WHERE에서 못 쓰는가"의 근거가 실행 순서임.
-- FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY 순으로 평가되므로,
-- 별칭은 SELECT 이후 단계인 ORDER BY에서만 사용할 수 있음.
SELECT vin, vehicle_speed AS spd -- 5) 출력 컬럼 확정, 별칭 부여
FROM telemetry_logs -- 1) 대상 테이블 확정
WHERE vehicle_speed >= 60 -- 2) 행 필터 (여기서 별칭 spd 사용 불가)
ORDER BY spd DESC; -- 6) 정렬 (별칭 사용 가능)
-- ■ DISTINCT
-- [왜] 로그 테이블에서 "존재하는 차량 목록"처럼 중복 제거가 필요할 때 씀.
-- 내부적으로 정렬 또는 해시가 들어가므로 대용량에서는 비용이 큼.
SELECT DISTINCT vin FROM telemetry_logs;
-- ■ WHERE 비교 / 범위 / 목록 / 패턴 연산자
-- [왜] 인덱스를 탈 수 있는 1차 필터가 전부 WHERE에서 결정됨.
-- [어떻게] BETWEEN a AND b(양끝 포함), IN(목록), LIKE(% : 0자 이상, _ : 정확히 1자)
SELECT vin, battery_level FROM telemetry_logs WHERE battery_level BETWEEN 60 AND 80;
SELECT vin, error_code FROM dtc_logs WHERE error_code IN ('P0A80', 'U0100');
SELECT vin, model FROM vehicles WHERE vin LIKE '5YJ%'; -- 제조사(WMI) 필터의 인덱스 친화 버전
SELECT vin, model FROM vehicles WHERE model LIKE 'Model!_%' ESCAPE '!'; -- '_' 문자 자체를 찾는 문법 시연
-- ■ IS NULL / IS NOT NULL
-- [왜] NULL은 값이 아니라 "모름" 상태라서 = NULL 비교는 참도 거짓도 아닌 UNKNOWN이
-- 됨. 전용 연산자 IS NULL만 NULL을 판정할 수 있음.
SELECT vin FROM vehicles WHERE efficiency IS NULL;
-- ■ 연결 연산자 ||
-- [왜] 코드값과 라벨을 합쳐 사람이 읽을 표시 문자열을 만들 때 씀.
SELECT vin || ' (' || model || ')' AS display_label FROM vehicles;# 함수 : 문자 / 숫자 / 날짜 / 변환 / NULL / 조건
함수 또는 메서드라고 프로그래밍 언어에서 부르는 특정 작업을 수행하는 코드가 있듯이 SQL에도 이러한 함수가 존재한다. 다만 함수라고 부르기에는 단순한 구조이기에 "단일행 함수"라고도 부르기 때문에 다른 자료를 찾아볼 때 혼돈이 없길 바란다.
그냥 한마디로 단순 함수기능, 즉 엑셀에서 사용하는 함수 정도로 생각하면 된다. (무서워할 필요가 없다는 것이다.)
-- ■ 문자열 : SUBSTR, INSTR, LENGTH, REPLACE, LPAD, TRIM
-- [왜] VIN 17자리 규격(1~3자리 제조사 WMI, 10번째 자리 연식)을 자르는 작업이
-- CCS 도메인 전처리의 기본기임.
-- [어떻게] SUBSTR(문자열, 시작위치, 길이). 오라클은 시작위치가 1부터이고,
-- 음수 시작위치는 끝에서부터 셈.
SELECT vin,
SUBSTR(vin, 1, 3) AS wmi_code, -- 제조사 식별 코드
SUBSTR(vin, 10, 1) AS model_year_code, -- 연식 코드
INSTR(vin, 'TF') AS tf_pos, -- 부분 문자열 위치 (없으면 0)
LENGTH(vin) AS vin_len, -- 17 검증용
LPAD(SUBSTR(vin, -4), 17, '*') AS masked_vin -- 뒤 4자리만 노출 (마스킹)
FROM vehicles;
-- ■ 숫자 : ROUND, TRUNC, MOD, CEIL, FLOOR
-- [왜] SOC와 전력량의 표시 자릿수 정리, 그리고 구간(버킷) 분석에 씀.
SELECT soc,
ROUND(soc, 1) AS soc_round, -- 반올림
TRUNC(soc, 1) AS soc_trunc, -- 버림
MOD(battery_level, 10) AS remainder10,
FLOOR(battery_level / 10) * 10 AS bucket10 -- 10% 단위 구간화
FROM telemetry_logs
WHERE soc IS NOT NULL;
-- ■ 날짜/시간 : SYSDATE, SYSTIMESTAMP, 산술, TRUNC, ADD_MONTHS, MONTHS_BETWEEN, INTERVAL
-- [왜] 시계열 로그의 모든 조회 조건이 날짜 연산으로 만들어짐.
-- [어떻게] DATE ± 숫자는 "일" 단위 연산임. 시/분 단위는 INTERVAL이 명확함.
SELECT SYSDATE AS now_date, -- DATE (초 단위까지)
SYSTIMESTAMP AS now_ts, -- TIMESTAMP (소수점 초 + 시간대)
TRUNC(SYSDATE) AS today_0h, -- 시각 절삭 → 오늘 00시
TRUNC(SYSDATE, 'MM') AS month_first, -- 이번 달 1일
SYSDATE - 7 AS week_ago,
SYSTIMESTAMP - INTERVAL '30' MINUTE AS half_hour_ago,
ADD_MONTHS(SYSDATE, -1) AS a_month_ago,
MONTHS_BETWEEN(SYSDATE, DATE '2026-01-01') AS months_diff
FROM dual;
-- 최근 24시간 로그 조회 : 시계열 필터의 표준형
SELECT vin, ts FROM telemetry_logs WHERE ts >= SYSTIMESTAMP - INTERVAL '24' HOUR;
-- 시간대별 건수 : ts가 TIMESTAMP 타입이라 EXTRACT(HOUR ...) 사용 가능
-- (DATE 타입 컬럼이면 EXTRACT(HOUR)가 안 되므로 TO_CHAR(컬럼,'HH24')를 씀)
SELECT EXTRACT(HOUR FROM ts) AS hh, COUNT(*) AS cnt
FROM telemetry_logs
GROUP BY EXTRACT(HOUR FROM ts)
ORDER BY hh;
-- ■ 변환 : TO_CHAR / TO_DATE / TO_NUMBER
-- [왜] 자바에서 넘어오는 값은 문자열인 경우가 많고, 화면 출력은 포맷팅이 필요함.
-- 명시적 변환을 생략하면 오라클이 묵시적 변환을 하는데, 이것이 인덱스를
-- 무력화하는 원인이 됨 (근거와 실습은 PART 12).
SELECT TO_CHAR(ts, 'YYYY-MM-DD HH24:MI:SS') AS ts_str,
TO_CHAR(odometer, 'FM99,999,990.0') AS pretty_odo
FROM telemetry_logs
FETCH FIRST 3 ROWS ONLY;
SELECT TO_DATE('2026-08-01', 'YYYY-MM-DD') AS parsed_date,
TO_NUMBER('0012') AS parsed_num
FROM dual;
-- ■ NULL 처리 : NVL, NVL2, COALESCE, NULLIF (원문서 6번 예제의 확장)
-- [왜] NULL이 산술에 섞이면 결과 전체가 NULL이 됨. 집계와 계산 전에 기본값 치환이
-- 필수임. NVL은 오라클 전용이고, COALESCE가 표준이며 인자 여러 개를 왼쪽부터
-- 평가함.
SELECT vin,
NVL(efficiency, 0) AS eff_nvl,
NVL2(efficiency, '측정됨', '미측정') AS eff_flag, -- NOT NULL이면 2번째, NULL이면 3번째 반환
COALESCE(efficiency, 0.15) AS eff_std
FROM vehicles;
-- NULLIF : 두 값이 같으면 NULL 반환. 0으로 나누기 오류(ORA-01476) 방어의 정석
SELECT vin, ROUND(soc / NULLIF(battery_level, 0), 4) AS soc_per_level
FROM telemetry_logs;
-- ■ 조건 : CASE / DECODE
-- [왜] 행별 분류(라벨링)의 기본 도구임. CASE는 표준이고 범위 조건이 가능하며,
-- DECODE는 오라클 전용 등치 비교 단축형이라 레거시 코드 해석용으로 필요함.
SELECT vin, battery_level,
CASE
WHEN battery_level >= 60 THEN 'NORMAL'
WHEN battery_level >= 20 THEN 'LOW'
WHEN battery_level IS NULL THEN 'N/A'
ELSE 'CRITICAL'
END AS soc_grade,
DECODE(gear, 'P', '주차', 'D', '주행', '기타') AS gear_kr
FROM telemetry_logs;# 집계 함수와 GROUP BY / HAVING / ROLLUP
아무래도 디비 특성상 COUNT를 사용할 일이 많다. 데이터가 정형화 되어있다보니 개수를 세어야 하는 경우가 있는데, 아래의 테슬라 코드기반 예제처럼 특정 조건에 해당되는 차량대수를 세야 하는 경우 사용할 수 있는 명령어들이다.
-- ■ COUNT(*) vs COUNT(컬럼) vs COUNT(DISTINCT 컬럼) (면접 단골)
-- [왜] COUNT(*)는 행 수 전부를 세고, COUNT(컬럼)은 그 컬럼이 NULL인 행을 제외함.
-- 이 차이만으로 "센서 결측이 몇 건인가"를 바로 계산할 수 있음.
SELECT COUNT(*) AS all_rows,
COUNT(soc) AS soc_measured,
COUNT(*) - COUNT(soc) AS soc_missing, -- 결측 건수
COUNT(DISTINCT vin) AS car_cnt
FROM telemetry_logs;
-- ■ SUM / AVG / MAX / MIN + GROUP BY
-- [왜] 차량 단위 지표 산출의 기본형임. GROUP BY에 없는 일반 컬럼은 SELECT에 올 수
-- 없는데, 그룹당 값이 하나로 정해지지 않기 때문임 (ORA-00979의 근거).
SELECT vin,
COUNT(*) AS log_cnt,
ROUND(AVG(vehicle_speed), 1) AS avg_speed,
MAX(vehicle_speed) AS top_speed,
MIN(battery_level) AS min_battery
FROM telemetry_logs
GROUP BY vin;
-- ■ WHERE(집계 전) vs HAVING(집계 후) : 원문서 2번 예제의 일반형
-- [왜] WHERE는 그룹을 만들기 전에 행을 줄여 스캔 비용을 낮추고, HAVING은 집계
-- 결과값에 대한 조건이라 집계가 끝난 뒤에만 판단할 수 있음.
SELECT vin, COUNT(*) AS defect_cnt, MAX(ts) AS last_detected
FROM dtc_logs
WHERE error_code = 'P0A80' -- 1) 대상 코드로 행 축소 (집계 전)
GROUP BY vin -- 2) 차량별 그룹화
HAVING COUNT(*) >= 2 -- 3) 빈발 차량만 (샘플 데이터라 기준을 2회로 둠)
ORDER BY defect_cnt DESC;
-- ■ ROLLUP / GROUPING (리포트용 확장 GROUP BY)
-- [왜] "모델별 + 코드별 + 소계 + 총계"를 UNION ALL로 세 번 집계하면 스캔이 3회지만,
-- ROLLUP은 스캔 1회로 계층 소계까지 만듦. CUBE는 모든 조합, GROUPING SETS는
-- 원하는 조합만 지정함.
-- [어떻게] ROLLUP(a, b) : (a,b) → (a) → 전체 순서로 소계 생성.
-- GROUPING(컬럼) = 1 이면 그 행이 소계/총계 행이라는 뜻.
SELECT CASE GROUPING(v.model) WHEN 1 THEN '[전체]' ELSE v.model END AS model,
CASE GROUPING(d.error_code) WHEN 1 THEN '[소계]' ELSE d.error_code END AS error_code,
COUNT(*) AS cnt
FROM dtc_logs d
JOIN vehicles v ON v.vin = d.vin
GROUP BY ROLLUP(v.model, d.error_code);# JOIN
아...조인(JOIN) 정말 많이 사용하는 명령어이다. 왜냐하면 디비 안에 테이블들이 너무 많이 쪼개져 있기 때문이다. 이를 데이터 정규화, normalization라고 부르는데, 데이터의 중복이 수정의 어려움을 최소화하고자 나눠두는 것이다. 그렇다 보니 필요한 데이터를 쏙쏙 골라와야 하는데 이럴 때 사용하는 명령어가 JOIN이다.
-- ■ INNER JOIN
-- [왜] 두 테이블 모두에 존재하는 키만 결합함. 로그에 차량 마스터 정보를 붙이는
-- 기본형임.
SELECT t.vin, v.model, t.ts, t.vehicle_speed
FROM telemetry_logs t
JOIN vehicles v ON v.vin = t.vin;
-- ■ LEFT OUTER JOIN + IS NULL : "부재"를 찾는 정석 패턴
-- [왜] 기준(왼쪽) 테이블 행은 전부 남기고, 상대가 없으면 NULL로 채움. 그래서
-- "충전 기록이 없는 차량" 같은 부재 질문은 LEFT JOIN 후 상대 키 IS NULL로 품.
SELECT v.vin, v.model
FROM vehicles v
LEFT JOIN charging_processes c ON c.car_id = v.car_id
WHERE c.id IS NULL; -- 조인 상대가 없어 NULL로 남은 행 = 충전 기록 없음
-- ■ ON 조건 vs WHERE 조건 (아우터 조인 최대 함정, 면접 단골)
-- [왜] LEFT JOIN에서 오른쪽 테이블 필터를 WHERE에 쓰면 NULL 행이 걸러져 사실상
-- INNER JOIN이 되어 버림. 오른쪽 필터는 ON에 둬야 왼쪽 행이 보존됨.
-- (1) 의도대로 : 모든 차량을 남기고, 8월 충전 건만 붙임
SELECT v.vin, c.start_date
FROM vehicles v
LEFT JOIN charging_processes c
ON c.car_id = v.car_id
AND c.start_date >= DATE '2026-08-01';
-- (2) 함정 : 8월 충전이 없는 차량은 행 자체가 사라짐 (INNER JOIN과 동일해짐)
SELECT v.vin, c.start_date
FROM vehicles v
LEFT JOIN charging_processes c ON c.car_id = v.car_id
WHERE c.start_date >= DATE '2026-08-01';
-- ■ RIGHT / FULL / CROSS JOIN
-- [왜] RIGHT는 LEFT의 방향 반전이라 관례상 LEFT로 통일해 씀. FULL은 양쪽 모두
-- 보존함. CROSS는 모든 조합(카티션 곱)이라 의도적 조합 생성 외에는 대부분
-- 조인 조건 누락 실수의 결과임.
SELECT v.vin, c.id FROM charging_processes c RIGHT JOIN vehicles v ON v.car_id = c.car_id;
SELECT v.vin, c.id FROM vehicles v FULL JOIN charging_processes c ON v.car_id = c.car_id;
SELECT v.model, g.gear
FROM vehicles v
CROSS JOIN (SELECT DISTINCT gear FROM telemetry_logs) g; -- 모델 x 기어 전 조합 생성
-- ■ SELF JOIN : nuScenes prev/next 연결 리스트가 근거
-- [왜] nuScenes 어노테이션은 next 토큰으로 다음 프레임과 이어짐. 같은 테이블을
-- 두 번 참조하면 "현재 프레임 vs 다음 프레임"의 라이다 포인트 변화를 비교할
-- 수 있음.
SELECT cur.token AS cur_token,
cur.num_lidar_pts AS cur_pts,
nxt.num_lidar_pts AS next_pts,
nxt.num_lidar_pts - cur.num_lidar_pts AS pts_diff
FROM ns_sample_annotation cur
JOIN ns_sample_annotation nxt ON nxt.token = cur.next_token;
-- ■ 오라클 전용 (+) 아우터 조인 (레거시 해석용)
-- [왜] 자바 8 시절 레거시 쿼리에 아직 많이 남아 있음. (+)가 붙은 쪽이 "부족해도
-- 되는 쪽"(NULL 허용 측)이라는 규칙만 알면 ANSI로 번역해 읽을 수 있음.
SELECT v.vin, c.id
FROM vehicles v, charging_processes c
WHERE v.car_id = c.car_id(+); -- ANSI 번역 : vehicles LEFT JOIN charging_processes# 서브쿼리 : 스칼라 / 인라인 뷰 / 상관 / EXISTS / ANY / ALL
-- ■ 스칼라 서브쿼리 (SELECT 절, 1행 1열 반환)
-- [왜] 행마다 딸려오는 단일 값(차량별 마지막 수신 시각 등)을 붙일 때 씀. 단, 바깥
-- 행 수만큼 반복 실행될 수 있어 대용량에서는 조인이나 윈도우 함수로 바꿈.
SELECT v.vin,
(SELECT MAX(t.ts) FROM telemetry_logs t WHERE t.vin = v.vin) AS last_ts
FROM vehicles v;
-- ■ 인라인 뷰 (FROM 절 서브쿼리) : 원문서 4번 예제의 뼈대
-- [왜] 집계나 윈도우 함수의 결과를 "테이블처럼" 취급해 한 번 더 필터링할 때 씀.
SELECT vin, ROUND(avg_speed, 1) AS avg_speed
FROM (SELECT vin, AVG(vehicle_speed) AS avg_speed
FROM telemetry_logs
GROUP BY vin)
WHERE avg_speed >= 80;
-- ■ 상관 서브쿼리 (바깥 행 참조)
-- [왜] "차량별 자기 평균보다 빠른 로그"처럼 바깥 행의 값을 조건에 참조해야 하는
-- 질문의 표준 해법임.
SELECT t.vin, t.ts, t.vehicle_speed
FROM telemetry_logs t
WHERE t.vehicle_speed > (SELECT AVG(x.vehicle_speed)
FROM telemetry_logs x
WHERE x.vin = t.vin); -- 바깥 행의 vin 참조 = 상관
-- ■ EXISTS / NOT EXISTS, 그리고 NOT IN의 NULL 함정 (면접 단골)
-- [왜] EXISTS는 존재 여부만 확인하고 멈추는 세미 조인이라 의도가 명확함.
-- NOT IN은 비교 목록에 NULL이 하나라도 있으면 전체 결과가 0건이 되는 함정이
-- 있어서, 부정 조건은 NOT EXISTS가 안전함.
SELECT v.vin FROM vehicles v
WHERE EXISTS (SELECT 1 FROM dtc_logs d
WHERE d.vin = v.vin AND d.error_code = 'P0A80');
SELECT v.vin FROM vehicles v
WHERE NOT EXISTS (SELECT 1 FROM dtc_logs d WHERE d.vin = v.vin); -- 결함 이력 없는 차
-- ■ ANY / ALL
-- [왜] "어느 하나보다라도(ANY)" 또는 "전부보다(ALL)" 크거나 작은 조건을 연산자로
-- 직접 표현함. > ALL 은 최댓값 초과, > ANY 는 최솟값 초과와 같은 뜻임.
SELECT vin, vehicle_speed
FROM telemetry_logs
WHERE vehicle_speed > ALL (SELECT vehicle_speed
FROM telemetry_logs
WHERE vin = 'KMHL14JA5TA000003');# 집합 연산자 : UNION / UNION ALL / INTERSECT / MINUS
-- [왜] 서로 다른 소스(주행 로그, 충전 로그)를 세로로 합치거나 차집합을 구할 때 씀.
-- 규칙 : 컬럼 개수와 자료형이 대응돼야 하고, ORDER BY는 맨 마지막에 1번만 씀.
-- [어떻게] UNION은 중복 제거를 위한 내부 정렬 비용이 들고, UNION ALL은 그대로 이어
-- 붙여서 빠름. 중복이 없거나 상관없다면 UNION ALL이 기본 선택임.
-- 차량별 이벤트 타임라인 통합 (이기종 로그의 세로 결합)
SELECT vin, ts, 'TELEMETRY' AS src
FROM telemetry_logs
UNION ALL
SELECT v.vin, c.start_date, 'CHARGE'
FROM charging_processes c
JOIN vehicles v ON v.car_id = c.car_id
ORDER BY vin, ts;
-- 주행 기록은 있는데 결함 이력은 없는 차량 (MINUS = 표준 SQL의 EXCEPT)
SELECT vin FROM telemetry_logs
MINUS
SELECT vin FROM dtc_logs;
-- 두 로그 모두에 등장하는 차량
SELECT vin FROM telemetry_logs
INTERSECT
SELECT vin FROM dtc_logs;# 윈도우 함수 : 순위 / 이동 / 누적
-- ■ ROW_NUMBER vs RANK vs DENSE_RANK vs NTILE
-- [왜] 세 순위 함수는 동점 처리 방식이 다름. 동점에도 유일 번호가 필요하면
-- ROW_NUMBER, 공동 순위 후 건너뛰면 RANK(1,1,3), 안 건너뛰면 DENSE_RANK(1,1,2).
-- NTILE(n)은 균등 n분할이라 상위 25% 같은 분위 분석에 씀.
SELECT vin, ts, vehicle_speed,
ROW_NUMBER() OVER (PARTITION BY vin ORDER BY vehicle_speed DESC) AS rn,
RANK() OVER (PARTITION BY vin ORDER BY vehicle_speed DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY vin ORDER BY vehicle_speed DESC) AS drnk,
NTILE(2) OVER (PARTITION BY vin ORDER BY vehicle_speed DESC) AS half
FROM telemetry_logs;
-- ■ 차량별 최신 1건 추출 (원문서 4번 패턴의 표준형)
-- [왜] 윈도우 함수는 실행 순서상 SELECT 단계의 산출물이라 WHERE에서 쓸 수 없음.
-- 그래서 WITH나 인라인 뷰로 감싼 뒤 바깥에서 rn = 1 로 필터링함.
WITH latest AS (
SELECT vin, ts, soc, battery_level,
ROW_NUMBER() OVER (PARTITION BY vin ORDER BY ts DESC) AS rn
FROM telemetry_logs
)
SELECT vin, ts, soc, battery_level
FROM latest
WHERE rn = 1;
-- ■ LAG / LEAD : 직전, 직후 행 참조
-- [왜] "직전 수신 대비 SOC 변화량" 같은 시계열 증감 계산의 표준 도구임.
-- [어떻게] LAG(컬럼, 오프셋, 기본값). 3번째 인자를 주면 파티션 첫 행의 NULL을
-- 그 값으로 치환함.
SELECT vin, ts, soc,
LAG(soc, 1) OVER (PARTITION BY vin ORDER BY ts) AS prev_soc,
soc - LAG(soc, 1, soc) OVER (PARTITION BY vin ORDER BY ts) AS soc_diff,
LEAD(ts, 1) OVER (PARTITION BY vin ORDER BY ts) AS next_ts
FROM telemetry_logs
WHERE soc IS NOT NULL;
-- ■ 집계 OVER + 프레임(ROWS BETWEEN) : 이동평균과 누적합
-- [왜] GROUP BY는 행을 뭉개지만, 집계 OVER는 원본 행을 유지한 채 집계값을 붙임.
-- 프레임(ROWS ...)이 "몇 행 범위로 집계할지"를 정의함.
SELECT vin, ts, vehicle_speed,
ROUND(AVG(vehicle_speed) OVER (PARTITION BY vin ORDER BY ts
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS ma3,
SUM(vehicle_speed) OVER (PARTITION BY vin ORDER BY ts
ROWS UNBOUNDED PRECEDING) AS running_sum
FROM telemetry_logs;
-- ■ FIRST_VALUE / LAST_VALUE 와 기본 프레임 함정 (면접 심화)
-- [왜] ORDER BY가 있는 윈도우의 기본 프레임은 "파티션 처음부터 현재 행까지"임.
-- 그래서 LAST_VALUE는 프레임을 끝까지 열어 주지 않으면 항상 자기 자신을
-- 반환하는 함정이 있음.
SELECT vin, ts, soc,
FIRST_VALUE(soc) OVER (PARTITION BY vin ORDER BY ts) AS first_soc,
LAST_VALUE(soc) OVER (PARTITION BY vin ORDER BY ts) AS last_soc_wrong,
LAST_VALUE(soc) OVER (PARTITION BY vin ORDER BY ts
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS last_soc_right
FROM telemetry_logs
WHERE soc IS NOT NULL;# WITH(CTE), 재귀, 계층 쿼리(CONNECT BY), 행 생성기
-- ■ WITH 다중 CTE
-- [왜] 서브쿼리 중첩을 이름 붙은 단계로 풀어 가독성을 확보함. 같은 중간 결과를
-- 두 번 이상 참조할 때 특히 유리함.
WITH per_car AS (
SELECT vin, COUNT(*) AS log_cnt, AVG(vehicle_speed) AS avg_spd
FROM telemetry_logs
GROUP BY vin
),
overall AS (
SELECT AVG(avg_spd) AS fleet_avg FROM per_car
)
SELECT p.vin,
ROUND(p.avg_spd, 1) AS avg_spd,
ROUND(o.fleet_avg, 1) AS fleet_avg
FROM per_car p
CROSS JOIN overall o;
-- ■ 재귀 WITH (11gR2+) : 날짜 행 생성 후 결측일 채우기
-- [왜] 로그가 없는 날은 GROUP BY 결과에 아예 나타나지 않음. "빠진 날짜를 0으로
-- 채운 일별 리포트"를 만들려면 달력 행을 먼저 생성해 LEFT JOIN해야 함.
-- [어떻게] 재귀 WITH는 컬럼 목록 (d) 명시가 필수임.
WITH calendar (d) AS (
SELECT DATE '2026-07-27' FROM dual
UNION ALL
SELECT d + 1 FROM calendar WHERE d < DATE '2026-08-02'
)
SELECT c.d, NVL(x.cnt, 0) AS dtc_cnt
FROM calendar c
LEFT JOIN (SELECT TRUNC(ts) AS d, COUNT(*) AS cnt
FROM dtc_logs
GROUP BY TRUNC(ts)) x
ON x.d = c.d
ORDER BY c.d;
-- ■ CONNECT BY LEVEL : 한 줄 행 생성기 (오라클 전용)
-- [왜] 위 재귀 WITH의 오라클 관용 단축형. 연속 더미 행이 필요할 때 가장 짧음.
SELECT TRUNC(SYSDATE) - LEVEL + 1 AS d
FROM dual
CONNECT BY LEVEL <= 7; -- 오늘 포함 최근 7일 생성
-- ■ START WITH ... CONNECT BY PRIOR : 계층(연결 리스트) 순회
-- [왜] nuScenes 어노테이션은 prev/next 토큰 사슬임. 루트(prev IS NULL)에서 시작해
-- 트래킹 순서(LEVEL)를 복원하는 것이 계층 쿼리의 정확한 용례임.
-- [어떻게] PRIOR는 "부모 행의" 컬럼이라는 뜻임. 자식.prev_token = 부모.token 으로
-- 연결하고, ORDER SIBLINGS BY는 계층 구조를 깨지 않는 정렬임.
SELECT LEVEL AS seq_in_track, token, instance_token, num_lidar_pts
FROM ns_sample_annotation
START WITH prev_token IS NULL
CONNECT BY prev_token = PRIOR token
ORDER SIBLINGS BY token;# 페이징 + 실행계획 + 인덱스 / 바인드 변수 (자바 연동 실전)
-- ■ ROWNUM의 동작 원리 (원문서 7번의 근거 보강)
-- [왜] ROWNUM은 행이 조건을 통과한 순간 1부터 부여됨. 그래서 두 가지 함정이 생김.
-- (1) WHERE ROWNUM > 1 : 첫 후보가 1을 받고 탈락 → 다음 후보가 다시 1 → 영원히 0건.
-- (2) 같은 블록의 ORDER BY보다 먼저 매겨짐 → 정렬 후 자르려면 인라인 뷰가 필수.
SELECT *
FROM (SELECT vin, ts, vehicle_speed
FROM telemetry_logs
ORDER BY vehicle_speed DESC)
WHERE ROWNUM <= 3; -- 정렬을 먼저 확정한 뒤 상위 3건을 자름
-- ■ ROW_NUMBER 페이징 (11g 표준 관용구)
-- [왜] ROWNUM 방식은 "N번째부터"를 직접 못 자르므로, 번호를 먼저 붙이고 바깥에서
-- 범위를 거는 구조가 11g 페이징의 정석이었음.
SELECT vin, ts, vehicle_speed
FROM (SELECT vin, ts, vehicle_speed,
ROW_NUMBER() OVER (ORDER BY ts DESC) AS rn
FROM telemetry_logs)
WHERE rn BETWEEN 4 AND 6; -- 2페이지 (페이지당 3건)
-- ■ OFFSET ... FETCH (12c+) + 자바 바인드 변수 공식
-- [왜] 표준 문법이라 가독성이 가장 좋고, 페이지 계산값을 바인드 변수로 넘기면
-- 실행계획이 재사용됨.
SELECT vin, ts, vehicle_speed
FROM telemetry_logs
ORDER BY ts DESC
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;
-- 자바(JDBC) : "... ORDER BY ts DESC OFFSET ? ROWS FETCH NEXT ? ROWS ONLY"
-- ps.setInt(1, (page - 1) * pageSize);
-- ps.setInt(2, pageSize);
-- ■ EXPLAIN PLAN : 실행계획 확인
-- [왜] "인덱스를 탔는가"는 추측이 아니라 실행계획으로 증명해야 함. 성능 개선을
-- 말할 때의 근거 자료가 이것임.
EXPLAIN PLAN FOR
SELECT *
FROM telemetry_logs
WHERE vin = '5YJ3E1EA1TF000001'
AND ts >= SYSTIMESTAMP - INTERVAL '1' DAY;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- ■ 인덱스를 무력화하는 4가지 패턴과 교정
-- [왜] 인덱스는 "저장된 값 그대로"만 탐색할 수 있음. 컬럼을 가공하는 순간 못 탐.
-- (1) 컬럼 가공 : 원문서 1번 예제의 SUBSTR 필터가 정확히 이 사례임
-- 나쁨 : WHERE SUBSTR(vin, 1, 3) = '5YJ'
-- 교정 : WHERE vin LIKE '5YJ%' (선두 일치라 인덱스 범위 탐색 가능)
-- 또는 : PART 2의 함수 기반 인덱스 ix_tel_wmi 를 만들고 원래 식을 유지
-- (2) 날짜 가공 :
-- 나쁨 : WHERE TRUNC(ts) = TRUNC(SYSDATE)
-- 교정 : WHERE ts >= TRUNC(SYSDATE) AND ts < TRUNC(SYSDATE) + 1
-- (3) 묵시적 형변환 : 문자 컬럼 vs 숫자 리터럴
-- 나쁨 : WHERE ecu_id = 12 (오라클이 TO_NUMBER(ecu_id)를 씌워 인덱스 무력화,
-- 숫자 아닌 값을 만나면 ORA-01722 런타임 오류)
-- 교정 : WHERE ecu_id = '0012' (리터럴이 컬럼 자료형을 따라감)
-- (4) 선두 와일드카드 : WHERE vin LIKE '%000001' 은 시작점을 못 잡아 범위 탐색 불가.
SELECT vin FROM telemetry_logs WHERE vin LIKE '5YJ%';
SELECT vin, error_code FROM dtc_logs WHERE ecu_id = '0012';
-- ■ 바인드 변수 (자바 PreparedStatement의 DB 쪽 근거)
-- [왜] 리터럴을 박으면 값마다 SQL 문장이 달라져 매번 하드 파싱이 일어나고, 문자열
-- 결합 SQL은 SQL 인젝션 통로가 됨. 바인드 변수는 실행계획 재사용과 인젝션
-- 차단을 동시에 해결함.
-- [어떻게] SQL*Plus 실습 :
-- VARIABLE b_vin VARCHAR2(17)
-- EXEC :b_vin := '5YJ3E1EA1TF000001'
-- SELECT COUNT(*) FROM telemetry_logs WHERE vin = :b_vin;
-- 자바 : "SELECT ... WHERE vin = ?" 로 쓰고 ps.setString(1, vin) 으로 바인딩함.# Results
정리하다 보니, 많아졌지만 모든 명령어를 한 번에 사용하지는 않는다. 그리고 모든 명령어를 몰라도 원하는 데이터를 찾고 사용하는데 문제는 없다. 예를 들어 엑세를 다루는데, 모든 기능을 몰라도 사용하는데 지장은 없지 않은가. 물론 모든 기능을 알고 나면 확실히 작업하는데 편리한 것처럼 SQL도 마찬가지라고 본다. 그래서 위에서부터 연습해 보면서 이해해 나간다면 SQL 사용이 쉬워질 것이다. 정리하다 보니 생각보다 많아서 사실 나도 놀랐지만, AI용 프롬프트가 필요한 브로는 댓글로 알려주면 추후 해당 포스트를 업데이트하도록 하겠다.
728x90
'Knowledge Transfer > Data Systems' 카테고리의 다른 글
| Problem: Universal Base Cycle for N Devices (0) | 2026.05.25 |
|---|---|
| Problem: Merge K Sensor Data Streams Alternately (0) | 2026.05.24 |
댓글