SQL Server 에서 무작위로 (random) 데이터를 가져오는 방법
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
'SQL Server' 카테고리의 다른 글
SQL Server 테이블 데이터 중복제거 방법 (0) | 2019.02.09 |
---|---|
SQL Server 조인(join) 을 이용한 데이터 업데이트 방법 (0) | 2019.02.09 |
SQL Server default 제약조건(constraint) 추가, 변경, 제거 (0) | 2019.02.09 |
SQL Server CTE 를 이용한 INSERT, UPDATE, DELETE, 재귀쿼리 예제 (0) | 2019.02.08 |
SQL Server 에서 mysql 의 group_concat 과 같은 효과 내기 (0) | 2019.02.08 |
SQL Server CTE 를 이용한 INSERT, UPDATE, DELETE, 재귀쿼리 예제
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)
'SQL Server' 카테고리의 다른 글
SQL Server 테이블 데이터 중복제거 방법 (0) | 2019.02.09 |
---|---|
SQL Server 조인(join) 을 이용한 데이터 업데이트 방법 (0) | 2019.02.09 |
SQL Server default 제약조건(constraint) 추가, 변경, 제거 (0) | 2019.02.09 |
SQL Server 에서 무작위로 (random) 데이터를 가져오는 방법 (0) | 2019.02.09 |
SQL Server 에서 mysql 의 group_concat 과 같은 효과 내기 (0) | 2019.02.08 |
SQL Server 에서 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
'SQL Server' 카테고리의 다른 글
SQL Server 테이블 데이터 중복제거 방법 (0) | 2019.02.09 |
---|---|
SQL Server 조인(join) 을 이용한 데이터 업데이트 방법 (0) | 2019.02.09 |
SQL Server default 제약조건(constraint) 추가, 변경, 제거 (0) | 2019.02.09 |
SQL Server 에서 무작위로 (random) 데이터를 가져오는 방법 (0) | 2019.02.09 |
SQL Server CTE 를 이용한 INSERT, UPDATE, DELETE, 재귀쿼리 예제 (0) | 2019.02.08 |