最大値 - sqlserver グループ毎 top




各GROUP BYグループの最初の行を選択しますか? (8)

基準

最も興味深い候補をPostgres 9.49.5でテストし、半分で現実的な200k行のテーブルと10kの別個のcustomer_id顧客あたり20行 )をテストします

Postgres 9.5では、効果的に86446の別個の顧客で2番目のテストを実行しました。 下記( 平均2.3行/顧客 )を参照してください。

セットアップ

メインテーブル

CREATE TABLE purchases (
  id          serial
, customer_id int  -- REFERENCES customer
, total       int  -- could be amount of money in Cent
, some_column text -- to make the row bigger, more realistic
);

より一般的なセットアップであるので、私はserial (以下に追加されたPK制約)と整数customer_id使用します。 また、通常は複数の列を補うためにsome_columnを追加しました。

ダミーデータ、PK、インデックス - 典型的なテーブルにもいくつかの不完全なタプルがあります:

INSERT INTO purchases (customer_id, total, some_column)    -- insert 200k rows
SELECT (random() * 10000)::int             AS customer_id  -- 10k customers
     , (random() * random() * 100000)::int AS total     
     , 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM   generate_series(1,200000) g;

ALTER TABLE purchases ADD CONSTRAINT purchases_id_pkey PRIMARY KEY (id);

DELETE FROM purchases WHERE random() > 0.9; -- some dead rows

INSERT INTO purchases (customer_id, total, some_column)
SELECT (random() * 10000)::int             AS customer_id  -- 10k customers
     , (random() * random() * 100000)::int AS total     
     , 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM   generate_series(1,20000) g;  -- add 20k to make it ~ 200k

CREATE INDEX purchases_3c_idx ON purchases (customer_id, total DESC, id);

VACUUM ANALYZE purchases;

customerテーブル - 上位クエリ用

CREATE TABLE customer AS
SELECT customer_id, 'customer_' || customer_id AS customer
FROM   purchases
GROUP  BY 1
ORDER  BY 1;

ALTER TABLE customer ADD CONSTRAINT customer_customer_id_pkey PRIMARY KEY (customer_id);

VACUUM ANALYZE customer;

9.5の2番目のテストでは、同じ設定を使用しましたが、 customer_idを生成するrandom() * 100000を使用してcustomer_idごとにわずかな行を取得しました。

テーブルpurchasesオブジェクトサイズ

このクエリで生成されます

               what                | bytes/ct | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
 core_relation_size                | 20496384 | 20 MB        |           102
 visibility_map                    |        0 | 0 bytes      |             0
 free_space_map                    |    24576 | 24 kB        |             0
 table_size_incl_toast             | 20529152 | 20 MB        |           102
 indexes_size                      | 10977280 | 10 MB        |            54
 total_size_incl_toast_and_indexes | 31506432 | 30 MB        |           157
 live_rows_in_text_representation  | 13729802 | 13 MB        |            68
 ------------------------------    |          |              |
 row_count                         |   200045 |              |
 live_tuples                       |   200045 |              |
 dead_tuples                       |    19955 |              |

クエリ

1. CTEのrow_number() ( 他の回答を参照 )

WITH cte AS (
   SELECT id, customer_id, total
        , row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
   FROM   purchases
   )
SELECT id, customer_id, total
FROM   cte
WHERE  rn = 1;

2.副問合せのrow_number() (私の最適化)

SELECT id, customer_id, total
FROM   (
   SELECT id, customer_id, total
        , row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
   FROM   purchases
   ) sub
WHERE  rn = 1;

3. DISTINCT ON ( 他の回答を参照 )

SELECT DISTINCT ON (customer_id)
       id, customer_id, total
FROM   purchases
ORDER  BY customer_id, total DESC, id;

4. LATERALサブクエリを持つLATERAL ( ここを参照 )

WITH RECURSIVE cte AS (
   (  -- parentheses required
   SELECT id, customer_id, total
   FROM   purchases
   ORDER  BY customer_id, total DESC
   LIMIT  1
   )
   UNION ALL
   SELECT u.*
   FROM   cte c
   ,      LATERAL (
      SELECT id, customer_id, total
      FROM   purchases
      WHERE  customer_id > c.customer_id  -- lateral reference
      ORDER  BY customer_id, total DESC
      LIMIT  1
      ) u
   )
SELECT id, customer_id, total
FROM   cte
ORDER  BY customer_id;

5. LATERAL customerテーブル( こちらを参照 )

SELECT l.*
FROM   customer c
,      LATERAL (
   SELECT id, customer_id, total
   FROM   purchases
   WHERE  customer_id = c.customer_id  -- lateral reference
   ORDER  BY total DESC
   LIMIT  1
   ) l;

6. ORDER BY array_agg() ( 他の回答を参照 )

SELECT (array_agg(id ORDER BY total DESC))[1] AS id
     , customer_id
     , max(total) AS total
FROM   purchases
GROUP  BY customer_id;

結果

EXPLAIN ANALYZE (およびすべてのオプションをオフ )で実行した上記のクエリの実行時間は5回です。

すべてのクエリでは、 purchases2_3c_idx インデックスのみのスキャンを使用しpurchases2_3c_idx (他の手順の中でも)。 それらのうちのいくつかは、インデックスのサイズが小さく、より効果的なものがあります。

A. Postgres 9.4、200k行、〜20個/ customer_id

1. 273.274 ms  
2. 194.572 ms  
3. 111.067 ms  
4.  92.922 ms  
5.  37.679 ms  -- winner
6. 189.495 ms

B. Postgres 9.5と同じです

1. 288.006 ms
2. 223.032 ms  
3. 107.074 ms  
4.  78.032 ms  
5.  33.944 ms  -- winner
6. 211.540 ms  

C.と同じですが、 customer_idあたり〜2.3行

1. 381.573 ms
2. 311.976 ms
3. 124.074 ms  -- winner
4. 710.631 ms
5. 311.976 ms
6. 421.679 ms

2011年の元(古い)ベンチマーク

私は、PostgreSQL 9.1で3つのテストを65579行の実際のテーブルと1列のbtreeインデックスで実行し 、3つのカラムそれぞれに5回の実行時間を要しました。
@OMGPoniesの最初のクエリ( A )と上記のDISTINCT ONソリューション ( B )の比較:

  1. この場合、テーブル全体を選択すると5958行になります。

    A: 567.218 ms
    B: 386.673 ms
    
  2. 条件WHERE customer BETWEEN x AND y使用してWHERE customer BETWEEN x AND yに1000行の結果が得られます。

    A: 249.136 ms
    B:  55.111 ms
    
  3. WHERE customer = x持つ単一の顧客を選択します。

    A:   0.143 ms
    B:   0.072 ms
    

他の回答に記載されている指標を用いて同じ試験を繰り返した

CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);

1A: 277.953 ms  
1B: 193.547 ms

2A: 249.796 ms -- special index not used  
2B:  28.679 ms

3A:   0.120 ms  
3B:   0.048 ms

タイトルが示唆するように、私はGROUP BYグループ化された各行セットの最初の行を選択したいと思います。

具体的には、次のようなpurchasesテーブルがあるとpurchasesます。

SELECT * FROM purchases;

私の出力:

id | customer | total
---+----------+------
 1 | Joe      | 5
 2 | Sally    | 3
 3 | Joe      | 2
 4 | Sally    | 1

私は、各customer行った最大の購入totaltotal )のidを照会したいと思いcustomer 。 このようなもの:

SELECT FIRST(id), customer, FIRST(total)
FROM  purchases
GROUP BY customer
ORDER BY total DESC;

期待される成果:

FIRST(id) | customer | FIRST(total)
----------+----------+-------------
        1 | Joe      | 5
        2 | Sally    | 3

Oracle 9.2以降(本来は8i +ではない)、SQL Server 2005+、PostgreSQL 8.4+、DB2、Firebird 3.0+、Teradata、Sybase、Vertica:

WITH summary AS (
    SELECT p.id, 
           p.customer, 
           p.total, 
           ROW_NUMBER() OVER(PARTITION BY p.customer 
                                 ORDER BY p.total DESC) AS rk
      FROM PURCHASES p)
SELECT s.*
  FROM summary s
 WHERE s.rk = 1

どのデータベースでもサポート:

しかし、あなたは関係を壊すために論理を追加する必要があります:

  SELECT MIN(x.id),  -- change to MAX if you want the highest
         x.customer, 
         x.total
    FROM PURCHASES x
    JOIN (SELECT p.customer,
                 MAX(total) AS max_total
            FROM PURCHASES p
        GROUP BY p.customer) y ON y.customer = x.customer
                              AND y.max_total = x.total
GROUP BY x.customer, x.total

PostgreSQLでは、これは通常、 より簡単で高速です (以下のパフォーマンスの最適化)。

SELECT DISTINCT ON (customer)
       id, customer, total
FROM   purchases
ORDER  BY customer, total DESC, id;

または、出力列の順序番号でより短い(クリアではない場合)

SELECT DISTINCT ON (2)
       id, customer, total
FROM   purchases
ORDER  BY 2, 3 DESC, 1;

totalにNULLを指定できる場合(いずれにしても傷つくことはありませんが、既存のインデックスと一致させたい場合):

...
ORDER  BY customer, total DESC NULLS LAST, id;

主なポイント

  • DISTINCT ONは、標準のPostgreSQL拡張です( SELECTリスト全体でDISTINCTのみが定義されています)。

  • DISTINCT ON句に任意の数の式をリストすると、結合された行の値によって重複が定義されます。 マニュアル:

    明らかに、少なくとも1つの列の値が異なる場合、2つの行は異なるとみなされます。 この比較では、NULL値は等しいとみなされます。

    大胆な強調。

  • DISTINCT ONORDER BYと組み合わせることができます。 先頭の式は、同じ順序で先頭のDISTINCT ON式に一致する必要がありDISTINCT ONORDER BYに式を追加して、各ピアのグループから特定の行を選択することができます。 私は最後の項目としてidを追加して、ネクタイを壊しました:

    "最高のtotal共有する各グループから最小のidを持つ行を選んでください。"

    totalがNULLの場合、 ほとんどの場合、NULL以外の値が最大の行が必要です。 示されたようにNULLS LAST追加します。 詳細:

  • SELECTリストは、 DISTINCT ONまたはORDER BY式によって制約されません。 (上記の単純なケースでは不要です):

    • DISTINCT ONまたはORDER BY含める必要はありません

    • SELECTリストに他の式を含めることができます。 これは、はるかに複雑なクエリをサブクエリや集計/ウィンドウ関数に置き換えるための手段です。

  • 私はPostgresのバージョン8.3-10でテストしました。しかし、バージョン7.1以来、この機能は少なくとも基本的に常に存在しています。

インデックス

上記のクエリの完全なインデックスは、一致する順序と一致する並べ替え順序で3つの列すべてにまたがる複数列インデックスです。

CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);

現実世界のアプリケーションにはあまりにも専門的かもしれません。 しかし、読み込みパフォーマンスが重要な場合に使用してください。 クエリにDESC NULLS LASTがある場合、Postgresはソート順が一致するようにインデックス内で同じものを使用します。

効果/パフォーマンスの最適化

あなたは、すべてのクエリに合わせて調整されたインデックスを作成する前に、コストと利益を判断する必要があります。 上記の指標の可能性は、 データの分布に大きく依存します

インデックスは、事前ソートされたデータを提供するために使用されます。また、Postgres 9.2以降では、インデックスが基になるテーブルよりも小さい場合、インデックスのみのスキャンの恩恵を受けることもできます。 ただし、インデックス全体をスキャンする必要があります。

基準

ここでは古くなっているシンプルなベンチマークがありました。 私はこの別の答えの詳細なベンチマークに置き換えました。


このソリューションは、Erwinが指摘するように効率的ではありません.SubQが存在するためです

select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;

クエリ:

SELECT purchases.*
FROM purchases
LEFT JOIN purchases as p 
ON 
  p.customer = purchases.customer 
  AND 
  purchases.total < p.total
WHERE p.total IS NULL

それはどうやって! (私はそこに行ったことがある)

私たちは、購入ごとに合計が最大になるようにしたいと考えています。

いくつかのTheoretical Stuff (クエリを理解したい場合はこの部分をスキップしてください)

T(customer、id)が証明できる最大値であることを証明するには、次のいずれかを証明する必要があります。

  • ∀xT(顧客、id)> T(顧客、x)(この合計はその顧客の他のすべての合計よりも高い)

または

  • ∃xT(顧客、id)<T(顧客、x)(その顧客にはそれ以上の合計は存在しない)

最初のアプローチでは、私が本当に好きではないその名前のすべてのレコードを取得する必要があります。

もう1つは、これ以上の記録がないと言うためにはスマートな方法が必要です。

SQLに戻る

私たちが名前のテーブルを結合したままにして、合計が結合されたテーブルより小さい場合:

      LEFT JOIN purchases as p 
      ON 
      p.customer = purchases.customer 
      AND 
      purchases.total < p.total

同じユーザーの合計が高い別のレコードを持つすべてのレコードが結合されるようにします。

purchases.id, purchases.customer, purchases.total, p.id, p.customer, p.total
1           , Tom           , 200             , 2   , Tom   , 300
2           , Tom           , 300
3           , Bob           , 400             , 4   , Bob   , 500
4           , Bob           , 500
5           , Alice         , 600             , 6   , Alice   , 700
6           , Alice         , 700

これは、必要なグループ化がなくても、購入ごとに最大の合計をフィルタリングするのに役立ちます。

WHERE p.total IS NULL

purchases.id, purchases.name, purchases.total, p.id, p.name, p.total
2           , Tom           , 300
4           , Bob           , 500
6           , Alice         , 700

それが私たちが必要とする答えです。


受け入れられたOMG Poniesの「どのデータベースでもサポートされています」ソリューションは、私のテストから優れたスピードを持っています。

ここでも私は同じアプローチを提供していますが、より完全なクリーンなデータベースソリューションです。 タイは考慮されます(顧客ごとに1つの行だけを取得することを望み、顧客あたりの最大合計の複数のレコードさえも仮定します)。また、購入テーブルの実際の一致する行に対して他の購入フィールド(purchase_payment_idなど)が選択されます。

どのデータベースでもサポート:

select * from purchase
join (
    select min(id) as id from purchase
    join (
        select customer, max(total) as total from purchase
        group by customer
    ) t1 using (customer, total)
    group by customer
) t2 using (id)
order by customer

このクエリは、特に、購入テーブルに(顧客、合計)のような複合インデックスがある場合はかなり高速です。

リマーク:

  1. t1、t2はサブクエリエイリアスであり、データベースに応じて削除できます。

  2. 警告using (...)節は現在、2017年1月のこの編集時点のMS-SQLとOracleデータベースではサポートされていません。たとえばon t2.id = purchase.idなどで自分自身を展開する必要がありますon t2.id = purchase.id構文SQLite、MySQL、PostgreSQLで動作します。


非常に高速なソリューション

SELECT a.* 
FROM
    purchases a 
    JOIN ( 
        SELECT customer, min( id ) as id 
        FROM purchases 
        GROUP BY customer 
    ) b USING ( id );

テーブルがidによってインデックス付けされている場合は本当に非常に高速です:

create index purchases_id on purchases (id);

  • 集計された行のセットから任意の条件で行を選択したい場合。

  • max/minに加えて別の( sum/avg )集計関数を使用する場合。 したがってDISTINCT ONヒントを使用することはできません

次のサブクエリを使用することができます:

SELECT  
    (  
       SELECT **id** FROM t2   
       WHERE id = ANY ( ARRAY_AGG( tf.id ) ) AND amount = MAX( tf.amount )   
    ) id,  
    name,   
    MAX(amount) ma,  
    SUM( ratio )  
FROM t2  tf  
GROUP BY name

amount = MAX( tf.amount )を、1つの制限で任意の条件に置き換えることができます。このサブクエリは複数の行を返さない

しかし、もしあなたがそうしたことをしたいのであれば、あなたはおそらくウィンドウ関数を探しています





greatest-n-per-group