[解決済み] PostgreSQL - あるカラムの最大値を持つ行を取得する

質問

Postgresのテーブル（"lives"）に、time_stamp、usr_id、transaction_id、lives_remainingの列を持つレコードを扱っています。私は、各usr_idの最新のlives_remainingの合計を与えるクエリが必要です。

1. 複数のユーザー（usr_idが異なる）が存在します。
   
   
2. time_stamp は一意な識別子ではありません: 同じ time_stamp でユーザー イベント (テーブルの行ごと) が発生することがあります。
   
   
3. trans_id は、非常に小さな時間範囲でのみ一意です: 時間の経過とともに繰り返されます。
   
   
4. (特定のユーザーに対する) remaining_lives は、時間の経過とともに増加または減少する可能性があります。

の例です。

タイムスタンプ|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ごとに最新のデータを持つ行の他の列にアクセスする必要があるので、私はこのような結果を与えるクエリが必要です。

タイムスタンプ|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（1番目）とtrans_id（2番目）の両方を使用する必要があります。また、その情報をサブクエリからメインクエリに渡して、適切な行の他のカラムのデータを提供する必要があります。これは、私が動作するようになったハックアップクエリです。

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を持つことを発見した行を取得することによって、よりシンプルにできるように思えます。テーブルquot;lives"には解析するために数千万行があるので、このクエリをできるだけ高速かつ効率的にしたいのです。私はRDBMとPostgresの初心者なので、適切なインデックスを効果的に使用する必要があることは分かっています。最適化する方法については少し迷っています。

似たような議論を見つけました。 はこちら . Oracleの分析関数に相当する何らかのPostgresを実行することは可能ですか？

集約関数（MAXなど）で使用される関連列情報へのアクセス、インデックスの作成、より良いクエリの作成などに関するアドバイスがあれば、ぜひともお願いします。

P.S. 私の例のケースを作成するために以下を使用することができます。

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

どのように解決するのですか？

158k行の擬似ランダム行を持つテーブルで（usr_idは0から10kの間で一様に分布。 trans_id は 0 から 30 の間に一様に分布しています)。

以下のクエリコストとは、Postgresのコストベースのオプティマイザのコスト推定値です（Postgresのデフォルトの xxx_cost PgAdminIIIを起動し、quot;Query/Explain options"をquot;Analyze"に設定して問い合わせを実行すると、必要なI/OとCPUリソースの計量関数であるこの推定値を得ることができます。

• Quassnoy のクエリの推定コストは 745k (!) で、1.3 秒で完了します (複合インデックスを指定した場合 ( usr_id , trans_id , time_stamp ))
• Billのクエリの推定コストは93kで、2.9秒で完了します（複合インデックスを持つ( 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行の完全な結果セットの取得を含んでいます。

目標は最小限のコスト見積もり と 最小のクエリ実行時間であり、推定コストに重点を置いています。 クエリの実行は実行時の条件（例えば、関連する行がすでに完全にメモリにキャッシュされているかどうか）に大きく依存することがありますが、コストの見積もりはそうではありません。 一方、コストの見積もりはまさに見積もりであることに留意してください。

最良の問い合わせ実行時間は、負荷のない専用のデータベースで実行したときに得られます（例えば、開発用PCでpgAdminIIIを使用して遊んでいるときなど）。 一方のクエリが他方よりわずかに(<20%)速く見えるが、他方のクエリに はるかに がある場合、一般に、実行時間は長いがコストが低い方を選択する方が賢明でしょう。

クエリ実行時に本番マシンのメモリに競合がない（例えば、RDBMSキャッシュとファイルシステムキャッシュが同時クエリやファイルシステムの活動によって破壊されない）と予想される場合、スタンドアロン（例えば、開発PC上のpgAdminIII）モードで得られたクエリ時間は代表的なものになるでしょう。 実運用環境において競合が発生した場合、問い合わせ時間は推定コスト比に比例して減少します。 一方 一方、より高いコストのクエリは、同じデータを何度も再訪することになります (安定したキャッシュがない場合、追加の 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は ウィンドウ機能 のようなシンプルで効率的なものを書くことができることを意味します。

クエリ#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
 );