

MySQLである日付のTOPIX終値(`thattopix.adjust_price`)と1年後のTOPIX終値(`topixfuture.adjust_price`)を比較するクエリーを作成します。  
休日のデータは格納されていないため、もし1年後のTOPIX終値がない場合は、1年後以降でひっかかる最初の終値を引っ張りたいと考えていますが方法がわからず往生しています。  
私が求める方法は存在するのでしょうか？

[code]

    SELECT
        thattopix.year,
        topixfuture.adjust_price,
        thattopix.adjust_price,
        topixfuture.year as futureyear
    FROM
        stockdata as thattopix
        LEFT JOIN (
            SELECT year, price, adjust_price
            FROM stockdata
            WHERE code_id=3912
        ) AS topixfuture ON thattopix.year <= DATE_SUB(topixfuture.year,INTERVAL 1 YEAR)
    WHERE
        code_id = 3912
        AND thattopix.year >= DATE_SUB(topixfuture.year,INTERVAL 1 YEAR)
    ;
    
[/code]

◆ stockdataテーブル

[code]

    +--------------+------------+------+-----+---------+----------------+
    | Field        | Type       | Null | Key | Default | Extra          |
    +--------------+------------+------+-----+---------+----------------+
    | id           | int(11)    | NO   | PRI | NULL    | auto_increment |
    | year         | date       | YES  |     | NULL    |                |
    | code_id      | int(11)    | NO   | MUL | NULL    |                |
    | price        | float      | YES  |     | NULL    |                |
    | adjust_price | float      | YES  |     | NULL    |                |
    +--------------+------------+------+-----+---------+----------------+
    
[/code]

※ 2010年～2011年のデータを入力しています

sayuriさん  
payanekoさん  
ご検討いただきありがとうございます！！！  
無事抽出できました！  
本当に助かりました。  
質問のクローズ方法がわからなかったので、ひとまず本文にてお礼申し上げます_(. _.)_


CC BY-SA 4.0
2020-12-27T17:55:05.743
72930
2020-12-28T05:40:33.893
2020-12-28T05:40:33.893
25428
25428
question
mysql
MySQLでマッチした最初の行を結合したい


副問い合わせでグループ関数(`min`関数)を使うことで、topixfutureの日付をthattopixの翌年以降かつ最古の日付に合わせることができます。

**サンプルSQL**

[code]

    WITH stockdata
    AS (SELECT 1 AS id, cast('20101201' AS date) AS year, 3912 AS code_id, 10 AS price, 10 AS adjust_price UNION ALL
        SELECT 2 AS id, cast('20111201' AS date) AS year, 3912 AS code_id, 11 AS price, 11 AS adjust_price UNION ALL -- 翌年同日
        SELECT 3 AS id, cast('20101226' AS date) AS year, 3913 AS code_id, 12 AS price, 12 AS adjust_price UNION ALL
        SELECT 4 AS id, cast('20120104' AS date) AS year, 3913 AS code_id, 13 AS price, 13 AS adjust_price UNION ALL -- 翌々年最初
        SELECT 5 AS id, cast('20120105' AS date) AS year, 3913 AS code_id, 13 AS price, 13 AS adjust_price)          -- 翌々年最初 + 1日
    SELECT thattopix.*, topixfuture.*
    FROM stockdata AS thattopix
        LEFT JOIN stockdata AS topixfuture
        ON thattopix.code_id = topixfuture.code_id
        AND topixfuture.year = (SELECT min(tmp.year)
                                FROM stockdata AS tmp
                                WHERE tmp.code_id = thattopix.code_id
                                AND tmp.year >= DATE_ADD(thattopix.year, INTERVAL 1 YEAR))
    
[/code]

**実行結果**

[code]

    id    year    code_id    price    adjust_price    id    year    code_id    price    adjust_price
    1    2010-12-01    3912    10    10    2    2011-12-01    3912    11    11
    2    2011-12-01    3912    11    11    NULL    NULL    NULL    NULL    NULL
    3    2010-12-26    3913    12    12    4    2012-01-04    3913    13    13
    4    2012-01-04    3913    13    13    NULL    NULL    NULL    NULL    NULL
    5    2012-01-05    3913    13    13    NULL    NULL    NULL    NULL    NULL
    
[/code]


CC BY-SA 4.0
2020-12-28T02:47:28.247
72936
2020-12-28T02:47:28.247
9820
72930
answer


まず総当たりの中から１年後以降のものを取り出し、なおかつその順位を振っておく。順位が`1`のものが最も近い日付となっている。

[code]

    SELECT that.id AS thatid, future.id AS futureid, ROW_NUMBER() OVER (PARTITION BY that.id ORDER BY future.year) AS no
    FROM stockdata AS that, stockdata AS future
    WHERE DATE_ADD(that.year, INTERVAL 1 YEAR) <= future.year
    
[/code]

これを使えば

[code]

    SELECT that.year, future.adjust_price, that.adjust_price, future.year as futureyear
    FROM stockdata AS that
    INNER JOIN (
        SELECT that.id AS thatid, future.id AS futureid, ROW_NUMBER() OVER (PARTITION BY that.id ORDER BY future.year) AS no
        FROM stockdata AS that, stockdata AS future
        WHERE DATE_ADD(that.year, INTERVAL 1 YEAR) <= future.year
    ) AS map ON that.id = map.thatid AND map.no = 1
    INNER JOIN stockdata AS future ON map.futureid = future.id
    
[/code]

とか。

* * *

先にテーブルを完成させてから順位`1`で絞り込んでもいいですが、どっちがいいのかなぁ。

[code]

    SELECT thatyear, futureprice, thatprice, futureyear
    FROM (
        SELECT
           that.year AS thatyear,
           future.adjust_price AS futureprice,
           that.adjust_price AS thatprice,
           future.year as futureyear,
           ROW_NUMBER() OVER (PARTITION BY that.id ORDER BY future.year) AS no
        FROM stockdata AS that, stockdata AS future
        WHERE DATE_ADD(that.year, INTERVAL 1 YEAR) <= future.year
    ) AS t
    WHERE no = 1
    
[/code]


CC BY-SA 4.0
2020-12-28T03:19:26.567
72937
2020-12-28T03:19:26.567
4236
72930
answer