PostgreSQL ROUND 함수 데이터형

PostgreSQL

ROUND function 시 데이터형을 명시해야 정확한 값이 나온다.

(예시)

SELECT ROUND((1412/231),2)
FROM 스키마.테이블;

--결과 : 6.00
SELECT ROUND((1412/231::numeric),2)
FROM 스키마.테이블;

--결과 : 6.12
SELECT round((1412::numeric/231),2)
FROM 스키마.테이블;

--결과 : 6.12
SELECT round((1412::numeric/231::numeric),2)
FROM 스키마.테이블;

--결과 : 6.12

비교 연산자(!=)와 NULL, 발생 가능한 실수

비교 연산자에서는 NULL이 배제된다.
(반면 논리 연산자에서는 NULL이 배제되지 않는다.)

 

가령,

Query 1.

SELECT *
FROM A FULL OUTER JOIN B ON ~
WHERE A.컬럼1 = ‘값1’ OR B.컬럼2 = ‘값2’

 

위 Query 1. 결과는 아래 Query 2.  결과와 일치하지 않는다.

 

Query 2.

SELECT *
FROM A FULL OUTER JOIN B ON ~
WHERE A.컬럼1 != ‘값1’ AND B.컬럼2 = ‘값2’

UNION

SELECT *
FROM A FULL OUTER JOIN B ON ~
WHERE A.컬럼1 = ‘값1’ AND B.컬럼2 != ‘값2’

(A, B의 컬럼 차수 및 데이터형은 일치한다고 가정한다)

 

A.컬럼1 과 B.컬럼2 의 NULL 값이 결과에서 배제되기 때문에
해당 컬럼의 NULL 존재 여부에 따라서 Query 2. 의 결과 수가 Query 1.보다 적을 수도 있다.

비교 연산자에서 NULL이 배제된다는 점을 고려했을 때,
Query1 과 동일한 결과를 도출하기 위해서 올바른 Query는 다음과 같다.

 

Query 3.

SELECT *
FROM A FULL OUTER JOIN B ON ~
WHERE A.컬럼1 = ‘값1’ AND (B.컬럼2 != ‘값2’ OR B.컬럼2 IS NULL)

UNION

SELECT *
FROM A FULL OUTER JOIN B ON ~
WHERE (A.컬럼1! = ‘값1’ OR A.컬럼1 IS NULL) AND B.컬럼2 = ‘값2’

(A, B의 컬럼 차수 및 데이터형은 일치한다고 가정한다)

 

결론 : 비교 연산자와 논리 연산자를 함께 사용할 경우, NULL 처리에 유의하자!

GROUP BY 절의 ALIAS 사용 제한 시 대안

GROUP BY 절

  1. GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용한다.
  2. 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다.
  3. GROUP BY 절에서는 SELECT 절과는 달리 ALIAS 명을 사용할 수 없다.
  4. 집계 함수는 WHERE 절에는 올 수 없다. (집계 함수를 사용할 수 있는 GROUP BY 절보다 WHERE 절이 먼저 수행된다)
  5. WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거시킨다.
  6. HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시할 수 있다.
  7. GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력한다.
  8. HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치한다.

 

☞ 대안 :

특수한 상황에 의해 반드시 ALIAS를 GROUP BY 절에 사용해야할 경우,

      FROM 절에 Inline View를 생성 →  별칭을 제거 →  GROUP BY 실행

 

 

PostgreSQL 과 R 연결하기 (R Studio)

R Studio에서 다음 코드 실행

> install.packages(“DBI”)

> install.packages(“odbc”)

> install.packages(“RPostgreSQL”)

# 설치
> require(“RPostgreSQL”)

>con<-dbConnect(dbDriver(“PostgreSQL”), dbname=”dbname“, host=”localhost”, port=5432, user=”user_name“,password=”password“)

> dbListTables(con)
# 접속

 

출처 : https://stackoverflow.com/questions/40642657/connecting-r-to-postgresql-database

국가공인 SQL 개발자 자격증 취득

자 격 번 호 : SQLD – 0310126
합 격 일 자 : 2018년 12월 28일
유 효 기 간 : 2018.12.28 ~ 2020.12.27
시험 범위 중 데이터 모델링 파트가 난이도가 있었다.
’19년 하반기나 내후년 중 여유가 있으면 SQL 전문가 자격증도 취득할 생각이다.

ROLL UP 활용한 개수 및 비율 구하기

테이블 내 집계 관련 파생속성이 주어지지 않았을 때 아래 SQL문은 유용하다.

 

SELECT CASE WHEN 컬럼1 IS NULL THEN ‘총계’ ELSE 컬럼1 END ‘컬럼명, ROUND(COUNT(*) “개수”, 200*COUNT(*)/SUM(COUNT(*))OVER(),2) “비율”
FROM (SELECT NVL(컬럼, ‘NULL’) 컬럼1 FROM 테이블)
GROUP BY ROLLUP(컬럼1)
ORDER BY CASE ~

 

tip1. 200을 곱하는 이유는 ROLLUP 때문이다.
tip2. 인라인뷰에서 NVL 처리를 해주는 것이 ROLLUP 총계 표시를 위해 바람직하다.

DB 서버가 해외에 있을 때 발생 가능한 실수

2018년 11월 기준, 국내 외산 데이터센터 현황은 다음과 같다.

2016년 1월 (설립) – AWS
2017년 2월 (설립) – MS
2019년 5월 (예정) – Oracle
2019년 중   (추진) – Google

외산 클라우드 솔루션을 사용 시 데이터센터가 해외에 위치할 경우,
쿼리 과정에서 식별한 (사소하지만 critical한) 문제가 있어 공유한다.

타겟팅 추출하는 과정에서 현 시간 날짜 속성을 자주 사용한다.
가령 ‘최근 2주’라는 조건이 있다고 가정해보자.

참고사항으로
1. 데이터센터의 소재지는 한국과 16시간 차이나는 미국이며
2. 클라우드 솔루션의 VM이 데이터센터와 같은 곳에 위치해있다.
3. 달력을 보니 오늘 날짜가 2018년 5월 1일이다.

이 경우 쿼리는 다음과 같이 구성할 수 있다.

WHERE …. 날짜컬럼 BETWEEN SYSTIMESTAMP-14+16/24 AND SYSTIMESTAMP+16/24

(참고사항 2번으로 인해 CURRENT_TIMESTAMP와 SYSTIMESTAMP는 같으므로
SYSTIMESTAMP 대신 CURRENT_TIMESTAMP를 사용해도 결과는 같다)

문제가 없어 보인다. 문제를 찾으려고 하지 않을 만큼 매우 쉬운 쿼리다.

자, 이제 몇 달이 흘러 11월 28일이다. 쿼리 결과를 다시 확인해보았다.

그런데?

1시간이 오차가 있는 것이다. 왜일까?

Summer Time을 고려하지 않아서이다. 다음은 2018년 기준 미국 LA의 Summer Time 적용 시간 변화다.

LA_SUMMER_TIME

맨 처음 쿼리가 작성된 시점이 PDT (UTC-7h)이고, 마지막으로 쿼리 결과를 확인한 시점이 PST (UTC-9h)이다.  이 Summer Time 적용을 고려했을 때, 쿼리는 다음과 같이 수정되어야 한다.

수정 전
WHERE …. 날짜컬럼 BETWEEN SYSTIMESTAMP-14+16/24 AND SYSTIMESTAMP+16/24

수정 후
WHERE …. 날짜컬럼 BETWEEN SYS_EXTRACT_UTC(SYSTIMESTAMP)-14+9/24 AND SYS_EXTRACT_UTC(SYSTIMESTAMP)+9/24

 

결론
국가 간 시간 차를 반영할 때 표준시간을 기준으로 생각하고
SYS_EXTRACT_UTC() function을 사용하자!

 

참고
https://www.timeanddate.com/time/zone/usa/los-angeles

Window Function

1. 왜 Window인가?

  • Window function의 목적 : 행과 행 간의 관계를 쉽게 표현(정의/비교/연산)하기 위함
  • 나오게된 배경 : 기존의 RDBMS는 컬럼 간(column-column) 관계를 다루었음. 행 간의 관계를 다루기 위해서는 PL/SQL 또는 인라인 뷰(Inline View)를 활용해야 했음. 이 과정의 불편함을 해소하기 위해 window function이 출시됨
  •  ‘Window’ 명칭 : 행과 행 간의 관계를 다루는 과정에서 필연적으로 관계의 범위를 한정해야 함. 이것이 행을 둘러싼 프레임(frame) 또는 창(window) 같다고 하여 ‘window’란 명칭이 부여됨 (아래 그림1 참고)
  • OVER 절 : 범위를 한정시키는 문법 규칙은 후술되는 OVER 절에 귀속됨. 결국 OVER절을 얼마나 잘 이해하고 사용하느냐가 Window function의 관건임 (WINDOW_FUNCTION은 기본)

    (참고 그림1)
    windows_function.PNG

2. 문법

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ( [PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절] ) FROM 테이블 명;

*OVER 절은 필수. 구성 요소는 다음과 같음 :
– PARTITION BY 절 : 전체 집합을 기준 (컬럼) 에 의해 소그룹으로 구분
– ORDER BY 절 : 어떤 항목 (컬럼) 에 대해 순위를 지정할 지 기술
– WINDOWING 절 :  함수의 대상이 되는 행 기준의 범위를 강력하게 지정

WINDOWING 절
ROWS 는 물리적인 결과 행의 수, RANGE 는 논리적인 값에 의한 범위
2 中 1 택

 BETWEEN 사용 타입
 ROWS | RANGE BETWEEN
 UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING
 AND
 UNBOUNDED FOLLOWING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING

 BETWEEN 미사용 타입
 ROW | RANGE
 UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING

3. WINDOW_FUNCTION 종류

  • 순위함수 : RANK, DENSE_RANK, ROW_NUMBER
    *동일 값에 대해서는 동일 순위를 매김
  • 집계함수 : SUM, MAX, MIN, AVG, COUNT
  • 순서함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD
  • 비율함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT
  • 선형 분석을 포함한 통계 분석 관련 함수

 

참고
https://en.wikipedia.org/wiki/SQL_window_function
http://www.dbguide.net/db.db?cmd=view&boardUid=148205&boardConfigUid=9&categoryUid=216&boardIdx=135&boardStep=1
http://wiki.gurubee.net/pages/viewpage.action?pageId=27427796

DB 처음 접속 시 자주 사용되는 쿼리

1.  접근 가능한 테이블 목록 조회

SELECT *
FROM all_tables;

 

2. 테이블 간단 설명 조회 (컬럼/Nullable/Type)

DESC 테이블명

 

3. 해당 테이블 스키마 조회

SELECT *
FROM all_tab_columns
WHERE table_name=’테이블명‘;

 

4.  테이블 별 제약조건 조회

SELECT *
FROM user_constraints
WHERE table_name=’테이블명‘;