ultimo - seleccionar primer registro mysql

Seleccionar el primer y último valor en un grupo

Question

Tengo una tabla MySql que consiste en cotizaciones de acciones diarias (abierto, alto, bajo, cerrado y volumen) que intento convertir en datos semanales sobre la marcha. Hasta ahora, tengo la siguiente función, que funciona para los altos, bajos y volumen:

SELECT MIN(_low), MAX(_high), AVG(_volume),
CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
GROUP BY myweek
ORDER BY _date;

Necesito seleccionar la primera instancia de _open en la consulta anterior. Entonces, por ejemplo, si hubo un feriado el lunes (en una semana en particular) y el mercado de valores se abrió el martes, el valor abierto debe seleccionarse del martes que se agrupa en su semana. Del mismo modo, el valor cercano debe ser el último _close de esa semana.

¿Es posible seleccionar algo como FIRST () y LAST () en MySql para que lo anterior se pueda incluir en un único SELECT en lugar de usar consultas de selección anidadas?

Esta es la declaración de creación de mi tabla para tener una idea del esquema:

delimiter $$
CREATE TABLE `mystockdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `symbol_id` int(11) NOT NULL,
  `_open` decimal(11,2) NOT NULL,
  `_high` decimal(11,2) NOT NULL,
  `_low` decimal(11,2) NOT NULL,
  `_close` decimal(11,2) NOT NULL,
  `_volume` bigint(20) NOT NULL,
  `add_date` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Symbol_Id` (`symbol_id`,`add_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8$$

Actualización: no hay nulos, donde sea que haya un feriado / fin de semana, la tabla no lleva ningún registro para esa fecha.

Answer 1

Es probable que necesite la función COALESCE para obtener el primer valor. Sin embargo, debe asegurarse de que los días sin datos (fines de semana y días festivos) tengan un valor nulo para _open en esos días sin datos.

El uso sería:

SELECT MIN(_low), MAX(_high), AVG(_volume), COALESCE(_open)
CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
GROUP BY myweek
ORDER BY _date;

Para el último valor (), solo puedo pensar en una solución bastante hacky, que sería usar GROUP_CONCAT y luego manipulación de cadenas para obtener el último valor de la lista. Entonces quizás algo como esto:

SELECT MIN(_low), MAX(_high), AVG(_volume), COALESCE(_open), SUBSTRING_INDEX(GROUP_CONCAT(_close), '','', -1)
CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
GROUP BY myweek
ORDER BY _date;

Tenga en cuenta que también puede usar el enfoque GROUP_CONCAT para el primer elemento en lugar de fusionarse si desea una consulta de aspecto coherente.

SELECT MIN(_low), MAX(_high), AVG(_volume), SUBSTRING_INDEX(GROUP_CONCAT(_open), '','', 1), SUBSTRING_INDEX(GROUP_CONCAT(_close), '','', -1)
CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
GROUP BY myweek
ORDER BY _date;

Para que GROUP_CONCAT funcione correctamente, también debe asegurarse de que las fechas sin valores tengan nulo en los campos _open y _close .

Answer 2

MySql no tiene una función de agregación First() o Last() . Pero podría simularlos utilizando GROUP_CONCAT , que crea un conjunto de todos los valores _open y _close de la semana ordenados por _date para _open y por _date desc para _close , y extrayendo el primer elemento del conjunto:

SELECT
  MIN(_low),
  MAX(_high),
  AVG(_volume),
  CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek,
  SUBSTRING_INDEX(GROUP_CONCAT(CAST(_open AS CHAR) ORDER BY _date), '','', 1 ) as first_open,
  SUBSTRING_INDEX(GROUP_CONCAT(CAST(_close AS CHAR) ORDER BY _date DESC), '','', 1 ) as last_close
FROM mystockdata
GROUP BY myweek
ORDER BY _date;

Otra solución sería esta, que usa subconsultas con LIMIT 1 en la cláusula SELECT :

SELECT
  MIN(_low),
  MAX(_high),
  AVG(_volume),
  CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek,
  (select _open from mystockdata m where WEEK(m._date)=WEEK(mystockdata._date) order by _date LIMIT 1) as first_open,
  (select _close from mystockdata m where WEEK(m._date)=WEEK(mystockdata._date) order by _date desc LIMIT 1) as last_close
FROM mystockdata
GROUP BY myweek
ORDER BY _date;

Answer 3

Básicamente, lo que necesita hacer: 1. agrupar por PRODUCTID 2. dentro de cada grupo, ordenar por LOCALIZACIÓN 3. seleccionar el PRIMER precio para el mismo producto ordenado por UBICACIÓN Colocándolos juntos, puede usar la siguiente consulta:

SELECT PRODUCTID, 
   SUBSTRING_INDEX(GROUP_CONCAT(CAST(LOCATION AS CHAR) ORDER BY LOCATION DESC), '','', 1) AS LOCATION,
   SUBSTRING_INDEX(GROUP_CONCAT(CAST(PRICE AS CHAR) ORDER BY LOCATION DESC), '','', 1) AS PRICE
FROM ProductLocation
GROUP BY PRODUCTID;

Tenga en cuenta que MySQL no tiene las funciones agregadas FIRST () y LAST () para GROUP BY, pero que FIRST () AND LAST () se pueden simular utilizando las funciones GROUP_CONCAT () y SUBSTRING_INDEX ().