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

Question

###前提・実現したいこと
使用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

Comments

[shoko1]

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

[monagano]

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

Answer 1

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
;

Comments

[monagano]

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

[A.Ichi]

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

Answer 2

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

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

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

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

Comments

[monagano]

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

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

[szk.]

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

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

[monagano]

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

Answer 3

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

試してみてください。

Comments

[monagano]

回答いただき、ありがとうございます。
以下の通り、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

[koizumi]

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

[monagano]

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

Answer 4

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

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の書式は若干違うかもしれません

Comments

[monagano]

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

[yambejp]

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

[monagano]

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