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

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

新規登録して質問してみよう
ただいま回答率
85.50%
PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

2回答

693閲覧

【PostgreSQL】該当日付がない場合、一番直前の日付でjoinを行いたい

T_K_T_K

総合スコア5

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2020/03/04 05:20

PostgreSQLで2つのテーブル間において、管理IDと日付を基にleft joinをしたいと考えています。
以下表のテーブルAは日付が歯抜けない状態になっていますが、
テーブルBは日付が歯抜けになっており、単純にleft joinした場合、
該当日付がない場合はnullになってしまうかと思います。

そうではなく、該当日付がない場合は一番直前の日付のレコードをjoinさせたいと考えているのですが、
上手い方法が思いつきません。
お力を貸していただけますと幸いです。

joinするイメージとしては、
例えばテーブルBの管理ID:Aは2020/02/01はレコードがない為、
テーブルAの管理ID:A、2020/02/01のレコードについては、
一番直前の2020/01/31のレコードをjoinしたいです。

■テーブルA

日付管理ID数値A
2020/01/31A9
2020/02/01A7
2020/02/02A2
2020/02/03A4
2020/02/04A2
2020/02/05A3
2020/01/31B1
2020/02/01B2
2020/02/02B2
2020/02/03B1
2020/02/04B6
2020/02/05B1

■テーブルB

日付管理ID数値B
2020/01/31A10
2020/02/02A11
2020/02/05A12
2020/01/31B20
2020/02/01B21
2020/02/05B22

■結合後テーブル(Aを基にBをleft join)

日付管理ID数値A数値B
2020/01/31A910
2020/02/01A710
2020/02/02A211
2020/02/03A411
2020/02/04A211
2020/02/05A312
2020/01/31B120
2020/02/01B221
2020/02/02B221
2020/02/03B121
2020/02/04B621
2020/02/05B122

気になる質問をクリップする

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2020/03/04 07:47

該当日のレコードがない場合に、一日前のみ参照するのか、何日前までさかのぼってやるのか、それ次第によっては大きくSQLも変わりそうな気がします。
sazi

2020/03/04 09:01

テーブルBにある日付は、必ずテーブルAの日付と一致しますか?
Orlofsky

2020/03/04 10:56

質問のテーブル定義はCREATE TABLEで、テーブルに存在するデータのINSERTに修正した方が適切なコメントが付き易いです。
T_K_T_K

2020/03/04 23:26

諸々コメントありがとうございます! m6u様 ⇒該当日のレコードがない場合は日付と管理IDをキーにして  直前の日付を探しに行き、存在しなければjoin後は数値Nをnullにしたいです。 sazi様 ⇒言葉が足りておらず申し訳ございません。  必ずしも一致するわけではありません。  テーブルBに日付が存在しない場合もありますのでその場合は、  left join後に数値Bをnullにしたいです。 Orlofsky様 ⇒分かりづらい表記となり申し訳ございません。  ご指摘いただきありがとうございます。
Orlofsky

2020/03/04 23:30

ここの掲示板は質問も回答も修正できます。
guest

回答2

0

ベストアンサー

こんな感じで。

SQL

1with テーブルA as ( 2 select * from (values 3 ('2020/01/31', 'A', 9) 4 ,('2020/02/01', 'A', 7) 5 ,('2020/02/02', 'A', 2) 6 ,('2020/02/03', 'A', 4) 7 ,('2020/02/04', 'A', 2) 8 ,('2020/02/05', 'A', 3) 9 ,('2020/01/31', 'B', 1) 10 ,('2020/02/01', 'B', 2) 11 ,('2020/02/02', 'B', 2) 12 ,('2020/02/03', 'B', 1) 13 ,('2020/02/04', 'B', 6) 14 ,('2020/02/05', 'B', 1) 15 ) as w(日付,管理ID,数値A) 16 17) , テーブルB as ( 18 select * from (values 19 ('2020/01/31', 'A', 10) 20 ,('2020/02/02', 'A', 11) 21 ,('2020/02/05', 'A', 12) 22 ,('2020/01/31', 'B', 20) 23 ,('2020/02/01', 'B', 21) 24 ,('2020/02/05', 'B', 22) 25 ) as w(日付,管理ID,数値B) 26) 27select ta.日付, ta.管理id, ta.数値A 28 ,(select 数値B from テーブルB 29 where 管理id=ta.管理id 30 and 日付 = ( 31 select Max(日付) from テーブルB 32 where 管理id=ta.管理id and 日付<=ta.日付 33 ) 34 ) 数値B 35from テーブルA ta

追記

2つのテーブルの中間テーブルに該当するインラインビューでリレーション

SQL

1with テーブルA as ( 2 select * from (values 3 ('2020/01/31', 'A', 9) 4 ,('2020/02/01', 'A', 7) 5 ,('2020/02/02', 'A', 2) 6 ,('2020/02/03', 'A', 4) 7 ,('2020/02/04', 'A', 2) 8 ,('2020/02/05', 'A', 3) 9 ,('2020/01/31', 'B', 1) 10 ,('2020/02/01', 'B', 2) 11 ,('2020/02/02', 'B', 2) 12 ,('2020/02/03', 'B', 1) 13 ,('2020/02/04', 'B', 6) 14 ,('2020/02/05', 'B', 1) 15 ) as w(日付,管理ID,数値A) 16 17) , テーブルB as ( 18 select * from (values 19 ('2020/01/31', 'A', 10) 20 ,('2020/02/02', 'A', 11) 21 ,('2020/02/05', 'A', 12) 22 ,('2020/01/31', 'B', 20) 23 ,('2020/02/01', 'B', 21) 24 ,('2020/02/05', 'B', 22) 25 ) as w(日付,管理ID,数値B) 26) 27select ta.日付, ta.管理id, ta.数値A, tb.数値B 28from テーブルA ta 29 left join ( 30 select ta.管理ID, ta.日付 日付A, max(tb.日付) 日付B 31 from テーブルA ta inner join テーブルB tb 32 on ta.管理ID=tb.管理ID and ta.日付>=tb.日付 33 group by ta.管理ID, ta.日付 34 ) map 35 on ta.管理ID=map.管理id and ta.日付=map.日付A 36 left join テーブルB tb 37 on map.管理id=tb.管理id and map.日付B=tb.日付

投稿2020/03/04 09:42

編集2020/03/05 01:57
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

T_K_T_K

2020/03/05 00:16

コメントいただきありがとうございます! 試してみたところ、 >This type of correlated subquery pattern is not supported yet というエラーが返ってきました。 環境はRedshiftなのですが、RedshiftはPostgreSQLと互換性がるので 問題ないかと思っていたのですが、一部サポートされていない相関サブクエリがあるようです、、、 https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_correlated_subqueries.html 恐らく文法的には正しいが、Redshift側が対応していないということかと思われます、、、
sazi

2020/03/05 01:58 編集

追記しました。
T_K_T_K

2020/03/05 07:37

ありがとうございます! 実現したいことが出来ました! 本当に助かりました!
guest

0

「テーブルB」が日付的に抜けがあるということなので、
日付を1日引き算した内容を返す「テーブルB’」を用意して、
「テーブルA」に対してLEFT JOINを「テーブルB」と「テーブルB’」に対して行います。

「テーブルB」の日付がNULLだった場合に「テーブルB’」のデータを参照するよう、
COALESCE(テーブルBのカラム, テーブルB’のカラム)
などとすればとりあえず1日前のデータをつなぎとめることはできます。

これがもしも2日前まで遡るなら、
2日前の日付にした同様な「テーブルB’’」を用意するというふうに考えます。

投稿2020/03/04 07:51

退会済みユーザー

退会済みユーザー

総合スコア0

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

T_K_T_K

2020/03/04 23:14

コメントいただきありがとうございます! >これがもしも2日前まで遡るなら、 >2日前の日付にした同様な「テーブルB’’」を用意するというふうに考えます。 申し訳ございません。 自分が出した例が適切でなかったです。 テーブルAに存在するレコードが必ずしもテーブルBに存在するわけではありませんので、 そもそも対象のテーブルB’が存在しない場合もあります。 また対象のレコードがテーブルBに存在しなかった場合は結合後は 数値Bの値はNULLになっていて欲しいです。
退会済みユーザー

退会済みユーザー

2020/03/05 01:19

質問文から受けた印象で、これで行けると思ったのですが、 そうじゃないんですね、そうですかー。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問