"Partition By" 또는 "Max" 사용 방법

"Partition By" 또는 "Max" 사용 방법

다음 표가 있습니다(my_data).

year |  X  |  Y
-----+-----+-----
2010 |  A  |  10
2011 |  A  |  20
2011 |  B  |  99
2009 |  C  |  30
2010 |  C  |  40

가장 높은 연도와 관련된 데이터만 검색할 수 있는 가장 적합한 / 가장 작은 SQL 문은 다음과 같이 'X'로 그룹화됩니다.

year |  X  |  Y
-----+-----+-----
2011 |  A  |  20
2011 |  B  |  99
2010 |  C  |  40

이 결과 테이블은 조인에 사용됩니다.

select year, x,y
from (
      select year, x, y, max(year) over(partition by x) max_year
      from my data
      )
where  year = max_year

select * from (
  select year, x, y, row_number() over (partition by x order by year desc ) rn 
  from my_data
) where rn = 1

휴대가 가능하고 외부 조인을 사용할 수도 있습니다.

select t1.year, t1.x, t1.y
  from my_data t1
  left join my_data t2
    on t2.x = t1.x
   and t2.year > t1.year
 where t2.x is null

다른 솔루션보다 훨씬 간단합니다.

SELECT x, max(year), MAX(y) KEEP (DENSE_RANK FIRST ORDER BY year DESC)
  FROM table
  GROUP BY x

CTE(Common Table Expression)를 사용할 수 있으며, 중복된 행에서도 작동합니다(필요한 경우). 실행 계획이 거의 동일합니다.

;With my_data_cte as (
    SELECT [year], x,y,ROW_NUMBER() OVER (
        PARTITION BY x
        ORDER BY [year] desc) as rn
FROM [dbo].[my_data])
select [year], x,y from my_data_cte 
where rn = 1

select year, x, y 
 from my_data stable 
where stable.year = (select max(year) 
                     from my_data tables 
                     where tables.x = stable.x);

Gary Myers, 예를 들어 값 A의 경우 2010보다 작은 연도에 최대값이 있는 경우 솔루션이 작동하지 않습니다.(예를 들어, 2005,A,50 행이 존재하는 경우)올바른 솔루션을 얻으려면 다음을 사용합니다.(값만 교환합니다.

SELECT x, max(y), MAX(year) KEEP (DENSE_RANK FIRST ORDER BY y DESC)
FROM test
GROUP BY x

-- I had a slightly different case and just wandering why this one should't work 
SELECT my_data.x , my_data.y , my_data1.max_year 
FROM my_data
INNER JOIN 
( 
  SELECT x , max (year ) as max_year
  FROM my_data
  -- WHERE 1=1
  -- AND FILTER1=VALUE1
  GROUP BY my_data.x
) my_data1
ON ( my_data.x = my_data1.x )

기준에서 하위 쿼리를 사용하여 각 X의 가장 최근 연도를 선택할 수 있습니다.

select a.year, a.x, a.y
from my_data a
where
  a.year = (
    select max(a_yr.year) from my_data a_yr
    where a_yr.x = a.x);

데이터:

year |  X  |  Y
-----+-----+-----
2010 |  A  |  10
2011 |  A  |  20
2011 |  B  |  99
2009 |  C  |  30
2010 |  C  |  40

결과:

year |  X  |  Y  
-----+-----+-----
2011 |  A  |  20 
2011 |  B  |  99 
2010 |  C  |  40 

제가 제한적으로 테스트한 결과, 이 방법은 partition by를 사용하는 것보다 더 빠른 것 같습니다.

이것은 또한 해결책이 될 수 있습니다.

abc에서 가장 최근 날짜로 가장 큰((e),(g),(c),(a),(b)를 선택합니다.

가장 간단한 것은

Select * 
from table 
where year = (select max(year) from table)

연간 인덱스가 없는 경우 테이블 스캔이 발생할 수 있습니다.하지만 인덱스가 있으면 성능이 좋아야 합니다.

언급URL : https://stackoverflow.com/questions/6198320/how-to-use-partition-by-or-max