SQLite 통계 작성 값이 0이 나오는 경우 (feat. 정수 나눗셈)

안녕하세요.

요즘 앱 개발을 하면서 통계 쿼리를 작성할 일이 있었습니다.

보통은 조회 쿼리도 ORM을 통해서 처리하지만 통계만큼은 SQL문을 작성하는 게 더 편하더군요.

작성한 통계 자체는 복잡하지 않고 결과도 백분율(0~100%)로 나오는 쿼리였습니다.

그런데 막상 작성해 놓고 테스트를 해보니 결과가 0 아니면 100만 나오는 거 아니겠습니까?

어째서 결과가 0?

그래서 디버깅을 위해서 백분율 쿼리를 단순화(하드코딩) 시켜서 접근해 봤습니다.

백분율 쿼리라는 게 결국 (분자 / 분모) * 100으로 계산하니 아래와 같이 돌려봤는데..

SELECT (3 / 4) * 100;

결과는 0이 나왔습니다. (이럴 수가..??)

DBMS마다 나눗셈을 처리하는 방식이 다르다

기존에 오라클을 사용하면서 전혀 겪어보지 못한 문제점이라 당황하면서 구글링을 시작합니다.

구글링 후 결론은 SQLite가 정수와 정수 간의 나눗셈을 정수형 결과로 반환해 주는 게 문제더군요.

일반적으로 JAVA 같은 프로그래밍 언어의 정수 나눗셈과 비슷한 결과를 반환합니다.

다른 DBMS들은 어떤 결과를 반환해 주는지 온라인 SQL 에디터들을 통해서 확인해 봤는데요.

<Oracle 결과>

오라클은 제가 앱 개발 전에 오랫동안 사용해 온 DBMS입니다.

0.75로 실수형 결과로 반환합니다. (믿고 있었다고!)

 

<MariaDB 결과>

MariaDB(MySQL) 역시 실수형 결과로 반환합니다.

 

<SQLite 결과>

SQLite는 정수형 결과로 반환합니다.

 

<PostgreSQL 결과>

PostgreSQL은 오라클과 호환성이 좋은 편이라 실수형을 예상했는데 정수형 결과로 반환합니다.

 

<MS SQL 결과>

MS SQL 역시 정수형 결과로 반환합니다.

그래서 해결은?

해결방법은 프로그래밍 언어든 DBMS든 정수형과 실수형을 연산하면 결과는 실수형이 되기 때문에 분모나 분자를 실수형으로 바꿔주는 방식을 선택했습니다.

 

SQLite에서 실수형으로 바꾸는 방법은 CAST 연산자를 사용하는 방법과 1.0을 곱하는 방법이 있습니다.

 

[CAST 연산자 사용]

SELECT CAST(3 AS REAL) / 4; --결과 : 0.75

[1.0을 곱하기]

SELECT 1.0 * 3 / 4; --결과 : 0.75

저는 1.0을 곱하는 방법으로 처리를 했습니다.

마무리

기본적으로 다양한 DBMS들이 ANSI SQL*을 따르기 때문에 SQL문이 비슷합니다. 하지만 본인들의 DBMS에 ANSI SQL에서 벗어난 기능을 더 추가하거나, 같은 표준이라도 다르게 구현을 하여 결과가 미세하게 다른 부분들이 있습니다. 따라서 프로그래밍 언어뿐만 아니라 DBMS를 갈아탈 때도 이러한 점을 유의해야 할 것 같습니다.

 

[ChatGPT]
ANSI SQL*이란?

ANSI SQL은 SQL(Structured Query Language)의 표준 규격을 정의하는 표준화 기구인 ANSI(American National Standards Institute)가 제정한 SQL 표준 규격을 의미합니다. 이 표준은 ANSI/ISO/IEC SQL 표준이라고도 합니다.

SQL은 관계형 데이터베이스 관리 시스템(RDBMS)에서 사용되는 쿼리 언어로, 데이터베이스에서 데이터를 조작하고 검색하는 데 사용됩니다. 그러나, 다양한 DBMS 제조사들이 각자 독자적인 SQL 구문을 사용하고 있어, 이로 인해 SQL 문법이 서로 다르고 호환성이 떨어지는 문제가 발생하였습니다.

이러한 문제를 해결하기 위해 ANSI SQL 표준이 제정되었습니다. ANSI SQL 표준은 SQL 언어의 기본적인 문법과 데이터 타입, 함수, 연산자, 키워드 등을 통일화하여 SQL 언어를 사용하는 모든 DBMS에서 동일한 구문을 사용할 수 있도록 하는 것을 목적으로 합니다.

또한, ANSI SQL 표준은 SQL의 표준 규격이므로, 표준 규격에 따르는 DBMS 제조사들은 서로 호환성이 높아지며, 이는 다른 DBMS 제조사의 제품으로 데이터 이전이나 연동을 할 때도 큰 장점이 됩니다.

따라서, ANSI SQL은 SQL을 사용하는 모든 사용자들에게 중요한 개념이며, SQL 문법을 학습하거나 DBMS를 선택할 때 반드시 고려해야 할 사항 중 하나입니다.