mysql - 複数カラム - sqlserver max




SQLは、列の最大値を持つ行のみを選択します。 (20)

私はこのテーブルをドキュメントに用意しています(ここでは簡略化されたバージョンです):

+------+-------+--------------------------------------+
| id   | rev   | content                              |
+------+-------+--------------------------------------+
| 1    | 1     | ...                                  |
| 2    | 1     | ...                                  |
| 1    | 2     | ...                                  |
| 1    | 3     | ...                                  |
+------+-------+--------------------------------------+

IDごとに1行を選択して最大のリバースのみを選択するにはどうすればよいですか?
上記のデータでは、結果に[1, 3, ...][2, 1, ..] 2つの行が含まれている必要が[2, 1, ..] 。 私はMySQLを使用しています

現在、私はwhileループのチェックを使用して、結果セットから古いrevを検出して上書きします。 しかし、これは結果を達成する唯一の方法ですか? SQLソリューションはありませんか?

更新
答えが示唆するように、SQLソリューションとここではsqlfiddleデモがあります。

アップデート2
私は上記のsqlfiddleを追加した後、質問がupvotedされた割合が回答のupvote率を上回っていることに気付きました。 それは意図ではありません! フィドルは、答え、特に受け入れられた答えに基づいています。


一目見ただけで...

必要なのは、 MAX集計関数を持つGROUP BY句だけです。

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

それは決して簡単なことではありませんか?

私はcontent列が必要であることに気付きました。

これはSQLでよくある質問です。いくつかのグループ識別子ごとに列に最大値を持つ行のデータ全体を探します。 私は私のキャリアの間にそれをたくさん聞いた。 実際には、私の現在の仕事の技術面接で私が答えた質問の一つでした。

実際、コミュニティでは、 greatest-n-per-group質問に対処するために単一のタグが作成されています。

基本的には、この問題を解決するには2つの方法があります。

単純なgroup-identifier, max-value-in-groupサブクエリで結合する

このアプローチでは、最初にgroup-identifier, max-value-in-group (上記ですでに解決済み)であるgroup-identifier, max-value-in-group見つけます。 次に、 group-identifiermax-value-in-group両方で等価性のあるサブクエリにテーブルを結合します。

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

左自己結合、調整条件とフィルタの調整

この方法では、テーブルを自分自身に参加させたままにしました。 もちろん、平等はgroup-identifierます。 次に、2つのスマートな動き:

  1. 2番目の結合条件は、左側の値が正しい値よりも小さい
  2. ステップ1を実行すると、実際に最大値を持つ行は右側にNULLを持つことになりNULLLEFT JOIN 、覚えていますか?)。 次に、結合された結果をフィルタリングし、右側がNULL行のみを表示しNULL

だからあなたは次のようになります:

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

結論

どちらのアプローチも全く同じ結果をもたらします。

group-identifiergroup-identifier max-value-in-groupを持つ2つの行がある場合、両方の方法で両方の行が結果になります。

どちらの方法もSQL ANSIと互換性があるため、その「風味」に関係なく、お気に入りのRDBMSで動作します。

どちらのアプローチもパフォーマンスに優しいですが、あなたの走行距離はさまざまです(RDBMS、DB構造、索引など)。 したがって、あるベンチマークよりも1つのアプローチを選択すると、 そして、あなたに最も合ったものを選んでください。


SELECT * FROM Employee where Employee.Salary(Employe_idによるEmployeeグループからの最大(給与)の選択)ORDER BY Employee.Salary


select文に多数のフィールドがあり、それらのフィールドのすべてに最適化されたコードを使用して最新の値を設定する場合は、次のようにします。

select * from
(select * from table_name
order by id,rev desc) temp
group by id 

revidMAX() maxRevId 1つの値に結合し、それを元の値に戻すときに結合なしで選択を行うことができます:

SELECT maxRevId & ((1 << 32) - 1) as id, maxRevId >> 32 AS rev
FROM (SELECT MAX(((rev << 32) | id)) AS maxRevId
      FROM YourTable
      GROUP BY id) x;

単一の表ではなく複雑な結合がある場合は、特に高速です。 従来のアプローチでは、複雑な結合が2回行われます。

上記の組み合わせは、 revidINT UNSIGNED (32ビット)であり、結合された値がBIGINT UNSIGNED (64ビット)に適合するとき、ビット関数で単純です。 idrevが32ビット値より大きいか、複数の列で構成されている場合は、値を例えばMAX()適切なパディング付きのバイナリ値に組み合わせる必要があります。


ここでそれを行う良い方法です

次のコードを使用します。

with temp as  ( 
select count(field1) as summ , field1
from table_name
group by field1 )
select * from temp where summ = (select max(summ) from temp)

このようなもの?

SELECT yourtable.id, rev, content
FROM yourtable
INNER JOIN (
    SELECT id, max(rev) as maxrev FROM yourtable
    WHERE yourtable
    GROUP BY id
) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)

このフィールドの最大値を持つフィールドだけでレコードを取得するもう1つの方法があります。 これは私が作業しているプラ​​ットフォームであるSQL400で動作します。 この例では、フィールドFIELD5の最大値を持つレコードは、次のSQL文によって取得されます。

SELECT A.KEYFIELD1, A.KEYFIELD2, A.FIELD3, A.FIELD4, A.FIELD5
  FROM MYFILE A
 WHERE RRN(A) IN
   (SELECT RRN(B) 
      FROM MYFILE B
     WHERE B.KEYFIELD1 = A.KEYFIELD1 AND B.KEYFIELD2 = A.KEYFIELD2
     ORDER BY B.FIELD5 DESC
     FETCH FIRST ROW ONLY)

これはsqlite3の私のために働く:

SELECT *, MAX(rev) FROM t1 GROUP BY id

*を使用すると、重複する列が表示されますが、それほど大きな問題はありません。


これはどう:

select all_fields.*  
from  (select id, MAX(rev) from yourtable group by id) as max_recs  
left outer join yourtable as all_fields  
on max_recs.id = all_fields.id

これらの答えのどれも私のために働いていません。

これは私のために働いたものです。

with score as (select max(score_up) from history)
select history.* from score, history where history.score_up = score.max

パフォーマンスを保証することはできませんが、ここではMicrosoft Excelの限界に触発されています。 それはいくつかの良い機能があります

良いもの

  • それは、たとえタイがある場合でも(時には有用な)ただ一つの "最大レコード"の復帰を強制すべきです。
  • それは結合を必要としません

アプローチ

ちょっと醜いので、 revカラムの有効な値の範囲について知っておく必要があります。 rev列が小数点を含めて0.00と999の間の数値であるが、小数点の右側には2桁しかないことがわかっていると仮定しよう(例えば、34.17が有効な値)。

物事の要点は、あなたが望むデータと一緒にプライマリ比較フィールドを連結/パッキングする文字列によって単一の合成カラムを作成することです。 このようにして、SQLのMAX()集計関数がすべてのデータを返すようにすることができます(これは、単一の列にパックされているためです)。 その後、データを展開する必要があります。

上記の例をSQLで書かれた方法

SELECT id, 
       CAST(SUBSTRING(max(packed_col) FROM 2 FOR 6) AS float) as max_rev,
       SUBSTRING(max(packed_col) FROM 11) AS content_for_max_rev 
FROM  (SELECT id, 
       CAST(1000 + rev + .001 as CHAR) || '---' || CAST(content AS char) AS packed_col
       FROM yourtable
      ) 
GROUP BY id

パッキングは、 revの値に関係なく、 revカラムを既知の文字長の数にすることによって開始されます。

  • 3.2は1003.201になる
  • 57は1057.001
  • 923.88は1923.881になる

2つの数値の文字列比較では、2つの数値の数値比較と同じ「最大」が得られ、部分文字列関数を使用して元の数値に簡単に戻すことができます(これは1つの形式でも、どこにでも)。


仕事をする別の方法は、OVER PARTITION句でMAX()解析関数を使用することです

SELECT t.*
  FROM
    (
    SELECT id
          ,rev
          ,contents
          ,MAX(rev) OVER (PARTITION BY id) as max_rev
      FROM YourTable
    ) t
  WHERE t.rev = t.max_rev 

この記事ですでに説明しているもう1つのOVER PARTITIONソリューションは、

SELECT t.*
  FROM
    (
    SELECT id
          ,rev
          ,contents
          ,ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
      FROM YourTable
    ) t
  WHERE t.rank = 1 

この2 SELECTは、Oracle 10gでもうまく機能します。


私がほとんど見たことのない第3の解決策は、MySQL固有のもので、次のようになります。

SELECT id, MAX(rev) AS rev
 , 0+SUBSTRING_INDEX(GROUP_CONCAT(numeric_content ORDER BY rev DESC), ',', 1) AS numeric_content
FROM t1
GROUP BY id

はい、それはひどい(文字列やバックに変換など)に見えますが、私の経験では、通常、他のソリューションよりも高速です。 たぶん私のユースケースのためだけかもしれませんが、私は数百万のレコードと多くのユニークなIDを持つテーブルでそれを使用しています。 たぶんMySQLは他のソリューションを最適化するのが難しいからです(少なくともこのソリューションを考え出すと、5.0日後)。

1つの重要なことは、GROUP_CONCATに構築できる文字列の最大長があることです。 group_concat_max_len変数を設定して、この制限を引き上げることをおgroup_concat_max_lenます。 また、行数が多い場合は、これがスケーリングの制限になることに注意してください。

とにかく、あなたのコンテンツフィールドがすでにテキストの場合、上記は直接動作しません。 その場合はおそらく\ 0のような別の区切り記号を使いたいと思うでしょう。 また、 group_concat_max_len制限をgroup_concat_max_len実行します。


私の好みは可能な限り小さなコードを使用することです...

あなたはINを使ってこれを試すことができます:

SELECT * 
FROM t1 WHERE (id,rev) IN 
( SELECT id, MAX(rev)
  FROM t1
  GROUP BY id
)

私の心にはそれほど複雑ではありません...読みやすく、維持しやすいです。


私はいくつかの列でレコードをランク​​付けしてこれを行うのが好きです。 この場合、 idグループ化した格付け値をランク付けします。 より高いrevを持つ人は、より低い順位を持つでしょう。 したがって、最高revは1のランキングになります。

select id, rev, content
from
 (select
    @rowNum := if(@prevValue = id, @rowNum+1, 1) as row_num,
    id, rev, content,
    @prevValue := id
  from
   (select id, rev, content from YOURTABLE order by id asc, rev desc) TEMP,
   (select @rowNum := 1 from DUAL) X,
   (select @prevValue := -1 from DUAL) Y) TEMP
where row_num = 1;

変数を導入すると全体が遅くなるかどうかはわかりません。 しかし、少なくとも私はYOURTABLE 2度YOURTABLEていません。


私はこれが最も簡単な解決策だと思う:

SELECT *
FROM
    (SELECT *
    FROM Employee
    ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;
  • SELECT *:すべてのフィールドを返します。
  • FROM Employee:テーブルが検索されました。
  • (SELECT * ...)サブクエリ:Salaryでソートされたすべての人を返します。
  • GROUP BY employeesub.Salary ::各従業員の一番上にソートされた給与行を強制的に返された結果にします。

1行だけが必要な場合は、さらに簡単です:

SELECT *
FROM Employee
ORDER BY Employee.Salary DESC
LIMIT 1

また、他の目的に分解し、理解し、変更するのが最も簡単だと思います。

  • ORDER BY Employee.Salary DESC:最高給与で給与を計算します。
  • LIMIT 1:1つの結果だけを返します。

このアプローチを理解し、これらの類似の問題を解決することは簡単です。給与が最低の従業員を取得し(DESCをASCに変更)、トップ10の従業員を取得し(LIMIT 1をLIMIT 10に変更)、別のフィールドを使用してソート(ORDER BY Employee.SalaryからORDER BY Employee.Commissionまで)など。


私は答えがないSQLウィンドウ関数のソリューションを提供して驚いています:

SELECT a.id, a.rev, a.contents
  FROM (SELECT id, rev, contents,
               ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
          FROM YourTable) a
 WHERE a.rank = 1 

SQL標準ANSI / ISO標準SQL:2003に追加され、その後、ANSI / ISO標準SQL:2008で拡張され、すべての主要ベンダーでウィンドウ(またはウィンドウ)機能が利用可能になりました。 ネクタイの問題を処理するために使用できるランク関数には、 RANK, DENSE_RANK, PERSENT_RANKような種類があります。


私は自分の問題を解決するために以下を使用しました。 私は最初にテンポラリテーブルを作成し、ユニークIDごとに最大回転数を挿入しました。

CREATE TABLE #temp1
(
    id varchar(20)
    , rev int
)
INSERT INTO #temp1
SELECT a.id, MAX(a.rev) as rev
FROM 
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as a 
GROUP BY a.id
ORDER BY a.id

次に、これらの最大値(#temp1)をすべての可能なid / contentの組み合わせに結合しました。 これを行うことで、私は自然に非最大のid / contentの組み合わせを除外し、それぞれの最大rev値だけ残しています。

SELECT a.id, a.rev, content
FROM #temp1 as a
LEFT JOIN
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as b on a.id = b.id and a.rev = b.rev
GROUP BY a.id, a.rev, b.content
ORDER BY a.id

SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;

select * from yourtable
group by id
having rev=max(rev);




greatest-n-per-group