Coautor: Till Westmann, Director de Ingeniería
En bits para Couchbase 6.5, la próxima versión de Couchbase Server, ya están disponibles. El servicio Analytics, que soporta un eficiente procesamiento paralelo de consultas, ha añadido soporte para las funciones de ventana definidas en la directiva sql:2003 estándar.
Para muchos de nosotros (incluido yo) las funciones ventana son una característica mágica que rara vez utilizamos. Pero las funciones ventana hacen que las consultas SQL sean más concisas, mejorando así la legibilidad, lo que facilita el mantenimiento de las consultas para las aplicaciones del mundo real. De otro modo, las consultas expresadas mediante funciones ventana implicarían costosas autouniones y subconsultas difíciles de optimizar, lo que daría lugar a consultas de ejecución lenta. Voy a ilustrar el punto mediante el cálculo de un total que es el ejemplo más práctico para el uso de funciones de ventana.Hay varias maneras de calcular un total en SQL. En esta entrada del blog, voy a calcular el total de dos maneras - la vieja escuela utilizando un INNER JOIN y las nuevas funciones de ventana disponibles en Couchbase 6.5.
¿Qué es un total móvil?
A Total actual es un total que se ajusta continuamente para tener en cuenta los elementos que se van añadiendo. También se denomina suma parcial. En el ejemplo siguiente, el total actual para una fecha de transacción sería el total actual del InvoiceID más el TransactionAmount actual.
Aunque arriba he calculado el RunningTotal, tenga en cuenta que los tres primeros campos son los datos de origen y que el RunningTotal debe calcularse mediante una consulta SQL.
Requisitos previos
Descargar Servidor Couchbase 6.5 y configurar un bucket de Couchbase para los datos de ejemplo para ejecutar las consultas en esta entrada del blog.
Crea un bucket Couchbase llamado "invoices" con los datos que aparecen a continuación.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
[ { "facturas": { "InvoiceID": 1, "FechaTransacción": "2019-08-11", "ImporteTransacción": 100 } }, { "facturas": { "InvoiceID": 2, "FechaTransacción": "2019-08-11", "ImporteTransacción": 200 } }, ... ... { "facturas": { "InvoiceID": 3, "FechaTransacción": "2019-08-11", "ImporteTransacción": 800 } } ] |
En la mesa de trabajo Analytics, ejecute la siguiente sentencia para crear el conjunto de datos
|
1 2 |
CREAR DATASET facturas en facturas; CONECTAR ENLACE Local; |
Consulta Inner Join para Running total en un día determinado
Para cada InvoiceID, necesitamos recuperar el importe de la transacción y todos los importes de transacciones anteriores. Esto se logra utilizando un Inner Join con una condición para obtener cada factura y las anteriores a ella.
|
1 2 3 4 5 |
SELECCIONE inv1.InvoiceID , inv1.FechaTransacción, inv1.TransactionAmount, SUM(inv2.TransactionAmount) AS RunningTotal DESDE facturas inv1 ÚNASE A facturas inv2 EN inv1.InvoiceID >= inv2.InvoiceID DONDE inv1.FechaTransacción = "2019-08-11" GRUPO POR inv1.InvoiceID, inv1.FechaTransacción, inv1.TransactionAmount PEDIR POR inv1.InvoiceID |
JOINS y NoSQL no siempre se han llevado bien. De hecho, la sabiduría convencional es que los datos en el mundo NoSQL están desnormalizados para evitar JOINs. Pero Couchbase Analytics fue creado para la exploración de datos y el análisis ad-hoc que incluye uniones y agregaciones complejas. El motor de consulta de Analytics puede procesar JOINs que no son soportados por otros vendedores NoSQL.
- Cassandra - no admite JOIN en CQL
- MongoDB - no se pueden JOINAR colecciones sharded
En caso de que te estés preguntando cómo Couchbase Analytics puede unir datos JSON, te recomiendo meterte bajo el capó en esto presentación en vídeo con el profesor Mike Carey, Arquitecto Jefe Consultor para Couchbase.
Consulta de la función Ventana para el total de ejecución en un día determinado
Si no está familiarizado con las funciones de ventana, le recomiendo que lea Documentación de PostgresSQL que constituye una excelente introducción:
"A función de ventana realiza un cálculo a través de un conjunto de filas de la tabla que están relacionadas de alguna manera con la fila actual. Esto es comparable al tipo de cálculo que puede realizarse con una función de agregación. Pero a diferencia de las funciones de agregación normales, el uso de una función de ventana no hace que las filas se agrupen en una única fila de salida: las filas conservan sus identidades separadas. Entre bastidores, la función ventana es capaz de acceder a algo más que la fila actual del resultado de la consulta."
Este es el aspecto de la consulta real -
|
1 2 3 4 |
SELECCIONE InvoiceID, FechaTransacción, TransactionAmount, SUM(TransactionAmount) EN (PEDIR POR InvoiceID) como RunningTotal DESDE facturas DONDE FechaTransacción = "2019-08-11" PEDIR POR InvoiceID |
Además de la sencillez y concisión, hay un par de cosas interesantes en la afirmación anterior -
- Hay una función agregada pero no una cláusula group by. Normalmente, las funciones de agregación requieren una cláusula de agrupación, pero como estamos utilizando la cláusula OVER, SUM se considera una función de ventana.
- La cláusula OVER recupera un conjunto específico de filas, relativas a la fila actual, y realiza una operación sobre un campo específico.
- ORDER BY define el orden lógico en el que se evalúa la función ventana.
Nota: Couchbase soporta funciones de ventana tanto para el servicio de consultas como para el de análisis, y la misma consulta puede ejecutarse tanto en el servicio de consultas como en el de análisis. Para ejecutar en el servicio de consulta, es necesario crear los siguientes índices
|
1 2 |
CREAR PRIMARIO ÍNDICE EN facturas; CREAR ÍNDICE invoices_id EN facturas(InvoiceID); |
El propósito de esta entrada de blog era introducir funciones de ventana en Couchbase Analytics. Las funciones ventana NO son nuevas en SQL, son parte del estándar SQL y están disponibles en las principales bases de datos relacionales y herramientas de Big Data como Hive y Spark. Con la versión 6.5, Couchbase las está llevando a las bases de datos documentales. Si quieres experimentar esto de primera mano - por favor descargar los bits y participa con nosotros en foros.
Recursos
Novedades de Couchbase Server 6.5
