【SQL】二つのテーブルを結合する方法

Question

初学者です。お世話になります。
下記のように二つのテーブルを結合したいのですが、上手い方法が分かりません。
使うべき構文などをご教示いただけないでしょうか。具体的には「購入日時点における、最新の終値」を結合させるところで詰まっています。

何卒よろしくお願いします。

概要

　BigQueryを使っています。

• 二つのテーブルを結合したい。
• テーブルA(closing_price)は株価終値テーブル(日付、銘柄id、終値)
  • 1日ごとの終値を記録したもの。
• テーブルB(contract)は株式購入テーブル(購入日、購入者id、購入銘柄id、購入時株価、購入口数)
  • 購入した株の情報を記録したもの。
• テーブルBにテーブルAを左外部結合して、「購入日時点における、最新の終値を持つ行」を結合したい。

やりたいこと

• 例えば下記のようなサンプルデータがあります。

CREATE TABLE `project.test.closing_price`
(
  date DATE NOT NULL,
  stock_id INT64 NOT NULL,
  closing_price INT64 NOT NULL,
);
INSERT INTO `project.test.closing_price`
( date, stock_id,closing_price )
VALUES
("2022-10-21",1,1501),
("2022-10-22",1,1502),
("2022-10-23",1,1503),
("2022-10-24",1,1504),
("2022-10-25",1,1505);
CREATE TABLE `project.test.contract`
(
  contract_date DATE NOT NULL,
  user_id INT64 NOT NULL,
  stock_id INT64 NOT NULL,
  stock_price INT64 NOT NULL,
  units INT64 NOT NULL,
);
INSERT INTO `project.test.contract`
( contract_date, user_id, stock_id, stock_price,units )
VALUES
("2022-10-21",1,1,1501,1),
("2022-10-22",1,1,1502,1),
("2022-10-23",1,1,1503,1),
("2022-10-24",1,1,1504,1),
("2022-10-25",1,1,1505,1),
("2022-10-26",1,1,1506,1),
("2022-10-27",1,1,1507,1),
("2022-10-28",1,1,1508,1),
("2022-10-29",1,1,1509,1),
("2022-10-30",1,1,1510,1);

• 例えば下記のようなSQLを実行すると、当然ですが、「購入日時点における、最新の終値を持つ行」だけでなく、「購入日以前の終値を持つ行」全てと結合されてしまいます。

SELECT * FROM `project.test.contract` AS A
LEFT JOIN `project.test.closing_price` AS B
ON A.stock_id = B.stock_id and A.contract_date > B.date
order by A.contract_date;

• 結果

contract_date	user_id	stock_id	stock_price	units	date	stock_id_1	closing_price
2022-10-21	1	1	1501	1
2022-10-22	1	1	1502	1	2022-10-21	1	1501
2022-10-23	1	1	1503	1	2022-10-21	1	1501
2022-10-23	1	1	1503	1	2022-10-22	1	1502
2022-10-24	1	1	1504	1	2022-10-21	1	1501
2022-10-24	1	1	1504	1	2022-10-22	1	1502
2022-10-24	1	1	1504	1	2022-10-23	1	1503
2022-10-25	1	1	1505	1	2022-10-21	1	1501
2022-10-25	1	1	1505	1	2022-10-22	1	1502
2022-10-25	1	1	1505	1	2022-10-23	1	1503
2022-10-25	1	1	1505	1	2022-10-24	1	1504
~~~~~

• 欲しい結果
  • これを「購入日時点における、最新の終値を持つ行」一つとだけ結合させたいのですが、いまいちやり方が分かりません。

contract_date	user_id	stock_id	stock_price	units	date	stock_id_1	closing_price
2022-10-21	1	1	1501	1
2022-10-22	1	1	1502	1	2022-10-21	1	1501
2022-10-23	1	1	1503	1	2022-10-22	1	1502
2022-10-24	1	1	1504	1	2022-10-23	1	1503
2022-10-25	1	1	1505	1	2022-10-24	1	1504
~~~~~

Comments

[yambejp]

想定するSQLの種類とバージョンは？

[Jonathan_Sf]

すみません。bigqueryですね。

[yambejp]

BigQueryタグがあるのでつけてみるとよいかもしれません
(逆にBigQuery専用の書き方を希望される場合はあまり多くの回答は期待できないかもしれませんが)

[yambejp]

サンプルデータと、想定する結果も提示されると回答が付きやすくなると思います

[Orlofsky]

質問は修正できます。
テーブル定義はCREATE TABLE文に、データはINSERT文に、現行のSELECT文とその結果、希望する結果結果も含めて Markdownの[コード]を使って提示してください。
https://teratail.com/help/question-tips#questionTips37

Answer 1

OLAP版のSQLiteを目指しているというDuckDBの勉強中で，挑戦してみました。
プロから見ると変なコードと思いますが，こんな感じでどうでしょうか?

なお，DuckDBでは`テーブル名` が使えないので，
project.test.contract→contract，project.test.closing_price →closing_price
で試しています。

WITH
  all_data AS (
    SELECT
      *
      FROM `project.test.contract`
             AS A LEFT JOIN
           `project.test.closing_price` AS B 
                      ON A.stock_id = B.stock_id AND A.contract_date > B.date
     ORDER BY A.contract_date
  ) ,
  rank_data AS (
    SELECT
      *
      , RANK() OVER(PARTITION BY contract_date, stock_id
                    ORDER BY date DESC)
        AS rank FROM all_data
  )
SELECT contract_date, user_id, stock_id, stock_price,
       units, date, closing_price
  FROM
    rank_data
 WHERE
   rank=1
   ;

一応結果です。stock_idがかぶっているので左側のみ残しています。

D .read test20230205-2.sql
┌───────────────┬─────────┬──────────┬─────────────┬───────┬────────────┬───────────────┐
│ contract_date │ user_id │ stock_id │ stock_price │ units │    date    │ closing_price │
│     date      │  int64  │  int64   │    int64    │ int64 │    date    │     int64     │
├───────────────┼─────────┼──────────┼─────────────┼───────┼────────────┼───────────────┤
│ 2022-10-21    │       1 │        1 │        1501 │     1 │            │               │
│ 2022-10-22    │       1 │        1 │        1502 │     1 │ 2022-10-21 │          1501 │
│ 2022-10-23    │       1 │        1 │        1503 │     1 │ 2022-10-22 │          1502 │
│ 2022-10-24    │       1 │        1 │        1504 │     1 │ 2022-10-23 │          1503 │
│ 2022-10-25    │       1 │        1 │        1505 │     1 │ 2022-10-24 │          1504 │
│ 2022-10-26    │       1 │        1 │        1506 │     1 │ 2022-10-25 │          1505 │
│ 2022-10-27    │       1 │        1 │        1507 │     1 │ 2022-10-25 │          1505 │
│ 2022-10-28    │       1 │        1 │        1508 │     1 │ 2022-10-25 │          1505 │
│ 2022-10-29    │       1 │        1 │        1509 │     1 │ 2022-10-25 │          1505 │
│ 2022-10-30    │       1 │        1 │        1510 │     1 │ 2022-10-25 │          1505 │
├───────────────┴─────────┴──────────┴─────────────┴───────┴────────────┴───────────────┤
│ 10 rows                                                                     7 columns │
└───────────────────────────────────────────────────────────────────────────────────────┘