MySQL: Calculations with Current/Future Row Values with different Criteria in same table?

user_3718843 0
Viewed 34

I need this:

AVG(T1.CLS - T3.OPN) WHERE MIN(T1.ID) > T3.ID AND T1.YRWK = T3.YRWK

From this:

ID DT YRWK OPN CLS S
124 2005-09-06 18:16:00 200536 1138.50 1138.50 3
216 2005-09-06 21:25:00 200536 1137.75 1138.00 3
230 2005-09-06 21:53:00 200536 1137.50 1137.50 1
290 2005-09-07 00:20:00 200536 1138.25 1138.50 3
299 2005-09-07 00:32:00 200536 1138.25 1138.25 1
315 2005-09-07 01:02:00 200536 1138.50 1138.50 3
318 2005-09-07 01:05:00 200536 1138.50 1138.25 1
320 2005-09-07 01:07:00 200536 1138.00 1137.75 1
322 2005-09-07 01:09:00 200536 1138.00 1137.75 1
325 2005-09-07 01:12:00 200536 1138.25 1138.25 3

T1 is an example of a derived table where S = 1, and T3 is all rows where S = 3.

DETAILS:

  • I need an AVG of [CLS - OPN] where only use the CLS-values from the rows where S = 1, and the OPN-values from the rows where S = 3 AND WHERE CLS-Value is the EARLIEST row AFTER the OPN-value row, AND where OPN/CLS values only occur within the same YRWK.

EXPECTED RESULTS:

  • 1137.50 - 1138.50 = -1.00 ([ID230],CLS) - ([ID124,OPN])
  • 1137.50 - 1137.75 = -0.25 ([ID230],CLS - ([ID216,OPN])
  • 1138.25 - 1138.25 = 0.00 ([ID299],CLS - ([ID290,OPN])
  • 1138.25 - 1138.50 = -0.25 ([ID318],CLS - ([ID315,OPN])

ANSWER = -0.375 AVG

WHAT I HAVE TRIED:

DROP TABLE IF EXISTS T1;
CREATE TEMPORARY TABLE T1
SELECT
    A.id AS ID1,
    dt AS DT1,
    YEARWEEK(dt) AS YRWK1,
    `open` AS O1,
    `close` AS C1,
    scalper AS S1
FROM
    t_1min A
JOIN
    t_calendar B
ON
    B.id = A.id
JOIN
    t_1min_pivot C
ON
    C.id = A.id
WHERE
    scalper = 1
;
DROP TABLE IF EXISTS T3;
CREATE TEMPORARY TABLE T3
SELECT
    A.id AS ID3,
    dt AS DT3,
    YEARWEEK(dt) AS YRWK3,
    `open` AS O3,
    `close` AS C3,
    scalper AS S3
FROM
    t_1min A
JOIN
    t_calendar B
ON
    B.id = A.id
JOIN
    t_1min_pivot C
ON
    C.id = A.id
WHERE
    scalper = 3
;

CREATE INDEX T1ID ON T1(ID1);
CREATE INDEX T3ID ON T3(ID3);

                SELECT
                    ID3,
                    O3,
                    (

                                    SELECT
                                        MIN(ID1)
                                    FROM
                                        T1
                                    WHERE
                                        ID1 > ID3
                                ) MINID1
                FROM
                    (
                        SELECT
                            *
                        FROM
                            T3
                    ) S1T3
LIMIT 100
;

I don't think this is the most efficient way to do this, and I can't figure out how to pull the C1 and YRWK values in to achieve the final AVG calculation. I don't think a self-join would work as if you pre-filter the S=1 and S=3 into separate tables, there's no common ID to join them together on.

INSERT CODE FOR DATA:

INSERT INTO `t_1min` (`ID`, `DT`, `YRWK`, `O`, `C`, `S`) VALUES (124, '2005-09-06 18:16:00', 200536, 1138.50, 1138.50, 3);
INSERT INTO `t_1min` (`ID`, `DT`, `YRWK`, `O`, `C`, `S`) VALUES (216, '2005-09-06 21:25:00', 200536, 1137.75, 1138.00, 3);
INSERT INTO `t_1min` (`ID`, `DT`, `YRWK`, `O`, `C`, `S`) VALUES (230, '2005-09-06 21:53:00', 200536, 1137.50, 1137.50, 1);
INSERT INTO `t_1min` (`ID`, `DT`, `YRWK`, `O`, `C`, `S`) VALUES (290, '2005-09-07 00:20:00', 200536, 1138.25, 1138.50, 3);
INSERT INTO `t_1min` (`ID`, `DT`, `YRWK`, `O`, `C`, `S`) VALUES (299, '2005-09-07 00:32:00', 200536, 1138.25, 1138.25, 1);
INSERT INTO `t_1min` (`ID`, `DT`, `YRWK`, `O`, `C`, `S`) VALUES (315, '2005-09-07 01:02:00', 200536, 1138.50, 1138.50, 3);
INSERT INTO `t_1min` (`ID`, `DT`, `YRWK`, `O`, `C`, `S`) VALUES (318, '2005-09-07 01:05:00', 200536, 1138.50, 1138.25, 1);
INSERT INTO `t_1min` (`ID`, `DT`, `YRWK`, `O`, `C`, `S`) VALUES (320, '2005-09-07 01:07:00', 200536, 1138.00, 1137.75, 1);
INSERT INTO `t_1min` (`ID`, `DT`, `YRWK`, `O`, `C`, `S`) VALUES (322, '2005-09-07 01:09:00', 200536, 1138.00, 1137.75, 1);
INSERT INTO `t_1min` (`ID`, `DT`, `YRWK`, `O`, `C`, `S`) VALUES (325, '2005-09-07 01:12:00', 200536, 1138.25, 1138.25, 3);
mysql
1 Answers

If your version of MySQL supports common table expressions, then:

with cte as (
    select
          (select id from t_1min AS cls where cls.dt > opn.dt and cls.s = 1 and opn.s = 3 order by cls.dt limit 1) c_id
         , opn.id as o_id
    from t_1min AS opn
)

which produces the wanted id pairs, and from that

with cte as (
    select
          (select id from t_1min AS cls where cls.dt > opn.dt and cls.s = 1 and opn.s = 3 order by cls.dt limit 1) c_id
         , opn.id as o_id
    from t_1min AS opn
)

select
       cte.c_id,cte.o_id, clv.c, opv.o
     , date_format(clv.dt,'%Y-%m-%d %H:%i:%s') c_dt
     , date_format(opv.dt,'%Y-%m-%d %H:%i:%s') o_dt
from cte
inner join t_1min AS clv on cte.c_id = clv.id
inner join t_1min AS opv on cte.o_id = opv.id

which uses those id pairs to get the needed open and close values, like this:

+------+------+---------+---------+---------------------+---------------------+
| c_id | o_id |    c    |    o    |        c_dt         |        o_dt         |
+------+------+---------+---------+---------------------+---------------------+
|  230 |  124 | 1137.50 | 1138.50 | 2005-09-06 21:53:00 | 2005-09-06 18:16:00 |
|  230 |  216 | 1137.50 | 1137.75 | 2005-09-06 21:53:00 | 2005-09-06 21:25:00 |
|  299 |  290 | 1138.25 | 1138.25 | 2005-09-07 00:32:00 | 2005-09-07 00:20:00 |
|  318 |  315 | 1138.25 | 1138.50 | 2005-09-07 01:05:00 | 2005-09-07 01:02:00 |
+------+------+---------+---------+---------------------+---------------------+

and, from that derived table, we can calculated the average:

with cte as (
    select
          (select id from t_1min AS cls where cls.dt > opn.dt and cls.s = 1 and opn.s = 3 order by cls.dt limit 1) c_id
         , opn.id as o_id
    from t_1min AS opn
)

select avg(c-o) avg_co
from (
    select
        cte.c_id,cte.o_id, clv.c, opv.o, clv.dt c_dt, opv.dt o_dt
    from cte
    inner join t_1min AS clv on cte.c_id = clv.id
    inner join t_1min AS opv on cte.o_id = opv.id
    ) AS d

+-----------+
|  avg_co   |
+-----------+
| -0.375000 |
+-----------+

If your version does not support cte's then just use a derived table subquery instead:

select avg(c-o) avg_co
from (
    select
        cte.c_id,cte.o_id, clv.c, opv.o, clv.dt c_dt, opv.dt o_dt
    from (
          select
                (select id from t_1min AS cls where cls.dt > opn.dt and cls.s = 1 and opn.s = 3 order by cls.dt limit 1) c_id
               , opn.id as o_id
          from t_1min AS opn
          ) as cte
    inner join t_1min AS clv on cte.c_id = clv.id
    inner join t_1min AS opv on cte.o_id = opv.id
    ) AS d

demo @ db<>fiddle here

user_20677530
Related