개발자생각

특정 테이블에 데이터를 insert 할 경우 기존 쿼리나 테이블에서 데이터를 조회하여 그 결과를 insert 할때 사용하는 쿼리이다.

select into의 경우 기존 테이블이 없는 경우 새로 테이블을 생성하며 insert 하고, insert into from의 경우 기존 테이블이 존재할때 해당 테이블에 조회결과를 insert 하는 역할을 한다.

[select into의 예 - 기존 테이블을 백업하는 경우나 쿼리 결과를 테이블로 저장하는 경우 유용]

SELECT * INTO MY_TABLE_BACKUP
FROM 
(
SELECT * FROM
MY_TABLE

) TB1

MY_TABLE의 모든 내용(*) 을 조회하여 결과테이블 MY_TABLE_BACKUP 테이블을 생성하며 insert 해준다.

(이때 주의할점은 생성되는 테이블의 컬럼크기는 해당컬럼 값중 최대 길이로 맞춰진다.)

[INSERT INTO FROM 의 예]

INSERT INTO MY_TABLE_BACKUP 
SELECT * FROM
(
SELECT * FROM
MY_TABLE
) TB1

빨간색 쿼리의 경우 MY_TABLE 에서 모든 항목을 조회하여 저장하게 되므로 결국 

SELECT * INTO MY_TABLE_BACKUP 
FROM  
MY_TABLE

와 같은 문장이다. 

괄호안을 쿼리로 작성한 이유는 쿼리 결과로도 데이터를 insert 할수 있음을 보여주기 위함이다.

만약 자동증가값이 존재하는 테이블의 경우 select into나 insert into from을 사용할때 에러가 발생하는데 이 경우 해당 쿼리 앞뒤로 SET IDENTITY_INSERT 문장을 on / off 해주면 된다.

[자동증가값이 있는 테이블의  예]

SET IDENTITY_INSERT MY_TABLE_BACKUP ON ; 

INSERT INTO MY_TABLE_BACKUP 
SELECT * FROM
( 
SELECT * FROM
MY_TABLE
) TB1

SET IDENTITY_INSERT MY_TABLE_BACKUP OFF ;

over 함수는 쿼리의 결과에서 다른 카운트 값을 얻고자 할때 사용한다.

[전체 카운트 필요한 경우]

Select

count(*) OVER () AS total_COUNT,

* from 

my_table 

where my_value='a'

OFFSET 0 ROW
FETCH FIRST 50 ROW ONLY

이 경우 쿼리를 실행하면 my_value컬럼값이 'a'인  0~50번째 데이터만 가져오는 쿼리이다.

하지만 해당 조건(컬럼값이 a 인)에 일치하는 전체 데이터가 몇건인지 필요한 경우가 가끔있다.

(리스트나 게시판등에서 현재 페이지에 10개의 값이 나오는데 전체 글갯수가 필요한 경우라든가...)

이 경우 over절을 count 와 함께 사용하면 전체 조회건이 카운트 된다.

[개별 카운트 및 순번 필요한 경우]

SELECT 
 POP_YEAR, TB1.pop_month, TO_NAME,FROM_CODE, SUM(CAST(POP_TOTAL_MAN AS INT)   ) AS TOTAL_MAN 
 ,ROW_NUMBER() OVER (PARTITION BY POP_YEAR ORDER BY SUM(POP_TOTAL_MAN)  DESC) AS RankNo
FROM 
APT_POP_MOVE TB1
WHERE  
1=1
and pop_month = '12'
and to_name = '전국' and  from_code in ('00','11','41','26')
GROUP BY TB1.POP_YEAR, TB1.pop_month, TB1.TO_NAME, TB1.FROM_CODE

위 쿼리의 경우 년도별 12월의 전국 인구이동에 대한 쿼리인데 RankNo 부분이 각 년도별 정렬기준이 되게 하기 위해 over 절과 함께 row_number 사용하였다.

partition by에는 순번의 기준이 컬럼을 order by 부분에는 정렬 기준을 정해주면 된다.

[파티션 기준에 따른 집계함수 사용]

SELECT SalesOrderNumber AS OrderNumber, ProductKey,

          OrderQuantity AS Qty,

          SUM(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Total,

          AVG(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Avg,

          COUNT(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Count,

          MIN(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Min,

          MAX(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Max

FROM dbo.FactResellerSales

WHERE SalesOrderNumber IN(N'SO43659',N'SO43664') AND ProductKey LIKE '2%'

ORDER BY SalesOrderNumber,ProductKey;

이 경우 OrderNumber 값을 기준으로 묶어 각각의 집계함수(sum, avg, count, min, max)에 대한 값을 반환환다.

OrderNumber이 [SO43659] 인 경우만 살펴보면 각각의 product 값에 따른 Qty값은 존재하지만 total은 총합(sum)인 16이 , 평균은 3이 product의 종류는 5종(count)이 반환된다. 

OrderNumber  Product  Qty  Total  Avg  Count  Min  Max  
-----------       -------    ---    -----  ---    -----   ---    ---  
SO43659          218       6     16     3       5       1     6  
SO43659          220       4     16     3       5       1     6  
SO43659          223       2     16     3       5       1     6  
SO43659          229       3     16     3       5       1     6  
SO43659          235       1     16     3       5       1     6  
SO43664          229       1      2     1       2       1     1  
SO43664          235       1      2     1       2       1     1