김연욱님에 심심풀이 입니다.

헉..근데 사실 심심풀이 수준을 넘은 고강도 mssql에 깊이 있는 내용들 같습니다.

출처 : sqler

심심풀이 15탄 - max값 구하기

이번에는 최대값을 구하는것에 대해서 생각해보자.

쉬운 주제이니 부담없이 그냥 쭈~~욱 읽어보면 될듯...

/* ============================================================================== */
--먼저 테스트를 위한 샘플부터 만들고(이전 심심풀이 14탄에서 사용했던 샘플임)
--drop table p   -- 상품 테이블
create table p
( product_id int not null primary key
, product_name varchar(10)
, maker_id int)
go

set nocount on
declare @i int
set @i = 1
while (@i <= 100)
begin
   insert into p values (@i, '상품'+right('00'+convert(varchar(3),@i),3),
                         convert(int, rand()*10)+1)
   set @i = @i + 1
end
set nocount off

--drop table a   -- 판매 테이블
create table a
( dt char(8) not null
, seq int not null
, qty int not null
, product_id int not null)
go
alter table a
   add constraint pk_a primary key(dt, seq)

create index idx on a(product_id)
go
set nocount on
declare @i int, @j int, @dt datetime
set @dt = '2002-01-01'
while (@dt <= '2003-06-30')
begin
    set @i = convert(int, rand()*1000)+1
    set @j = 1
    while (@j <= @i)
    begin
       insert into a values (convert(char(8),@dt,112),
                             @j,
                             convert(int, rand()*1000)+1,
                             convert(int, rand()*100)+1 )
       set @j = @j + 1
    end
set @dt = dateadd(dd, 1, @dt)
end
set nocount off
go
select count(*) from a   --275862
/* ============================================================================== */
기본적으로 최대값 하면 생각나는것이 max()함수이다.

간단하게 먼저 살펴보면

2003년 6월 데이터중에서 일별 최대판매량을 구해야한다고 하면

select dt, max(qty) as maxqty from a where dt like '200306%' group by dt

이런식의 쿼리가 생각날것이다. 가장 일반적이고 많이 사용하는 형태이다.

한가지 경우를 더 보자.

2003-06-17일 하루치 데이터중에서 최대판매량은 ?

select max(qty) as 최대판매량 from a where dt='20030617'

역시나 간단하다.
그러나, 이런경우 max()함수 말고도 top 과 order by 를 사용해서 하는 방법도 있다.

select top 1 qty from a where dt='20030617' order by qty desc

이런식으로 top 1 과 order by desc 을 조합해서도 쿼리가 가능하다.

그럼, 둘중에서 어느것이 더 효율이 좋은지 한번 테스트 해보자.

--dbcc dropcleanbuffers
--dbcc freeproccache
declare @dt datetime
set @dt = '20020101'
while(@dt <= '20030630')
begin
   --select max(qty) from a where dt=convert(char(8),@dt,112)   --1번
   select top 1 qty from a where dt=convert(char(8),@dt,112) order by qty desc --2번
   set @dt = dateadd(dd,1,@dt)
end

각각을 5회씩 실행시키고 역시나 프로필러를 돌려서 결과를 확인하였다.

1번 : max()함수 사용시

CPU   Reads   Duration
---   -----   --------
453   3105    1406
359   3072    970
375   3072    966
375   3072    970
375   3072    970


2번 : top 과 order by 사용시

CPU   Reads   Duration
---   -----   --------
594   3099    1466
375   3072    1126
437   3072    1110
390   3072    1126
391   3072    1110

역시나, top 과 order by 를 사용한것 보다는 max()함수를 사용한것이
조금더 효율이 좋다.

/* ============================================================================== */
이번에는 다른 예를 보자.

문제 : 2003년 06월중 일별판매량의 합이 가장큰 일자와 그 날의 판매량은 ?

select top 1 dt, sum(qty) as sumqty
from a
where dt like '200306%'
group by dt
order by sum(qty) desc

이와 같이 합계나 카운트를 구한다음 그것의 최대값 또는 최소값 하나만을 필요로 하거나,
최대값 하나만 필요한것이 아니라 다른 컬럼까지 필요하다면
이런식으로 order by 와 top 을 적절히 사용하면 간단하게 처리된다.

/* ============================================================================== */
또, 다른 예를 보자.

문제 : 2003년 06월중 판매량이 가장많은 상품의 상품정보는 ?
       (단, 여러건일 경우 가장최근 데이터 1개만을 보여줘야함.)

< 출력형태 >

판매일자         판매량      상품코드    상품명       
---------------- ----------- ----------- ----------
20030627         94          1000        상품094

이런식의 출력이 나와야 한다.

이번문제의 경우 두가지로 풀어볼수가 있다.

하나는 역시나 max()함수와 top 그리고 order by 를 활용하는 방법이구
또 다른 하나는 문자열 결합방식으로 푸는 것이다.

말로 할려니까 너무 힘들구 직접 쿼리문을 보면

--dbcc dropcleanbuffers
--dbcc freeproccache

--쿼리1.
select top 1 a.dt, a.qty, a.product_id, p.product_name
  from a inner join p
    on (p.product_id = a.product_id)
where qty = (select max(qty) from a where dt like '200306%')
   and dt like '200306%'
order by dt desc, seq desc

역시나 그다지 어렵지 않고 많이 사용하는 쿼리문이다.

우선 max()함수를 사용하여 200306월 데이터중 max(qty)를 구해서 그것을 가지고
판매테이블에서 max(qty)와 같은 수량을 판매한 데이터만을 추려서
order by desc 을 사용하여 최대값이 위로 오도록 해놓구서 top 1 으로 맨 위의
결과만을 뿌려주는 것이다.

아래 수치는 위 쿼리문을 5회 반복해서 돌리구 프로필러로 확인한 내용이다.

CPU   Reads   Duration
---   -----   --------
125   176     186
63    71      63
62    71      63
63    71      76
62    71      60


--쿼리2.
select substring(maxval,11,8) as 판매일자,
       convert(int,substring(maxval,19,10)) as 판매량,
       convert(int,substring(maxval,1,10)) as 상품코드,
       p.product_name as 상품명
from (
      select max(right('0000000000'+convert(varchar(10),qty),10) +
                 dt + 
                 right('0000000000'+convert(varchar(10),product_id),10)) as maxval
      from a where dt like '200306%'
     ) x1 inner join p
on p.product_id = convert(int,substring(maxval,19,10))

이번에는 일반적으로 잘 사용하지 않는 방법으로 문자열 연결방식을 활용한 방법이다.

먼저, inline-view 안쪽을 보면 qty컬럼이 int 형이므로 문자열 결합을 위하여 varchar()로
형변환을 시키고, dt 컬럼은 문자니까 그냥두고, 역시나 product_id컬럼이 int형이므로
varchar()로 형변환을 시킨후에 문자열을 결합하였다.
qty 컬럼과 product_id 컬럼을 varchar()로 형변환후 max() 함수를 사용하여 최대값을
구해야 하고, 또, inline-view 바깥쪽에서 다시 개별 컬럼으로 쪼개야 하므로 고정자릿수를
만들기 위해서 앞에 '0'을 붙이고 right()함수를 사용하여 각각 10자리씩 고정시켰다.

0000001000200306270000000094
이런 형식으로 결합이 되었다.(개별컬럼을 고정길이로 맞춰주는것이 중요...)

이제 inline-view 바깥쪽에서 위와 같이 결합된것을 substring() 함수를 사용하여 쪼개면 된다.

아래 수치는 위 쿼리문을 5회 반복해서 돌리구 프로필러로 확인한 내용이다.

CPU   Reads   Duration
---   -----   --------
157   142     220
109   60      106
109   60      110
110   60      110
109   60      106

쿼리1 과 쿼리2의 속도비교를 해보자.
2번 방식이 IO량은 적지만 CPU나 Duration 수치는 더 높다.
아마두 많은 함수 사용으로 인한 속도저하인것 같다.
그래서 2번 방식 보다는 1번 방식이 훨씬 효율이 더 좋다.

그리고, 위에서 조건을 [여러건일경우 가장 최근 데이터]라는 조건을 주었는데, 그러한
조건 없이 최대값과 동일한 모든 데이터를 봐야한다면 역시나 1번 방법을 사용해야한다. 

/* ============================================================================== */
또, 다른 예를 보자.

상품별로 가장 최근에 판매한 내역을 보자.

먼저 테이블의 인덱스 상황을 확인해보자.

exec sp_helpindex a

index_name  index_description                                   index_keys
----------  --------------------------------------------------  -----------
idx         nonclustered located on PRIMARY                     product_id
pk_a        clustered, unique, primary key located on PRIMARY   dt, seq


--set statistics profile off
set statistics profile on

--쿼리1

select a.*
from a inner join
   (select product_id, max(dt+right('0000000000'+convert(varchar(10),seq),10)) max_val
    from a
    group by product_id
) x
on a.product_id=x.product_id
and a.dt=left(x.max_val,8)
and a.seq=convert(int,substring(x.max_val,9,10))

Rows  Exec  StmtText
----  ----  ---------------------------------------------------------------------
100    1    |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[product_id], [Expr1002]) WITH PREFETCH)
100    1       |--Stream Aggregate(GROUP BY:([a].[product_id]) DEFINE:([Expr1002]=MAX([a].[dt]+right
('0000000000'+Convert([a].[seq]), 10))))
275036 1       |  |--Index Scan(OBJECT:([tempdb].[dbo].[a].[idx]), ORDERED FORWARD)
100    100     |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[a].[pk_a]), SEEK:([a].[dt]=substring
([Expr1002], 1, 8) AND [a].[seq]=Convert(substring([Expr1002], 9, 10))),  WHERE:([a].[product_id]=[a].
[product_id]) ORDERED FORWARD)


CPU   Reads   Duration
---   -----   --------
969   1774    986
1000  1774    1000
1000  1774    1016
985   1774    983
1000  1774    1043


--쿼리2

select product_id,
       left(max_val,8) as dt,
       convert(int,substring(max_val,9,10)) as seq,
       convert(int,substring(max_val,19,10)) as qty
from (
select product_id,
       max(dt+
       right('0000000000'+convert(varchar(10),seq),10)+
       right('0000000000'+convert(varchar(10),qty),10)) as max_val
from a
    group by product_id
) x

Rows  Exec  StmtText
----  ----  ---------------------------------------------------------------------
100    1    |--Compute Scalar(DEFINE:([Expr1003]=substring([Expr1002], 1, 8), [Expr1004]=Convert(substring
([Expr1002], 9, 10)), [Expr1005]=Convert(substring([Expr1002], 19, 10))))
100    1       |--Hash Match(Aggregate, HASH:([a].[product_id]) DEFINE:([Expr1002]=MAX([a].[dt]+right
('0000000000'+Convert([a].[seq]), 10)+right('0000000000'+Convert([a].[qty]), 10))))
275036 1          |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[a].[pk_a]))


CPU   Reads   Duration
---   -----   --------
1640  1039    1656
1625  1039    1640
1610  1039    1610
1593  1039    1610
1594  1039    1623


쿼리1 과 쿼리2의 속도비교를 해보자.
2번 방식이 IO량은 적지만 CPU나 Duration 수치는 더 높다.

이것은 1번방식의 경우 해당상품별최근판매내역을 구하기 위해서 사용한 쿼리가
데이터페이지를 읽지않고 단지 인덱스페이지만을 읽어서 처리가 가능하기 때문이다.

즉, 2번 방식의 경우 전체 테이블 스캔을 하면서 hash match를 통하여 컬럼을 연결하여 그것의
max()값을 구하고 있는데 반해서
1번 방식의 경우는 인덱스페이지만을 읽어서 상품별 최근판매내역을 구하기 위한 정보를 구하고
해당테이블을 100번만 index seek을 하기때문이다.

이와같이 인덱스를 사용하여 최대값을 구하기 위한 정보를 얻을수 있는경우는
1번쿼리방식이 더 유리한 경우가 많다.

그럼, 왜 굳이 2번 방식을 언급했는지 아래 문제를 보자.
/* ============================================================================== */
문제 : 2003년 06월중 일별 판매량이 가장큰날과 그날의 판매량,
       가장작은날과 그날의 판매량은 얼마인가 ?

< 출력형식 >

최소판매일  최소판매량  최대판매일  최대판매량      
----------- ----------- ----------- -----------
20030602    6268        20030626    487032

이번경우와 같이 지금까지 언급했던 한가지 경우가 아니라, 최대값과 최소값을 같이 구해야 한다면
이때는 문자열 결합방식으로 처리하는것이 유리하다.
물론, min(), max()함수를 사용할수 있겠지만 쿼리문이 조금 복잡해지구
또한 테이블을 몇번은 읽어야 할것이다.
그렇지만 문자열결합방식의 가장큰 장점은 테이블을 단 한번만 읽어도 된다는것이다.

--쿼리
select substring(minqty,11,8) as 최소판매일,
       convert(int,substring(minqty,1,10)) as 최소판매량,
       substring(maxqty,11,8) as max판매일,
       convert(int,substring(maxqty,1,10)) as max판매량
from (
      select max(sumqty) maxqty, min(sumqty) minqty
      from (
            select dt, right('0000000000'+convert(varchar(10),sum(qty)),10)+dt as sumqty
            from a
            where dt like '200306%'
            group by dt
           ) x1
     ) x2


쿼리문을 보면 인라인뷰를 여러번 사용하여 복잡한것 같지만 자세히 보면
별루 복잡하지도 않구, 어려운 문장이 없다. 고로 주석은 생략...ㅋㅋㅋ

/* ------------------------------------------------------------------------------ */
비슷한 경우로 한가지 만 더 예를 들어보면

문제 : 2003-06 상품별 판매량 top 10 을 구하자, 해당상품의 최대, 최소 판매일과 판매량을 보자.

< 출력형식 >

product_id  product_name 월판매량    최다판매일       최다판매량  최소판매일       최소판매량      
----------- ------------ ----------- ---------------- ----------- ---------------- -----------
65          상품065      93470       20030622         1000        20030627         14
82          상품082      92601       20030608         999         20030627         1
39          상품039      88649       20030617         996         20030616         3
11          상품011      87940       20030628         997         20030628         3
94          상품094      85221       20030627         1000        20030626         26
43          상품043      84616       20030623         990         20030629         6
66          상품066      84481       20030603         998         20030609         1
73          상품073      83881       20030603         996         20030611         5
80          상품080      83448       20030608         1000        20030629         5
3           상품003      83148       20030604         999         20030620         5

--쿼리
select x.product_id, p.product_name, x.sumqty as 월판매량,
       substring(maxval,11,8) as 최다판매일,
       convert(int,substring(maxval,1,10)) as 최다판매량,
       substring(minval,11,8) as 최소판매일,
       convert(int,substring(minval,1,10)) as 최소판매량
from (
      select top 10 product_id, sum(qty) sumqty,
             max(right('0000000000'+convert(varchar(10), qty),10)+dt) as maxval,
             min(right('0000000000'+convert(varchar(10), qty),10)+dt) as minval
      from a
      where dt like '200306%'
      group by product_id
      order by sum(qty) desc
     ) x inner join p
on p.product_id = x.product_id

/* ------------------------------------------------------------------------------ */
실행계획을 살펴보면

Rows  Exec  StmtText
----  ----  ---------------------------------------------------------------------
10     1    |--Compute Scalar(DEFINE:([Expr1007]=substring([Expr1003], 11, 8),
                                      [Expr1008]=Convert(substring([Expr1003], 1, 10)),
                                      [Expr1009]=substring([Expr1004], 11, 8),
                                      [Expr1010]=Convert(substring([Expr1004], 1, 10))))
10     1       |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[product_id]))
10     1          |--Sort(TOP 10, ORDER BY:([Expr1002] DESC))
100    1          |  |--Hash Match(Aggregate, HASH:([a].[product_id])
                        DEFINE:([Expr1002]=SUM([a].[qty]),
                                [Expr1003]=MAX(right('0000000000'+Convert([a].[qty]),10)+[a].[dt]),
                                [Expr1004]=MIN(right('0000000000'+Convert([a].[qty]),10)+[a].[dt])))
16013  1          |     |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[a].[pk_a]),
                           SEEK:([a].[dt] >= '200306' AND [a].[dt] < '200307'), 
                           WHERE:(like([a].[dt], '200306%', NULL)) ORDERED FORWARD)
10     10         |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[p].[PK__p__32E0915F]),
                     SEEK:([p].[product_id]=[a].[product_id]) ORDERED FORWARD)

6월 한달치 데이터만 clustered index seek 을 하면서 원하는대로 잘 처리하였다.
/* ============================================================================== */
추가1 : 비슷한 내용이 Q/A란에 올라와서 해당내용 추가

>  TABLE A     매장코드
>                    업체코드
>     
>  TABLE B     업체코드
>                    업체명
>
>  TABLE C     발생일자
>                    업체코드
>                    매출금액       으로 구성이 되어있을때
>
> 매장코드(A1)을 받아서
> TABLE A에서 TABLE B와 C를 JOIN해서 업체명과 가장최근일의 매출금액을 가져오고 싶습니다.
>
> 결과
>       매장코드  업체코드   업체명            최근일매출금액
>        A1        A001       가나통상          2,000

--먼저 필요한 샘플데이터 만들구
--drop table #a
--drop table #b
--drop table #c

create table #a(매장코드 char(3),업체코드 char(5))
create table #b(업체코드 char(5),업체명 char(20))
create table #c(발생일자 char(10),업체코드 char(5),매출금액 int)
go
insert into #a values('a1','a001')

insert into #b values('a001','가나통상')

insert into #c values('2003-07-01','a001',100)
insert into #c values('2003-07-02','a001',200)
insert into #c values('2003-07-03','a001',2000)
go

이번문제와 같이 특정컬럼만 필요하다면 상관서브쿼리를 사용하여
top과 order by를 사용하여 처리할수도 있다.


select a.매장코드,a.업체코드,b.업체명,
      isnull((select top 1 매출금액 from #c c
              where c.업체코드 = a.업체코드
              order by 발생일자 desc),0) as 최근매출금액
from #a a inner join #b b
on a.업체코드 = b.업체코드
where a.매장코드 = 'a1'
/* ============================================================================== */
추가2. 사용자정의 함수를 사용한 max()처리

문저 : northwind DB에서 상품별로 가장최근에 판매된 판매량과 그 판매일자를 보자.

use northwind
go

--먼저 사용할 함수를 하나 만들고
--drop function f1
create function dbo.f1(@productid int)
   returns varchar(20)
as
begin
   declare @qty int, @dt datetime

   select top 1 @qty = od.quantity, @dt=o.orderdate
   from orders o inner join [order details] od
   on o.orderid = od.orderid
   where od.productid = @productid
   order by o.orderdate desc

   return (convert(char(10), @dt, 120) + convert(varchar(10),@qty))
end
go
--판매일과 수량을 같이 봐야하므로 문자열연결방식으로 일자와 판매량을 연결하여 리턴

--이제 함수를 사용하여 쿼리하여 보자.

--1번 쿼리)
select productid, productname, left(val,10) as dt, substring(val, 11, 10) as qty
from (select productid, productname, dbo.f1(productid) as val from products) x

위에서 하던 방법대로 inline-view를 사용하였다.

하지만 이렇게 쿼리를 하게되면 inline-view가 먼저 한번실행되구(즉, 함수가 한번실행)
inline-view밖에서 left(), substring()으로 다시 쪼개는 방식으로 쿼리가 실행되는것이
아니고

두쿼리가 하나로 합쳐져서
--2번 쿼리)
select productid, productname,
       left(dbo.f1(productid),10) as dt,
       substring(dbo.f1(productid), 11, 10) as qty
from products
이렇게 실행이 된다.(즉, 함수가 두번실행되는것이다.)

그래서 쿼리를 조금 수정해야한다.

--3번 쿼리)
select productid, productname, left(val,10) as dt, substring(val, 11, 10) as qty
from (select top 100 percent productid, productname, dbo.f1(productid) as val
      from products order by productid desc
     ) x
order by productid asc

inline-view가 먼저 실행되도록 하기위해서 inline-view에서 top 100 percent 와
order by 를 사용했고, inline-view밖에서 다시 order by를 해주었다.
어느것 하나라도 빼버리면 inline-view가 먼저 실행되지 않는다.

정말로 한번만 읽는지 확인을 해보자.

먼저 기준을 삼기 위해서

select productid, productname, dbo.f1(productid) as val from products

이쿼리를 돌려보고 위에 쿼리를 순서대로 돌려서 프로필러에서 확인을 해보았다.

기준 : 5583
1번  : 11103
2번  : 11115
3번  : 5584

프로필러에서 Reads항목을 확인한 수치이다.

결과를 보면 3번 쿼리의 경우 정말로 한번 실행된것을 알수가 있다.

함수를 한번 실행시키기 위해서 불필요하게 order by 를 두번이나 실행시켰지만
위의 쿼리 같은경우 group by 를 해야하는 대상 row수가 매우 적기 때문에(77row)
부담이 되지 않아서 이렇게 했지만, 만약 order by 를 해야하는 대상 row수가
매우 많다면 차라리 함수를 두번 실행시키는 것이 더 낳은 경우도 있을것이다.
역시나, 상황에 따라서 판단해야 한다.

/* ============================================================================== */
추가3. 필요한 컬럼이 많을경우

> Access에서 사용되는 last()함수를 SQL에서 구현하려면 어떻게 해야하나요?
>
> Table A.
>    관리번호   날짜                 수량     금액     . ......
>       aaa          20010801
>       aaa          20030801
>       aaa          20030804
>       bbb          20030801
>       bbb          20030805
>       bbb          20030806
>       ccc          20030806
>       ccc          20030810
>       ccc          20030822
>
> 위 데이터를 관리번호로 Group by해서 묶음별 마지막 날짜의 데이터를 모두 가져오고 싶은데
> ACcess에서는 last()함수 (마지막값)으로 해주면 되던데 SQL에서는 도대체 어떻게 해야하나요?
>
>     [결과]
>    관리번호   날짜   수량   금액
>       aaa          20030804
>       bbb          20030806
>       ccc          20030822

--drop table #a
create table #a(관리번호 varchar(10), 날짜 varchar(8), 수량 int, 금액 int)
insert into #a values ('aaa', '20010801', 1, 100)
insert into #a values ('aaa', '20030801', 2, 200)
insert into #a values ('aaa', '20030804', 3, 300)
insert into #a values ('bbb', '20030801', 1, 100)
insert into #a values ('bbb', '20030805', 2, 200)
insert into #a values ('bbb', '20030806', 3, 300)
insert into #a values ('ccc', '20030806', 1, 100)
insert into #a values ('ccc', '20030810', 2, 200)
insert into #a values ('ccc', '20030822', 3, 300)
go

해당테이블의 컬럼이 많이 있고 그 컬럼들이 모두 필요할경우이며 pk가 관리번호+날짜 라면

select x.관리번호, x.날짜, x.수량, x.금액
from #a x inner join (select 관리번호, max(날짜) 큰날짜 from #a group by 관리번호) y
on (y.관리번호 = x.관리번호 and y.큰날짜 = x.날짜)

이런식으로 쿼리하면 될것 같네요.


/* ============================================================================== */
추가4. min()함수에서의 응용

> 조회월의 첫번째 데이타를 가져 오려고합니다.
>
> 예를 들면..
> 데이타가...
>
> 거래처        날짜                      기초액       매출액       입금액      잔액
> 001           2001-01-01 00:00:00      150                100         50          100
> 001           2001-01-04 00:00:00      100                  0          60          160
> 001           2001-01-07 00:00:00      160                 50           0          110
> 001           2001-01-10 00:00:00      110                 50         50          110
> 001           2001-01-15 00:00:00      110                  0         10          120
> 001           2001-02-01 00:00:00      120                 30          0           90
> 001           2001-02-03 00:00:00       90                 10        100          180
> 001           2001-02-05 00:00:00      180                  0         40          220
> 001           2001-02-09 00:00:00      220                200          0           20
>
>
> 한 날짜의 한 업체엔 기초액뿐만 아니라 매출액과 입금액, 잔액 필드가 더 있습니다.
>
> 제가 하려는 것은..
> 데이타가 저런 식으로 되어 있다면 조회조건을 거래처 코드와 날짜의 년월만주고..
> 거래처별로 월별 합계를 구하려고 합니다.
>
>
> 거래처    날짜(yyyymm)          기초액      매출액     입금액     잔액  
> 001      2001.01                  150        200         170       120
>          2001.02                  120        240         140        20
>
>
> 기초액은 그달의 첫번째 데이터의 기초액을 그대로 가져 왔음 하는데요.
> 매출액과 입금액은 그 달의 합계,
> 잔액은 그달의 마지막 잔액을 그냥 가져와야 합니다..(잔액은 기초액 - 매출액 + 입금액 이 맞아야 함..)
> 어떻게 하면 좋을까요.


--drop table a
create table a(거래처 char(3), 날짜 datetime, 기초액 int, 매출액 int, 입금액 int, 잔액 int)
insert into a values('001', '2001-01-01 00:00:00', 150, 100,  50, 100)
insert into a values('001', '2001-01-04 00:00:00', 100,   0,  60, 160)
insert into a values('001', '2001-01-07 00:00:00', 160,  50,   0, 110)
insert into a values('001', '2001-01-10 00:00:00', 110,  50,  50, 110)
insert into a values('001', '2001-01-15 00:00:00', 110,   0,  10, 120)
insert into a values('001', '2001-02-01 00:00:00', 120,  30,   0,  90)
insert into a values('001', '2001-02-03 00:00:00',  90,  10, 100, 180)
insert into a values('001', '2001-02-05 00:00:00', 180,   0,  40, 220)
insert into a values('001', '2001-02-09 00:00:00', 220, 200,   0,  20)
go

declare @거래처 char(3), @날짜 datetime
set @거래처='001'

select @거래처 as 거래처,
       left(convert(char(10),날짜,120),7) as 날짜,
       substring(min(convert(char(10),날짜,120)+convert(char(10),기초액)),11,10) as 기초액,
       sum(매출액) as 매출액,
       sum(입금액) as 입금액,
       convert(int,substring(min(convert(char(10),날짜,120)+convert(char(10),기초액)),11,10))
       - sum(매출액) + sum(입금액) as 잔액
from a
where 거래처=@거래처
group by left(convert(char(10),날짜,120),7)


거래처  날짜    기초액               매출액      입금액      잔액         
---- ---------- -------------------- ----------- ----------- -----------
001  2001-01    150                  200         170         120
001  2001-02    120                  240         140         20

(2개 행 적용됨)

이번문제는 최대값이 아니라 최소값을 구해야하는 문제였다.
max()함수 대신에 min()함수를 사용하여 문자열 연결방식을 조금 응용한것이다.
기초액을 구하기 위하여
substring(min(convert(char(10),날짜,120)+convert(char(10),기초액)),11,10)
이렇게
min()함수 내에서 날짜와 기초액을 문자로 변형시켜 연결시킨후 그것의 min()값을 구하여
불필요한 날짜 값을 제거하여 해당월의 가장작은 날짜의 기초액을 가져온것이다.

/* ============================================================================== */
사례1. Q/A질문

> 사용량 조회를 하려고 하는데요.
>
> Table "aaa" 에는 ymd,       ge_few,   ge_no 가 있구요.
>                      (Datetime)(decimal)(varchar)
>
> 이걸 기준으로
> 검침일(ymd), 검침량(ge_few), 전검침일(alias old_ymd), 전검침량(alias old_ge_few),
> 사용일(ymd - old_ymd), 사용량(ge_few - old_ge_few), 검침기번호(ge_no) 를 구하려고 하는데요.
> 전검침일은 검침일의 바로 전 최대 날짜를 구하면 됩니다.
> 두개를 어떻게 붙여야 하는지 몰라서요.
> (검침일 기준으로 나와야 합니다. ge_no 하고 ymd 가 PK 입니당.)
>
> (참.. 검침일이 데이타의 첫날이면 max(ymd)를 하게 되면 날짜는 1900-01-01 00:00:00 이 나오던데..
> 이 날짜는 어떻게 처리하면 좋을까요?
> 검침일이라는게 꼭 하루에 한번씩이 아닐수도 있거든요. 아니 하루에 한번은 한번인데.
> 전검침일이 꼭 어제가 아니라는 거죠. 어제일수도 있구. 그저께일수도 있구 한달전일수도 있구. -.-;;)


--drop table aaa
create table aaa(ymd datetime not null, ge_few decimal(10), ge_no varchar(10) not null)
go
alter table aaa
   add constraint pk_aaa primary key(ge_no, ymd)
go
insert into aaa values('2003-11-01', 10, 'a001')
insert into aaa values('2003-11-12', 20, 'a001')
insert into aaa values('2003-12-01', 30, 'a001')
insert into aaa values('2003-12-04', 40, 'a001')
insert into aaa values('2003-12-15', 50, 'a001')
insert into aaa values('2003-12-01', 10, 'a002')
insert into aaa values('2003-12-05', 30, 'a002')
insert into aaa values('2003-12-15', 50, 'a002')
go

select ymd as 검침일,
       ge_few as 검침량,
       convert(datetime,substring(old,1,20)) as 전검침일,
       convert(decimal,substring(old,21,10)) as 전검침량,
       datediff(dd, convert(datetime,substring(old,1,20)), ymd) as 사용일,
       ge_few - convert(decimal,substring(old,21,10)) as 사용량,
       ge_no as 검침기번호
from (
      select ymd, ge_few, ge_no,
            (select max(convert(char(20),ymd,120)+convert(char(10),ge_few))
             from aaa y
             where y.ge_no=x.ge_no and y.ymd < x.ymd) as old
      from aaa x
     ) x1


검침일                   검침량   전검침일                 전검침량  사용일  사용량  검침기번호
------------------------ -------- ------------------------ --------- ------- ------- ----------
2003-11-01 00:00:00.000  10       NULL                     NULL      NULL    NULL    a001
2003-11-12 00:00:00.000  20       2003-11-01 00:00:00.000  10        11      10      a001
2003-12-01 00:00:00.000  30       2003-11-12 00:00:00.000  20        19      10      a001
2003-12-04 00:00:00.000  40       2003-12-01 00:00:00.000  30        3       10      a001
2003-12-15 00:00:00.000  50       2003-12-04 00:00:00.000  40        11      10      a001
2003-12-01 00:00:00.000  10       NULL                     NULL      NULL    NULL    a002
2003-12-05 00:00:00.000  30       2003-12-01 00:00:00.000  10        4       20      a002
2003-12-15 00:00:00.000  50       2003-12-05 00:00:00.000  30        10      20      a002

(8개 행 적용됨)

전검침내역이 없는 경우는 해당업무룰에 따라서 처리해주시면 되겠죠.

/* ============================================================================== */
사례2. Q/A질문

> 조회월의 첫번째 데이타를 가져 오려고합니다.
>
> 예를 들면..
> 데이타가...
>
> 거래처      날짜                           기초액       매출액       입금액      잔액
> 001           2001-01-01 00:00:00      150                100         50          100
> 001           2001-01-04 00:00:00      100                  0          60          160
> 001           2001-01-07 00:00:00      160                 50           0          110
> 001           2001-01-10 00:00:00      110                 50         50          110
> 001           2001-01-15 00:00:00      110                  0         10          120
> 001           2001-02-01 00:00:00      120                 30          0           90
> 001           2001-02-03 00:00:00       90                 10        100          180
> 001           2001-02-05 00:00:00      180                  0         40          220
> 001           2001-02-09 00:00:00      220                200          0           20
>
>
> 한 날짜의 한 업체엔 기초액뿐만 아니라 매출액과 입금액, 잔액 필드가 더 있습니다.
>
> 제가 하려는 것은..
> 데이타가 저런 식으로 되어 있다면 조회조건을 거래처 코드와 날짜의 년월만주고..
> 거래처별로 월별 합계를 구하려고 합니다.
>
>
> 거래처   날짜(yyyymm)          기초액      매출액     입금액      잔액  
> 001      2001.01                  150        200         170       120
>          2001.02                  120        240         140        20
>
>
> 기초액은 그달의 첫번째 데이터의 기초액을 그대로 가져 왔음 하는데요.
> 매출액과 입금액은 그 달의 합계, 잔액은 그달의 마지막 잔액을 그냥 가져와야 합니다.
> (잔액은 기초액 - 매출액 + 입금액 이 맞아야 함..)
> 어떻게 하면 좋을까요.


--drop table a
create table a(거래처 varchar(3), 날짜 datetime, 기초액 int, 매출액 int, 입금액 int, 잔액 int)
insert into a values('001', '2001-01-01 00:00:00', 150, 100,  50, 100)
insert into a values('001', '2001-01-04 00:00:00', 100,   0,  60, 160)
insert into a values('001', '2001-01-07 00:00:00', 160,  50,   0, 110)
insert into a values('001', '2001-01-10 00:00:00', 110,  50,  50, 110)
insert into a values('001', '2001-01-15 00:00:00', 110,   0,  10, 120)
insert into a values('001', '2001-02-01 00:00:00', 120,  30,   0,  90)
insert into a values('001', '2001-02-03 00:00:00',  90,  10, 100, 180)
insert into a values('001', '2001-02-05 00:00:00', 180,   0,  40, 220)
insert into a values('001', '2001-02-09 00:00:00', 220, 200,   0,  20)
go

declare @거래처 varchar(3), @날짜 datetime
set @거래처='001'

select @거래처 as 거래처,
       left(convert(varchar(10),날짜,120),7) as 날짜,
       substring(min(convert(varchar(10),날짜,120)+convert(varchar(10),기초액)),11,10) as 기초액,
       sum(매출액) as 매출액,
       sum(입금액) as 입금액,
       convert(int,substring(min(convert(varchar(10),날짜,120)+convert(varchar(10),기초액)),11,10))
       - sum(매출액) + sum(입금액) as 잔액
from a
where 거래처=@거래처
group by left(convert(varchar(10),날짜,120),7)


거래처  날짜    기초액               매출액      입금액      잔액         
---- ---------- -------------------- ----------- ----------- -----------
001  2001-01    150                  200         170         120
001  2001-02    120                  240         140         20

(2개 행 적용됨)

/* ============================================================================== */
사례3. Q/A질문 : IDENTITY와 채번

> [nums] [bigint] IDENTITY (1, 1) NOT NULL ,
> 쿼리 상에서 자동 증가 시키는 경우와
> 코딩상에서 값을 증가시키는 경우(nums=nums+1)에서
>  어떤 것이 자원효율 및 속도 상에 유리한지 알고 싶습니다.
> 왜 유리한지도 알고 싶습니다.

자원효율이나 속도만을 따진다면 당연히 identity가 더 좋습니다.
이것은 채번루틴의 경우 항상 max() 값을 구해야 하고
또 그것을 트랜잭션으로 묶어줘야 하기 때문에 lock에 관해서도
신경을 쓰셔야 합니다. 하지만 identity의 경우는 그런것이 거의 필요가 없지요.

identity는 코딩상에서 값을 증가시킨다고 하셨는데 그것 보다는 하나의 테이블 속성으로 이해하세요.

identity 속성을 사용하느냐 마느냐의 가장 일반적인 기준은
반드시 연속적인 일련번호가 필요한가 입니다.

업무적으로 반드시 연속된 일련번호가 필요한것이 아니라면
identity 속성을 사용하실것을 권장합니다만
업무적으로 반드시 연속된 일련번호가 필요하다면 identity 속성을
사용할수는 없고 별도의 채번루틴을 구성하셔야 합니다.

실제로 간단히 성능을 테스트 해보자.
/*------------------------------------------------------------------*/
1. identity의 경우
/*------------------------------------------------------------------*/
drop table a
create table a(id int identity(1,1) not null)
create unique index idx on a(id)
go
이렇게 테이블 하나 만들어 두고
아래쿼리를 쿼리분석기에서 창을 10개 띄워놓고 10개를 동시에 실행시킨다.

set nocount on
waitfor time '15:50'
declare @i int
set @i = 1
while (@i <= 10000)
begin
insert into a default values
set @i = @i + 1
end
set nocount off

몇개나 들어갔는지 보구
select count(*) from a --100000

프로필러에서 캡춰된 내용을보면
CPU         Duration
-------     ------------
1. 2687 84406
2. 2344 88500
3. 2859 83623
4. 2641 87763
5. 2829 89920
6. 2610 87703
7. 2735 83533
8. 2500 89500
9. 2922 86110
10.2469 84876

/*------------------------------------------------------------------*/
2. 채번루틴의 경우
/*------------------------------------------------------------------*/
다시 테스트 하기 위해서 테이블을 지우고 다시만든다.
drop table a
create table a(id int not null)
create unique index idx on a(id)

이렇게 테이블 하나 만들어 두고
아래쿼리를 쿼리분석기에서 창을 10개 띄워놓고 10개를 동시에 실행시킨다.

set nocount on
waitfor time '16:10'
declare @i int
set @i = 1
while (@i <= 10000)
begin
insert into a(id)
select isnull(max(id),0)+1 from a
set @i = @i + 1
end
set nocount off
몇개나 들어갔는지 보구
select count(*) from a --99932

10만개가 아니다.
특별히 트랜잭션 처리를 하지 않았기 때문에 이경우는 아래 메시지와 같이
중복키 오류로 인하여 10만개의 row가 아니라 99932개의 row만이 입력되었다.

서버: 메시지 2601, 수준 14, 상태 3, 줄 6
고유 인덱스 'idx'이(가) 있는 'a' 개체에 중복 키 행을 삽입할 수 없습니다.
문이 종료되었습니다.

프로필러에서 캡춰된 내용을보면
CPU         Duration
-------     ------------
1. 5360 209363
2. 5156 215920
3. 4703 217686
4. 5484 217013
5. 5203 216330
6. 4719 218856
7. 5484 214186
8. 5578 213453
9. 5015 216343
10.4781 221640

/*------------------------------------------------------------------*/
이 결과를 보면 cpu점유율의 경우는 약2배 Duration의 경우는 약3배 정도의
차이를 보여주고 있다.
물론 어느정도의 오차를 감안한다고 하더라도 큰차이이고
또한 채번루틴의 경우 트랜잭션 처리를 반드시 해주어야 하므로
그것을 감안하면 이 경우보다는 훨씬 더 많은 차이가 날것이다.

/* ============================================================================== */
물론, 이외에두 max() 관련되 내용은 무지 많지만 오늘의 주 요지는 문자열 연결방식 이므로
이정도로 줄이고, 나중에 필요하다면 추가하도록 하겠습니다.

좀더 흥미로운 내용이 많이 있습니다.. HOME > SQL를 확인하세요

애드센스로 즐거운 블로깅 하세요 매일매일 늘어 가는 수익 즐거운 인터넷 세상입니다.
도움말을 오픈해 두시고 위의 [Google Adsense로 귀하의 ~~]를 클릭하셔서 가입을 진행하시면 됩니다.

그리고 더불어 간편하게 사진을 편집하는 Picasa, 불필요한 팝업없는 FireFox, Real player로 실시간 스포츠 관전, 요금 없는 Sky pe로 즐거운 통화등 구글 툴로 즐거운 컴생활을 즐기세요!!
일단 패키지를 설치해 보세요 컴퓨터 생활이 틀려집니다.