ejemplos - funciones de agrupamiento mysql

SQL: función agregada y grupo por

Question

Considere la tabla de Oracle emp . Me gustaría obtener los empleados con el salario más alto con department = 20 y job = clerk . Supongamos también que no hay una columna "empno" y que la clave primaria implica varias columnas. Puedes hacer esto con:

select * from scott.emp
where deptno = 20 and job = ''CLERK''
and sal =  (select max(sal) from scott.emp
            where deptno = 20 and job = ''CLERK'')

Esto funciona, pero tengo que duplicar la prueba deptno = 20 y job = ''CLERK'', que me gustaría evitar. ¿Hay una forma más elegante de escribir esto, tal vez usando un group by ? Por cierto, si esto es importante, estoy usando Oracle.

Answer 1

¡Eso es genial! No sabía que podía hacer una comparación de (x, y, z) con el resultado de una instrucción SELECT. Esto funciona muy bien con Oracle.

Como nota adicional para otros lectores, a la consulta anterior le falta un "=" después de "(deptno, job, sal)". Tal vez el formateador Stack Overflow se lo comió (?).

De nuevo, gracias Mark.

Answer 2

En Oracle lo haría con una función analítica, por lo que solo consultaría la tabla emp una vez:

SELECT *
  FROM (SELECT e.*, MAX (sal) OVER () AS max_sal
          FROM scott.emp e
         WHERE deptno = 20 
           AND job = ''CLERK'')
 WHERE sal = max_sal

Es más simple, más fácil de leer y más eficiente.

Si desea modificarlo para listar esta información para todos los departamentos, entonces deberá usar la cláusula "PARTITION BY" en OVER:

SELECT *
  FROM (SELECT e.*, MAX (sal) OVER (PARTITION BY deptno) AS max_sal
          FROM scott.emp e
         WHERE job = ''CLERK'')
 WHERE sal = max_sal
ORDER BY deptno

Answer 3

En Oracle también puede usar la instrucción EXISTS, que en algunos casos es más rápida.

Por ejemplo ... SELECT nombre, número FROM cust DONDE cust IN (SELECCIONAR cust_id FROM big_table) AND ingresó> SYSDATE -1 sería lento.

pero SELECT name, number FROM cust c WHERE EXISTS (SELECCIONE cust_id FROM big_table DONDE cust_id = c.cust_id ) AND ingresó> SYSDATE -1 sería muy rápido con una indexación adecuada. También puedes usar esto con múltiples parámetros.

Answer 4

Hay muchas soluciones También podría mantener su diseño de consulta original simplemente agregando alias de tabla y uniéndose en los nombres de columna, aún así solo tendría DEPTNO = 20 y JOB = ''CLERK'' en la consulta una vez.

SELECT 
  * 
FROM 
  scott.emp emptbl
WHERE
  emptbl.DEPTNO = 20 
  AND emptbl.JOB = ''CLERK''
  AND emptbl.SAL =  
    (
      select 
        max(salmax.SAL) 
      from 
        scott.emp salmax
      where 
        salmax.DEPTNO = emptbl.DEPTNO
        AND salmax.JOB = emptbl.JOB
    )

También se puede observar que la palabra clave "ALL" se puede usar para estos tipos de consultas que le permitirán eliminar la función "MAX".

SELECT 
  * 
FROM 
  scott.emp emptbl
WHERE
  emptbl.DEPTNO = 20 
  AND emptbl.JOB = ''CLERK''
  AND emptbl.SAL >= ALL  
    (
      select 
        salmax.SAL
      from 
        scott.emp salmax
      where 
        salmax.DEPTNO = emptbl.DEPTNO
        AND salmax.JOB = emptbl.JOB
    )

Espero que eso ayude y tenga sentido.

Answer 5

Lo siguiente está ligeramente sobrediseñado, pero es un buen patrón SQL para las consultas "top x".

SELECT 
 * 
FROM 
 scott.emp
WHERE 
 (deptno,job,sal) IN
 (SELECT 
   deptno,
   job,
   max(sal) 
  FROM 
   scott.emp
  WHERE 
   deptno = 20 
   and job = ''CLERK''
  GROUP BY 
   deptno,
   job
  )

También tenga en cuenta que esto funcionará en Oracle y Postgress (creo) pero no en MS SQL. Para algo similar en MS SQL ver pregunta SQL Query para obtener el último precio

Answer 6

Si estuviera seguro de la base de datos dirigida, iría con la solución de Mark Nold, pero si alguna vez quiere un dialecto agnóstico SQL *, intente

SELECT * 
FROM scott.emp e
WHERE e.deptno = 20 
AND e.job = ''CLERK''
AND e.sal = (
  SELECT MAX(e2.sal) 
  FROM scott.emp e2
  WHERE e.deptno = e2.deptno 
  AND e.job = e2.job
)

* Creo que esto debería funcionar en todas partes, pero no tengo los entornos para probarlo.