PostgreSQL - 각 GROUP BY 그룹에 있는 열의 최대값이 있는 행을 가져옵니다.

PostgreSQL - 각 GROUP BY 그룹에 있는 열의 최대값이 있는 행을 가져옵니다.

저는 time_stamp, usr_id, transaction_id 및 lives_remaining에 대한 열이 있는 레코드를 포함하는 Postgres 테이블("lives"라고 함)을 다루고 있습니다. total_remaining 합니다.

1. 사용자가 .
   
2. . 에서 행별로 하나씩)가 한 time_messagetime_message를 할 수 있습니다. 때때로 동일한 time_message를 사용하여 사용자 이벤트(테이블의 행별로 하나씩)가 발생합니다.
   
3. 는 매우 합니다. 이 지남에 따라 trans_id를 반복합니다. 시간이 지남에 따라 반복됩니다.
   
4. 지정된 사용자에 대한 remaining_timeout은 시간이 지남에 따라 증가하거나 감소할 수 있습니다.

예:

time_sys|sys_sys|usr_id|trans_id-----------------------------------------07:00  |       1       |   1  |   109:00  |       4       |   2  |   210:00  |       2       |   3  |   310:00  |       1       |   2  |   411:00  |       4       |   1  |   511:00  |       3       |   1  |   613:00  |       3       |   3  |   1

지정된 각 usr_id에 대한 최신 데이터로 행의 다른 열에 액세스해야 하므로 다음과 같은 결과를 제공하는 쿼리가 필요합니다.

time_sys|sys_sys|usr_id|trans_id-----------------------------------------11:00  |       3       |   1  |   610:00  |       1       |   2  |   413:00  |       3       |   3  |   1

앞서 언급했듯이, 각 usr_id는 수명을 얻거나 잃을 수 있으며, 때때로 이러한 타임스탬프 이벤트가 동일한 타임스탬프를 가질 정도로 가까이에서 발생합니다!따라서 이 쿼리는 작동하지 않습니다.

SELECT b.time_stamp,b.lives_remaining,b.usr_id,b.trans_id FROM 
      (SELECT usr_id, max(time_stamp) AS max_timestamp 
       FROM lives GROUP BY usr_id ORDER BY usr_id) a 
JOIN lives b ON a.max_timestamp = b.time_stamp

대신 올바른 행을 식별하려면 time_stamp(첫 번째)와 trans_id(두 번째)를 모두 사용해야 합니다.그런 다음 하위 쿼리에서 해당 행의 다른 열에 대한 데이터를 제공하는 기본 쿼리로 해당 정보를 전달해야 합니다.이것은 제가 작업하기 위해 해킹된 쿼리입니다.

SELECT b.time_stamp,b.lives_remaining,b.usr_id,b.trans_id FROM 
      (SELECT usr_id, max(time_stamp || '*' || trans_id) 
       AS max_timestamp_transid
       FROM lives GROUP BY usr_id ORDER BY usr_id) a 
JOIN lives b ON a.max_timestamp_transid = b.time_stamp || '*' || b.trans_id 
ORDER BY b.usr_id

좋아요, 효과는 있지만 마음에 안 들어요.쿼리 내의 쿼리, 자체 조인이 필요하며 MAX가 가장 큰 타임스탬프와 trans_id를 가진 행을 잡는 것이 훨씬 더 간단할 것 같습니다.테이블 "lives"에는 구문 분석할 행이 수천만 개이므로 이 쿼리를 최대한 빠르고 효율적으로 수행했으면 합니다.저는 특히 RDBM과 Postgres에 익숙하지 않기 때문에 적절한 인덱스를 효과적으로 사용해야 한다는 것을 알고 있습니다.최적화하는 방법에 대해 좀 망설여집니다.

저는 여기서 비슷한 논의를 발견했습니다.Oracle 분석 기능과 동일한 Postgres 유형을 수행할 수 있습니까?

집계 함수(MAX 등)에서 사용하는 관련 열 정보에 액세스하고 인덱스를 생성하고 더 나은 쿼리를 생성하는 방법에 대한 조언을 제공해 주시면 감사하겠습니다.

추신: 다음을 사용하여 예제 사례를 만들 수 있습니다.

create TABLE lives (time_stamp timestamp, lives_remaining integer, 
                    usr_id integer, trans_id integer);
insert into lives values ('2000-01-01 07:00', 1, 1, 1);
insert into lives values ('2000-01-01 09:00', 4, 2, 2);
insert into lives values ('2000-01-01 10:00', 2, 3, 3);
insert into lives values ('2000-01-01 10:00', 1, 2, 4);
insert into lives values ('2000-01-01 11:00', 4, 1, 5);
insert into lives values ('2000-01-01 11:00', 3, 1, 6);
insert into lives values ('2000-01-01 13:00', 3, 3, 1);

다음을 기반으로 한 깨끗한 버전을 제안합니다.DISTINCT ON(문서 참조):

SELECT DISTINCT ON (usr_id)
    time_stamp,
    lives_remaining,
    usr_id,
    trans_id
FROM lives
ORDER BY usr_id, time_stamp DESC, trans_id DESC;

균일하게 분포되어 usr_id, 158k는 usr_id 0과 10k는 usr_id 0과 10k는 usr_id와 10k는 usr_id와 10k는 usr_id와 10k는 usr_id와 10k는 usr_id의 관계가 있는 에서,trans_id ~), 0 ~ 30 사 30 이 균 게 분 포 하 일 과 에 ▁uniformly

비용은 옵티마이저 견적은 Postgres입니다.)입니다.xxx_costvalues한 I 및 리소스의 입니다. ()을 하면 이 수 . PgAdmin을 실행하여 이 값을 얻을 수 있습니다.III, "쿼리/설명 옵션"이 "분석"으로 설정된 상태에서 쿼리에서 "쿼리/설명(F7)" 실행

• 의 추정치를 3초 됩니다(Quassnoy 쿼리는 745k(!)의). (Quassnoy의 쿼리는 1.3초 안에 완료됩니다.usr_id,trans_id,time_stamp))
• 됩니다((Bill 쿼 93k 비용의를정복초에됩 2 2.9료에면주가합지어수지의고지며으리치는있가추▁(복▁(▁)▁bill▁a▁on에▁hasbill▁index▁in▁query)▁('다ks▁ofgiven면주).)usr_id,trans_id))
• 아래 쿼리 #1의 비용 추정치는 16k이며 800ms 내에 완료됩니다(복합 인덱스는 다음과 같습니다).usr_id,trans_id,time_stamp))
• 아래의 쿼리 #2는 14k의 비용 추정치를 가지며, 800ms 내에 완료됩니다. (복합 함수 인덱스는 다음과 같습니다.)usr_id,EXTRACT(EPOCH FROM time_stamp),trans_id))
  • 이것은 Postgres별입니다.
• 아래의 3번 쿼리(Postgres 8.4+)는 2번 쿼리(또는 그보다 더 나은)와 비슷한 비용 추정 및 완료 시간을 갖습니다.usr_id,time_stamp,trans_id)); 스캔할 수 있는 장점이 있습니다.lives테이블은 한 번만 가능하며, 메모리에서 정렬을 수용하기 위해 일시적으로 work_mem을 늘리면(필요한 경우) 모든 쿼리 중에서 가장 빠릅니다.

위의 모든 시간에는 전체 10k 행 결과 세트의 검색이 포함됩니다.

목표는 최소 비용 견적과 최소 쿼리 실행 시간이며, 추정 비용에 중점을 둡니다.쿼리 실행은 런타임 조건(예: 관련 행이 이미 메모리에 완전히 캐시되었는지 여부)에 따라 크게 달라질 수 있지만, 비용 추정치는 그렇지 않습니다.반면에, 비용 추정치는 정확히 그것, 추정치라는 것을 명심하세요.

전용 데이터베이스에서 로드 없이 실행할 때 최상의 쿼리 실행 시간을 얻을 수 있습니다(예: pgAdmin으로 실행).개발 PC의 III.)쿼리 시간은 실제 시스템 로드/데이터 액세스 범위에 따라 프로덕션에 따라 달라집니다.하나의 쿼리가 다른 쿼리보다 약간 빠르지만(<20%) 비용이 훨씬 높을 경우 일반적으로 실행 시간은 높지만 비용은 낮은 쿼리를 선택하는 것이 더 현명합니다.

쿼리가 실행될 때 운영 시스템에서 메모리 경쟁이 없을 것으로 예상되는 경우(예: RDBMS 캐시 및 파일 시스템 캐시가 동시 쿼리 및/또는 파일 시스템 작업에 의해 스레시되지 않을 것임) 독립 실행형에서 얻은 쿼리 시간(예: pgAdmin)개발 PC의 III) 모드가 대표적입니다.프로덕션 시스템에서 경합이 발생하는 경우, 비용이 낮은 쿼리는 캐쉬에 크게 의존하지 않는 반면, 비용이 높은 쿼리는 동일한 데이터를 반복적으로 재검토하기 때문에(안정적인 캐쉬가 없는 경우 추가 I/O가 트리거됨), 예상 비용 비율에 비례하여 쿼리 시간이 저하됩니다.

              cost | time (dedicated machine) |     time (under load) |
-------------------+--------------------------+-----------------------+
some query A:   5k | (all data cached)  900ms | (less i/o)     1000ms |
some query B:  50k | (all data cached)  900ms | (lots of i/o) 10000ms |

실행하는 것을 잊지 마십시오.ANALYZE lives필요한 인덱스를 생성한 후 한 번.

---

쿼리 #1

-- incrementally narrow down the result set via inner joins
--  the CBO may elect to perform one full index scan combined
--  with cascading index lookups, or as hash aggregates terminated
--  by one nested index lookup into lives - on my machine
--  the latter query plan was selected given my memory settings and
--  histogram
SELECT
  l1.*
 FROM
  lives AS l1
 INNER JOIN (
    SELECT
      usr_id,
      MAX(time_stamp) AS time_stamp_max
     FROM
      lives
     GROUP BY
      usr_id
  ) AS l2
 ON
  l1.usr_id     = l2.usr_id AND
  l1.time_stamp = l2.time_stamp_max
 INNER JOIN (
    SELECT
      usr_id,
      time_stamp,
      MAX(trans_id) AS trans_max
     FROM
      lives
     GROUP BY
      usr_id, time_stamp
  ) AS l3
 ON
  l1.usr_id     = l3.usr_id AND
  l1.time_stamp = l3.time_stamp AND
  l1.trans_id   = l3.trans_max

쿼리 #2

-- cheat to obtain a max of the (time_stamp, trans_id) tuple in one pass
-- this results in a single table scan and one nested index lookup into lives,
--  by far the least I/O intensive operation even in case of great scarcity
--  of memory (least reliant on cache for the best performance)
SELECT
  l1.*
 FROM
  lives AS l1
 INNER JOIN (
   SELECT
     usr_id,
     MAX(ARRAY[EXTRACT(EPOCH FROM time_stamp),trans_id])
       AS compound_time_stamp
    FROM
     lives
    GROUP BY
     usr_id
  ) AS l2
ON
  l1.usr_id = l2.usr_id AND
  EXTRACT(EPOCH FROM l1.time_stamp) = l2.compound_time_stamp[1] AND
  l1.trans_id = l2.compound_time_stamp[2]

2013/01/29 업데이트

마지막으로 버전 8.4에서 Postgres는 Window Function을 지원하므로 다음과 같이 간단하고 효율적으로 작성할 수 있습니다.

쿼리 #3

-- use Window Functions
-- performs a SINGLE scan of the table
SELECT DISTINCT ON (usr_id)
  last_value(time_stamp) OVER wnd,
  last_value(lives_remaining) OVER wnd,
  usr_id,
  last_value(trans_id) OVER wnd
 FROM lives
 WINDOW wnd AS (
   PARTITION BY usr_id ORDER BY time_stamp, trans_id
   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 );

상관된 하위 쿼리나 GROUP BY를 사용하지 않는 또 다른 방법이 있습니다.저는 포스트그레 전문가가 아닙니다.SQL 성능 조정 기능을 사용하려면 이 기능과 다른 사용자가 제공하는 솔루션을 모두 사용해 보는 것이 더 잘 작동하는지 확인하는 것이 좋습니다.

SELECT l1.*
FROM lives l1 LEFT OUTER JOIN lives l2
  ON (l1.usr_id = l2.usr_id AND (l1.time_stamp < l2.time_stamp 
   OR (l1.time_stamp = l2.time_stamp AND l1.trans_id < l2.trans_id)))
WHERE l2.usr_id IS NULL
ORDER BY l1.usr_id;

라고 가정하고 있습니다.trans_id된 값 고유합니다.time_stamp.

Postgressql 9.5에는 DISTINCT ON이라는 새로운 옵션이 있습니다.

SELECT DISTINCT ON (location) location, time, report
    FROM weather_reports
    ORDER BY location, time DESC;

중복 행을 제거하고 첫 번째 행만 내 ORDER BY 절에 정의된 대로 남깁니다.

공식 문서를 참조

당신이 언급한 다른 페이지의 마이크 우드하우스의 답변 스타일이 마음에 듭니다.특히 최대화되는 항목이 단일 열일 때는 특히 간결하며, 이 경우 하위 쿼리가 사용할 수 있습니다.MAX(some_col)그리고.GROUP BY 두 으로 구성된 수량이 , 이 다른열, 그나당경최우, 대해야화할 2-다트수있습을 할 수 .ORDER BY플러스LIMIT 1대신(Quassnoi가 수행한 것처럼):

SELECT * 
FROM lives outer
WHERE (usr_id, time_stamp, trans_id) IN (
    SELECT usr_id, time_stamp, trans_id
    FROM lives sq
    WHERE sq.usr_id = outer.usr_id
    ORDER BY trans_id, time_stamp
    LIMIT 1
)

구문을 을 찾습니다.WHERE (a, b, c) IN (subquery)필요한 폭언의 양을 줄여주기 때문에 좋습니다.

사실 이 문제에 대한 해괴한 해결책이 있습니다.한 지역에서 각 숲의 가장 큰 트리를 선택하려고 합니다.

SELECT (array_agg(tree.id ORDER BY tree_size.size)))[1]
FROM tree JOIN forest ON (tree.forest = forest.id)
GROUP BY forest.id

여러분이 숲별로 나무를 그룹화할 때, 분류되지 않은 나무 목록이 있을 것이고 여러분은 가장 큰 나무를 찾아야 합니다.먼저 행을 크기별로 정렬하고 목록의 첫 번째 행을 선택합니다.비효율적으로 보일 수 있지만 수백만 개의 행이 있는 경우 다음과 같은 솔루션보다 훨씬 빠릅니다.JOIN의 모래WHERE조건들.

로 BTW, 참로고.ORDER_BY위해서array_agg 9. Postgresql 9.0에 .

창 기능으로 할 수 있습니다.

SELECT t.*
FROM
    (SELECT
        *,
        ROW_NUMBER() OVER(PARTITION BY usr_id ORDER BY time_stamp DESC) as r
    FROM lives) as t
WHERE t.r = 1

SELECT  l.*
FROM    (
        SELECT DISTINCT usr_id
        FROM   lives
        ) lo, lives l
WHERE   l.ctid = (
        SELECT ctid
        FROM   lives li
        WHERE  li.usr_id = lo.usr_id
        ORDER BY
          time_stamp DESC, trans_id DESC
        LIMIT 1
        )

에 인덱스 (usr_id, time_stamp, trans_id)이 쿼리를 크게 향상시킵니다.

당신은 항상, 항상 어떤 종류의 것을 가져야 합니다.PRIMARY KEY당신의 테이블에서.

여기서 한 가지 주요 문제가 있다고 생각합니다. 주어진 행이 다른 행보다 나중에 발생했다는 것을 보장하기 위해 단조롭게 증가하는 "카운터"는 없습니다.다음 예를 들어 보겠습니다.

timestamp   lives_remaining   user_id   trans_id
10:00       4                 3         5
10:00       5                 3         6
10:00       3                 3         1
10:00       2                 3         2

이 데이터에서 가장 최근의 항목을 확인할 수 없습니다.두 번째인가요, 아니면 마지막인가요?이 데이터에 적용할 수 있는 정렬 또는 최대() 함수는 없습니다.

타임스탬프의 해상도를 높이는 것이 큰 도움이 될 것입니다.데이터베이스 엔진은 요청을 직렬화하므로 충분한 해상도로 두 타임스탬프가 동일하지 않음을 보장할 수 있습니다.

또는 아주 오랫동안 롤오버되지 않는 trans_id를 사용합니다.trans_id가 롤오버된다는 것은 복잡한 연산을 수행하지 않는 한 trans_id 6이 trans_id 1보다 최신인지 여부를 (동일한 타임스탬프에 대해) 알 수 없다는 것을 의미합니다.

언급URL : https://stackoverflow.com/questions/586781/postgresql-fetch-the-rows-which-have-the-max-value-for-a-column-in-each-group