☰

PostgreSQL - 各GROUP BYグループの列の最大値を持つ行を取得する 質問する

tag-icon • tag-icon sql postgresql query-optimization cbo cost-based-optimizer
PostgreSQL - 各GROUP BYグループの列の最大値を持つ行を取得する 質問する

私は、time_stamp、usr_id、transaction_id、lives_remaining の列を持つレコードを含む Postgres テーブル (「lives」という) を扱っています。各 usr_id の最新の lives_remaining 合計を返すクエリが必要です。

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

例：

タイムスタンプ|残り寿命|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 の最新データを含む行の他の列にアクセスする必要があるため、次のような結果を返すクエリが必要です。

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

同様の議論を見つけましたここOracle 分析関数と同等のタイプの Postgres を実行できますか?

集計関数 (MAX など) で使用される関連列情報へのアクセス、インデックスの作成、より優れたクエリの作成に関するアドバイスをいただければ幸いです。

PS 私のサンプルケースを作成するには、以下を使用できます。

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

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;

おすすめ記事