복붙노트

[SQL] PostgreSQL은 - 열에 대해 최대 값을 갖는 행을 페치

SQL

PostgreSQL은 - 열에 대해 최대 값을 갖는 행을 페치

나는 TIME_STAMP, usr_id, TRANSACTION_ID 및 lives_remaining에 대한 열이있는 레코드를 포함 ( "삶"이라고 함) 포스트 그레스 테이블을 취급하고 있습니다. 나는 각 usr_id 나에게 가장 최근의 lives_remaining 총을 줄 것이다 질의를 필요로

예:

time_stamp|lives_remaining|usr_id|trans_id
-----------------------------------------
  07:00  |       1       |   1  |   1    
  09:00  |       4       |   2  |   2    
  10:00  |       2       |   3  |   3    
  10:00  |       1       |   2  |   4    
  11:00  |       4       |   1  |   5    
  11:00  |       3       |   1  |   6    
  13:00  |       3       |   3  |   1

나는 각각의 주어진 usr_id에 대한 액세스에 대한 최신 데이터 행의 다른 컬럼이 필요합니다, 나는이 같은 결과를 제공하는 질의를 필요로한다 :

time_stamp|lives_remaining|usr_id|trans_id
-----------------------------------------
  11:00  |       3       |   1  |   6    
  10:00  |       1       |   2  |   4    
  13: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을 가지고 발견 행을 잡아하여 훨씬 더 간단 할 수 있다는 것을 나에게 보인다. 나는이 쿼리로 신속하고 효율적으로 가능한 있도록하고 싶습니다 있도록 테이블 "삶", 구문 분석에 수십 수백만 개의 행의가 있습니다. 내가 적절한 인덱스를 효율적으로 사용할 수 있도록해야한다는 것을 알 수 있도록 나는, 특히 RDBM와 포스트 그레스에 새로운 해요. 나는 최적화하는 방법에 손실 조금 있어요.

여기 비슷한 토론을 발견했다. 나는 오라클 분석 함수에 포스트 그레스의 상당의 몇 가지 유형을 수행 할 수 있습니까?

(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);

해결법

  1. ==============================

    1.158K 의사 랜덤 행 테이블 (균일 0과 10,000 사이에 분배 usr_id 균일 0과 30 사이에 분배 trans_id)

    158K 의사 랜덤 행 테이블 (균일 0과 10,000 사이에 분배 usr_id 균일 0과 30 사이에 분배 trans_id)

    아래 쿼리 비용, 나는 필요한 I / O 및 CPU 자원의 무게 기능 추정치이다 '(기본 xxx_cost 값 포스트 그레스 포스트 그레스와) 비용 기반 최적화 프로그램의 비용 견적'을 언급하고있다; 당신이 "쿼리 / 옵션 설명"세트 쿼리의 "쿼리 / (F7)을 설명하라"PgAdminIII을 발사하고 실행하여이를 얻을 수 있습니다 "분석"

    모든 시간은 위의 전체 10,000 행의 검색은 결과 세트를 포함한다.

    당신의 목표는 예상 비용에 중점을 최소의 비용 추정 및 최소한의 쿼리 실행 시간입니다. 쿼리 실행 (관련 행이 이미 완전히 메모리에 캐시되지 않았거나, 예를 들어 여부), 비용 추정치는 크게 런타임 환경에 의존하지 수있는 반면. 비용 견적은, 정확히 추정입니다 반면에, 명심하십시오.

    부하없이 전용 데이터베이스에서 실행될 때 가장 좋은 쿼리 실행 시간을 얻을 수있다 (예를 들어, 개발 PC에 pgAdminIII와 함께 연주.) 쿼리 시간은 실제 기계로드 / 데이터 액세스 확산에 따라 생산 달라집니다. 하나 개의 쿼리가 빠르게 약간 다른 것보다 (<20 %)을 표시하지만 훨씬 높은 비용이 때, 일반적으로 더 높은 실행 시간이지만 낮은 비용으로 하나를 선택 현명 할 것이다.

    당신은 쿼리가 실행되는 시간에 생산 기계의 메모리에 대한 경쟁이있을 것으로 예상하는 경우 (예 : RDBMS 캐시와 파일 시스템 캐시 동시 쿼리 및 / 또는 파일 시스템 활동에 의해 생겼을되지 않습니다) 다음 쿼리 시간은 당신이 얻을 수 독립형 (예 pgAdminIII 개발 PC에) 모드에서 대표 될 것입니다. 생산 시스템의 경합이있는 경우 비용이 낮은 쿼리가 더 높은 비용으로 쿼리가 반복해서 같은 데이터를 다시 방문하는 반면 캐시에 많이 의존하지 않는, 질의 시간 (트리거, 예상 비용 비율에 비례하여 저하됩니다 추가 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 |

    한 번에 필요한 인덱스를 생성 한 후 목숨을 분석 실행하는 것을 잊지 마십시오.

    검색어 # 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년 1월 29일 갱신

    마지막으로, 버전 8.4로, 포스트 그레스는 간단하고 효율적으로 뭔가를 쓸 수있는 의미 창 기능을 지원합니다 :

    검색어 # 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
 );
  2. ==============================

    2.나는 DISTINCT ON (문서 참조)에 따라 클린 버전을 제안 할 것입니다 :

    나는 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;
  3. ==============================

    3.여기에 어떤 상관 관계 서브 쿼리 또는 GROUP BY를 사용하여 발생하는 또 다른 방법입니다. 난 당신이 모두이 당신을 위해 잘 작동하는보고 다른 사람에 의해 주어진 해결 방법을 시도해보십시오 제안, 그래서 나는, PostgreSQL의 성능 튜닝 전문가가 아니에요.

    여기에 어떤 상관 관계 서브 쿼리 또는 GROUP BY를 사용하여 발생하는 또 다른 방법입니다. 난 당신이 모두이 당신을 위해 잘 작동하는보고 다른 사람에 의해 주어진 해결 방법을 시도해보십시오 제안, 그래서 나는, PostgreSQL의 성능 튜닝 전문가가 아니에요.

    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의 주어진 값보다 적어도 고유한지 가정입니다.

  4. ==============================

    4.나는 당신이 언급 한 다른 페이지에있는 마이크 우드 하우스의 대답의 스타일을 좋아한다. 그것은 어떤 부질 그냥 다른 컬럼에 의해 MAX (some_col) 및 그룹을 사용할 수있는 경우에,하지만 경우에 당신은이 부분의 양을 극대화 할 수 있고, 단 하나의 열이 특히 간결 것은 이상 최대화 될 때, 당신 여전히 ORDER BY 플러스 대신 LIMIT 1 (Quassnoi에 의해 수행으로)를 사용하여 수행 할 수 있습니다 :

    나는 당신이 언급 한 다른 페이지에있는 마이크 우드 하우스의 대답의 스타일을 좋아한다. 그것은 어떤 부질 그냥 다른 컬럼에 의해 MAX (some_col) 및 그룹을 사용할 수있는 경우에,하지만 경우에 당신은이 부분의 양을 극대화 할 수 있고, 단 하나의 열이 특히 간결 것은 이상 최대화 될 때, 당신 여전히 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
)

    나는 (A, B, C) IN (하위 쿼리) 좋은이 필요 말씨의 양이 줄어 듭니다 때문에 행 생성자 구문을 사용하여 찾을 수 있습니다.

  5. ==============================

    5.사실이 문제에 대한 해키 솔루션이있다. 하자 당신이 지역의 각 숲의 큰 나무를 선택하고 싶은 말은.

    사실이 문제에 대한 해키 솔루션이있다. 하자 당신이 지역의 각 숲의 큰 나무를 선택하고 싶은 말은.

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

    숲으로 그룹화 나무는 나무의 정렬되지 않은리스트가있을 것입니다 그리고 당신은 큰 일을 찾아야합니다. 당신이해야 할 첫 번째 일은 자신의 크기에 의해 행을 정렬하고 목록의 첫 번째를 선택하는 것입니다. 수도는 비효율적 인 것 같습니다하지만 당신은 수백만 개의 행이있는 경우는의와 WHERE 조건을 가입 포함하는 솔루션에 비해 상당히 빨라집니다.

    BTW, 메모 array_agg에 대한 ORDER_BY는 PostgreSQL을 9.0에 도입된다

  6. ==============================

    6.DISTINCT ON라는 PostgreSQL을 9.5에서 새로운 옵션이 있습니다

    DISTINCT ON라는 PostgreSQL을 9.5에서 새로운 옵션이 있습니다

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

    그것은 BY 절 내 순서를 정의 중복 행 만 잎 첫 번째 행을 제거합니다.

    공식 문서를 참조하십시오

  7. ==============================

    7.

    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의 일종이 있어야합니다.

  8. ==============================

    8.난 당신이 여기에서 큰 문제를 가지고 생각 : 주어진 행이 다른 것보다 시간 이후에 일어난 보장을 "카운터"를 증가에는 단조가 없습니다. 이 예제를 보자

    난 당신이 여기에서 큰 문제를 가지고 생각 : 주어진 행이 다른 것보다 시간 이후에 일어난 보장을 "카운터"를 증가에는 단조가 없습니다. 이 예제를 보자

    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 6 trans_id 1보다 최신 여부 (동일한 타임 스탬프) 수단을 통해 롤은 당신이 말할 수있는 trans_id을 가졌어요.

  9. ==============================

    9.또 다른 해결책은 당신이 유용 할 수 있습니다.

    또 다른 해결책은 당신이 유용 할 수 있습니다.

    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

    10. from https://stackoverflow.com/questions/586781/postgresql-fetch-the-row-which-has-the-max-value-for-a-column by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 데이터베이스 구조 변경에 대한 버전 관리 시스템이 있습니까?  (0) 2020.05.18
[SQL] 어떻게 컨트롤러 CakePHP의 SQL 덤프를 볼 수 있습니까?  (0) 2020.05.18
[SQL] 포함하지 "사이의"SQL  (0) 2020.05.18
[SQL] 나는 SQL Server 2005의 일부 레코드를 업데이트 CASE 문을 사용하려면  (0) 2020.05.18
[SQL] 64 기수 문자열로 저장 UUID  (0) 2020.05.18
,

티스토리툴바