Programing

열에 대한 Max 값이있는 행을 가져옵니다.

lottogame 2020. 10. 4. 10:14
반응형

열에 대한 Max 값이있는 행을 가져옵니다.

표: 

UserId, Value, Date.

각 UserId에 대한 UserId, max (Date) 값을 얻고 싶습니다. 즉, 최신 날짜가있는 각 UserId의 값입니다. SQL에서 간단히 수행 할 수있는 방법이 있습니까? (바람직하게는 Oracle)

업데이트 : 모호함에 대한 사과 : 모든 사용자 ID를 가져와야합니다. 그러나 각 UserId에 대해 해당 사용자가 최신 날짜를 가지고있는 행만.

그러면 my_date 열 값이 해당 사용자 ID에 대한 my_date의 최대 값과 동일한 모든 행이 검색됩니다. 최대 날짜가 여러 행에있는 사용자 ID에 대해 여러 행을 검색 할 수 있습니다.

select userid,
       my_date,
       ...
from
(
select userid,
       my_date,
       ...
       max(my_date) over (partition by userid) max_my_date
from   users
)
where my_date = max_my_date

"분석 기능이 흔들린다"

편집 : 첫 번째 의견에 관하여 ...

"분석 쿼리 및 자체 조인을 사용하면 분석 쿼리의 목적이 무효화됩니다."

이 코드에는 자체 조인이 없습니다. 대신 분석 함수를 포함하는 인라인 뷰의 결과에 조건자가 배치됩니다. 매우 다른 문제이며 완전히 표준 방식입니다.

"Oracle의 기본 창은 파티션의 첫 번째 행에서 현재 행까지입니다."

기간 설정 절은 order by 절이있는 경우에만 적용됩니다. order by 절이 없으면 기본적으로 windowing 절이 적용되지 않으며 명시 적으로 지정할 수 없습니다.

코드가 작동합니다.

많은 사람들이 하위 쿼리 또는 기타 공급 업체별 기능을 사용하여이를 수행하는 것을 보지만, 종종 다음과 같은 방법으로 하위 쿼리없이 이러한 종류의 쿼리를 수행합니다. 일반 표준 SQL을 사용하므로 모든 브랜드의 RDBMS에서 작동합니다.

SELECT t1.*
FROM mytable t1
  LEFT OUTER JOIN mytable t2
    ON (t1.UserId = t2.UserId AND t1."Date" < t2."Date")
WHERE t2.UserId IS NULL;

즉 t1, 동일 UserId하고 더 큰 날짜를 가진 다른 행이 존재하지 않는 행을 가져옵니다 .

( "Date"식별자는 SQL 예약어이기 때문에 구분 기호에 넣었습니다.)

인 경우 t1."Date" = t2."Date"두 배가 나타납니다. 일반적으로 테이블에는 auto_inc(seq)키 가 id있습니다. 배가를 피하기 위해 다음을 사용할 수 있습니다.

SELECT t1.*
FROM mytable t1
  LEFT OUTER JOIN mytable t2
    ON t1.UserId = t2.UserId AND ((t1."Date" < t2."Date") 
         OR (t1."Date" = t2."Date" AND t1.id < t2.id))
WHERE t2.UserId IS NULL;

@Farhan의 다시 코멘트 :

다음은 더 자세한 설명입니다.

외부 조인 시도 가입 t1과를 t2. 기본적으로의 모든 결과 t1가 반환 되며에 일치하는 항목이있는 경우t2 에도 반환됩니다. t2의 지정된 행 과 일치하는 항목이없는 경우 t1쿼리는 여전히의 행을 반환하고의 모든 열에 대한 자리 표시 자로 t1사용합니다 . 이것이 바로 외부 조인이 일반적으로 작동하는 방식입니다.NULLt2

이 쿼리의 트릭은이의 일치 그러한 조건 가입 설계하는 것입니다 t2일치해야한다 동일 userid 하고, 이상을 date . t2더 큰 행이 존재하면 그에 대해 비교 date되는 행이 t1그에 대해 가장 클 수 없다는 생각 date입니다 userid. 그러나 일치하는 행이 없으면 (즉 , 행 t2보다 큰 행이 존재하지 않는 경우) 행이 주어진 행에 대해 가장 큰 행 이라는 것을 압니다 .datet1t1dateuserid

이러한 경우의 열 (때 일치가 없다) t2됩니다 NULL에 지정된에도 열 조인 조건 -. 그래서 우리가를 사용하는 이유는 주어진 .에 대해 WHERE t2.UserId IS NULL더 큰 행을 찾을 수없는 경우를 찾고 있기 때문 입니다.dateuserid

SELECT userid, MAX(value) KEEP (DENSE_RANK FIRST ORDER BY date DESC)
  FROM table
  GROUP BY userid

정확한 열 이름은 모르지만 다음과 같습니다.

    사용자 ID, 값 선택
      사용자 u1에서
     여기서 날짜 = (최대 (날짜) 선택
                     사용자 u2에서
                    여기서 u1.userid = u2.userid)

작업 중이 아니므로 Oracle은 제공 할 수 없지만 Oracle은 IN 절에서 여러 열을 일치시킬 수 있다는 것을 기억하는 것 같습니다. 이는 적어도 상관 된 하위 쿼리를 사용하는 옵션을 피해야합니다. 생각.

아마도 다음과 같습니다 (열 목록을 괄호로 묶어야하는지 여부를 기억할 수 없음).

SELECT * 
FROM MyTable
WHERE (User, Date) IN
  ( SELECT User, MAX(Date) FROM MyTable GROUP BY User)

편집 : 그냥 진짜로 시도 :

SQL> create table MyTable (usr char(1), dt date);
SQL> insert into mytable values ('A','01-JAN-2009');
SQL> insert into mytable values ('B','01-JAN-2009');
SQL> insert into mytable values ('A', '31-DEC-2008');
SQL> insert into mytable values ('B', '31-DEC-2008');
SQL> select usr, dt from mytable
  2  where (usr, dt) in 
  3  ( select usr, max(dt) from mytable group by usr)
  4  /

U DT
- ---------
A 01-JAN-09
B 01-JAN-09

따라서 다른 곳에서 언급 된 새로운 기능 중 일부가 더 성능이 좋더라도 작동합니다.

Oracle을 요청한 것을 알고 있지만 SQL 2005에서는 이제 다음을 사용합니다.


-- Single Value
;WITH ByDate
AS (
SELECT UserId, Value, ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC) RowNum
FROM UserDates
)
SELECT UserId, Value
FROM ByDate
WHERE RowNum = 1

-- Multiple values where dates match
;WITH ByDate
AS (
SELECT UserId, Value, RANK() OVER (PARTITION BY UserId ORDER BY Date DESC) Rnk
FROM UserDates
)
SELECT UserId, Value
FROM ByDate
WHERE Rnk = 1

테스트 할 Oracle이 없지만 가장 효율적인 솔루션은 분석 쿼리를 사용하는 것입니다. 다음과 같이 보일 것입니다.

SELECT DISTINCT
    UserId
  , MaxValue
FROM (
    SELECT UserId
      , FIRST (Value) Over (
          PARTITION BY UserId
          ORDER BY Date DESC
        ) MaxValue
    FROM SomeTable
  )

나는 당신이 외부 쿼리를 제거하고 내부 쿼리를 구분할 수 있다고 생각하지만 확실하지 않습니다. 그동안 나는 이것이 작동한다는 것을 알고 있습니다.

분석 쿼리에 대해 배우려면 http://www.orafaq.com/node/55 및 http://www.akadia.com/services/ora_analytic_functions.html을 읽어 보시기 바랍니다 . 다음은 간단한 요약입니다.

내부 분석 쿼리는 전체 데이터 세트를 정렬 한 다음 순차적으로 처리합니다. 처리 할 때 특정 기준에 따라 데이터 세트를 분할 한 다음 각 행에 대해 일부 창을 살펴보고 (기본값은 현재 행에 대한 분할의 첫 번째 값이며 기본값도 가장 효율적 임) 다음을 사용하여 값을 계산할 수 있습니다. 분석 함수의 수 (그 목록은 집계 함수와 매우 유사 함).

이 경우 내부 쿼리가 수행하는 작업입니다. 전체 데이터 세트는 UserId, Date DESC 순으로 정렬됩니다. 그런 다음 한 번에 처리합니다. 각 행에 대해 UserId와 해당 UserId에 대해 표시된 첫 번째 날짜를 반환합니다 (날짜는 DESC로 정렬되므로 최대 날짜입니다). 이것은 중복 된 행에 대한 답을 제공합니다. 그런 다음 외부 DISTINCT는 중복을 스쿼시합니다.

이것은 분석 쿼리의 특별한 예가 아닙니다. 훨씬 더 큰 승리를 위해 재정 영수증 테이블을 가져와 각 사용자와 영수증에 대해 계산하는 것을 고려하십시오. 분석 쿼리는이를 효율적으로 해결합니다. 다른 솔루션은 효율성이 떨어집니다. 이것이 2003 SQL 표준의 일부인 이유입니다. (안타깝게도 Postgres는 아직 가지고 있지 않습니다. Grrr ...)

QUALIFY 절이 가장 간단하면서도 최고가 아닐까요?

select userid, my_date, ...
from users
qualify rank() over (partition by userid order by my_date desc) = 1

맥락을 위해 Teradata에서는이 QUALIFY 버전을 사용하여 17 초에, '인라인보기'/ Aldridge 솔루션 # 1을 사용하여 23 초에 적절한 크기 테스트를 실행합니다.

에서은 Oracle 12c+, 당신이 사용할 수있는 상위 N 분석 함수와 함께 쿼리를 rank매우 간결이를 달성하기 없이 하위 쿼리 :

select *
from your_table
order by rank() over (partition by user_id order by my_date desc)
fetch first 1 row with ties;

위는 사용자 당 최대 my_date가있는 모든 행을 반환합니다.

당신이 최대 날짜가 하나 개의 행을 원하는 경우, 다음을 대체 rank와 함께 row_number:

select *
from your_table
order by row_number() over (partition by user_id order by my_date desc)
fetch first 1 row with ties;

PostgreSQL 8.4 이상에서는 다음을 사용할 수 있습니다.

select user_id, user_value_1, user_value_2
  from (select user_id, user_value_1, user_value_2, row_number()
          over (partition by user_id order by user_date desc) 
        from users) as r
  where r.row_number=1

사용 ROW_NUMBER()고유은 내림차순 순위에 할당 할 Date각 UserId각의 첫 번째 행에 다음, 필터 UserId(즉, ROW_NUMBER= 1).

SELECT UserId, Value, Date
FROM (SELECT UserId, Value, Date,
        ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC) rn
      FROM users) u
WHERE rn = 1;

Select  
   UserID,  
   Value,  
   Date  
From  
   Table,  
   (  
      Select  
          UserID,  
          Max(Date) as MDate  
      From  
          Table  
      Group by  
          UserID  
    ) as subQuery  
Where  
   Table.UserID = subQuery.UserID and  
   Table.Date = subQuery.mDate

직장에서 "실시간"예제를 작성해야했습니다. :)

이것은 동일한 날짜의 UserId에 대한 여러 값을 지원 합니다.

열 : 사용자 ID, 값, 날짜

SELECT
   DISTINCT UserId,
   MAX(Date) OVER (PARTITION BY UserId ORDER BY Date DESC),
   MAX(Values) OVER (PARTITION BY UserId ORDER BY Date DESC)
FROM
(
   SELECT UserId, Date, SUM(Value) As Values
   FROM <<table_name>>
   GROUP BY UserId, Date
)

MAX 대신 FIRST_VALUE를 사용하고 계획 설명에서 찾아 볼 수 있습니다. 나는 그것을 가지고 놀 시간이 없었다.

물론 거대한 테이블을 검색하는 경우 쿼리에 FULL 힌트를 사용하는 것이 더 좋습니다.

select VALUE from TABLE1 where TIME = 
   (select max(TIME) from TABLE1 where DATE= 
   (select max(DATE) from TABLE1 where CRITERIA=CRITERIA))

이런 것 같아요. (구문 오류에 대해 용서하십시오.이 시점에서 HQL을 사용하는 데 익숙합니다!)

편집 : 또한 질문을 잘못 읽으십시오! 쿼리 수정 ...

SELECT UserId, Value
FROM Users AS user
WHERE Date = (
    SELECT MAX(Date)
    FROM Users AS maxtest
    WHERE maxtest.UserId = user.UserId
)

나는이 변형을 이전 쿼리로 만들었습니다.

SELECT UserId, Value FROM Users U1 WHERE 
Date = ( SELECT MAX(Date)    FROM Users where UserId = U1.UserId)

(T-SQL) 먼저 모든 사용자와 최대 날짜를 가져옵니다. 테이블과 결합하여 maxdates에서 사용자에 해당하는 값을 찾으십시오.

create table users (userid int , value int , date datetime)
insert into users values (1, 1, '20010101')
insert into users values (1, 2, '20020101')
insert into users values (2, 1, '20010101')
insert into users values (2, 3, '20030101')

select T1.userid, T1.value, T1.date 
    from users T1,
    (select max(date) as maxdate, userid from users group by userid) T2    
    where T1.userid= T2.userid and T1.date = T2.maxdate

결과 :

userid      value       date                                    
----------- ----------- -------------------------- 
2           3           2003-01-01 00:00:00.000
1           2           2002-01-01 00:00:00.000

여기에 대한 대답은 Oracle뿐입니다. 다음은 모든 SQL에서 좀 더 정교한 답변입니다.

전체 숙제 결과가 가장 좋은 사람은 누구입니까 (최대 숙제 점수 합계)?

SELECT FIRST, LAST, SUM(POINTS) AS TOTAL
FROM STUDENTS S, RESULTS R
WHERE S.SID = R.SID AND R.CAT = 'H'
GROUP BY S.SID, FIRST, LAST
HAVING SUM(POINTS) >= ALL (SELECT SUM (POINTS)
FROM RESULTS
WHERE CAT = 'H'
GROUP BY SID)

그리고 좀 더 어려운 예는 설명이 필요합니다.

2008 년에 가장 많이 사용 된 책 (ISBN 및 제목)을 제공합니다. 즉, 2008 년에 가장 많이 빌 렸습니다.

SELECT X.ISBN, X.title, X.loans
FROM (SELECT Book.ISBN, Book.title, count(Loan.dateTimeOut) AS loans
FROM CatalogEntry Book
LEFT JOIN BookOnShelf Copy
ON Book.bookId = Copy.bookId
LEFT JOIN (SELECT * FROM Loan WHERE YEAR(Loan.dateTimeOut) = 2008) Loan 
ON Copy.copyId = Loan.copyId
GROUP BY Book.title) X
HAVING loans >= ALL (SELECT count(Loan.dateTimeOut) AS loans
FROM CatalogEntry Book
LEFT JOIN BookOnShelf Copy
ON Book.bookId = Copy.bookId
LEFT JOIN (SELECT * FROM Loan WHERE YEAR(Loan.dateTimeOut) = 2008) Loan 
ON Copy.copyId = Loan.copyId
GROUP BY Book.title);

이것이 (누구나) 도움이되기를 바랍니다 .. :)

감사합니다, Guus

주어진 UserID에 대해 날짜가 고유하다고 가정하면 다음과 같은 TSQL이 있습니다.

SELECT 
    UserTest.UserID, UserTest.Value
FROM UserTest
INNER JOIN
(
    SELECT UserID, MAX(Date) MaxDate
    FROM UserTest
    GROUP BY UserID
) Dates
ON UserTest.UserID = Dates.UserID
AND UserTest.Date = Dates.MaxDate

I'm quite late to the party but the following hack will outperform both correlated subqueries and any analytics function but has one restriction: values must convert to strings. So it works for dates, numbers and other strings. The code does not look good but the execution profile is great. 

select
    userid,
    to_number(substr(max(to_char(date,'yyyymmdd') || to_char(value)), 9)) as value,
    max(date) as date
from 
    users
group by
    userid

The reason why this code works so well is that it only needs to scan the table once. It does not require any indexes and most importantly it does not need to sort the table, which most analytics functions do. Indexes will help though if you need to filter the result for a single userid.

select userid, value, date
  from thetable t1 ,
       ( select t2.userid, max(t2.date) date2 
           from thetable t2 
          group by t2.userid ) t3
 where t3.userid t1.userid and
       t3.date2 = t1.date

IMHO this works. HTH

I think this should work?

Select
T1.UserId,
(Select Top 1 T2.Value From Table T2 Where T2.UserId = T1.UserId Order By Date Desc) As 'Value'
From
Table T1
Group By
T1.UserId
Order By
T1.UserId

First try I misread the question, following the top answer, here is a complete example with correct results:

CREATE TABLE table_name (id int, the_value varchar(2), the_date datetime);

INSERT INTO table_name (id,the_value,the_date) VALUES(1 ,'a','1/1/2000');
INSERT INTO table_name (id,the_value,the_date) VALUES(1 ,'b','2/2/2002');
INSERT INTO table_name (id,the_value,the_date) VALUES(2 ,'c','1/1/2000');
INSERT INTO table_name (id,the_value,the_date) VALUES(2 ,'d','3/3/2003');
INSERT INTO table_name (id,the_value,the_date) VALUES(2 ,'e','3/3/2003');

--

  select id, the_value
      from table_name u1
      where the_date = (select max(the_date)
                     from table_name u2
                     where u1.id = u2.id)

--

id          the_value
----------- ---------
2           d
2           e
1           b

(3 row(s) affected)

This will also take care of duplicates (return one row for each user_id):

SELECT *
FROM (
  SELECT u.*, FIRST_VALUE(u.rowid) OVER(PARTITION BY u.user_id ORDER BY u.date DESC) AS last_rowid
  FROM users u
) u2
WHERE u2.rowid = u2.last_rowid

Just tested this and it seems to work on a logging table

select ColumnNames, max(DateColumn) from log  group by ColumnNames order by 1 desc

This should be as simple as:

SELECT UserId, Value
FROM Users u
WHERE Date = (SELECT MAX(Date) FROM Users WHERE UserID = u.UserID)

Solution for MySQL which doesn't have concepts of partition KEEP, DENSE_RANK. 

select userid,
       my_date,
       ...
from
(
select @sno:= case when @pid<>userid then 0
                    else @sno+1
    end as serialnumber, 
    @pid:=userid,
       my_Date,
       ...
from   users order by userid, my_date
) a
where a.serialnumber=0

Reference: http://benincampus.blogspot.com/2013/08/select-rows-which-have-maxmin-value-in.html

If you're using Postgres, you can use array_agg like

SELECT userid,MAX(adate),(array_agg(value ORDER BY adate DESC))[1] as value
FROM YOURTABLE
GROUP BY userid

I'm not familiar with Oracle. This is what I came up with

SELECT 
  userid,
  MAX(adate),
  SUBSTR(
    (LISTAGG(value, ',') WITHIN GROUP (ORDER BY adate DESC)),
    0,
    INSTR((LISTAGG(value, ',') WITHIN GROUP (ORDER BY adate DESC)), ',')-1
  ) as value 
FROM YOURTABLE
GROUP BY userid

Both queries return the same results as the accepted answer. See SQLFiddles:

  1. Accepted answer
  2. My solution with Postgres
  3. My solution with Oracle

If (UserID, Date) is unique, i.e. no date appears twice for the same user then:

select TheTable.UserID, TheTable.Value
from TheTable inner join (select UserID, max([Date]) MaxDate
                          from TheTable
                          group by UserID) UserMaxDate
     on TheTable.UserID = UserMaxDate.UserID
        TheTable.[Date] = UserMaxDate.MaxDate;

select   UserId,max(Date) over (partition by UserId) value from users;

참고URL : https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column

반응형

'Programing' 카테고리의 다른 글

iloc, ix 및 loc은 어떻게 다릅니 까?  (0) 2020.10.04
CSV 파일 데이터를 PostgreSQL 테이블로 가져 오는 방법은 무엇입니까?  (0) 2020.10.04
Git은 GitHub에서 특정 분기를 가져옵니다.  (0) 2020.10.04
Python의 디렉토리 트리 목록  (0) 2020.10.04
Perl에서 로케일 설정 경고를 수정하는 방법은 무엇입니까?  (0) 2020.10.04

'Programing'의 다른글

  • 현재글열에 대한 Max 값이있는 행을 가져옵니다.

관련글

댓글

티스토리툴바