質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.37%

SQLであるカラムの文字列でグループ分けして、最大行を取得したい。

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 841

score 8

発生している問題

SQLで以下のテーブルがあります。
KEY1の先頭2桁の文字でグループ分けして、
グループ分けした中からKEY1,KEY2,KEY3が最大の行を取得するSQLがわかりません。

KEY1,KEY2,KEY3
"A1191028000001" , "00002" , "0" ⇒取得したい行
"B1191028000001" , "00001" , "1"
"B1191028000002" , "00001" , "1"
"B1191028000003" , "00002" , "0"
"B1191028000003" , "00002" , "1" ⇒取得したい行

上記のテーブルを基にSQLを実行した場合、以下の結果が得られるSQLを教えてください。
KEY1,KEY2,KEY3
"A1191028000001" , "00002" , "0"
"B1191028000003" , "00002" , "1"

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • yambejp

    2019/10/28 13:09

    つまりKEY2やKEY3の値は比較の対象ではないのですね?

    キャンセル

  • g1081828

    2019/10/28 13:26

    質問が悪かったのでテーブル内用を更新しました。
    やりたいこととしてまず、KEY1で文字列を比較し、同じ値であればKEY2を文字列で比較。
    KEY1もKEY2も同じ値であれば、KEY3を文字列で比較したいです。

    キャンセル

  • Orlofsky

    2019/10/28 14:15

    テーブル設計の入門で習うことですが、ひとつのカラムにはひとつの意味しか持たせない、って掟があります。
    > KEY1の先頭2桁の文字でグループ分けして
    であれば、KEY1の先頭2桁と3桁目以降を別のカラムに分けます。

    KEY1にインデックスが設定してあっても、SUBSTR する時にインデックスが使われず、テーブル全行のKEY1をSUBSTRして、...
    データ量が増えるとどんどん遅くなります。

    キャンセル

回答 2

checkベストアンサー

0

Postgreの関数は詳しくないので適当に読み替えてみてください

create table tbl(KEY1 varchar(20),KEY2 varchar(10),KEY3 varchar(10));
insert into tbl values
('A1191028000001','00002','0'),
('B1191028000001','00001','1'),
('B1191028000002','00001','1'),
('B1191028000003','00002','1'),
('C1191028000001','00002','1'),
('C1191028000002','00002','1');


select * from tbl as t1
where not exists
(select 1 from tbl where
 substr(KEY1,1,2)=substr(t1.KEY1,1,2) and
 KEY1>t1.KEY1
);

調整

  • KEY1を比較
  • 同じならKEY2を比較
  • 同じならKEY3を比較
  • すべて同じなら1個だけ
create table tbl(KEY1 varchar(20),KEY2 varchar(10),KEY3 varchar(10));
insert into tbl values
('A1191028000001','00002','0'),
('B1191028000001','00001','1'),
('B1191028000002','00001','1'),
('B1191028000003','00002','0'),
('B1191028000003','00002','1'),
('C1191028000001','00002','1'),
('C1191028000002','00001','1'),
('C1191028000002','00002','0'),
('D1191028000001','00001','0'),
('D1191028000001','00001','0');


select distinct * from tbl as t1
where not exists
(select 1 from tbl where
  (substr(KEY1,1,2)=substr(t1.KEY1,1,2) and
    (KEY1>t1.KEY1 or
     KEY1=t1.KEY1 and KEY2>t1.KEY2 or
     KEY1=t1.KEY1 and KEY2=t1.KEY2 and KEY3>t1.KEY3 
    )
  )
);

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

Key1, Key2, Key3を配列にして比較すると簡潔な記述にはなります。

with tbl as (
  select * from (values
     ('A1191028000001' , '00002' , '0')
    ,('B1191028000001' , '00001' , '1')
    ,('B1191028000002' , '00001' , '1')
    ,('B1191028000003' , '00002' , '0')
    ,('B1191028000003' , '00002' , '1')
  ) as tbl(Key1, Key2, Key3)
)
select * from tbl t
where not exists(
        select 1 from tbl 
        where key1 like substr(t.key1, 1, 2) || '%'
          and array[key1, key2, key3]::text[] > array[t.key1, t.key2, t.key3]::text[]
      )


但し、(key1, key2, key)のGinインデックスを持たない場合は、性能が出せないかもしれません。
その場合は、yambejpさんの条件のようにandとorで繋げる必要があります

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.37%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る