[TeraDate] Random 함수
[TeraData] Extract 추출
EXTRACT : 추출
EXTRACT([YEAR|MONTH|DAY|HOUR|MINUTE|SECOND] FROM date-value)
보면 알겠지만 date-value에서 원하는 부분을 추출한다.
예)
EXTRACT(YEAR FROM DATE'2010-12-20' + 30) = 2011
EXTRACT(MONTH FROM DATE'2010-12-20' - 30) = 11
EXTRACT(DAY FROM DATE'2010-12-20' + 30) = 19
EXTRACT(HOUR FROM TIME'10:20:30') = 10
EXTRACT(MINUTE FROM TIME'10:20:30') = 20
EXTRACT(SECOND FROM TIME'10:20:30') = 30
연, 달, 일은 DATE, 시, 분, 초는 TIME
[출처] [Teradata] EXTRACT|작성자 Ehne
[TeraData] Sample 함수
오라클의 샘플함수와 사용방법이 다르다.
SAMPLE {n[, n2, n3...] | d[, d2, d3...]}
건수와 비율로 설정할 수 있는데
정수일 경우 건수, 소수(0~1)일 경우 비율건수다.
예)
SELECT * FROM table SAMPLE 10;
=> 10건의 행을 랜덤하게 추출한다.
SELECT * FROM table SAMPLE 0.1;
=> 10%건의 행을 랜덤하게 추출한다. 0.1대신 .1로 써도 된다.
=> %결과가 소수점이 나오는 경우 .4999 이상이면 올림된다. => 반올림 된다는 말.
SAMPLEID라는 키워드를 사용할 수 있다.
예) SELECT cols, SAMPLEID FROM table, SAMPLE 2, 3, 1 ORDER BY SAMPLEID;
결과)
cols SAMPLEID
---- ---------
a 1
b 1
ab 2
c 2
abb 2
ccc 3
총 행 수 보다 SAMPLE에서 뽑아내는 행 수가 더 많으면 경고가 발생한다.
Warning: 7473 Requested sample is larger than table row. All rows returned.
소수 값의 합이 1을 넘거나 정수와 소수를 섞어 사용하면 실패한다.
Failure 5473 SAMPLE clause has invalid set of arguments.
SAMPLE절에 WITH REPLACEMENT를 함께 쓰면 결과에 중복값도 포함된다. (확실치 않음)
예) SELECT cols FROM table SAMPLE WITH REPLACEMENT 5;
결과)
cols
----
a
a
b
ab
b
다음과 같은 경우에는 의도와는 다른 결과가 나온다.
SELECT SUM(col) FROM table SAMPLE 10; // 10개의 샘플을 뽑아 더할 것을 의도했다.
그러나 위의 쿼리는 전체 테이블의 SUM값이 나온다.
Warning: 7473 Requested sample is larger than table rows. All rows returned
그럼 어떻게 처리해야할까?
SELECT SUM(col) FROM (SELECT col FROM table SAMPLE 10);
위와 같이 10개의 결과를 가지는 서브쿼리(테이블)를 만들어 SUM시키면 된다.
이렇게도 쓸수있다. RANDOMIZED ALLOCATION - 무슨 기능인지 몰라서 설명은 패스...
SAMPLE WITH REPLACEMENT RANDOMIZED ALLOCATION 4, 5
그룹별 샘플링
예)
SELECT * FROM table
SAMPLE
WHEN col BETWEEN 0 AND 10 THEN 0.2
WHEN col BETWEEN 10 AND 100 THEN .4
WHEN col BETWEEN 100 AND 1000 THEN 0.25, 0.1
END
ORDER BY col;
=> col이 0~10인 그룹에서 20%건, 10~100인 그룹에서 40%건, 100~1000인 그룹에서 25%, 10%건을 추출한다.
예2)
SELECT * FROM table
SAMPLE WITH REPLACEMENT
WHEN col < 100 THEN 10
WHEN col < 200 THEN 5, 5
ELSE 1, 2, 3
END;
[출처] [Teradata] SAMPLE 함수|작성자 Ehne
[TeraData] CAST 함수 (데이터 타입 변환)
다른 DB환경에서도 사용 가능한 것 같지만 일단 Teradata 교제에서 나오는 내용이니 Teradata로 분류
Char -> DATE
CAST('20100101' AS DATE FORMAT 'YYYYMMDD')
=> 20100101 // DATE형 - 출력 포맷은 툴에 따라 다를 수 있음.
Char -> Numeric
CAST('1234' AS DECIMAL(4, 2) FORMAT '9(2)V9(2)');
=> 12.34 // Numeric Overflow 주의. 가능한 명확한 포맷을 지정
Char -> TIME
CAST('01:00:00' AS TIME(0))
=> 01:00:00 // TIME(n)에서 n은 초에 대한 정밀도이다. 따라서 주어진 Char의 초의 정밀도가 n 이하여야 한다.
Char -> TIMESTAMP
CAST('2010-01-01 01:01:01.12345' AS TIMESTAMP(5));
=> 2010-01-01 01:01:01.12345 // TIMESTAMP(n)에서 n은 TIME과 마찬가지다.
DATE -> Char
CAST(CURRENT_DATE AS CHAR(10))
=> 2013-04-10
CAST(CAST(CURRENT_DATE AS FORMAT 'YYYY/MM/DD') AS CHAR(10)); // 결과 포맷을 다르게 하기 위해서 AS FORMAT을 추가
=> 2012/03/29
DATE -> TIMESTAMP
CAST(DATE '2010-01-01' AS TIMESTAMP(1));
=> 2010-01-01 00:00:00.0
Numeric -> Char
CAST(12345 AS CHAR(5))
=> 12345 // 명시적으로 CAST를 사용하지 않을 경우 일부 데이터에 대해서 truncation이 발생할 수 있으므로 주의
Numeric -> Numeric
CAST((A * B) / C AS DECIMAL(15, 2)); // A, B, C가 DEC(8, 2)일 때 결과값을 DEC(15, 2)로 변환하는 경우
TIMESTAMP -> Char
CAST(CURRENT_TIMESTAMP AS CHAR(26));
=> 2013-04-10-18.25.31.210000
CAST(CAST(CURRENT_TIMESTAMP AS FORMAT 'YYYY/MM/DDBHH:MI:SS.S(6)') AS CHAR(26));
=> 2013-04-10 18:25:31.210000 // 결과 포맷을 다르게 하기 위해서 AS FORMAT을 추가
TIMESTAMP -> DATE
CAST(CURRENT_TIMESTAMP AS DATE);
=> 2013-04-10
CAST(TIMESTAMP '2010-01-01 01:02:03' AS DATE);
=> 2010-01-01
[출처] [Teradata] CAST 함수 (데이터 타입 변환)|작성자 Ehne
[SQL] NULL 관련 함수
NVL(c, v) : c가 NULL이 아니면 NULL, NULL이면 v를 반환.
NVL2(c, v1, v2) : [Oracle] c가 NULL이 아니면 v1, NULL이면 v2를 반환.
NULLIF(c1, c2) : c1 <> c2 이면 NULL, c1 = c2 이면 c1을 반환.
COALESCE(c1 [, c2, c3, ...], v) : c1이 NULL이 아니면 c1. c1이 NULL이면서 c2가 NULL이 아니면 c2. c2도 NULL이면 c3...
결국 끝까지 NULL이면 v를 반환.
[출처] [SQL] NULL 관련 함수|작성자 Ehne
[TeraData] IN, EXISTS 제공자와 확인자
IN은 제공자 EXISTS는 확인자 역할을 한다.
IN은 먼저 서브쿼리를 분석해와서 메인쿼리에게 비교하기 위한 값들을 제공하고
EXISTS는 단지 존재하는지만 확인해주는 역할을 한다.
IN과 EXISTS의 가장 큰 차이는 찾으려는 값에 NULL이 있을 때다.
IN은 다음과 같이 동작한다. subquery1의 반환값이 'A', 'B', 'C'라고 가정
'A' IN (subquery1)
=> 'A' IN ('A', 'B', 'C')
=> 'A' = 'A' OR 'A' = 'B' OR 'A' = 'C'
=> true OR false OR false
=> true
따라서 NOT IN은
'A' NOT IN (subquery1)
=> 'A' NOT IN ('A', 'B', 'C')
=> 'A' <> 'A' AND 'A' <> 'B' AND 'A' <> 'C'
=> false AND true AND true
=> false
이번에는 subquery2의 반환값에 NULL이 있다고 가정
'A' IN (subquery2)
=> 'A' IN ('A', 'B', NULL)
=> 'A' = 'A' OR 'A' = 'B' OR 'A' = NULL
=> true OR false OR ?
=> ? // 결과적으로는 false와 같아진다.
그렇다면 NOT IN은
'A' NOT IN (subquery2)
=> 'A' NOT IN ('A', 'B', NULL)
=> 'A' <> 'A' AND 'A' <> 'B' AND 'A' <> NULL
=> false AND true AND ?
=> ? // NULL이 포함된 결과와 IN, NOT IN을 한다면 둘다 ? 이므로 false나 마찬가지.
즉, false는 아니지만... NULL이 있을 때는 IN 과 NOT IN의 결과가 같다.
중요한 점은. NULL값이 있으면 서브쿼리가지고 뭔가를 하려고 했던 메인쿼리에서 아무것도 할 수가 없다.
다음은 EXISTS. subquery3에서 조건에 의해 찾은 값이 있다고 가정 (찾은 값이 NULL이더라도)
subquery3 AS (SELECT NULL FROM table WHERE cmp) // 조건 cmp에 의해 찾아도 결과는 항상 NULL이다. 그러나 찾았다는게 중요
EXISTS (subquery3)
=> EXISTS (찾았다!)
=> true
따라서 NOT EXISTS는
NOT EXISTS (subquery3)
=> NOT EXISTS (찾았다!)
=> false
즉, 조건만 맞으면 결과가 NULL이든 뭐든 true or false로 나눠진다.
추가적으로
IN을 쓸 때는 서브쿼리 범위가 작아야 유리하다. 서브쿼리 전체를 뒤지기 때문(?)
EXIEST를 쓸 때는 메인쿼리 범위가 작아야 유리. 서브쿼리는 있는지만 확인 (찾을 때까지만 돌고 있으면 끝남)
IN을 사용할 때는 NULL값이 있는지 확인하고 처리 해줘야 한다.
[SQL] NULL 관련 함수 참고
[출처] [SQL] IN과 EXISTS (NOT IN과 NOT EXISTS)|작성자 Ehne
[TeraData] 중복 가능한 테이블 생성하기 (MULTISET)
Teradata에서 테이블을 만들 때 기본적으로 중복값을 허용하지 않게 만들어진다.
SET / MULTISET이 중복 허용을 설정하는 키워드다.
MULTISET을 지정하면 테이블의 중복행을 허용한다. (즉 SET이 기본값이다.)
예)
CREATE MULTISET TABLE table
(
...
)
UNIQUE PRIMARY INDEX(col1, col2);
반대로 SET을 지정하면 테이블의 중복행을 허용하지 않는다.
기본값이므로 생략해도 된다.
[출처] [Teradata] SET / MULTISET|작성자 Ehne
[TeraData] 수학 함수
ABS(arg) : 절대값
예) ABS(-1.24) = 1.24;
EXP(arg) : 오일러 상수 e의 arg제곱
예) EXP(3) = e3
LOG(arg) : 밑이 10인 로그 값
예) Log(100) = Log(_10) 100 = 2
LN(arg) : 밑이 e인 로그 값
예) Log(10) = Log(_e) 10
SQRT(arg) : 2제곱근 값
예) SQRT(10000) = √(10000) = 100
[출처] [Teradata] 수학 함수|작성자 Ehne
[TeraData] 데이터 타입
문자
CHARACTER(n) or CHAR(n)
n bytes (최대 길이는 아직 잘 모르겠음;)
n 만큼의 고정 길이를 가진다.
VARCHAR(n)
0 ~ n bytes (사실 잘 모르겠다...)
저장된 문자열의 길이에 따라 길이가 달라진다. = 가변 길이
CLOB(n[K|M|G]) // Charactor Large OBject // KMG는 킬로 메가 기가
정말 큰 문자를 다룰 때 쓴다. 저장공간은 이름만 봐도 알 듯.
예) CLOB(3200), CLOB(64K), CLOB(128M), CLOB(2G) // Fixed length up to 2GB in size.
----------------------------------------------------------------------------------------------------------
정수
BYTEINT (ANSI 표준은 아님)
1 byte
-128 ~ 127 (4자)
SMALLINT
2 bytes
-32,768 ~ 32,767 (6자)
INTEGER (INT)
4 bytes
-2,147,483,648 ~ 2,147,483,647 (11자)
BIGINT
8 bytes
-9,233,372,036,854,775,807 ~ (길어서 생략. 안적어도 알 듯.) (20자)
----------------------------------------------------------------------------------------------------------
실수
DECIMAL(m, n) or DEC(m, n) or NUMERIC(m, n) // m은 유효숫자, n은 소수 자릿수
m = 1 ~ 2 : 1 byte
m = 3 ~ 4 : 2 bytes
m = 5 ~ 9 : 4 bytes
m = 10 ~ 18 : 8 bytes
m = 18 ~ 38 : 16 bytes // m은 38이 최대값이다.
FLOAT
2 x 10-307 ~ 2 x 10308 // 무지 큰 수도 표현할 수 있지만 정밀도는 15자리다.
----------------------------------------------------------------------------------------------------------
바이트
BYTE(n), VARBYTE(n)
n = 1 ~ 64,000
BLOB(n[K|M|G]) // Binary Large OBject // Fixed length up to 2GB in size.
예) BLOB(3200), BLOB(32K), BLOB(32M), BLOB(2G)
----------------------------------------------------------------------------------------------------------
날자
DATE
4 bytes // 내부적으로는 INTEGER. 따라서 숫자 연산이 가능.
ANSI 표준 표현은 'YYYY-MM-DD'이며
최소값 '0001-01-01', 최대값 '9999-12-31'이다.
기타 등등
INTERVAL YEAR(n)
INTERVAL MONTH(n)
INTERVAL DAY(n)
INTERVAL YEAR(n) TO MONTH
INTERVAL HOUR(n)
INTERVAL MINUTE(n)
INTERVAL SECOND(n)
INTERVAL DAY(n) TO HOUR
INTERVAL DAY(n) TO MINUTE
INTERVAL DAY(n) TO SECOND
INTERVAL HOUR(n) TO MINUTE
INTERVAL HOUR(n) TO SECOND
INTERVAL MINUTE(n) TO SECOND
TIME(n)
TIME(n) WITH TIME ZONE
TIME(n) WIDTH TIME ZONE TIMESTAMP(n)
TIMESTAMP(n) WITH TIME ZONE
TIMESTAMP WITH TIME ZONE
출처 - http://blog.naver.com/nitrogehne?Redirect=Log&logNo=150165784902