I want to combine the first line that matches in MySQL

Create a query that compares the TOPIx closing value (thattopix.adjust_price) for a date that is MySQL with the TOPIx closing value (topixfuture.adjust_price) after one year.
The holiday data is not stored, so if there is no TOPIX closing price after a year, I would like to pull the first closing price after a year, but I don't know how to do it.
Is there any way I can find it?

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, INTERVAL1YEAR)
WHERE
    code_id = 3912
    AND thattopix.year>=DATE_SUB (topixfuture.year, INTERVAL1YEAR)
;

◆stockdata table

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

2010 Data for 2010-2011 are entered

Dear Sayuri, Dear Payaneko, Thank you for your consideration!!
It was successfully extracted!
It was really helpful.
I didn't know how to close the question, so I'd like to thank you in the text for now_(..)

mysql

2022-09-30 17:45

2 Answers

By using the group function (min function) in subqueries, you can match the date of topixfuture to the next year and the oldest date of thattopix.

Sample SQL

 WITH stockdata
AS(SELECT1 AS id, cast('20101201' AS date) AS year, 3912 AS code_id, 10 AS price, 10 AS adjust_price UNION ALL
    SELECT2AS id,cast('201111201'AS date)AS year,3912AS code_id,11AS price,11AS adjust_price UNION ALL -- same day of the following year
    SELECT3 AS id, cast('20101226' AS date) AS year, 3913 AS code_id, 12 AS price, 12 AS adjust_price UNION ALL
    SELECT4 AS id, cast('20120104' AS date) AS year, 3913 AS code_id, 13 AS price, 13 AS adjust_price UNION ALL -- first year after next
    SELECT 5 AS id, cast ('20120105' AS date) AS year, 3913 AS code_id, 13 AS price, 13 AS adjust_price) -- first day of the following year +1 day
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, INTERVAL1YEAR))

Run Results

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-013912 11 11 NULL NULL NULL NULL NULL 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 NULL NULL NULL NULL.
5 2012-01-05 3913 13 13 NULL NULL NULL NULL NULL NULL NULL NULL NULL NUL

2022-09-30 17:45

First of all, take out one year or later from the round-robin and shake the ranking.The nearest date is 1.

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, INTERVAL1YEAR)<=future.year

If you use this,

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 (PARTION BY that.id ORDER BY future.year) AS no
    FROM stockdata AS that, stockdata AS future
    WHERE DATE_ADD(that.year, INTERVAL1YEAR)<=future.year
AS map ON that.id = map.thatid AND map.no = 1
INNER JOIN stockdata AS future ON map.futureid=future.id

and so on.

You can finish the table first and then narrow it down to the ranking 1, but I wonder which one is better.

SELECT that year, futureprice, thatprice, futureyear
FROM(
    SELECT
       that.year AS that year,
       future.adjust_price AS futureprice,
       that.adjust_price AS thatprice,
       future.year as futureyear,
       ROW_NUMBER() OVER(PARTION BY that.id ORDER BY future.year) AS no
    FROM stockdata AS that, stockdata AS future
    WHERE DATE_ADD(that.year, INTERVAL1YEAR)<=future.year
ASt
WHERE no=1

2022-09-30 17:45

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656