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

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

ただいまの
回答率

89.98%

SQL 特定条件によるデータ抽出および集計

解決済

回答 4

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 2,729

monagano

score 244

前提・実現したいこと

使用DB PostgreSQL9.1.23

以下のテーブルについてSQLによるデータ抽出を行おうとしています。

サンプルテーブル(sample_tbl)

フィールド名 gid group_name user_name ctime data1
データ型 bigint text text text smallint
UNIQUE制約 × × × ×
インデックス - btree btree btree btree

■条件

  • テーブルの設計は変更できません
  • ctimeはtext型ですが、日時データが格納されています フォーマット:yyyy-mm-dd hh24:mi:ss.MS
  • group_nameとuser_nameの組み合わせごとにctimeが最新のレコードを抽出します
  • ctimeが最新のレコードが複数ある場合、gidが大きいレコードを対象とします
  • 抽出したレコードをgroup_nameごとに集計し、各group_nameのdata1の合計値を結果として求めます

■データサンプル

gid group_name user_name ctime data1
1 aaa xxx 2017-06-24 12:00:00.001 3
2 aaa xxx 2017-06-24 12:00:00.002 4
3 aaa xxx 2017-06-24 12:00:00.002 1
4 aaa yyy 2017-06-24 12:00:00.002 3
5 bbb xxx 2017-06-24 12:00:00.001 3
6 xxx 2017-06-24 12:00:00.001 3

実装したSQL

SELECT
      group_name
    , sum(data1) 
  FROM
    sample_tbl 
    INNER JOIN ( 
      SELECT
            max(sample_tbl.gid) AS gid 
        FROM
          sample_tbl 
          INNER JOIN ( 
            SELECT
                  group_name
                , user_name
                , max(to_timestamp(ctime, 'yyyy-mm-dd hh24:mi:ss.MS')) AS ctime 
              FROM
                sample_tbl 
              GROUP BY
                group_name
                , user_name
          ) AS latest_id_tbl 
            ON to_timestamp(sample_tbl.ctime, 'yyyy-mm-dd hh24:mi:ss.MS') = latest_id_tbl.ctime 
            AND sample_tbl.group_name = latest_id_tbl.group_name 
            AND sample_tbl.user_name = latest_id_tbl.user_name 
        GROUP BY
          sample_tbl.group_name
          , sample_tbl.user_name
    ) AS gid_tbl 
      ON sample_tbl.gid = gid_tbl.gid 
  GROUP BY
    sample_tbl.group_name

アドバイスいただきたいこと

SQL初学者であるため、正しく抽出できているのか、組立てに問題がないか自信がありません。
サブクエリがネストしており、パフォーマンスに不安があります。
修正すべき箇所、より優れた書き方等ありましたら、ご指摘をお願いいたします。

追記1

アドバイスいただき、window関数のrankを使用して、以下の通り書き換えてみました。
誤りや改善の余地がありましたら、ご指摘いただけると幸いです。

SELECT
      group_name
    , sum(data1)
  FROM
    ( 
      SELECT
            *
          , rank() OVER ( 
            PARTITION BY
                group_name
                , user_name 
              ORDER BY
                to_timestamp(ctime, 'yyyy-mm-dd hh24:mi:ss.MS') DESC
                , gid DESC
          ) 
        FROM
          sample_tbl
    ) AS rank_tbl 
  WHERE
    rank = 1 
  GROUP BY
    group_name
  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

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

  • shoko1

    2017/06/28 11:35

    SQL・DBの質問はDBの種類、バージョンを明記すると正しい回答が得られやすいと思います。

    キャンセル

  • monagano

    2017/06/28 11:41

    ご指摘ありがとうございます。追記いたしました。

    キャンセル

回答 4

checkベストアンサー

+2

window関数のrank()を利用すれば、スマートに実現出来ると思います。

試してみてください。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/06/28 12:21

    回答いただき、ありがとうございます。
    以下の通り、rank関数に書き換えてみました。
    使い方に問題や改善の余地がありましたら、ご指摘いただけますでしょうか。

    SELECT
    group_name
    , sum(data1)
    FROM
    (
    SELECT
    *
    , rank() OVER (
    PARTITION BY
    group_name
    , user_name
    ORDER BY
    to_timestamp(ctime, 'yyyy-mm-dd hh24:mi:ss.MS') DESC
    , gid DESC
    )
    FROM
    sample_tbl
    ) AS rank_tbl
    WHERE
    rank = 1
    GROUP BY
    group_name

    キャンセル

  • 2017/06/28 12:59 編集

    PARTITION の指定も間違っていないようですし、ORDER もちゃんと指定できているようなので、問題無いと思われます。
    私の環境で試していないので、なんともですが、monaganoさんが、検索結果やパフォーマンスに納得出来れば、最善な方法だと思います。

    キャンセル

  • 2017/06/28 13:23

    rankのおかげですっきりと実装できました。
    パフォーマンスについても問題なさそうです。
    ありがとうございました。

    キャンセル

+2

SQL的には問題ありません。
このくらいのネストと条件なら一般的なPGは難なく読めるかと思います。

ただ一番ネストしたSQLがテーブル全件を対象としていますし、
同じテーブルを3回joinしているので
件数が増えると著しくパフォーマンスは落ちる可能性があります。

他に条件がありませんか?
ctimeが任意の期間だったり、他のカラムで条件があったり、、、
過去n日以前のデータは削除しているとか、、、
でないと、パフォーマンスは改善しないかと思われます。

あとはto_timestampしなくても最大値は取れそうな気がします。
件数が多くなった場合に関数の有り無しはパフォーマンスに影響します。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/06/28 12:24

    回答いただき、ありがとうございます。
    やはり、パフォーマンスには難があるのですね。

    >>あとはto_timestampしなくても最大値は取れそうな気がします。
    フォーマットがyyyy-mm-dd hh24:mi:ss.MSであれば、桁数が保障されている限り、文字列として大小比較しても問題ないという認識で良いのでしょうか。

    キャンセル

  • 2017/06/28 12:56

    件数とレスポンスはどのくらいを想定されていますか?
    数万件くらいなら問題ないはずですが、
    数十万単位になってくると改善が必要かと思われます。

    フォーマットはおっしゃる通り、
    桁数固定で数字が上がっていくだけなら、
    文字列比較でも問題ないはずです。

    キャンセル

  • 2017/06/28 13:21

    データ件数は最大100万件、レスポンス1秒程度が目安と考えております。
    to_timestampへの変換はコストが高そうなので、無変換での比較を検討してみようと思います。
    ありがとうございました。

    キャンセル

+2

ctimeはtext型ですが、日時データが格納されています フォーマット:yyyy-mm-dd hh24:mi:ss.MS

textですがフォーマットが統一しているので文字列にてmax

select t0.group_name, sum(t0.data1) from sample_tbl t0
join
(select t1.group_name,t1.user_name,t1.ctime,max(gid) gid from sample_tbl t1
    join (
        select group_name,user_name,max(ctime) ctime from sample_tbl
        group by group_name,user_name
        ) t2
    using (group_name,user_name,ctime)
    group by t1.group_name,t1.user_name,t1.ctime
) t3
using (gid,group_name,user_name,ctime)
group by t0.group_name
;

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/06/28 13:30

    回答いただき、ありがとうございます。
    USING による略記法の存在を初めて知りました。
    活用させていただきます。

    キャンセル

  • 2017/06/28 13:32

    基本ONと同じでJOINでテーブル双方の名前が一緒の場合に使えます。

    キャンセル

+1

抽出条件としてはこうなるので

select *
from sample_tbl as t1
having (select count(*)+1 from sample_tbl as t2 where 1
and t1.group_name=t2.group_name
and t1.user_name=t2.user_name
and (
t1.ctime<t2.ctime or
t1.ctime=t2.ctime and t1.gid<t2.gid
)
)=1

これをサブクエリにして集計するとこう

select group_name,sum(data1) as data1
from (
select group_name,data1
from sample_tbl as t1
having (select count(*)+1 from sample_tbl as t2 where 1
and t1.group_name=t2.group_name
and t1.user_name=t2.user_name
and (
t1.ctime<t2.ctime or
t1.ctime=t2.ctime and t1.gid<t2.gid
)
)=1
) as sub
group by group_name

postgreの書式は若干違うかもしれません

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/06/28 12:20

    回答いただきありがとうございます。
    having句の中でランク付けをすることで、一挙にctimeとgidの条件を組み合わせて抽出できるのですね。
    having句内のwhere 1 andという部分については、どういった意味合いがあるのでしょうか。

    キャンセル

  • 2017/06/28 12:27

    where 1は全データを抽出するという意味です
    その後and検索で条件を足していくと整形上ラクなのでよく使われる手法です

    キャンセル

  • 2017/06/28 12:31

    なるほど、整形上のテクニックとして使用されるのですね。
    ありがとうございます。

    キャンセル

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

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