Tip & Tech

Sql server 에서는 Random sampling 을 위해서 newid() 라는 함수를 주로 사용하는데 이 함수는 각 행별로 GUID (globally unique identifier) 값을 만들어 낸다. 이것은 중복이 없는 값이고 호출할때 마다 다른 값을 리턴한다.
따라서 order by 절을 이용하여 정렬하면 항상 다른 행을 리턴하게 된다.
랜덤 샘플링을 위해서 newid() 함수를 이용한 데이터 selection 은 일반적으로 제시되는 방법이다. 하지만 테이블이 매우 크거나 빠른시간내에 결과값이 나와야 하는경우 이 방법은 치명적인 약점을 가지고 있다. 전체 데이터에 대한 guid 생성과 이를 다시 정렬하여 상위 몇개를 뽑아 내는것은 보통 부하를 유발하는 것이 아니다.

우선 newid() 함수를 이용한 random sampling 을 알아보고 성능을 고려한 상위 몇개의 데이터를 추출하는 쿼리를 알아보자.


newid() 를 이용한 구문

 
SELECT TOP 1 col1 FROM table ORDER BY NEWID()
 

위 쿼리를 실제 테이블에 적용하여 실행계획을 확인한 것이다. Clustered index 를 순차적으로 스캔하면서 newid() 값을 만들고 전체 데이터에 대해 정렬을 하는데 정렬하는 비용이 장난이 아니다.



성능확보를 위한 트릭

 
SELECT TOP 1 col1 FROM table WHERE ascii(substring(col1, cast(floor(rand()*10) as int), 1))=97
 


위 쿼리의 실행계획을 보면 데이터 정렬과정이 없다. 따라서 매우 빠르게 상위 몇개의 결과값을 가져올수 있다. 위 쿼리에 대해 설명을 하자면 col1 이라는 column 에 대해서 cast(floor(rand()*10) as int) 에서 나온 integer 값부터 1 자리를 잘라서 ascii 값을 취할때 97(소문자 a) 값이 나오는 데이터를 가져 오라는 조건이다. cast(floor(rand()*10) as int) 에 의해 실행시마다 substring 의 start 값이 변하기 때문에 어느정도 random 결과값을 구할수 있다.
위 쿼리는 단순한 로직이기 때문에 전체 테이블에서 랜덤하게 결과값이 나오진 않는다. clustered index scan 을 하면서 매치된 결과값을 가져오기 때문에 col1 에 입력된 길이가 짧고 단순한 값이면 몇번을 수행하면 같은 값이 리턴 될수 있다.




CTE (Common Table Expression) 이란?

임시로 이름이 지정된 결과 집합을 지정한다. CTE는 단순 쿼리에서 파생되며 반드시 SELECT, INSERT, UPDATE 또는 DELETE 문중 하나와 같이 쓰인다. 자신에 대한 참조를 포함하는 재귀 쿼리가 가능하고 CREATE VEIW 의 생성에 사용할수 있다. 

기존에 파생 테이블을 위해서 임시테이블이나 테이불 반환함수 또는 뷰를 사용하여야 했으나 CTE 를 이용하면 파생데이터에 대한 정확한 정의를 직관적으로 확인할수 있다.

상하, 선후 관계를 나타내기 위해 계층적 쿼리, 즉 재귀적 퀴리가 필요할경우 CTE 가 매우 유용하다. 

중복데이터 처리에 유용하다.

CTE 는 sql server 2005 버전부터 지원된다.


CTE 과 VIEW 의 차이점

VIEW 는 인덱스를 생성할수 있으나 CTE 는 인덱스를 생성할수 없다.

VIEW 는 데이터베이스 안에서 물리적 객체로 존재하기 때문에 나 이외의 다른 사용자들과 객체를 공유하여 쿼리할수 있기 때문에 유연하고 데이터베이스 단위에서 관리 된다.

CTE 테이블은 일회용 뷰와 비슷하다.


CTE 구문

 
[ WITH <common_table_expression> [ ,...n ] ]  

<common_table_expression>::=  
    expression_name [ ( column_name [ ,...n ] ) ]  
    AS  
    ( CTE_query_definition ) 
 

expression_name : CTE 이름

column_name : AS 이하 쿼리에서 리턴할 칼럼들의 이름, 지정하지 않을경우 AS 이하 쿼리문의 첫번째 쿼리 칼럼명을 그래도 사용한다.

CTE_query_definition : 일반적인 뷰를 만들때 요구사항과 같은 제약을 갖는 SELECT 쿼리문을 사용할 수 있다.

CTE_query_definition 다음에 콤마(,) 로 구분하여 여러개의 CTE 를 하나의 WITH 구문 아래 작성하고 그아래  SELECT, INSERT, UPDATE 또는 DELETE 문중에서 사용할수 있다.

 

CTE 를 이용한 데이터 INSERT

사실상 기존 쿼리를 이용한 insert 방법과 큰 차이가 없다. CTE 자체가 다회사용이 불가능하기 때문에 기존 쿼리기반 insert....select... 를 대체할 필요를 느끼지 못한다. 

굉장히 복잡한 쿼리가 있어서 쿼리 내에서 CTE 를 여러번 참조해야 할 경우 유용할 수 있다

 
--CTE 를 이용한 INSERT

WITH test AS (

SELECT province, count(*) province_count

FROM a

GROUP BY province

)

INSERT INTO dbo.c (province, province_count)

SELECT * FROM test;
 
-------------------------------------------------------------

--기존 쿼리를 이용한 INSERT

INSERT INTO dbo.c (province, province_count)

SELECT province, count(*) province_count

FROM a

GROUP BY province



CTE 를 이용한 데이터 업데이트


 


if OBJECT_ID('test') is not null
DROP TABLE test
go
create table test(state varchar(100), city varchar(100), zipcode varchar(100))

insert into test(state, city, zipcode) values('GA','Peachtree City','30269-3428');
insert into test(state, city, zipcode) values('GA','Stockbridge','30281-5092');
insert into test(state, city, zipcode) values('GA','Milner','30257-3454');
insert into test(state, city, zipcode) values('GA','Atlanta','30326-1087');
insert into test(state, city, zipcode) values('GA','Atlanta','30339-3116');
insert into test(state, city, zipcode) values('GA','Fayetteville','30214-4544');
insert into test(state, city, zipcode) values('GA','Toccoa','30577-3860');
insert into test(state, city, zipcode) values('GA','Flowery Branch','30542-9998');
insert into test(state, city, zipcode) values('GA','Canton','30115-7255');
insert into test(state, city, zipcode) values('GA','Atlanta','30308-1881');
insert into test(state, city, zipcode) values('MN','St Paul','55125-9415');
insert into test(state, city, zipcode) values('MN','St Paul','55155-0001');
insert into test(state, city, zipcode) values('MN','New Prague','56071-2100');
insert into test(state, city, zipcode) values('MN','Eagan','55121-3530');
insert into test(state, city, zipcode) values('MN','Aitkin','56431-1337');
insert into test(state, city, zipcode) values('MN','Rochester','55902-1825');
insert into test(state, city, zipcode) values('MN','Minneapolis','55425-1628');
insert into test(state, city, zipcode) values('MN','Elk River','55330-1721');
insert into test(state, city, zipcode) values('MN','Bloomington','55431-1863');
insert into test(state, city, zipcode) values('MN','Edina','55439-2018');

if OBJECT_ID('test2') is not null
DROP TABLE test2
go

select distinct state, city into test2 from test;

alter table test2 add city_count int;

WITH test_cte AS (

SELECT city, city_count, (select count(*) from test where state=a.state and city=a.city) test_count

FROM test2 a

) 

update test_cte set city_count=test_count;
 


CTE 를 이용한 데이터 삭제 (CTE 를 이용한 중복제거)

오라클과 같이 명시적으로 ROWID를 쿼리 할수 없는 SQL SERVER 에서 중복데이터를 지우는게 쉽지 않았다. UNIQUE KEY 값을 가지고 있을때는 row_number() 나 rank() 함수를 이용하여 중복키 값을 쿼리하여 삭제할수 있으나 이마저도 없다면 IDENTITY 칼럼을 생성하여 UNIQUE 칼럼을 만들어 주거나 WITH IGNORE_DUP_KEY 옵션을 사용하여 중복제거된 새로운 테이블을 만들고 테이블을 교체하는 방법을 사용하였다.

하지만 CTE 쿼리를 이용할 경우 UNIQUE KEY 칼럼이 없다 할지라도 중복 데이터를 쉽게 삭제할 수 있다. JOIN 이나 UNION 등 CTE 쿼리에 멀티 테이블이 관련되지 않으면 삭제 쿼리를 수행할 수 있다.

 
if OBJECT_ID('test') is not null
DROP TABLE test
go

CREATE TABLE test(state varchar(100), city varchar(100), zipcode varchar(100))

INSERT INTO test(state, city, zipcode) VALUES('GA','Peachtree City','30269-3428');
INSERT INTO test(state, city, zipcode) VALUES('GA','Stockbridge','30281-5092');
INSERT INTO test(state, city, zipcode) VALUES('GA','Milner','30257-3454');
INSERT INTO test(state, city, zipcode) VALUES('GA','Atlanta','30326-1087');
INSERT INTO test(state, city, zipcode) VALUES('GA','Atlanta','30339-3116');
INSERT INTO test(state, city, zipcode) VALUES('GA','Fayetteville','30214-4544');
INSERT INTO test(state, city, zipcode) VALUES('GA','Toccoa','30577-3860');
INSERT INTO test(state, city, zipcode) VALUES('GA','Flowery Branch','30542-9998');
INSERT INTO test(state, city, zipcode) VALUES('GA','Canton','30115-7255');
INSERT INTO test(state, city, zipcode) VALUES('GA','Atlanta','30308-1881');
INSERT INTO test(state, city, zipcode) VALUES('MN','St Paul','55125-9415');
INSERT INTO test(state, city, zipcode) VALUES('MN','St Paul','55155-0001');
INSERT INTO test(state, city, zipcode) VALUES('MN','New Prague','56071-2100');
INSERT INTO test(state, city, zipcode) VALUES('MN','Eagan','55121-3530');
INSERT INTO test(state, city, zipcode) VALUES('MN','Aitkin','56431-1337');
INSERT INTO test(state, city, zipcode) VALUES('MN','Rochester','55902-1825');
INSERT INTO test(state, city, zipcode) VALUES('MN','Minneapolis','55425-1628');
INSERT INTO test(state, city, zipcode) VALUES('MN','Elk River','55330-1721');
INSERT INTO test(state, city, zipcode) VALUES('MN','Bloomington','55431-1863');
INSERT INTO test(state, city, zipcode) VALUES('MN','Edina','55439-2018');

go

WITH test_over_1city (state, city, zipcode, rnum) 
AS
(
SELECT state, city, zipcode, row_number() over(partition by state, city order by city) rnum

FROM test
)

DELETE test_over_1city WHERE rnum>1
;
 

row_number() 나 rank() 같은 함수를 사용하지 않더라도 조건을 이용하여 베이스 테이블의 데이터를 삭제할 수 있다.

 

if OBJECT_ID('test') is not null
DROP TABLE test_member
go

CREATE TABLE test(state varchar(100), city varchar(100), zipcode varchar(100))

INSERT INTO test(state, city, zipcode) VALUES('GA','Peachtree City','30269-3428');
INSERT INTO test(state, city, zipcode) VALUES('GA','Stockbridge','30281-5092');
INSERT INTO test(state, city, zipcode) VALUES('GA','Milner','30257-3454');
INSERT INTO test(state, city, zipcode) VALUES('GA','Atlanta','30326-1087');
INSERT INTO test(state, city, zipcode) VALUES('GA','Atlanta','30339-3116');
INSERT INTO test(state, city, zipcode) VALUES('GA','Fayetteville','30214-4544');
INSERT INTO test(state, city, zipcode) VALUES('GA','Toccoa','30577-3860');
INSERT INTO test(state, city, zipcode) VALUES('GA','Flowery Branch','30542-9998');
INSERT INTO test(state, city, zipcode) VALUES('GA','Canton','30115-7255');
INSERT INTO test(state, city, zipcode) VALUES('GA','Atlanta','30308-1881');
INSERT INTO test(state, city, zipcode) VALUES('MN','St Paul','55125-9415');
INSERT INTO test(state, city, zipcode) VALUES('MN','St Paul','55155-0001');
INSERT INTO test(state, city, zipcode) VALUES('MN','New Prague','56071-2100');
INSERT INTO test(state, city, zipcode) VALUES('MN','Eagan','55121-3530');
INSERT INTO test(state, city, zipcode) VALUES('MN','Aitkin','56431-1337');
INSERT INTO test(state, city, zipcode) VALUES('MN','Rochester','55902-1825');
INSERT INTO test(state, city, zipcode) VALUES('MN','Minneapolis','55425-1628');
INSERT INTO test(state, city, zipcode) VALUES('MN','Elk River','55330-1721');
INSERT INTO test(state, city, zipcode) VALUES('MN','Bloomington','55431-1863');
INSERT INTO test(state, city, zipcode) VALUES('MN','Edina','55439-2018');

go

WITH test_over_1city (state, city, zipcode, rnum) 
AS
(
SELECT state, city, zipcode, row_number() over(partition by state, city order by city) rnum

FROM test
)

DELETE test_over_1city WHERE zipcode like '559%'
;


오라클의 Connect_by_prior 에 대응하는 CTE 재귀쿼리


재귀쿼리 구문

 
WITH cte_alias (column_aliases)  
AS  
( 
cte_query_definition   --initialization 
UNION ALL 
cte_query_definition2 --recursive execution 
)  
SELECT * FROM cte_alias ;
 


재귀쿼리의 실제 적용예

WITH Managers AS 
( 
--initialization 
SELECT EmployeeID, LastName, ReportsTo  
FROM Employees 
WHERE ReportsTo IS NULL 
UNION ALL 
--recursive execution 
SELECT e.employeeID,e.LastName, e.ReportsTo 
FROM Employees e INNER JOIN Managers m  
ON e.ReportsTo = m.employeeID 
) 
SELECT * FROM Managers  
 


무한 루프를 막기위한 옵션

 
SELECT * FROM Managers OPTION (MAXRECURSION 4) 
 


프로그래밍을 하다보면 특정 칼럼을 기준으로 값들을 모두 합쳐서 보여줘야 하는 경우가 매우 많다. Mysql 의 경우 이런 요청을 일찍 반영하여 group_concat 이라는 문자열 취합 함수를 제공한다. 
그런데 왜 mssql 은 이런 함수를 일찍부터 제공하지 않았는지 의문이다. 공식적으로 mssql 2017 버전부터는 string_agg 라는 함수를 포함하여 이 기능을 제공하고 있다.

그렇다면 2017 버전 이하에서는 어떤 방법으로 이 기능을 구현할수 있을까? 다행히 For xml 구문을 이용하여 group_concat 과 같은 효과를 낼수 있다. 다만 쿼리가 길어지고 복잡해진다는 것이 불만이다.

아래에서는 2017 버전 이하를 위한 for xml 쿼리를 이용하는 방법과 2017 버전의 string_agg 함수의 사용방법을 알아보자.


SQL Server 2017 버전 이전 (단일테이블에서)

 
 

if OBJECT_ID('test_member') is not null
drop table test_member
go
create table test_member(member_id int, member_name varchar(100), depart_name varchar(100))
insert into test_member(member_id, member_name, depart_name) values('1','david','sales')
insert into test_member(member_id, member_name, depart_name) values('2','john','marketing')
insert into test_member(member_id, member_name, depart_name) values('3','harrison','sales')
insert into test_member(member_id, member_name, depart_name) values('4','taylor','marketing')
insert into test_member(member_id, member_name, depart_name) values('5','robert','marketing')
insert into test_member(member_id, member_name, depart_name) values('6','santoshi','sales')
insert into test_member(member_id, member_name, depart_name) values('7','marshall','sales')

SELECT depart_name , members= STUFF(( SELECT ', ' + n.member_name FROM dbo.test_member n WHERE m.depart_name = n.depart_name FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM dbo.test_member m group by depart_name ORDER BY m.depart_name
 


SQL Server 2017 버전 이전 (멀티테이블에서)

 
 
if OBJECT_ID('test_member') is not null
drop table test_member
go

create table test_member(member_id int, member_name varchar(100), birth_year varchar(100))
insert into test_member(member_id, member_name, birth_year) values(1,'david','1977')
insert into test_member(member_id, member_name, birth_year) values(2,'john','1988')
insert into test_member(member_id, member_name, birth_year) values(3,'harrison','1990')
insert into test_member(member_id, member_name, birth_year) values(4,'taylor','1979')
insert into test_member(member_id, member_name, birth_year) values(5,'robert','1980')
insert into test_member(member_id, member_name, birth_year) values(6,'santoshi','1980')
insert into test_member(member_id, member_name, birth_year) values(7,'marshall','1996')

if OBJECT_ID('test_sales_city') is not null
drop table test_sales_city
go

create table test_sales_city(member_id int, city varchar(100))
insert into test_sales_city(member_id, city) values(1, 'new york')
insert into test_sales_city(member_id, city) values(1, 'los angeles')
insert into test_sales_city(member_id, city) values(1, 'orange')
insert into test_sales_city(member_id, city) values(2, 'utah')
insert into test_sales_city(member_id, city) values(3, 'houston')
insert into test_sales_city(member_id, city) values(3, 'tucson')
insert into test_sales_city(member_id, city) values(4, 'adelli')
insert into test_sales_city(member_id, city) values(4, 'new york')
insert into test_sales_city(member_id, city) values(4, 'dc')
insert into test_sales_city(member_id, city) values(5, 'jersey city')
insert into test_sales_city(member_id, city) values(6, 'beach city')
insert into test_sales_city(member_id, city) values(7, 'south beach city')
insert into test_sales_city(member_id, city) values(7, 'siouxfall city')


SELECT m.member_id, m.member_name 
, cities= STUFF(( SELECT ', ' + n.city FROM dbo.test_sales_city n WHERE m.member_id = n.member_id FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM dbo.test_member m ORDER BY m.member_id


SQL Server 2017 버전 이후(단일테이블에서)

 

if OBJECT_ID('test_member') is not null
drop table test_member
go
create table test_member(member_id int, member_name varchar(100), depart_name varchar(100))
insert into test_member(member_id, member_name, depart_name) values('1','david','sales')
insert into test_member(member_id, member_name, depart_name) values('2','john','marketing')
insert into test_member(member_id, member_name, depart_name) values('3','harrison','sales')
insert into test_member(member_id, member_name, depart_name) values('4','taylor','marketing')
insert into test_member(member_id, member_name, depart_name) values('5','robert','marketing')
insert into test_member(member_id, member_name, depart_name) values('6','santoshi','sales')
insert into test_member(member_id, member_name, depart_name) values('7','marshall','sales')

SELECT depart_name, STRING_AGG(member_name, ',') as members
FROM test_member  GROUP BY depart_name
 


SQL Server 2017 버전 이후 (멀티테이블에서)

 
 
if OBJECT_ID('test_member') is not null
drop table test_member
go

create table test_member(member_id int, member_name varchar(100), birth_year varchar(100))
insert into test_member(member_id, member_name, birth_year) values(1,'david','1977')
insert into test_member(member_id, member_name, birth_year) values(2,'john','1988')
insert into test_member(member_id, member_name, birth_year) values(3,'harrison','1990')
insert into test_member(member_id, member_name, birth_year) values(4,'taylor','1979')
insert into test_member(member_id, member_name, birth_year) values(5,'robert','1980')
insert into test_member(member_id, member_name, birth_year) values(6,'santoshi','1980')
insert into test_member(member_id, member_name, birth_year) values(7,'marshall','1996')

if OBJECT_ID('test_sales_city') is not null
drop table test_sales_city
go

create table test_sales_city(member_id int, city varchar(100))
insert into test_sales_city(member_id, city) values(1, 'new york')
insert into test_sales_city(member_id, city) values(1, 'los angeles')
insert into test_sales_city(member_id, city) values(1, 'orange')
insert into test_sales_city(member_id, city) values(2, 'utah')
insert into test_sales_city(member_id, city) values(3, 'houston')
insert into test_sales_city(member_id, city) values(3, 'tucson')
insert into test_sales_city(member_id, city) values(4, 'adelli')
insert into test_sales_city(member_id, city) values(4, 'new york')
insert into test_sales_city(member_id, city) values(4, 'dc')
insert into test_sales_city(member_id, city) values(5, 'jersey city')
insert into test_sales_city(member_id, city) values(6, 'beach city')
insert into test_sales_city(member_id, city) values(7, 'south beach city')
insert into test_sales_city(member_id, city) values(7, 'siouxfall city')


select m.member_id, m.member_name, string_agg(c.city) cities
from test_member m inner join test_sales_city c on a.member_id=c.member_id
group by m.member_id, m.member_name
order by m.member_id