¿Cómo usar Group By y auto-unirse para devolver el conjunto de precios diarios mínimo, máximo, de apertura y cierre?
RESUELTO
¡Saludos, StackOverlow!
Mientras estaba fuera, la gente dejó 2 soluciones (gracias chicos, ¿cuál es el protocolo para repartir karma para dos soluciones de trabajo?)
Aquí está la solución que volví a publicar. se deriva de otra solución StackOver:
... y mi adaptación es:
SELECT
DATE_FORMAT(`DTE`, "%m/%d/%Y") AS trading_day,
MIN(`PRICE`) AS min_price,
MAX(`PRICE`) AS max_price,
SUBSTRING_INDEX(
GROUP_CONCAT(
PRICE
ORDER BY DTE ASC
)
, ',', 1 ) AS opn_price,
SUBSTRING_INDEX(
GROUP_CONCAT(
PRICE
ORDER BY DTE DESC
)
, ',', 1 ) AS cls_price
FROM `CHART_DATA`
GROUP BY trading_day
;
Los datos con los que comienza la "Q" anterior son los datos con los que estoy tratando de terminar. Con suerte, esto ayudará a alguien más ya que sospecho que mi registro es bastante común.
Estoy dispuesto a apostar que una de estas tres soluciones tiene una ventaja de rendimiento. Si alguien conoce el funcionamiento interno de MySQL y la optimización de consultas y le interesa recomendar la solución "preferida", será útil saberlo en el futuro.
END SOLUCIONADO
UPDATE # 2
Intentando llegar desde otra dirección usando esto:
http: //forums.mysql.com/read.php? 65,363723,363723
Yo obtengo
SELECT
DATE_FORMAT(`DTE`, "%m/%d/%Y") AS trading_day,
MIN(`PRICE`) AS min_price,
MAX(`PRICE`) AS max_price,
(SELECT opn_price FROM
(SELECT
DATE_FORMAT(`DTE`, "%m/%d/%Y") AS a_day,
PRICE AS opn_price,
MIN(DTE) AS opn
FROM `CHART_DATA`
GROUP BY a_day
ORDER BY opn ASC LIMIT 1) AS tblO) AS opnqt,
(SELECT cls_price FROM
(SELECT
DATE_FORMAT(`DTE`, "%m/%d/%Y") AS a_day,
PRICE AS cls_price,
MIN(DTE) AS cls
FROM `CHART_DATA`
GROUP BY a_day
ORDER BY cls DESC LIMIT 1) AS tblC) AS clsqt
FROM `CHART_DATA` cht
GROUP BY trading_day;
Esto sufre una disfunción similar a la consulta en la primera ACTUALIZACIÓN a continuación; el valor 'clsqt' (cls_price) devuelto es el último precio de cierre encontrado en los datos. Blech.
Plus, estamos comenzando a entrar nuevamente en el espacio de consulta "horriblemente complejo" y eso NO PUEDE ser bueno para el rendimiento.
Pero si alguien ve la solución para el valor 'clsqt', lo aceptaría con mucho gusto y resolvería el problema de rendimiento más adelante. :)
END ACTUALIZACIÓN # 2
ACTUALIZA
Tan cerca ... aquí es donde estoy hoy:
SELECT
DATE_FORMAT(`cht1`.`DTE`, "%m/%d/%Y") AS trading_day,
MIN(`cht1`.`PRICE`) AS min_price,
MAX(`cht1`.`PRICE`) AS max_price,
MIN(cht1.DTE) AS opn_date1,
MIN(cht1.DTE) AS opn_date2,
`cht2`.`PRICE` AS opn_price,
MAX(cht1.DTE) AS cls_date1,
MAX(cht3.DTE) AS cls_date3,
`cht3`.`PRICE` AS cls_price
FROM `CHART_DATA` cht1
LEFT JOIN `CHART_DATA` cht2
ON cht2.DTE = cht1.DTE
LEFT JOIN `CHART_DATA` cht3
ON cht3.DTE = cht1.DTE
GROUP BY trading_day
HAVING opn_date1 = opn_date2
AND cls_date1 = cls_date3
;
Esto vuelve a ejecutar todo correctamente PERO el 'cls_price' correcto (es returnign el mismo valor para 'cls_price' que 'opn_price').
in embargo, 'cls_date1' y 'cls_date3' son los valores correctos, por lo que debo estar cerca.
¿Alguien ve lo que no soy?
END UPDATE
He estado estudiando SO con respecto a las uniones izquierdas y las unidas por mi cuenta ... y debo admitir que no estoy haciendo grokking.
Encontré esta "Q" que parece muy cercana a lo que quiero:left unirse con condición para la tabla derecha en mysql
Lo que estoy tratando de hacer es recuperar los días de apertura, cierre, precio mínimo y máximo de una sola tabla (a continuación, se muestran datos de muestra).
Min y Max son fáciles:
SELECT
DATE_FORMAT(`DTE`, "%m/%d/%Y") AS trading_day,
MIN(`PRICE`) AS min_price,
MAX(`PRICE`) AS max_price
FROM `CHART_DATA`
GROUP BY trading_day;
Quiero que los resultados se devuelvan agrupados por fecha, algo así como:
'trading_day' 'opn_price' 'min_price' 'max_price' 'cls_price'
Bueno, entonces intento 'pasos de bebé' con una sola unión ...
SELECT
DATE_FORMAT(`cht1`.`DTE`, "%m/%d/%Y") AS trading_day,
MIN(`cht1`.`PRICE`) AS min_price,
MAX(`cht1`.`PRICE`) AS max_price,
`cht2`.`PRICE` AS opn_price
FROM `CHART_DATA` cht1
LEFT JOIN `CHART_DATA` cht2
ON cht2.DTE = MIN(cht1.DTE)
GROUP BY trading_day;
... y recibo el mensaje "Uso no válido de la función de grupo"
Por supuesto, eliminar el "GROUP BY" no es de ayuda, ya que necesito devolver columnas agregadas.
Tengo una solución realmente compleja que obtiene los resultados de apertura y cierre, pero no el mínimo y el máximo, y están en conjuntos de resultados separados. Tengo la sensación de que he hecho esto más complejo de lo necesario y que podría comprender lo que está sucediendo con las autouniones citadas en la "Q" mencionada anteriormente, que mi codificación general mejoraría de manera inconmensurable. Pero he pasado algo así como 12 horas en esto durante el pasado fin de semana y estoy más confiado que nunca.
Todas las ideas, explicaciones y observaciones son bienvenidas en este momento ...
/* SAMPLE TABLE AND DATA */
CREATE TABLE `CHART_DATA` (
`ID` varchar(10) DEFAULT NULL,
`DTE` datetime DEFAULT NULL,
`PRICE` double DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*Data for the table `CHART_DATA` */
INSERT INTO `chart_data`
(`id`,`dte`,`price`)
VALUES ('1','2011-01-01 00:10:00',0.65),
('2','2011-01-01 06:10:00',0.92),
('3','2011-01-01 12:10:00',0.59),
('4','2011-01-01 18:10:00',0.16),
('5','2011-01-02 00:10:00',0.28),
('6','2011-01-02 06:10:00',0.12),
('7','2011-01-02 12:10:00',0.92),
('8','2011-01-02 18:10:00',0.1),
('9','2011-01-03 00:10:00',0.34),
('10','2011-01-03 06:10:00',0.79),
('11','2011-01-03 12:10:00',1.23),
('12','2011-01-03 18:10:00',1.24),
('13','2011-01-04 00:10:00',1.12),
('14','2011-01-04 06:10:00',0.8),
('15','2011-01-04 12:10:00',0.65),
('16','2011-01-04 18:10:00',0.78),
('17','2011-01-05 00:10:00',0.65),
('18','2011-01-05 06:10:00',1.19),
('19','2011-01-05 12:10:00',0.89),
('20','2011-01-05 18:10:00',1.05),
('21','2011-01-06 00:10:00',0.29),
('22','2011-01-06 06:10:00',0.43),
('23','2011-01-06 12:10:00',0.26),
('24','2011-01-06 18:10:00',0.34),
('25','2011-01-07 00:10:00',0.22),
('26','2011-01-07 06:10:00',0.37),
('27','2011-01-07 12:10:00',1.22),
('28','2011-01-07 18:10:00',1.16),
('29','2011-01-08 00:10:00',0.3),
('30','2011-01-08 06:10:00',1.17),
('31','2011-01-08 12:10:00',0.62),
('32','2011-01-08 18:10:00',0.86),
('33','2011-01-09 00:10:00',0.84),
('34','2011-01-09 06:10:00',1.11),
('35','2011-01-09 12:10:00',0.92),
('36','2011-01-09 18:10:00',1.03),
('37','2011-01-10 00:10:00',1.13),
('38','2011-01-10 06:10:00',0.58),
('39','2011-01-10 12:10:00',1.03),
('40','2011-01-10 18:10:00',0.21),
('41','2011-01-11 00:10:00',0.12),
('42','2011-01-11 06:10:00',1.01),
('43','2011-01-11 12:10:00',0.19),
('44','2011-01-11 18:10:00',1.14),
('45','2011-01-12 00:10:00',0.55),
('46','2011-01-12 06:10:00',0.75),
('47','2011-01-12 12:10:00',0.66),
('48','2011-01-12 18:10:00',1.1),
('49','2011-01-13 00:10:00',0.68),
('50','2011-01-13 06:10:00',0.3),
('51','2011-01-13 12:10:00',0.9),
('52','2011-01-13 18:10:00',0.88),
('53','2011-01-14 00:10:00',0.64),
('54','2011-01-14 06:10:00',1.06),
('55','2011-01-14 12:10:00',1.12),
('56','2011-01-14 18:10:00',0.76),
('57','2011-01-15 00:10:00',0.18),
('58','2011-01-15 06:10:00',1.08),
('59','2011-01-15 12:10:00',0.66),
('60','2011-01-15 18:10:00',0.38),
('61','2011-01-16 00:10:00',1),
('62','2011-01-16 06:10:00',1.18),
('63','2011-01-16 12:10:00',1.15),
('64','2011-01-16 18:10:00',0.58),
('65','2011-01-17 00:10:00',1.04),
('66','2011-01-17 06:10:00',0.81),
('67','2011-01-17 12:10:00',0.35),
('68','2011-01-17 18:10:00',0.91),
('69','2011-01-18 00:10:00',0.14),
('70','2011-01-18 06:10:00',0.13),
('71','2011-01-18 12:10:00',1.03),
('72','2011-01-18 18:10:00',0.16),
('73','2011-01-19 00:10:00',1.05),
('74','2011-01-19 06:10:00',1.13),
('75','2011-01-19 12:10:00',1.21),
('76','2011-01-19 18:10:00',0.34),
('77','2011-01-20 00:10:00',0.63),
('78','2011-01-20 06:10:00',0.62),
('79','2011-01-20 12:10:00',0.19),
('80','2011-01-20 18:10:00',1.21),
('81','2011-01-21 00:10:00',0.83),
('82','2011-01-21 06:10:00',0.99),
('83','2011-01-21 12:10:00',0.83),
('84','2011-01-21 18:10:00',0.21),
('85','2011-01-22 00:10:00',0.8),
('86','2011-01-22 06:10:00',0.69),
('87','2011-01-22 12:10:00',0.87);