SQLにて指定日付以前、かつ最新のデータを1件取得したいです。

Question

前提・実現したいこと

現在下記のようなテーブルが存在しております。

テーブル・・・
ID ｜名前｜日付
01｜田中｜3/16
02｜田中｜3/15
03｜田中｜3/14
04｜山田｜3/17
05｜山田｜3/16
06｜鈴木｜3/15
07｜鈴木｜3/14
・・・

名前が田中かつ日付が3/15日以前で最新を1件
名前が鈴木かつ日付が3/14日以前で最新を1件

上記のような検索を実行し、下記の結果を得たいと考えております。

ID ｜名前｜日付
02｜田中｜3/15
07｜鈴木｜3/14

試したこと

SELECT ＊
FROM テーブル AS テーブルA
INNER JOIN （SELECT 名前,MAX(日付）AS 最新日付 FROM テーブル
WHERE （日付　<= ‘2021-03-15 00:00:00’ AND 名前 = ‘田中’)
GROUP BY 名前 AS テーブルB
ON テーブルA.名前 = テーブルB.名前
AND テーブルA.日付　＝　テーブルB.最新日付

ーーー
上記のような形で一応取ってくることはできましたが、
かなりスピードが遅く困っております・・・
SQLに関する理解不足で読みにくいSQLになってしまい大変失礼ですが、
この場合の正しいSQLの書き方についてご回答をお願いしてもよろしいでしょうか。

また、もし可能であれば１回のSQLで
名前が田中かつ日付が3/15日以前で最新を1件」「名前が鈴木かつ日付が3/14日以前で最新を1件」
を同時に抽出する方法などありますでしょうか。

すみませんがどうぞよろしくお願いします。

Comments

[sazi]

DBはMySQL、Postgresの何れですか？

それから、インデックスと実行計画の結果も質問に追記して下さい

[yowayowanitohei]

タグ付けややこしくてすみません。。。
Postgresになります。

[yambejp]

「より前（指定日を含まない）」なのですか
「以前（指定日を含む）」なのでしょうか？
想定見る限り後者のようですが、表題がより前となっているので・・・
それと同じ名前で同じ日付のデータは存在しないことは保証されていますか？

[yowayowanitohei]

「以前（指定日を含む）」になります。
すみませんが、どうぞよろしくお願いいたします。

Answer 1

SELECT *
FROM  テーブル
where (名前, 日付) in (
        select 名前, MAX(日付)
        FROM テーブル t inner join (
          select * from (values
            ('田中', '2021-03-15' ::date)
           ,('鈴木', '2021-03-14' ::date)
          ) as w(指定名前,指定日付) 
        ) p
        on t.名前=p.指定名前 and t.日付<=p.指定日付
        group by 名前
      ) 

※性能を求めるなら（名前,日付)のインデックスが必要です。
追記

Postgresのバージョンが8.0との事なので、パラメータの指定が分かりにくくなりますが、unnest()を使用した例です。

SELECT *
FROM  テーブル
where (名前, 日付) in (
        select 名前, MAX(日付)
        FROM テーブル t inner join (
          select 
                  unnest(array['田中','鈴木']::text[]) as 指定名前
                , unnest(array['2021-03-15', '2021-03-14']::date[]) as 指定日付
        ) p
        on t.名前=p.指定名前 and t.日付<=p.指定日付
        group by 名前
      ) 

※手元には8.0の環境はありませんので、確認は出来ておらず、エラーになるかもしれません。

他のセッションの影響を受けないように一時テーブルをパラメータに利用するようにすると、SQLを動的に変更する必要は無くなります。

CREATE TEMPORARY TABLE para (
  指定名前 text
, 指定日付 date
) ON COMMIT DELETE ROWS
;

insert into para values
 ('田中', '2021-03-15')
,('鈴木', '2021-03-14')
;
SELECT *
FROM  テーブル
where (名前, 日付) in (
        select 名前, MAX(日付)
        FROM テーブル t inner join para p
        on t.名前=p.指定名前 and t.日付<=p.指定日付
        group by 名前
      ) 
;

Comments

[yowayowanitohei]

丁寧なご回答ありがとうございます。
一点だけ追加でご質問させていただきたいのですが、

values
('田中', '2021-03-15' ::date)
,('鈴木', '2021-03-14' ::date)

上記箇所にてsyntaxerrorが発生してしまいます。

ERROR: syntax error at or near ","
行 8: ,('鈴木', '2021-03-14' ::date)

https://www.postgresql.jp/document/9.4/html/queries-values.html
ドキュメントでValuesリストの例代を試しに実行してみたのですが、
同じ箇所でエラーが発生してしまいます。。

VALUES (1, 'one'), (2, 'two'), (3, 'three');
行 1: SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) ...

この場合の対処法などご存知でしたら大変お手数をおかけしますが
お伺いしてもよろしいでしょうか。
どうぞよろしくお願いします。

[sazi]

追記しました

Answer 2

たとえばウィンドウ関数を利用するのはどうでしょうか?

SELECT 
  ID,
  名前,
  日付
FROM (
  SELECT 
    ID,
    名前,
    日付,
    RANK() OVER (PARTITION BY 名前 ORDER BY 日付 DESC) AS rnk
  FROM テーブル
  WHERE (名前 = '田中' AND 日付 <= '2021-03-15')
     OR (名前 = '鈴木' AND 日付 <= '2021-03-14')
) AS t
WHERE rnk = 1

Answer 3

select * from tbl
where (name,d) in (
select name,max(case when d>'2021-03-15' then 0 else d end) as d from tbl 
group by name
having name='田中'
union all
select name,max(case when d>'2021-03-14' then 0 else d end) as d from tbl 
group by name
having name='鈴木'
)