SQLで3つのテーブルから情報を取得したい

Question

以下の3つのテーブルからプロジェクトに対する最終更新者と更新日時を取得するにはどう記述すればいいでしょうか。
使用しているのはSQLiteです。

テーブル：PROJECT

ID	PROJECTNAME
1	hoge
2	hage
3	hige

テーブル：USER

ID	USERNAME
1	YAMADA
2	SATO

テーブル：HISTORY（UPDATEDT順になっている）

ID	PROJECTID(=PROJECT.ID)	USERID(=USER.ID)	UPDATEDT
1	1	1	2021/06/26 2:10:00
2	1	2	2021/06/27 12:00:00
3	1	1	2021/06/27 21:00:00
4	2	2	2021/06/28 16:45:00

上記から、以下の情報を取得したいです。

PROJECTID	PROJECTNAME	USERNAME	UPDATEDT
1	hoge	YAMADA	2021/06/27 21:00:00
2	hage	SATO	2021/06/28 16:45:00
3	hage

Answer 1

history に(PROJECTID, UPDATEDT desc, USERID)というインデックスを追加すると効率的だと思います。

select  prj.*, usr.username
from (
        select  prj.*
              , max(his.updatedt) over(partition by his.projectid) as lastupdate
        from  project as prj
              left join history as his
              on    prj.id = his.projectid
      ) as prj
      left join history as his 
      on      prj.id = his.projectid
          and prj.lastupdate=his.updatedt
      left join user as usr 
      on    usr.id = his.userid

projectから取得する項目が限定されるなら、いっそ以下の方が高速だと思います。

select  prj.*, usr.username
from (
        select  prj.id, prj.projectname
              , max(his.updatedt) as lastupdate
        from  project as prj
              left join history as his
              on    prj.id = his.projectid
        group by prj.id, prj.projectname
      ) as prj
      left join history as his 
      on      prj.id = his.projectid
          and prj.lastupdate=his.updatedt
      left join user as usr 
      on    usr.id = his.userid

Answer 2

やり方はいろいろあると思いますが、たとえば以下のようなクエリで実現できるかと思います。

SELECT P.id, P.projectname, U.username, H.updatedt
FROM PROJECT AS P
LEFT OUTER JOIN (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY projectid ORDER BY updatedt DESC) AS rownum
  FROM HISTORY
) AS H ON P.id = H.projectid
LEFT OUTER JOIN USER AS U ON U.id = H.userid
WHERE rownum IS NULL OR rownum = 1;

Comments

[Tak_]

実行速度が早かったです。ありがとうございます。

[sazi]

@Tak_さん
特にインデックスは無しという状況でしょうか？

[Tak_]

はい、現状インデックスは作成していません。
少し時間ができたらインデックスを作成してどれだけ改善できるか試してみようと思います。