Me pregunto si hay un método para implementar funciones analíticas de SQL sin usar las funciones incorporadas.

SELECT *,
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rownum,
    DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS denserank,
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM emp;
3
Teja 20 oct. 2017 a las 22:44

3 respuestas

La mejor respuesta

Aquí están las tres expresiones equivalentes:

select emp.*,
       (select count(*)
        from emp emp2
        where emp2.dept_id = emp.dept_id and
              (emp2.salary > emp.salary or
               emp2.salary = emp.salary and emp2.emp_id <= emp.emp_id
              )
       ) as "row_number",
       (select 1 + count(*)
        from emp emp2
        where emp2.dept_id = emp.dept_id and
              emp2.salary > emp.salary 
              )
       ) as "rank",
       (select count(distinct salary)
        from emp emp2
        where emp2.dept_id = emp.dept_id and
              emp2.salary >= emp.salary
       ) as "dense_rank",
from emp;

Esto supone la existencia de un emp_id para hacer que las filas sean únicas para "row_number".

7
Gordon Linoff 21 oct. 2017 a las 19:12

Esto funcionaría para todos los casos

select DEPT_ID, SALARY,

    (select count(*)+1  from emp r where r.SALARY>o.SALARY and r.dept_id=o.dept_id) **rank**,

    (select count(distinct SALARY )+1  from emp r where r.SALARY>o.SALARY and r.dept_id=o.dept_id) *d_rank*,

    (select count(*)+1  from (select x.*,rownum rn from ( select emp.* from emp  order by DEPT_ID asc,salary desc ) x) r where r.rn<o.rn and r.dept_id=o.dept_id) **rownumm**

from (select x.*,rownum rn from ( select emp.* from emp  order by DEPT_ID asc,salary desc ) x) o 

order by DEPT_ID,salary desc;

Para rango: - calculado usando (conteo de (valores menores que las filas actuales) +1

Para rango denso: - igual que el rango (cuente un valor distinto menos que las filas actuales) +1

Row_number: crea la consulta anidada generando rownum para cada fila que será distinta para todas las filas. Ahora, además de eso, haga la misma lógica que el rango (recuento de valores superiores al rownum anterior (rownum de la subconsulta seleccionada)) + 1

0
Dale K 23 oct. 2019 a las 08:43

Puede hacer esto con una subconsulta correlacionada.

select dept_id,salary,
(select count(*) from emp e1 where e1.dept_id=e.dept_id and e1.salary>=e.salary) as rnum
from emp e

Esto funciona bien cuando no hay vínculos.

1
Vamsi Prabhala 20 oct. 2017 a las 19:48