계산 열과 표현식

회원 게시판은 게시글의 조회수를 정수로 저장합니다. 화면에 목표 대비 조회 비율이 필요하다고 해서 그 값을 view_ratio라는 열에 다시 저장하면, 두 열이 어긋날 가능성이 생깁니다. 원래의 사실은 한 번만 저장하고, 보여 주는 데 필요한 값은 조회할 때 계산하는 편이 안전합니다.

이 글에서는 view_count에서 목표 대비 비율과 남은 조회수를 계산합니다. 산술식의 입력 타입과 0으로 나누기, NULL의 전파, 별칭을 쓸 수 있는 범위를 함께 확인해, 식이 계산된다는 것과 그 결과가 업무의 뜻에 맞는다는 것을 구별합니다.

계산 예제 데이터

이 글도 누적된 posts의 행 수와 id에 기대지 않도록, 세 행짜리 전용 테이블을 만들어 계산합니다. id를 직접 적어 넣으므로 몇 번을 다시 실행해도 같은 세 행이 만들어집니다. 조회수 30, 75, 40은 뒤에서 쓸 목표 조회수 60보다 낮은 글과 높은 글이 섞이도록 고른 값이고, 3번은 초안입니다.

DROP TABLE IF EXISTS ch4_calculation_post_demo;
CREATE TABLE ch4_calculation_post_demo (
  id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  title VARCHAR(80) NOT NULL,
  status VARCHAR(16) NOT NULL,
  view_count BIGINT UNSIGNED NOT NULL
) ENGINE = InnoDB;

INSERT INTO ch4_calculation_post_demo (id, title, status, view_count)
VALUES
  (1, 'SELECT 기초', 'PUBLISHED', 30),
  (2, 'WHERE 조건', 'PUBLISHED', 75),
  (3, 'JOIN 계획', 'DRAFT', 40);

별칭은 같은 SELECT 목록에서 다시 쓸 수 없다

조회수를 목표 대비 비율로 바꾼 뒤, 그 별칭을 같은 SELECT 목록의 다음 식에서 쓰려고 해 봅시다. 사람이 위에서 아래로 읽는 순서와 달리, 같은 SELECT 목록의 별칭은 옆의 식에서 열처럼 보이지 않습니다.

SET @target_views = 60;

SELECT
  id,
  view_count / NULLIF(@target_views, 0) AS view_ratio,
  view_ratio * 100 AS view_percent
FROM ch4_calculation_post_demo;

ERROR 1054 (42S22): Unknown column 'view_ratio' in 'field list'

MySQL은 view_ratio를 테이블의 열에서 찾다가 실패합니다. 별칭은 결과의 행이 만들어질 때 붙는 이름일 뿐, 위에서부터 차례로 선언되는 변수가 아니기 때문입니다.

짧은 식이라면 그대로 한 번 더 적고, 계산한 값을 다음 단계에서 열처럼 쓰려면 파생 테이블이나 공통 테이블 표현식(Common Table Expression, CTE), 뷰로 단계를 나눕니다. 다만 옵티마이저가 이런 단계를 합치거나 따로 계산해 둘 수 있으므로, 문법만으로 실제 계산 횟수까지 정해지지는 않습니다. 같은 식을 두 번 적기 싫다는 이유로 계산값을 열로 저장하면, 그 값을 계속 맞춰 주어야 하는 부담이 더 커집니다.

계산 열의 타입과 NULL

산술 연산자는 행마다 따로 적용되며, +, -, *, /의 결과 타입은 입력의 타입과 DBMS의 규칙에 따라 정해집니다. MySQL에서 정수끼리 /로 나눈 결과는 소수부가 있는 DECIMAL이고, 기본 설정(div_precision_increment가 4)에서는 소수 넷째 자리까지 계산됩니다. 그래서 40을 60으로 나누면 0.6667이 되고, 보여 줄 자릿수는 ROUND로 정합니다.

DBMS마다 이 규칙이 다릅니다. PostgreSQL이나 SQL Server에서는 정수끼리의 /가 몫만 남기므로, view_count * 100.0 / ...처럼 한쪽을 소수로 만들어 두면 어느 DBMS에서나 소수 나눗셈이 됩니다. MySQL에서 몫만 구하는 정수 나눗셈은 DIV라는 별도의 연산자가 맡습니다.

NULL이 들어간 산술식은 대부분 결과도 NULL입니다. 0으로 나누면 MySQL의 SELECT는 NULL을 돌려주는데, 이 교재의 SQL 모드에는 ERROR_FOR_DIVISION_BY_ZERO가 들어 있어 경고 1365(Division by 0)도 함께 남습니다. 같은 식을 INSERT나 UPDATE로 저장하려 하면 엄격 모드에서는 오류가 되므로, 분모가 0일 수 있는지를 업무의 규칙에서 먼저 확인합니다.

기본 산술식과 AS는 표준 SQL이고, ROUND도 널리 지원되지만 숫자 타입과 반올림 규칙의 세부는 DBMS마다 확인합니다. MySQL의 ROUND는 DECIMAL 같은 정확한 수에서 0.5를 0에서 멀어지는 쪽으로 반올림합니다.

더 깊이: 정확한 계산과 근삿값 계산이 갈리는 지점

MySQL은 식에 든 값의 종류를 보고 계산 방식을 정합니다. 식에 근삿값이 하나라도 있으면 식 전체를 부동소수점으로 계산하고, 근삿값 없이 소수부가 있는 정확한 값이 있으면 65자리 정밀도의 DECIMAL로, 정수만 있으면 BIGINT와 같은 64비트 정수로 계산합니다. 정확한 값은 정수 타입과 DECIMAL, 3.4처럼 소수점으로 적은 리터럴이고, 근삿값은 FLOAT와 DOUBLE, 2.34E0처럼 지수로 적은 리터럴입니다. 숫자 식에 문자열이 섞여도 그 문자열이 배정밀도 수로 바뀌므로 근삿값 계산이 됩니다. 그래서 view_count * 100.0은 DECIMAL로 계산되지만, 같은 값처럼 보이는 view_count * 1E2는 DOUBLE로 계산됩니다.

이 차이는 ROUND의 결과를 바꿉니다. 정확한 수에서는 0.5를 0에서 멀어지는 쪽으로 반올림하지만, 근삿값에서는 결과가 C 라이브러리를 따르고 많은 시스템에서 가장 가까운 짝수 쪽으로 반올림합니다. MySQL 8.4 매뉴얼의 예에서 ROUND(2.5)는 3이지만 ROUND(25E-1)은 2입니다. 반대로 DECIMAL이나 정수 열에 값을 저장할 때는 넣는 값이 근삿값이어도 0에서 멀어지는 쪽으로 반올림하고, 잘린 소수부는 엄격 모드에서도 오류가 아니라 노트 1265로만 알려 줍니다.

정확한 계산에도 한계는 있습니다. 1.0/3.0은 소수로 끝까지 적을 수 없는 값이므로, 같은 매뉴얼은 (1.0/3.0)*3.0이 정확히 1.0이 되지 않는다고 설명합니다. 비율을 계산해 다시 곱하는 식이라면, 나눗셈을 가장 마지막에 두는 편이 원래의 값을 덜 잃습니다.

달성률을 계산한다

목표 대비 달성률을 저장된 조회수에서 계산합니다. 목표가 NULL이거나 0일 수 있으므로 NULLIF로 0으로 나누기를 막고, 계산할 수 없다는 사실을 NULL로 남깁니다. NULLIF(a, b)는 두 값이 같으면 NULL을, 다르면 a를 돌려주므로, 목표가 60이면 분모는 그대로 60이고 목표가 0이면 분모가 NULL이 되어 결과도 NULL이 됩니다.

SET @target_views = 60;

SELECT
  id,
  title,
  view_count,
  ROUND(view_count / NULLIF(@target_views, 0), 2) AS view_ratio,
  @target_views AS target_views,
  ROUND(
    view_count * 100.0 / NULLIF(@target_views, 0),
    1
  ) AS target_percent
FROM ch4_calculation_post_demo
ORDER BY id;

id | title       | view_count | view_ratio | target_views | target_percent
1  | SELECT 기초 | 30         | 0.50       | 60           | 50.0
2  | WHERE 조건  | 75         | 1.25       | 60           | 125.0
3  | JOIN 계획   | 40         | 0.67       | 60           | 66.7

3번의 비율은 0.6667을 소수 둘째 자리까지 반올림한 0.67이고, 백분율은 66.66667을 소수 첫째 자리까지 반올림한 66.7입니다. 별칭에 비율(ratio)과 백분율(percent)이라는 단위가 드러나 있어, 받는 쪽이 두 값을 헷갈리지 않습니다.

SET @target_views = NULL;로 바꾸어 다시 실행하면 view_ratio와 target_views, target_percent가 모두 NULL이 됩니다. 조회수가 0이어서 0%인 경우와 목표가 없어서 계산할 수 없는 경우를 이렇게 구별할 수 있습니다.

2번처럼 125%가 된 값을 100%로 잘라 보여 줄지는 계산 문법이 아니라 제품이 정할 일입니다. 목표를 넘은 조회수를 그대로 남겨 두어야 분석에서 실제 반응을 볼 수 있고, 화면의 진행 막대만 100에서 멈추게 할 수도 있습니다. 저장값과 표시값을 나누어 두면 원래의 값을 손대지 않고 화면마다 다른 규칙을 적용할 수 있습니다.

저장해 둔 계산값이 어긋날 때

예전에 내보낸 데이터에 조회 비율을 캐시해 둔 값이 있다면, 저장된 조회수로 다시 계산한 값과 다른 행을 찾아야 합니다. 여기서는 스키마에 캐시 열을 더하지 않고, 예전 값을 CTE로 흉내 냅니다. WITH legacy_view_ratios AS (...)는 이 문장 안에서만 쓰는 이름 붙은 임시 결과를 만들고, 뒤의 SELECT는 그 이름을 테이블처럼 조인합니다. 괄호 안의 두 SELECT는 UNION ALL로 이어져 두 행이 됩니다. 반올림 자리에서 생기는 작은 차이는 무시하도록 0.01 이상 차이 나는 행만 찾습니다.

SET @target_views = 60;

WITH legacy_view_ratios AS (
  SELECT 1 AS id, CAST(0.50 AS DECIMAL(6,2)) AS cached_ratio
  UNION ALL
  SELECT 2, CAST(1.10 AS DECIMAL(6,2))
)
SELECT
  s.id,
  s.view_count,
  legacy.cached_ratio,
  ROUND(s.view_count / NULLIF(@target_views, 0), 2) AS calculated_ratio
FROM ch4_calculation_post_demo AS s
JOIN legacy_view_ratios AS legacy ON legacy.id = s.id
WHERE ABS(
  legacy.cached_ratio - ROUND(s.view_count / NULLIF(@target_views, 0), 2)
) >= 0.01;

id | view_count | cached_ratio | calculated_ratio
2  | 75         | 1.10         | 1.25

1번은 캐시와 계산값이 0.50으로 같아서 빠지고, 2번만 0.15만큼 어긋나 남았습니다. 이런 결과가 나오면 어느 값을 기준으로 삼을지 먼저 정한 뒤에 고칩니다. 계산값을 꼭 저장해야 한다면 생성 열(generated column)과 트리거, 애플리케이션의 이벤트 가운데 한 곳이 값을 맞추게 하고, 다시 계산하는 절차를 함께 둡니다.

비율과 금액에는 FLOAT보다 소수 자릿수가 정확한 DECIMAL이 맞는 경우가 많습니다. ROUND는 보여 줄 자릿수를 줄일 뿐, 저장 타입을 잘못 골라 쌓인 오차를 되돌리지 못합니다. 단위도 타입만큼 중요해서, 조회수와 고유 방문자 수를 같은 정수 열의 뜻으로 섞으면 멀쩡한 숫자처럼 저장되므로 열 이름과 API의 이름, 제약에서 단위를 고정합니다.

계산식을 시험하고 정하는 법

계산식의 시험에는 0과 1, 타입의 최댓값, NULL, 목표를 넘은 값을 넣습니다. BIGINT UNSIGNED의 범위를 넘는 곱셈이 더 넓은 타입으로 계산되는지, 그 결과를 좁은 열에 다시 저장할 때 범위 오류가 나는지도 따로 확인합니다.

보여 줄 때의 반올림과 정산할 때의 반올림은 시점이 다릅니다. 행마다 반올림한 뒤 더한 값과, 더한 뒤 반올림한 값은 다를 수 있으므로 업무가 어느 쪽을 요구하는지 적어 둡니다. 게시판의 통계는 원래의 조회수를 더한 뒤 마지막에 비율을 계산하는 편이 정보를 덜 잃습니다.

계산값을 API로 내보낼 때는 계산 방식의 버전도 생각합니다. 달성률의 규칙이 바뀌면 예전 응답과 지금 응답의 뜻이 달라지므로, 분석용 데이터에는 원래의 값과 계산식을 함께 남겨 다시 계산할 수 있게 합니다. 같은 식이 여러 화면에 되풀이되면 복사한 SQL을 하나씩 고치지 말고 뷰나 쿼리 계층으로 옮길 때인지 검토하되, 화면마다 반올림이 다르다면 원래의 조회수만 내주는 API가 더 안정적일 수 있습니다.

음수 조회수는 UNSIGNED나 CHECK로 저장할 때 막아야 하며, 조회할 때 ABS로 감추어서는 안 됩니다. 목표가 0인 행이 업무상 허용되는지도 먼저 정하는데, 0을 "목표 없음"으로 쓰면 NULL과 뜻이 겹치므로 되도록 NULL이나 별도의 상태로 나타냅니다.

이 단계의 회원 게시판 스키마에는 계산값을 새 열로 더하지 않습니다. 목표 조회수는 쿼리의 파라미터로, view_count는 저장된 사실로 두고, 달성률은 조회의 규칙에 둡니다. 나중에 통계 테이블을 설계할 때도 원래 조회수의 합계를 기준으로 삼으면 언제든 다시 계산할 수 있습니다.

먼저 SELECT의 계산식으로 정확성을 확인하고, 실제 비용이 드러난 뒤에 저장이나 캐시를 검토합니다. 계산하는 비용보다 어긋난 값을 바로잡는 비용이 더 클 수 있다는 점도 설계에 넣습니다.

정리

• 파생값은 되도록 최소한의 원래 사실에서 조회할 때 계산합니다.
• 별칭은 같은 SELECT 목록에서 다시 쓸 수 없으며, 단계를 나누려면 파생 테이블이나 CTE를 씁니다.
• MySQL에서 정수끼리의 /는 DECIMAL을 돌려주고, 0으로 나누면 SELECT는 NULL과 경고를 냅니다.
• 0과 NULL, 정밀도, 단위를 계산식의 시험 조건으로 다룹니다.
• 계산값을 저장한다면 값을 맞출 한 곳과 다시 계산하는 절차를 함께 둡니다.

연습 문제

1. 남은 조회수와 달성률

공개 게시글마다 목표까지 남은 조회수와 달성률을 구해 보세요. 목표를 넘으면 남은 조회수는 0으로 보여 주되, 달성률은 100을 넘는 실제 값을 남깁니다. 목표가 NULL이거나 0이면 두 결과 모두 NULL이어야 합니다.

SET @target_views = 60;

SELECT
  id,
  CASE
    WHEN @target_views IS NULL OR @target_views = 0 THEN NULL
    ELSE GREATEST(@target_views - CAST(view_count AS DECIMAL(20,0)), 0)
  END AS remaining_views,
  CASE
    WHEN @target_views IS NULL OR @target_views = 0 THEN NULL
    ELSE ROUND(view_count * 100.0 / @target_views, 1)
  END AS target_percent
FROM ch4_calculation_post_demo
WHERE status = 'PUBLISHED'
ORDER BY id;

id | remaining_views | target_percent
1  | 30              | 50.0
2  | 0               | 125.0

2. 0으로 나누면

SET @target_views = 0;으로 바꾸고, 본문의 달성률 쿼리와 NULLIF를 뺀 view_count / @target_views를 각각 실행하면 어떻게 다른가요?

3. 반올림의 시점

1번과 3번의 비율을 각각 소수 둘째 자리까지 반올림한 뒤 더한 값과, 두 조회수를 먼저 더해 비율을 구한 뒤 반올림한 값을 비교해 보세요. 목표는 60입니다.

다음 글 문자열·NULL·날짜 함수에서는 문자열과 NULL 대체, 날짜 함수가 보여 주는 값과 저장된 값의 뜻을 각각 어떻게 바꾸는지 다룹니다.