Cómo emular PERCENTILE_DISC en MySQL y otros RDBMS – Java, SQL y jOOQ.

En mi artículo anterior, mostré para qué se pueden usar las funciones de percentiles muy útiles (también conocidas como funciones de distribución inversa) para .

Desafortunadamente, estas funciones no están disponibles de forma ubicua en los dialectos de SQL. A partir del jOOQ 3.11, se sabe que funcionan en estos dialectos:

---

READ MORE – CLICK HERE

www.Down.co.ve

---

Dialecto	Como función agregada	Como función de la ventana
MariaDB 10.3.3	No	Sí
Oracle 18c	Sí	Sí
PostgreSQL 11	Sí	No
SQL Server 2017	No	No		No	] Oracle tiene la implementación más sofisticada, que admite tanto la función agregada del conjunto ordenado como la versión de la función de ventana: Función agregada: PERCENTILE_DISC (0.5) DENTRO DE GRUPO (ORDENAR POR x) Función de ventana: PERCENTILE_DISC (0.5) DENTRO DEL GRUPO (ORDEN POR x) OVER (PARTICIÓN POR y) Soluciones alternativas si la función no está disponible Afortunadamente, tan pronto como un RDBMS admite funciones de la ventana podemos hacerlo fácilmente emule PERCENTILE_DISC usando PERCENT_RANK y FIRST_VALUE como sigue. Estamos usando la base de datos Sakila en este ejemplo. Emulando las funciones de la ventana Vamos a emularlas primero, ya que requiere un poco menos de transformaciones de SQL. Esta consulta funciona fuera de la caja en Oracle: SELECT DISTINCT clasificación, percentile_disc (0.5) DENTRO DEL GRUPO (ORDEN POR longitud) OVER () x1, percentile_disc (0.5) DENTRO DEL GRUPO (ORDEN POR longitud) OVER (PARTICIÓN POR rating) x2 De la película ORDEN POR calificación Rendimiento CALIFICACIÓN X1 X2 ------------------- G 114 107 NC-17 114 112 PG 114 113 PG-13 114 125 R 114 115 Lo que podemos leer de esto es que la duración media de todas las películas es de 114 minutos, y la duración media de las películas por clasificación varía de 107 minutos a 125 minutos. He usado DISTINCT porque en este caso no nos importa visualizar estos valores por fila. Esto también funciona en SQL Server. Ahora, supongamos que estamos usando PostgreSQL, que no admite las funciones de la ventana de distribución inversa, o MySQL, que no admite las funciones de distribución inversa, pero ambas admiten PERCENT_RANK y FIRST_VALUE . Aquí está la consulta completa: SELECCIONAR DISTINTO clasificación, primer_valor (longitud) OVER ( ORDEN POR CASO CUANDO p1 <= 0.5 ENTONCES p1 FINALIZAN LAS NULLAS DE DESCUENTO ÚLTIMAS) x1, primer_valor (longitud) OVER ( PARTICION POR VALOR PEDIDO POR CASO CUANDO p2 <= 0.5 ENTONCES p2 FINALIZAN LAS NULLAS DE DESCUENTO ÚLTIMO) x2 DESDE ( SELECCIONAR clasificación, longitud, percent_rank () OVER (ORDER BY longitud) p1, percent_rank () OVER (PARTICIÓN POR calificación PEDIDO POR longitud) p2 De la película ) t ORDEN POR calificación Entonces, estamos haciendo esto en dos pasos (ejemplo visual más abajo): PERCENT_RANK : En una tabla derivada, estamos calculando el valor de PERCENT_RANK que atribuye a rango a cada fila ordenada por longitud, pasando de 0 a 1. Esto tiene sentido. Cuando buscamos el valor mediano, realmente estamos buscando el valor cuyo PERCENT_RANK es 0.5 o menos. Cuando buscamos el percentil del 90%, buscamos el valor cuyo PERCENT_RANK es 0.9 o menos FIRST_VALUE : una vez que encontramos el PERCENT_RANK nosotros ' no está del todo terminado todavía. Necesitamos encontrar la fila última cuya PERCENT_RANK es menor o igual al percentil en el que estamos interesados. Podría haber usado LAST_VALUE pero entonces tendría necesario recurrir al uso de la cláusula de rango bastante verbosa de las funciones de ventana. En cambio, al ordenar las filas en PERCENT_RANK (p1 o p2), traduje todos los rangos más altos que el percentil que estoy buscando en NULL usando un CASE expresión, y luego me aseguré de usar NULLS LAST que el percentil que estoy buscando será la primera fila en la especificación de la ventana de la función FIRST_VALUE . ¡Fácil! Para visualizar esto, ejecutemos estas consultas, que también proyectan los valores p1 y p2 respectivamente: SELECCIONE longitud, CASO CUANDO p1 <= 0.5 ENTONCES p1 FIN :: numérico (3,2) p1, primer_valor (longitud) OVER ( ORDEN POR CASO CUANDO p1 <= 0.5 ENTONCES p1 FINALIZAN LAS NULLAS DE DESCOSO (x) DESDE ( SELECCIONAR longitud, percent_rank () OVER (ORDER BY longitud) p1 De la película ) t ORDEN POR longitud; El resultado es longitud | p1 | x1 | ------- | ----- | ---- | 46 | 0.00 | 114 | 46 | 0.00 | 114 | 46 | 0.00 | 114 | 46 | 0.00 | 114 | 46 | 0.00 | 114 | 47 | 0.01 | 114 | ... 113 | 0,49 | 114 | 114 | 0,49 | 114 | 114 | 0,49 | 114 | 114 | 0,49 | 114 | 114 | 0,49 | 114 | 114 | 0,49 | 114 | 114 | 0,49 | 114 | 114 | 0,49 | 114 | 114 | 0,49 | 114 | 114 | 0,49 | 114 | 114 | 0,49 | 114 | <- Última fila cuyo PERCENT_RANK es <= 0.5 115 | 114 | 115 | 114 | 115 | 114 | 115 | 114 | 115 | 114 | 115 | 114 | ... 185 | 114 | 185 | 114 | 185 | 114 | Por lo tanto, la función FIRST_VALUE simplemente busca esa primera fila (de manera descendente, es decir, de abajo hacia arriba) cuyo valor p1 no es nulo. Lo mismo para p2: SELECCIONAR longitud, clasificación, CASO CUANDO p2 <= 0.5 ENTONCES p2 FIN :: numérico (3,2) p2, primer_valor (longitud) OVER ( PARTICION POR VALOR ORDEN POR CASO CUANDO p2 <= 0.5 ENTONCES p2 FINALIZAN LAS NULLAS DE DESCUENTO ÚLTIMAS) x2 DESDE ( SELECCIONAR clasificación, longitud, percent_rank () OVER (PARTICIÓN POR calificación PEDIDO POR longitud) p2 De la película ) t ORDEN POR calificación, longitud; Rendimiento: longitud | rating | p2 | x2 | ------- | ------- | ----- | ---- | 47 | G | 0.00 | 107 | 47 | G | 0.00 | 107 | 48 | G | 0.01 | 107 | 48 | G | 0.01 | 107 | ... 105 | G | 0,47 | 107 | 106 | G | 0,49 | 107 | 107 | G | 0,49 | 107 | 107 | G | 0,49 | 107 | <- Última fila en la partición G cuyo 108 | G | | 107 | PERCENT_RANK es <= 0.5 108 | G | | 107 | 109 | G | | 107 | ... 185 | G | | 107 | 185 | G | | 107 | 46 | PG | 0.00 | 113 | 47 | PG | 0.01 | 113 | 47 | PG | 0.01 | 113 | ... 111 | PG | 0.49 | 113 | 113 | PG | 0.49 | 113 | 113 | PG | 0.49 | 113 | <- Última fila en la partición PG cuyos 114 | PG | | 113 | PERCENT_RANK es <= 0.5 114 | PG | | 113 | ... ¡Perfecto! Tenga en cuenta que si su RDBMS no admite la cláusula NULLS LAST en su cláusula ORDER BY (por ejemplo, MySQL), puede que tenga la esperanza de que la clasificación por defecto NULLS LAST ] (MySQL lo hace), o puede emularlo como tal: - Este ORDEN POR x NULLS ÚLTIMO - Es lo mismo que esto. ORDEN POR CASO CUANDO x ES NULAR ENTONCES 1 OTRAS 0 FIN, X Emulando funciones agregadas Si estás usando SQL Server y quieres un comportamiento de función agregada, te recomiendo usar la función de ventana en su lugar y emular la agregación usando DISTINCT . Probablemente será más fácil que la siguiente emulación. ¡Sin embargo, verifica el rendimiento! Cuando estás usando, por ejemplo, MySQL, que no tiene soporte para la función de distribución inversa, entonces este capítulo es para usted. Aquí se explica cómo usar la versión de la función agregada en Oracle: - Sin GROUP BY SELECCIONAR percentile_disc (0.5) EN GRUPO (ORDEN POR longitud) x1 De la película - Con GROUP BY SELECCIONAR clasificación, percentile_disc (0.5) DENTRO DE GRUPO (ORDEN POR longitud) x2 De la película GRUPO POR calificación ORDEN POR calificación ¡Trivial! El resultado es el mismo que antes: X1 --- 114 CLASIFICACIÓN X2 ----------- G 107 NC-17 112 PG 113 PG-13 125 R 115 Ahora, emulemos esto en, por ejemplo, MySQL, usando funciones de ventana. - Sin GRUPO POR SELECCIONAR MAX (x1) x1 DESDE ( SELECCIONAR primer_valor (longitud) OVER ( ORDEN POR CASO CUANDO p1 <= 0.5 ENTONCES p1 FINALIZAN LAS NULLAS DE DESCOSO (x) DESDE ( SELECCIONAR longitud, percent_rank () OVER (ORDER BY longitud) p1 De la película ) t ) t; Es exactamente la misma técnica que antes, excepto que ahora tenemos que cambiar el comportamiento de la función de la ventana (no agrupar, conservar filas, repetir el valor de agregación en cada fila) de nuevo en el comportamiento de la función agregada (agrupar, colapsar filas) usando una función agregada, como MAX () . Esto es lo mismo que hice antes con DISTINCT para fines de ilustración. - Con GROUP BY SELECCIONAR clasificación, MAX (x2) x2 DESDE ( SELECCIONAR clasificación, primer_valor (longitud) OVER ( PARTICION POR VALOR ORDEN POR CASO CUANDO p2 <= 0.5 ENTONCES p2 FINALIZAN LAS NULLAS DE DESCUENTO ÚLTIMAS) x2 DESDE ( SELECCIONAR clasificación, longitud, percent_rank () OVER ( PARTICION POR VALOR ORDEN POR longitud) p2 De la película ) t ) t GRUPO POR calificación ORDEN POR calificación Todo lo que realmente estamos haciendo (de nuevo) es traducir la expresión GROUP BY a una expresión PARTICIÓN POR en la función de ventana, y luego rehacer el ejercicio anterior. Conclusión Las funciones de la ventana son extremadamente poderosas. Se pueden usar y combinar para calcular una variedad de otras agregaciones. Con el enfoque anterior, podemos calcular la función de distribución inversa PERCENTILE_DISC que no está disponible en la mayoría de RDBMS utilizando un enfoque más detallado pero igualmente poderoso que utiliza PERCENT_RANK y FIRST_VALUE en todos los RDBMS que admiten funciones de ventana. Se podría hacer un ejercicio similar con PERCENTILE_CONT con un enfoque un poco más complicado para encontrar que FIRST_VALUE que dejaré como un ejercicio para el lector. Un futuro La versión jOOQ podría emular esto para usted, automáticamente . ¿Le ha gustado este artículo? También te pueden gustar 10 trucos SQL que no creías posibles .