사례 연구: EDA 및 대치

SQL Server에서 함수와 저장 프로시저 작성하기

Meghan Kwartler

IT Consultant

택시 운행 비즈니스 과제

EU 사모펀드가 미국 교통 분야 투자 기회를 탐색 중.
요일·자치구별로 평균 거리당 요금, 승차 건수, 총 운행 시간은?
각 운전사 교대별로 자치구 내 어떤 픽업 지점을 배정할까요?

노란 택시 사진

핵심 EDA

분산 트랜잭션 데이터는 수집 보정 문제로 불가능한 값이 포함될 수 있음
- 미래 날짜
- 시작일 이전의 종료일

SELECT * 
FROM CapitalBikeShare 
WHERE 
  StartDate > GetDate() 
  OR EndDate > GetDate() 
  OR StartDate > EndDate

데이터 대치

Avg Fare/TripDistance 계산 시 0으로 나누기 오류
EDA로 Trip Distance=0인 TaxiRide 레코드 다수 발견
해결을 위한 대치 방법
- 평균 대치
- 핫덱
- 생략

평균 대치

결측값을 평균으로 대체
평균 자체는 변하지 않음
다른 열과의 상관관계가 증가

CREATE PROCEDURE dbo.ImputeDurMean 
AS
BEGIN
DECLARE @AvgTripDuration AS float

SELECT @AvgTripDuration = AVG(Duration) 
FROM CapitalBikeShare
WHERE Duration > 0

UPDATE CapitalBikeShare
SET Duration =  @AvgTripDuration
WHERE Duration = 0
END;

핫덱 대치

결측값을 무작위 선택값으로 대치
FROM 절의 TABLESAMPLE 사용

CREATE FUNCTION dbo.GetDurHotDeck()
RETURNS decimal (18,4)
AS BEGIN
RETURN (SELECT TOP 1 Duration
FROM CapitalBikeShare
TABLESAMPLE (1000 rows)
WHERE Duration >0)
END

SELECT 
  StartDate, 
  "TripDuration" = CASE WHEN Duration > 0 THEN Duration 
                        ELSE dbo.GetDurHotDeck() END 
FROM CapitalBikeShare;

SELECT 
  DATENAME(weekday, StartDate) AS DayofWeek, 
  AVG(Duration) AS 'AvgDuration' 
FROM CapitalBikeShare 
WHERE Duration > 0 
GROUP BY DATENAME(weekday, StartDate) 
ORDER BY AVG(Duration) desc

+-----------+-------------+
| DayofWeek | AvgDuration |
|-----------+-------------|
| Saturday  | 1476        |
| Sunday    | 1391        |
| Monday    |  979        |
| Friday    |  957        |
| Thursday  |  956        |
| Wednesday |  807        |
| Tuesday   |  763        |
+-----------+-------------|

이제 직접 해보세요!

SQL Server에서 함수와 저장 프로시저 작성하기