webdevqa.jp.net

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

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

具体的に言うと、purchasesテーブルがこのようになっているとします。

SELECT * FROM purchases;

私の出力:

 id |お客様total 
 --- + ---------- + ------ 
 1 |ジョー5 
 2 |サリー3 
 3 |ジョー2 
 4 |サリー1 

idごとに行われた最大の購入額(total)のcustomerを照会します。このようなもの:

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

予想される出力:

 FIRST(id)|お客様最初(合計)
 ---------- + ---------- + ------------- 
 1 |ジョー5 
 2 |サリー3 
1061
David Wolever

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
896
OMG Ponies

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拡張機能です(DISTINCTリスト全体でSELECTのみが定義されています)。

  • DISTINCT ON句に任意の数の式をリストします。結合された行の値は重複を定義します。 マニュアル:

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

    大胆な強調鉱山。

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

    "最高のidを共有する各グループから最小のtotalの行を選択します。"

    グループごとに最初を決定する並べ替え順序と一致しない方法で結果を並べ替えるには、別のORDER BYを使用して外側のクエリで上記のクエリをネストできます。のような:

  • totalがNULLになる可能性がある場合は、mostおそらくnull以外の値が最大の行が必要です。示されているようにNULLS LASTを追加します。詳細:

  • SELECT list は、DISTINCT ONまたはORDER BYの式による制約を受けません。 (上記の単純なケースでは必要ありません):

    • toDISTINCT ONまたはORDER BYの式を含める必要はありません。

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

  • Postgresバージョン8.3〜12でテストしましたが、この機能は少なくともバージョン7.1以降に存在しているため、基本的には常にです。

索引

上記のクエリのperfectインデックスは、 マルチカラムインデックス マッチングシーケンスとマッチングソート順で3つのカラムすべてにまたがります。

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

専門的すぎるかもしれません。ただし、特定のクエリの読み取りパフォーマンスが重要な場合に使用します。クエリにDESC NULLS LASTがある場合は、インデックスで同じものを使用して、ソート順が一致し、インデックスが適用されるようにします。

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

クエリごとにカスタマイズされたインデックスを作成する前に、コストとメリットを検討してください。上記のインデックスの可能性は、データ分布に大きく依存します。

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

基準

ここには、今では時代遅れの簡単なベンチマークがありました。 この個別の回答 の詳細なベンチマークに置き換えました。

1001

基準

Postgresを使用した最も興味深い候補のテスト9.4および9.5200k行の中間的な現実的なテーブルin purchasesおよび10k個別customer_idavg。顧客ごとに20行)。

Postgres 9.5の場合、事実上86446人の顧客を対象に2回目のテストを実行しました。下記を参照してください(avg。顧客あたり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;

私の2番目のテスト9.5では同じセットアップを使用しましたが、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サブクエリを使用したrCTE( こちらを参照

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. customerを含むLATERALテーブル( ここを参照

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. array_agg() with ORDER BY他の回答を参照

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(およびすべてのオプションoff)、ベストオブ5ランを使用した上記のクエリの実行時間。

Allクエリは、Index Only Scanon purchases2_3c_idx(他の手順の中で)を使用しました。インデックスのサイズを小さくするためのものもあれば、より効果的なものもあります。

A. 20万行でcustomer_idあたり20個のPostgres 9.4

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. Bと同じですが、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で3つのテストを実行しました9.165579行の実生活のテーブルと、関連する3つの列のそれぞれの単一列btreeインデックスで、最高の実行時間を取りました5回実行します。
比較 @ OMGPonies ' 最初のクエリ(A)と 上記DISTINCT ONソリューションB):

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

    A: 567.218 ms
    B: 386.673 ms
    
  2. 条件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
116

これは一般的な グループあたりの最大数 問題で、すでによくテストされており、非常に高い{ 最適化されたソリューション です。私は個人的には Bill Karwinによる左結合ソリューション他の多くの解決策を含むオリジナルの投稿 )を好みます。

この一般的な問題に対する解決策の束は、驚くべきことに、最も公式な情報源の1つ、 MySQLマニュアル !にあります。 一般的なクエリの例::特定の列のグループごとの最大値を保持する行 を参照してください。

43
TMS

Postgresでは、次のようにarray_aggを使うことができます。

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

これはあなたに各顧客の最大購入のidを与えるでしょう。

注意すべき点がいくつかあります。

  • array_aggは集約関数なので、GROUP BYと連携します。
  • array_aggを使用すると、それ自体を範囲とする順序付けを指定できます。したがって、クエリ全体の構造に制約はありません。デフォルトとは異なる処理を行う必要がある場合は、NULLのソート方法に関する構文もあります。
  • 配列を作成したら、最初の要素を取ります。 (Postgresの配列は1から始まり、0から始まりません)。
  • 3番目の出力列にも同様の方法でarray_aggを使用できますが、max(total)のほうが簡単です。
  • DISTINCT ONとは異なり、array_aggを使用すると、他の理由でGROUP BYを保持できます。
23

SubQが存在するため、Erwinが指摘したように、解はあまり効率的ではありません

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

私はこの方法を使います(postgresqlのみ): https://wiki.postgresql.org/wiki/First/last_%28aggregate%29

-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $1;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.first (
        sfunc    = public.first_agg,
        basetype = anyelement,
        stype    = anyelement
);

-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $2;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.last (
        sfunc    = public.last_agg,
        basetype = anyelement,
        stype    = anyelement
);

それならあなたの例は ほぼ のように動作するはずです:

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

警告:NULL行を無視します


編集1 - 代わりにpostgres拡張子を使う

今私はこのように使用します: http://pgxn.org/dist/first_last_agg/

Ubuntu 14.04にインストールするには:

apt-get install postgresql-server-dev-9.3 git build-essential -y
git clone git://github.com/wulczer/first_last_agg.git
cd first_last_app
make && Sudo make install
psql -c 'create extension first_last_agg'

それはあなたに最初と最後の機能を与えるpostgresの拡張です。上記の方法よりも明らかに速いです。


編集2 - 注文とフィルタリング

(これらのように)集約関数を使用する場合は、データをすでに順序付けしておく必要なく、結果を順序付けることができます。

http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES

そのため、順序付きの同等の例は次のようになります。

SELECT first(id order by id), customer, first(total order by id)
  FROM purchases
 GROUP BY customer
 ORDER BY first(total);

もちろん、あなたが集合体に収まると思うように、あなたは順序付けしてフィルターをかけることができます。とても強力な構文です。

7
matiu

非常に速い解決策

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

クエリ:

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

どのように動作しますか。 (私はそこに行ったことがある)

私達は私達が私達が各購入のための最も高い合計だけがあることを確かめたいと思います。


理論上のもの (クエリを理解したいだけの場合はこの部分を飛ばしてください)

Totalを関数T(customer、id)とします。ここで、与えられた合計(T(customer、id))が最高であることを証明するには、どちらかを証明したい 

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

OR

  • ≦T(customer、id)<T(customer、x)(その顧客にはこれ以上の合計はありません)

最初のアプローチでは、その名前のレコードをすべて入手する必要がありますが、これはあまり好きではありません。 

2番目のものはこれより高い記録がないことを言うために賢い方法を必要とするでしょう。


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

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

5
khaled_gomaa

PostgreSQLU-SQLIBM DB2 、および Google BigQuery SQL :にARRAY_AGG関数を使用します。

SELECT customer, (ARRAY_AGG(id ORDER BY total DESC))[1], MAX(total)
FROM purchases
GROUP BY customer
3
Valentin

承認されたOMG Poniesの "Supported by any database"ソリューションは私のテストではスピードが速いです。

ここで私は同じアプローチを提供しますが、より完全でクリーンなany-databaseソリューションを提供します。同順位が考慮され(各顧客に対して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

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

リマーク:

  1. t1、t2はデータベースによっては削除できる副照会の別名です。

  2. 警告 :2017年1月のこの編集時点では、using (...)句はMS-SQLとOracle dbでは現在サポートされていません。 on t2.id = purchase.idなどUSING構文はSQLite、MySQLそしてPostgreSQLで動作します。

2
Johnny Wong

SQL Serverでは、これを実行できます。

SELECT *
FROM (
SELECT ROW_NUMBER()
OVER(PARTITION BY customer
ORDER BY total DESC) AS StRank, *
FROM Purchases) n
WHERE StRank = 1

説明:ここで グループ化 は顧客に基づいて行われ、それから合計でそれを注文します、そしてそのような各グループはStRankとして通し番号を与えられて、StRankが1である最初の1人の顧客を取り出します。

1
Diwas Poudel

SQl Serverの最も効率的な方法は次のとおりです。 

with
ids as ( --condition for split table into groups
    select i from (values (9),(12),(17),(18),(19),(20),(22),(21),(23),(10)) as v(i) 
) 
,src as ( 
    select * from yourTable where  <condition> --use this as filter for other conditions
)
,joined as (
    select tops.* from ids 
    cross apply --it`s like for each rows
    (
        select top(1) * 
        from src
        where CommodityId = ids.i 
    ) as tops
)
select * from joined

そして使用された列のためのクラスタ化インデックスを作成することを忘れないで

0
BazSTR
  • 集約行のセットから(特定の条件によって)任意の行を選択したい場合。 

  • sum/avgに加えて別の(max/min)集計関数を使いたい場合そのため、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つの制限付きで任意の条件に置き換えることができます。この副問合せは、複数の行を返さないでください。

しかし、そのようなことをしたいのなら、おそらく ウィンドウ関数を探しています

0
Eugen Konkov