SQL:条件付き最大値の抽出が途中で途切れてしまう。

Question

##やりたいこと
以下のようなデータをSQLで抽出すること。

◆作りたいデータ(要点抜粋)

ID	基準日	入力日	入力データ
101	1/1	12/30	A
101	1/8	12/30	A
101	1/15	1/12	B
101	1/22	1/12	B
101	1/29	1/29	C
102	1/1	12/27	D
102	1/8	1/4	E
102	1/15	1/4	E
102	1/22	1/22	F
102	1/29	1/22	F

上記を構成する元データは以下のような形である。
◆元データ(要点抜粋)

ID	入力日	入力データ
101	12/30	A
101	1/12	B
101	1/29	C
102	12/27	D
102	1/4	E
102	1/22	F

◆カレンダー(追記しました)

日付	曜日
1/1	金
1/2	土
1/3	日
1/4	月
1/5	火
1/6	水

以降省略

つまり要件としては、
・基準日以前で最も新しい入力日
・その際の入力データ
の2点を、基準日ごとに抽出することである。

※基準日について
・毎週金曜日を基準日として考える。
・基準日はカレンダーのデータベースから取得する。
・毎週の入力日について、金曜日にチェックできるようにする。
・基準日ごとに最新の入力日と入力データを取得する。

##うまくいかないポイント
以下のようなSQLを記載したが、データが途中で途切れてしまう。

select
 ID,入力日,入力データ,カレンダー.日付 --「元データ」は省略します
from
 元データ a1
cross join
 カレンダー
join(
 select
  ID,MAX(入力日)
 from
  元データ
 group by ID) a2
on
 a1.ID=a2.ID
and
 a1.入力日=a2.入力日

where
 カレンダー.曜日 = 金
and
 a1.入力日 <= カレンダー.日付

出力されるデータは以下のような形である。

ID	基準日	入力日	入力データ
101	1/15	1/12	B
101	1/22	1/12	B
101	1/29	1/29	C
102	1/8	1/4	E
102	1/15	1/4	E
102	1/22	1/22	F
102	1/29	1/22	F

##質問
・データが途切れる要因は何か
→ものによって途切れる場所が異なる。
→nullはなし。
・改善するために必要な句・ポイントは何か

##環境
Windows10
Amazon Reshift
PowerBI
以上で発生。

以上です。
初学者なので根本的に理解が及んでいない点があるかもしれないですが、要点ご教授いただけると助かります。
よろしくお願いいたします。

Comments

[neko_the_shadow]

基準日というのはどこから出てきていますか?

[PauT]

情報不足申し訳ございません。
カレンダーから曜日を指定して引っ張ってきています。
質問内に追記しました。

[nonoshu]

すみません。カレンダーは見ましたが
基準日がどういう意味なのか咀嚼できていません。
基準日について解説お願いできますか？

[PauT]

基準日について説明を追記いたしました。
基準日は毎週金曜とし、ウィークリーで最新の入力日・入力データを取得したいと考えています。

[nonoshu]

なるほど。言い換えると
土曜日始まりの１週間だとして
週末（金曜日）時点での最新入力日とその入力データを一覧したい、ということですね。

[PauT]

おっしゃるとおりです！
また、一部コード抜けがありましたので修正しました。

[nonoshu]

開示頂いたSQLを実行してみましたが、「GROUP BY句にIDが含まれていません」（a2のサブクエリ部分）
など色々構文エラーが出て出力される形にならないのですが・・・
実際に書かれているSQL文は開示かのうですか？

[PauT]

GROUP BY句が抜けていました。大変申し訳ありません。
一部修正しましたが、もしかしたら別のエラーも出てしまうかもですね。。
社用PCが時間的に起動できないため、明日実際のSQL文を記載するようにします。
よろしくお願いいたします。

Answer 1

RANK関数を利用するとよいかと思います。

SELECT S.ID, S.基準日, S.入力日, S.入力データ
FROM (
  SELECT 
    T.ID, C.日付, T.入力日, T.入力データ, 
    RANK() OVER (PARTITION BY T.ID, C.日付 ORDER BY T.入力日 DESC)
  FROM カレンダー C
  JOIN 元データ T ON T.入力日 <= C.日付
  WHERE C.曜日 = '金'
) S (ID, 基準日, 入力日, 入力データ, ランク)
WHERE ランク = 1

Comments

[PauT]

ありがとうございます！
思いつかなかった構造なので、明日試してみます。
うまくいかなかったら、またご相談させてください！

[PauT]

先ほど実施してみたところ、無事欲しかったデータを取ることができました！
MAXだとできなかったのに…不思議です！
ありがとうございました。