

SQLServerのお勉強で躓いています。

やろうとした事は no, timestamp の2列から成る'table_a'に、 no, datetime, value
の3列から成る'table_b'を左外部結合するという事です（両テーブルとも no, datetime がプライマリキー）。  
結合条件は下記の通りです。

  * no: '='比較
  * timestamp:'table_b'の timestamp の中で、'table_a'の timestamp 以下の最大の timestamp

あまり上手い説明ではないので、具体例として下記コードをご覧下さい。

[code]

    IF object_id('table_a') IS NOT NULL
      DROP TABLE table_a;
    
    CREATE TABLE table_a (
      no         INTEGER not null
     ,timestamp  DATE    not null
     ,CONSTRAINT pk_table_a PRIMARY KEY CLUSTERED (no, timestamp)
    );
    
    IF object_id('table_b') IS NOT NULL
      DROP TABLE table_b;
    
    
    CREATE TABLE table_b (
      no         INTEGER not null
     ,timestamp  DATE    not null
     ,value      INTEGER not null
     ,CONSTRAINT pk_table_b PRIMARY KEY CLUSTERED (no, timestamp)
    );
    
    BEGIN TRANSACTION;
    
    INSERT INTO table_a (no, timestamp) 
    VALUES      (1, '2018-01-01')
               ,(1, '2018-01-04')
               ,(1, '2018-01-05')
               ,(2, '2018-01-01')
               ,(2, '2018-01-03')
               ,(2, '2018-01-09')
               ,(3, '2018-01-10')
               ,(3, '2018-01-11')
               ,(3, '2018-01-12')
    ;
    
    INSERT INTO table_b (no, timestamp, value) 
    VALUES      (1, '2018-01-01', 101)
               ,(1, '2018-01-02', 102)
               ,(2, '2018-01-02', 202)
               ,(3, '2017-01-01', 301)
               ,(3, '2018-01-12', 312)
    ;
    COMMIT;
    
    SELECT *
      FROM table_a
    ;
    
    SELECT *
      FROM table_b
    ;
    
    
    -- Query A: I can get what I want.
    SELECT A.no
          ,A.timestamp
          ,B.value
      FROM (
        SELECT A.no
              ,A.timestamp
              ,(SELECT MAX(B.timestamp) 
                  FROM table_b as B 
                 WHERE B.no = A.no
                   AND B.timestamp <= A.timestamp) as a_b_timestamp
          FROM table_a AS A
          ) as A
            LEFT OUTER JOIN table_b AS B
                  ON  A.no = B.no
                  AND A.a_b_timestamp = B.timestamp
     WHERE A.no in (1, 2)
    ;
    
     -- Query B: This query's result set is the same with A.
     SELECT A.no
          ,A.timestamp
          ,B.value
      FROM table_a AS A
             LEFT OUTER JOIN table_b AS B 
               ON A.no = B.no
              AND B.timestamp = (SELECT MAX(B.timestamp)    
                                   FROM table_b as B 
                                  WHERE B.no = A.no
                                    AND B.timestamp <= A.timestamp)
     WHERE A.no in (1, 2)
    ;
    
    -- Query C: The result set is far from what I expected, but I do not know reason.
    SELECT A.no
          ,A.timestamp
          ,B.value
      FROM table_a AS A
             LEFT OUTER JOIN table_b AS B 
               ON A.no = B.no
              AND A.timestamp = (SELECT MAX(B.timestamp)    
                                   FROM table_b as B 
                                  WHERE B.no = A.no
                                    AND B.timestamp <= A.timestamp)
     WHERE A.no in (1, 2)
     ;
    
[/code]

テーブルに以下のデータが準備されます。

table_a:  
no timestamp  
1 2018-01-01  
1 2018-01-04  
1 2018-01-05  
2 2018-01-01  
2 2018-01-03  
2 2018-01-09  
3 2018-01-10  
3 2018-01-11  
3 2018-01-12

table_b:  
no timestamp value  
1 2018-01-01 101  
1 2018-01-02 102  
2 2018-01-02 202  
3 2017-01-01 301  
3 2018-01-12 312

コード内にQuery A, B, C とコメントされたクエリがありますが、得られる結果セットは以下の通りです。私がほしかったのは A, B なのですが、 C
だけが異なる結果セットを返しています。

Query A,B の結果セット（これが欲しかった！）:  
no timestamp value  
1 2018-01-01 101  
1 2018-01-04 102  
1 2018-01-05 102  
2 2018-01-01 NULL  
2 2018-01-03 202  
2 2018-01-09 202

Query C の結果セット（ほしいもの違う）:  
no timestamp value  
1 2018-01-01 101  
1 2018-01-01 102  
1 2018-01-04 NULL  
1 2018-01-05 NULL  
2 2018-01-01 NULL  
2 2018-01-03 NULL  
2 2018-01-09 NULL

困っているのは C の結果がなぜ上のようになるのかが上手く説明出来ない点です。  
どなたか、理由をクリアに説明して頂けないでしょうか。


CC BY-SA 4.0
2019-01-29T16:01:50.787
52404
2019-01-30T13:09:52.160
2019-01-29T23:24:17.667
4236
31975
question
sql sql-server
外部結合の結果が想定と異なるが理由がピンとこない


table_bを外部結合するのに、table_bに関する条件が十分書かれていないのでは、期待する結果は得難いと言うのを意識しておかれた方がいいでしょう。

Query Cのサブクエリーはtable_aに依存した相関サブクエリーですから、table_aの行ごとに異なる値となります。最終的に`A.no in (1,
2)`で絞り込むのでそこ以外は省くとこんな感じ。

[code]

                      MAX(B.timestamp)
    (1) 1 2018-01-01  2018-01-01
    (2) 1 2018-01-04  2018-01-02
    (3) 1 2018-01-05  2018-01-02
    (4) 2 2018-01-01  NULL
    (5) 2 2018-01-03  2018-01-02
    (6) 2 2018-01-09  2018-01-02
    
[/code]

よってレコード(1)に対する外部結合条件は

[code]

    A.no = B.no AND A.timestamp = '2018-01-01'
    
[/code]

と言うことになります。table_bからこの条件にマッチするレコードを全てtable_aのこのレコードに結合します。`A.timestamp =
'2018-01-01'`は成立していますから、table_bから`A.no = B.no`にマッチするレコードを全部外部結合することになります。。

それがQuery Cの結果のこの部分です。

[code]

    1 2018-01-01 101
    1 2018-01-01 102
    
[/code]

一方、レコード(2)から(6)については、`A.timestamp =
(...MAX(B.timestamp)...)`の部分が成立することはないので、結合条件は常に偽と判定され、外部結合されるBのレコードは存在しません。結果、Bのカラムに対応する値はNULLになっています。

どの部分が理解しにくかったのか、コメント等いただければ、もう少し詳しく書けるかもしれません。


CC BY-SA 4.0
2019-01-29T22:45:34.617
52405
2019-01-29T22:45:34.617
13972
52404
answer


Query B及びC におけるON句が

>   * no: '='比較
>   * timestamp:'table_b'の timestamp の中で、'table_a'の timestamp 以下の最大の timestamp
>

に該当しているわけですが、最後の「〜以下の最大の **timestamp** 」はtable_a の timestamp ではなく、 table_b の
timestamp ですよね。

つまり条件はQuery Cのようなtable_aに対するもの

>
[code]

>           AND A.timestamp = (...)
>  
[/code]

ではなく、 Query Bのようなtable_bに対するもの

>
[code]

>           AND B.timestamp = (...)
>  
[/code]

になるはずです。

あとは、エイリアス名`B`を使い回しているせいで混乱を招いているのかなとも思いました(登場人物が2人だけのように見える)。  
Query Bをもう少し日本語文章に即した形で書くと次のような感じになるかと思います。

[code]

    SELECT *
    FROM table_a A
    LEFT JOIN table_b B1
    ON B1.no = A.no
    AND
    B1.timestamp = (
        SELECT MAX(B2.timestamp)
        FROM table_b B2
        WHERE B2.no = B1.no
        AND
        B2.timestamp <= A.timestamp
        GROUP BY B2.no
    )
    ;
    
[/code]


CC BY-SA 4.0
2019-01-30T13:09:52.160
52430
2019-01-30T13:09:52.160
2808
52404
answer