Eric Hill ( Eric Hill ), Desarrollador principal de sistemas JMP, SAS
NOTA: Este es solo el texto del artículo; no se incluyen las capturas de pantalla. Consulte la versión adjunta para obtener la versión completa.
Generador de consultas
La nueva herramienta JMP 12 para introducir sus datos SQL en JMP
(Actualizado para JMP 12.1)
Por Eric Hill, desarrollador de software, JMP
Contenido
- Introducción. 2
Sección 1: Establecer la conexión. 3
Sección 2: Selección de tablas para la consulta. 6
Sección 3: Construcción de la consulta. 11
Agregar columnas. 13
Guardar la consulta. 15
Ejecución de la consulta. 15
Respondiendo las preguntas. 16
- Filtración. 19
Filtros de indicaciones. 23
- Muestreo. 25
Escribiendo su propio SQL. 26
Sección 4: Gestión de consultas en segundo plano. 27
Panel de estado de consulta 28
Ventana de consultas en ejecución..28
Sección 5: Compartir consultas. 28
Sección 6: Ejecución de consultas desde el lenguaje de scripting JMP (JSL). 29
¿Qué novedades hay en JMP 12.1?. 31
- Buscar. 31
Agregar tablas relacionadas. 32
Abrir como plantilla. 34
Editar una fórmula de columna calculada. 34
Corrección de errores. 35
- Conclusión. 35
Introducción
JMP pone a su disposición una amplia gama de herramientas analíticas, pero estas no le servirán de nada si no puede obtener los datos que necesita para analizar en JMP de la forma correcta. JMP permite importar datos desde bases de datos relacionales desde hace tiempo mediante Archivo > Base de datos > Abrir tabla . Mesa abierta funciona bien en algunos aspectos, pero presenta algunas limitaciones importantes:
- La mayoría de los datos en bases de datos relacionales se almacenan de forma normalizada, lo que requiere uniones para obtener todos los datos que necesita, pero Mesa abierta no lo ayuda con las uniones; debe escribir el SQL usted mismo.
- Si bien Mesa abierta proporciona el Editor de fórmulas para crear una cláusula WHERE, no muestra, por ejemplo, los distintos valores de una variable categórica para ayudarlo a filtrarla.
- Puede resultar difícil compartir de forma segura sus consultas con otros, porque Mesa abierta almacena su contraseña en la cadena de conexión de los scripts JSL que genera.
- Mesa abierta no le ayuda con los pasos de posprocesamiento que se requieren una vez que se han importado los datos, como la limpieza de datos, los tipos de modelado y el formato.
Generador de consultas fue diseñado desde cero para abordar las limitaciones de Mesa abierta , ayudándole a crear consultas de múltiples tablas con filtrado interactivo solicitado que se puede compartir de forma segura con compañeros de trabajo.
En este artículo, utilizaré Generador de consultas con una base de datos PostgreSQL de ejemplo gratuita llamada Dellstore2 . PostgreSQL es una base de datos de código abierto que se puede descargar gratuitamente desde aquí:
http://www.postgresql.org/download/
La base de datos de muestra de Dellstore2 se puede encontrar aquí:
http://wiki.postgresql.org/wiki/Sample_Databases
La base de datos de muestra Dellstore2 también está disponible para otras plataformas de bases de datos (MySQL, Oracle y SQL Server).
Sección 1: Establecer la conexión
Inicie el Generador de consultas utilizando Archivo > Base de datos > Generador de consultas desde el menú:
También puedes iniciarlo desde la primera página de Arranque JMP :
Por último, en Windows, también puedes encontrar una herramienta Generador de consultas en la barra de herramientas de la ventana de Inicio :
Después de iniciar Generador de consultas , aparecerá el cuadro de diálogo Seleccionar conexión de base de datos :
El cuadro de diálogo Seleccionar conexión de base de datos es un subconjunto de la ventana Mesa abierta . El Generador de consultas y la Mesa abierta utilizan las mismas conexiones ODBC y, de hecho, las comparten, por lo que cualquier conexión que realice en cualquiera de los dos se mostrará en ambos.
Haga clic en "Nueva conexión..." para conectarse a su base de datos usando un DSN existente o para crear uno nuevo. Se le solicitarán las credenciales necesarias:
Después de conectarse, JMP recuperará los esquemas de la conexión para ayudarlo a asegurarse de que esta es la conexión que desea:
También puede ver las tablas disponibles en el esquema seleccionado. Haga clic en Siguiente para continuar.
Novedades en JMP 12.2: Observe los dos cuadros de búsqueda en el panel Esquemas – Tablas . Son nuevos en la actualización de JMP 12.1 y filtran mientras escribe en las listas Esquemas y Tablas . Sabemos que muchos de ustedes tienen cientos de esquemas y tablas en un esquema determinado, por lo que los nuevos cuadros de búsqueda pueden ayudarles a refinar la lista para que encuentren lo que buscan.
Sección 2: Selección de tablas para la consulta
Figura 1: Selección de tablas para la consulta
Al hacer clic en Próximo , aparecerá el cuadro de diálogo "Seleccionar tablas" . En este cuadro, seleccione las tablas que desea incluir en la consulta (consulte la Figura 1 ). Seleccione las tablas de la lista "Tablas disponibles" que desee incluir en la consulta y añádalas a las listas "Tablas principales" y "Tablas secundarias" en la esquina superior derecha.
- La tabla principal de una consulta suele ser la tabla de la que se deben recuperar todas las filas, independientemente de si coinciden con otra tabla. Esta es la tabla de hechos , en términos de almacenamiento de datos. Solo puede haber una tabla principal en una consulta.
- Las tablas secundarias suelen ser tablas de consulta. De forma predeterminada, las filas de las tablas secundarias solo se incluyen en la consulta cuando el valor de una columna de la tabla principal coincide con el valor de una columna específica de la tabla secundaria . Estas son las tablas de dimensión , en términos de almacenamiento de datos. Puede agregar tantas tablas secundarias a la consulta como desee.
El panel inferior derecho muestra información sobre la tabla seleccionada. La pestaña Columnas muestra información sobre cada columna de la tabla, incluyendo si es una clave de algún tipo:
Novedades en JMP 12.2:Observe el cuadro de búsqueda en el panel "Mesas disponibles" , nuevo en JMP 12.1. Úselo para encontrar la tabla que busca cuando sus esquemas contengan muchas tablas.
El panel Instantánea de tabla muestra una instantánea de los datos para que pueda asegurarse de que la tabla tenga la información que necesita:
Normalmente, al crear una consulta, se tiene en mente una o varias preguntas que se espera responder, y estas preguntas determinan las tablas que debe incluir la consulta. Los datos que estamos explorando provienen de una empresa ficticia de alquiler de DVD. Las preguntas que me gustaría responder son:
- ¿Qué géneros cinematográficos generan más ingresos?
- ¿Qué actores generan más ingresos?
La tabla de alquileres contiene un registro de cada evento de alquiler de DVD a lo largo de la vida de esta empresa, por lo que será la tabla principal de esta consulta. En el panel "Columnas" , puedo ver que la tabla de alquileres tiene columnas con relaciones de clave externa con las tablas de inventario y clientes :
Figura 2: Claves externas
Una relación de clave externa (también conocida como restricción de clave externa) implica que los valores de la columna de una tabla deben encontrarse en la columna de clave principal de otra tabla. En la Figura 2 , se observa que los valores de la columna id_de_inventario de la tabla alquileres deben existir en la columna de clave principal de la tabla inventario , los valores de la columna ID del cliente de la tabla alquileres deben existir en la columna de clave principal de la tabla cliente , y los valores de la columna id_del_personal de la tabla rentals solo existen en la columna de clave principal de la tabla personal . Para esta consulta, busco más información sobre la película alquilada, en lugar del cliente del miembro del personal involucrado en el alquiler, por lo que añadiré la tabla inventario a la consulta como tabla secundaria.
Al observar el panel Columnas de la tabla de inventario , puedo ver además una relación de clave externa con la tabla de películas :
Figura 3: Detalles de la tabla de inventario
La tabla "película" también se incluye en la consulta. Al revisar los detalles de la tabla , veo una columna "rental_rate" , que debería ser la necesaria para calcular los ingresos, pero no veo información sobre la categoría de la película ni sus actores.
Figura 4: Columnas en la tabla de películas
Sin embargo, al consultar la lista de Mesas disponibles , veo una tabla actor de cine y una tabla categoría de película , que parecen asignar el ID de una película a sus actores y categoría, así como tablas de actores y categorías que contienen más información sobre los actores y categorías. Por lo tanto, también quiero incluir estas tablas en la consulta. Sin embargo, si simplemente añado esas cuatro tablas, parece que algo falla:
Figura 5: Unión automática fallida
Los iconos de "señal de stop" con una "X" en el centro indican que JMP no pudo unir automáticamente esas dos tablas, por lo que tendré que configurar las uniones manualmente. Si la función de unión automática de JMP no suele funcionar correctamente, puede desactivarla desde el triángulo rojo junto a "Seleccionar tablas para consulta" .
Si selecciono público.actor_de_cine (t14) y hago clic en el botón Editar unión ( ), aparece el cuadro de diálogo Editar condición , que me permite configurar la unión. Inicialmente, el cuadro de diálogo se ve así:
Figura 6: Edición de la condición de unión
Dado que aún no existe una condición de unión, quiero unir actor de cine.id de película a inventario.film_id . Para ello, simplemente selecciono la tabla inventario en la lista de la columna izquierda y selecciono id de la película en ambas listas:
Figura 7: Edición de la condición de unión
De forma similar, puedo establecer la condición de unión para categoría pública de películas como inventario.film_id = categoría_película.film_id . Después de hacerlo, todo se ve mejor:
Figura 8: Las uniones entre tablas son válidas
Todas las tablas secundarias muestran el icono de diagrama de Venn de unión externa izquierda . Usando como ejemplo la unión que acabamos de crear entre actor de cine e inventario , una unión externa izquierda implica que todas las filas de la tabla inventario (la tabla izquierda en este caso) se incluirán en la consulta, independientemente de si hay una coincidencia en la tabla actor de cine , pero solo se incluirán las filas de la tabla film_actor que coincidan con una fila de la tabla inventory .
Por cierto, los alias de tabla t1 , t2 , t3 , etc., entre paréntesis después de los nombres de las tablas, son los alias que JMP asigna a las tablas al agregarlas a la consulta. Los alias de tabla facilitan la generación de SQL correcto y hacen que el SQL generado sea más legible. Puede cambiar el alias de una tabla haciendo clic con el botón derecho en la tabla en la lista de tablas Primarias o Secundarias y seleccionando "Cambiar alias" . Los alias de tabla no pueden contener espacios ni caracteres especiales.
Si necesita algo distinto a la unión externa izquierda, puede cambiar el tipo de unión mediante las casillas de verificación del cuadro de diálogo Editar Unirse . El tipo de unión y el icono del diagrama de Venn cambiarán según el estado de las casillas de verificación.
Figura 9: Cambiar el tipo de unión
Para comprobar si las uniones están configuradas correctamente, puede hacer clic en el botón " Vista previa de unión" . Esto ejecutará la consulta y mostrará una instantánea de los primeros cientos de filas de datos para que pueda comprobar que todo está correcto.
Ahora que tenemos todas las tablas en la consulta que necesitamos, podemos hacer clic en Generar consulta para que aparezca el propio Generador de consultas , donde podemos especificar qué columnas incluir en la consulta, criterios de filtro a utilizar, cómo se deben ordenar los datos resultantes y mucho más.
Sección 3: Construcción de la consulta
Cuando aparece inicialmente la ventana del Generador de consultas para esta consulta, se ve así:
- El cuadro "Nombre de la consulta" contiene el nombre de la consulta, que por defecto es el nombre de la tabla principal. El nombre de la consulta es el nombre asignado a la tabla de datos JMP resultante de la ejecución de la consulta y también es el nombre de archivo predeterminado al guardar la consulta. La fuente de datos muestra el nombre de la fuente de datos que utiliza la consulta.
- El cuadro Tablas muestra las tablas que se encuentran actualmente en la consulta. Para agregar o eliminar tablas o modificar uniones, haga clic en el botón Cambiar…
- La lista "Columnas disponibles" muestra todas las columnas de todas las tablas de la consulta. Seleccione columnas de esta lista y arrástrelas a los paneles "Columnas incluidas" , "Filtros " u "Ordenar por" . Si lo prefiere, puede hacer clic con el botón derecho para realizar la misma acción.
Novedad en JMP 12.1: El panel Columnas Disponibles ahora incluye un cuadro de Búsqueda para encontrar una columna específica en una lista larga. Al igual que los demás cuadros de Búsqueda , filtra a medida que escribe. - El panel "Columnas incluidas" muestra una cuadrícula con todas las columnas de su consulta. Úsela para configurar aspectos como el formato y el tipo de modelado que deben tener los datos de la columna tras importarse a JMP. También puede especificar agregaciones y Agrupar por para las columnas si desea que los datos se resuman antes de que se importen a JMP.
También hay una pestaña Muestra en este panel para generar muestras aleatorias de datos en el servidor, para aquellas bases de datos que admiten el muestreo. - La pestaña Vista previa de la consulta en esta área se actualiza automáticamente para mostrar una instantánea de los datos que devolverá la consulta al ejecutarla. La pestaña SQL muestra el SQL que el Generador de consultas genera según las selecciones actuales. La pestaña Script posterior a la consulta permite especificar un script JSL que se ejecutará una vez completada la consulta. La pestaña Estado de la consulta muestra el estado de las consultas que se ejecutan en segundo plano y permite cancelarlas.
- El panel Filtros le permite especificar criterios para filtrar los datos (creando la cláusula WHERE) y convertir el filtro en un mensaje que se ejecuta cada vez que ejecuta la consulta.
- El panel Ordenar por le permite especificar la columna o columnas que se utilizarán para ordenar las filas devueltas (cláusula ORDER BY).
Agregar columnas
Toda consulta necesita columnas. Añada columnas a la consulta mediante cualquiera de estos métodos:
- Haga clic en el botón Agregar todo para agregar todas las columnas disponibles de todas las tablas en la consulta
- Seleccione columnas específicas de la lista Columnas disponibles y haga clic en Agregar o arrástrelas.
- Haga doble clic en una columna en la lista Columnas disponibles .
Para esta consulta, agregaré id de la película , título y tarifa de alquiler de la tabla película , id de categoría y nombre de la tabla categoría , y id del actor , nombre de pila y apellido de la tabla actor .
Figura 10: Después de agregar columnas inicialmente
Al utilizar las columnas Nombre JMP, Tipo de modelado y Formato de las Columnas incluidas , puedo controlar cuál será el nombre, el tipo de modelado y el formato de cada columna después de que se importen los datos a JMP.
Figura 11: Después de ajustar los detalles de la columna
Para este análisis, preferiría que los nombres de los actores se concatenaran en una sola columna, en lugar de dividirse en columnas separadas de nombre de pila y apellido . Para ello, puedo agregar una columna calculada seleccionando "Agregar columna calculada" en el menú del triángulo rojo del panel " Columnas disponibles ".
Esto agregará una columna llamada Calc1 a la lista de Columnas disponibles , pero puedo hacer clic derecho en ella y usar "Renombrar columna" para renombrarla a Nombre del actor , agregarla a la consulta y eliminar "nombre de pila" y "apellido" . Así se ve la vista previa de la consulta después de hacer esto:
Figura 12: Vista previa de la consulta con columna calculada
(Esos valores de nombre de actor pueden parecer un poco extraños, pero de hecho son los nombres correctos de la base de datos. Los nombres de los actores parecen haber sido generados al emparejar aleatoriamente un nombre con un apellido).
Guardar la consulta
En este punto, he dedicado suficiente tiempo a esta consulta y me gustaría guardarla para no perder mi trabajo. Simplemente haga clic en el botón Guardar en la esquina inferior derecha de la ventana del Generador de consultas para guardar la consulta. Se le pedirá que proporcione un nombre de archivo; el texto del cuadro "Nombre de la consulta" se proporciona por defecto. Llamaré a la consulta categoría de película_actor . Se mostrará un mensaje en la esquina inferior izquierda de la ventana del Generador de consultas confirmando que la consulta se ha guardado:
Al guardar una consulta, se crea un archivo con la extensión .jmpquery . De vuelta en la ventana del Generador de consultas , observará que el nombre de la consulta se ha modificado para que coincida con el nombre del archivo, por lo que ahora es categoría de película_actor .
Si necesita crear otra consulta similar a la que tiene abierta, pero desea conservar el original, puede utilizar el botón Guardar como… para guardar una copia con un nombre diferente.
Ejecución de la consulta
También puedo ejecutar la consulta en este punto para crear una tabla de datos JMP que contenga todas las filas de la base de datos. Haga clic en el botón Ejecutar consulta en la parte inferior de la ventana del Generador de consultas para ejecutar la consulta. De forma predeterminada, JMP ejecuta consultas en segundo plano para que pueda seguir trabajando mientras se ejecutan. Utilice la pestaña Estado de la consulta en la sección central inferior de la ventana para ver el estado de las consultas que aún están en proceso desde esta ventana del Generador de consultas , o utilice Ver > Consultas en ejecución para ver el estado de todas las consultas que se están ejecutando en segundo plano. Esta consulta en particular tarda solo uno o dos segundos y devuelve 88.020 filas:
Figura 13: Resultados de la consulta en la tabla de datos de JMP
Tenga en cuenta que la tabla de datos JMP resultante tiene 3 scripts y una variable de tabla SQL definida:
Respondiendo las preguntas
En este punto, estoy cerca de poder responder las preguntas originales que buscaba, pero debo tomar una decisión: ¿Quiero que la base de datos resuma los resultados o que importe los datos sin procesar a JMP y que JMP haga el trabajo? Si los datos sin procesar no son demasiado grandes para caber en la memoria y su importación no requiere un tiempo prohibitivo, importarlos a JMP y realizar el análisis ofrece muchas ventajas: JMP es mucho más capaz de analizar datos que una base de datos relacional.
Para la tarea en cuestión, es sencillo usar la plataforma de distribución en Nombre de categoría y Nombre de actor , utilizando Tarifa de alquiler como variable Frecuencia . Si ordeno los histogramas resultantes por Conteo ascendente , obtengo un resultado que responde a ambas preguntas:
Figura 14: Uso de la plataforma de distribución en el resultado de la consulta
Sin embargo, si incorporar todos los datos sin procesar resultara prohibitivo en cuanto a tamaño o tiempo transcurrido, puedo usar la columna Agregación del panel Columnas incluidas para que la base de datos resuma los datos. Para ello, tendré que responder a las dos preguntas una por una.
To answer the question of ¿Qué géneros cinematográficos generan más ingresos?, I can remove the columns having to do with actor information from the query and ask the database to sum Tarifa de alquiler grouping by Nombre de la categoría, and then I can order the result by the summed Tarifa de alquiler. Here are the steps:
- Haga clic en la columna Agregación de Tarifa de alquiler y seleccione Suma .
Observe que la opción "Agrupar por" se activa para todas las variables que no tienen una agregación especificada. Si una columna de una consulta especifica una agregación, todas las columnas de la consulta que no la especifican deben ser columnas "Agrupar por ". - Right-click on the Nombre de la variable column for Tarifa de alquiler and select Ordenar por.
- Seleccione SUMA(t4.tasa_de_alquiler) en el panel Ordenar por y haga clic en el botón de flecha que apunta hacia abajo ( Descendente ).
El resultado se ve así:
De manera similar, al sacar las columnas de categorías y volver a colocar las columnas de actores, puedo responder a la pregunta de ¿Qué actores generan más ingresos?:
Filtración
Retrocedamos un momento al estado en que nuestra consulta recuperaba todos los datos sin procesar y usábamos la plataforma Distribución para responder a nuestras preguntas sobre ingresos. Una opción al realizar el análisis en JMP es solicitar el script para el análisis de JMP:
I can then paste that script into the Script posterior a la consulta panel of Generador de consultas:
Figura 15: Agregar un script JSL posterior a la consulta
Ese script ahora se ejecutará cada vez que ejecute la consulta.
Ahora, me gustaría refinar mis preguntas un poco. En lugar de analizar los ingresos por categoría y actor de todos los alquileres en la base de datos, quizá prefiera analizarlos según el país donde se alquiló la película, la tienda donde se alquiló o un rango de fechas específico.
La tabla "alquiler" tiene los campos "rental_date" e "ID del cliente" , así que estoy cerca de conseguir lo que necesito. Vimos antes que "ID del cliente" es una referencia de clave externa a la tabla "cliente" . De esta tabla, podemos obtener el país de origen del cliente y la tienda donde se alquiló el DVD.
Si hago clic en el botón "Cambiar…" del panel Tablas del Generador de Consultas, puedo volver atrás y agregar más tablas a la consulta. Para obtener información sobre el país, necesito agregar las tablas "cliente" , "dirección" , "ciudad" y "país" . Afortunadamente, gracias a las relaciones clave que el administrador de esta base de datos ha configurado, todas las tablas se unen automáticamente correctamente.
Tras agregar las tablas adicionales necesarias, de vuelta en el Generador de consultas, puedo añadir las columnas que necesito a la consulta: fecha de alquiler , ID de la tienda y país . También he configurado nombres JMP fáciles de usar y corregido el tipo de modelado de ID de la tienda .
Figura 16: Después de agregar las columnas Fecha de alquiler, ID de tienda y País
Primero, filtremos por país. Puedo hacer clic derecho en la variable t7.país en el panel "Columnas incluidas" y seleccionar "Filtrar por ". Después, aparece un filtro por país en el panel "Filtros" :
Figura 17: Filtro de país
En este punto, puedo simplemente desplazarme hacia abajo por la lista de países y marcar los que me interesan, y luego ejecutar la consulta, y solo se incluirán las filas de esos países.
De manera similar, puedo agregar filtros para ID de la tienda y fecha de alquiler , después de lo cual el panel Filtros se ve así:
Al añadir el filtro de fecha de alquiler , al ser una variable continua, obtengo un filtro de Comparación Simple por defecto. En su lugar, me gustaría un filtro de Rango . Usando el menú triangular rojo junto a t1.fecha_de_alquiler , puedo cambiar el tipo de filtro a Rango :
Ahora el filtro para t1.fecha_de_alquiler se ve así:
Permitiéndome especificar un rango de fechas. Por lo tanto, si quiero encontrar todos los alquileres del segundo semestre de 2005, puedo hacer lo siguiente:
Figura 18: Filtro de rango de fechas
Todos los tipos de filtro del Generador de consultas permiten desactivarlos eficazmente, lo que permite añadir varios filtros a una consulta, aunque no todos estén activos. Para los filtros categóricos que muestran una lista de valores, al seleccionar la opción "Todos" en la parte superior, se desactiva el filtro. Para los filtros que solicitan valores numéricos, al configurar el valor como " falta" (.) , se desactiva el filtro (o parte del mismo). Por ejemplo, si quiero todos los alquileres desde el 1 de enero de 2006 en adelante, puedo hacer lo siguiente:
Ahora el panel Filtros de mi consulta se ve como la Figura 19 a continuación.
| Figura 19: Uso de "Filtro faltante" para desactivarlo | Figura 20: Filtro con dos grupos OR |
Con las selecciones de filtro que se muestran en la Figura 19 , al ejecutar la consulta ahora se obtendrán las filas donde el país del cliente sea Francia o Alemania, y el DVD se alquiló en la tienda 2 el 1 de enero de 2006 o después. De forma predeterminada, se aplican los filtros AND a varios filtros, por lo que una fila debe coincidir con todos los filtros para aparecer en el resultado. Si necesita un filtro OR, haga clic en el botón O , lo que creará otro grupo de filtros y ambos se combinarán. Por ejemplo, el filtro que se muestra en la Figura 20 anterior devuelve los alquileres cuya categoría sea Drama o el título de la película contenga "Bucket" .
Filtros de indicaciones
Si bien es útil poder especificar criterios de filtro en el Generador de consultas , también puede crear una consulta para que la usen otros usuarios que no necesiten acceder a toda la interfaz del Generador de consultas , pero que deseen que puedan introducir diferentes criterios de filtro cada vez que ejecuten la consulta. La forma más sencilla de lograrlo es seleccionar "Todos los avisos al ejecutar" en el menú triangular rojo junto a "Filtros" .
Esto convierte todos los filtros de la consulta en filtros de solicitud . Si solo desea que algunos filtros soliciten y otros no, puede activar o desactivar la solicitud para cada filtro individual mediante el menú del triángulo rojo.
Después de configurar Todo aviso al ejecutar , cuando ejecuto esta consulta, se me presenta un mensaje para configurar los valores de filtro de la manera que quiero:
La configuración predeterminada para la solicitud será la que se haya seleccionado en el Generador de consultas (o en el archivo de consulta guardado si la consulta se ejecuta directamente desde el archivo) antes de ejecutar la consulta.
También puede personalizar las etiquetas utilizadas en la solicitud. Por ejemplo, supongamos que en lugar de " País: ", desea que la solicitud diga " País del cliente: ". De vuelta en el Generador de consultas, usando el menú triangular rojo del filtro t7.país , seleccione "Configurar solicitud" . Se abrirá un cuadro de diálogo que le permitirá cambiar la etiqueta de la solicitud y la tabla que proporciona los niveles de categoría de la solicitud:
Muestreo
Si su base de datos admite el muestreo aleatorio, la pestaña Muestra , en la sección central superior del Generador de consultas, mostrará las opciones de muestreo compatibles. He utilizado PostgreSQL para los ejemplos de este artículo, que admite el muestreo aleatorio simple de un número específico de filas:
SQL Server y Oracle admiten diferentes opciones de muestreo, por lo que la pestaña Muestra reflejará las diferentes opciones:
| Figura 21: Opciones de muestreo de SQL Server | Figura 22: Opciones de muestreo de Oracle |
El muestreo lo realiza la base de datos antes de que los datos se descarguen a JMP, por lo que es una buena forma de limitar la cantidad de filas recuperadas de consultas que, de otro modo, recuperarían una cantidad prohibitivamente grande de filas.
Escribiendo su propio SQL
Reconocemos que, a pesar de las numerosas funciones que Generador de consultas tiene ahora o podría tener en el futuro, en ocasiones podría necesitar una consulta que realice funciones que Generador de consultas no admite directamente. Además, es posible que ya haya escrito y depurado minuciosamente SQL que realiza exactamente la consulta que necesita que JMP realice. Generador de consultas ofrece dos opciones para proporcionar su propio SQL para una consulta:
- Expresión de filtro personalizada : si Generador de consultas está haciendo lo que necesita en cuanto a seleccionar tablas y especificar las columnas a incluir, pero solo desea tener una expresión SQL personalizada incluida como parte del filtro, puede seleccionar Agregar expresión personalizada en el menú de triángulo rojo de Filtros :
| Figura 23: Agregar una expresión de filtro personalizada | Figura 24: El filtro resultante |
Ingrese cualquier SQL que desee en el cuadro Expresión personalizada , siempre que sea SQL válido para la consulta.
Si un filtro de expresión personalizada se configura para que sea un filtro de solicitud, la expresión personalizada aparecerá en el cuadro de diálogo de solicitud de filtro y se podrá editar.
- Convertir la consulta completa en una consulta SQL personalizada : si solo desea escribir su propio SQL para una consulta completa, entonces, después de iniciar el Generador de consultas y especificar la conexión de datos, puede usar el menú de triángulo rojo en la parte superior izquierda del Generador de consultas junto al campo Nombre de la consulta y seleccionar Convertir a SQL personalizado :
Figura 25: Convertir a SQL personalizado
La ventana "Generador de consultas" cambiará al modo SQL personalizado, lo que le brindará un amplio espacio para ingresar su propio SQL. Si comenzó a generar la consulta interactivamente, el cuadro "SQL personalizado" se inicializará con el SQL generado según las selecciones realizadas hasta el momento (consulte la Figura 26 a continuación).
Figura 26: Consulta SQL personalizada
Puede editar el código SQL según sea necesario o pegarlo desde otra fuente. Luego, haga clic en el botón Actualizar para actualizar la Vista previa de la consulta . Advertencias:
- No es posible realizar un filtrado por indicaciones con una consulta SQL personalizada.
- Una vez que convierta una consulta a SQL personalizado y edite el SQL, si la revierte a interactiva, perderá cualquier personalización realizada. El Generador de consultas no intenta interpretar el SQL.
Sección 4: Gestión de consultas en segundo plano
El Generador de consultas ejecuta consultas en segundo plano de forma predeterminada. También es posible ejecutar consultas en segundo plano desde el lenguaje de scripting JMP (JSL) (véase la Sección 6 ). JMP ofrece dos maneras de ver la lista de consultas en segundo plano en ejecución y detenerlas si es necesario.
Panel de estado de consulta
En la sección central inferior del Generador de consultas, se encuentra la pestaña Estado de la consulta . Todas las consultas en segundo plano iniciadas desde esa ventana del Generador de consultas aparecerán en esa pestaña:
Si desea detener una consulta en ejecución, simplemente selecciónela en la lista y haga clic en el botón Detener . Si se han recuperado algunas filas, JMP abrirá una tabla de datos que contiene las filas ya recuperadas. El nombre de la tabla de datos indicará que el resultado es parcial.
Ventana de consultas en ejecución
En el menú Ver de la ventana de inicio o de otra ventana de JMP que incluya un menú, seleccione Consultas en ejecución . Se abrirá una ventana similar a la pestaña Estado de la consulta del Generador de consultas , pero la ventana Consultas en ejecución contendrá las consultas de tareas pendientes que se ejecutan en segundo plano, independientemente de cómo se hayan invocado (una ventana del Generador de consultas o un lenguaje de scripting JMP).
Aquí nuevamente, seleccione una consulta y haga clic en Detener para detener su ejecución y abrir cualquier resultado parcial.
Sección 5: Compartir consultas
En la introducción, se mencionó que Mesa Abierta creaba scripts que eran difíciles de compartir, ya que la contraseña de la base de datos estaba incluida en la cadena de conexión. El Generador de consultas funciona de forma diferente. Si guarda un archivo .jmpquery para una consulta cuya conexión requería una contraseña y lo abre en un editor de texto, verá algo similar a esto:
Puede ver que el nombre de usuario ( UID ) está presente en la cadena de conexión, pero luego aparece PWD=%_PWD_% . “%_PWD_%” no es la contraseña, sino un marcador de posición que el Generador de consultas agregó. Cuando el Generador de consultas detecta este marcador, sabe que originalmente se requería una contraseña. Por lo tanto, si la consulta se abre cuando el usuario no tiene conexión con el DSN dvdrental de Postgres , el Generador de consultas le solicitará la contraseña.
¿Y qué pasa con el DSN? ¿Qué sucede si envías este archivo .jmpquery a un usuario de JMP que no tiene un DSN llamado Postgres dvdrental definido en su equipo? Mesa abierta no puede continuar en tal situación, pero Generador de consultas informará al usuario y le dará la oportunidad de conectarse a un DSN diferente (que presumiblemente se conecta a la misma base de datos) o crear uno nuevo.
Debido a la posibilidad de que distintos usuarios tengan distintos DSN definidos en sus equipos, algunas organizaciones han optado por conexiones sin DSN . Actualmente, Generador de consultas no puede convertir una cadena de conexión basada en DSN a una sin DSN, pero si modifica una cadena de conexión en el script de una consulta para que, en lugar de especificar un DSN , especifique un controlador y un servidor , Generador de consultas propagará esta conexión sin DSN en cualquier script o archivo .jmpquery que genere.
Sección 6: Ejecución de consultas desde el lenguaje de scripting JMP (JSL)
Las consultas generadas por el Generador de Consultas también se pueden ejecutar desde scripts del Lenguaje de Scripting JMP (JSL) . La mejor opción es guardar la consulta como un archivo .jmpquery , abrirlo desde JSL y ejecutarlo. Por ejemplo, supongamos que tiene una consulta guardada en la carpeta c:\usuarios\público\temp llamada pg_rentals.jmpquery . Aquí hay algunas cosas que puede hacer con ese archivo de consulta desde JSL:
- Abra un archivo .jmpquery en el generador de consultas:
abierto( "c:\usuarios\público\temp\pg_rentals.jmpquery" );
consulta= abierto( "c:\usuarios\público\temp\pg_rentals.jmpquery",Privado );
- Open the query in Generador de consultas for editing:
consulta<< Modificar;
consulta<< Correr();
- Ejecute la consulta en primer plano. La tabla de datos resultante se abrirá en JMP al finalizar la consulta. dtResult será una referencia a la tabla de datos resultante.
dtResult=consulta<< Ejecutar en primer plano();
- Ejecute la consulta en primer plano, pero no abra la tabla de datos resultante en JMP; manténgala privada. dtResult será una referencia a la tabla de datos resultante.
dtResult=consulta<< Ejecutar en primer plano(Privado );
- Ejecute la consulta en segundo plano. La tabla de datos resultante se abrirá en JMP al finalizar la consulta. La ejecución en segundo plano no puede devolver una referencia a la tabla de datos resultante porque la consulta se ejecuta en segundo plano. Los ejemplos siguientes muestran cómo obtener una referencia a la tabla de datos resultante al ejecutar consultas en segundo plano.
consulta<< Ejecutar fondo();
- Ejecute la consulta en segundo plano. Si la consulta se ejecuta correctamente, invoque la plataforma de distribución en la tabla de datos de resultados ( resultado de la consulta es una variable JSL especial dentro de un script "Al ejecutarse" que hace referencia a la tabla de datos de resultados). Si el usuario cancela la consulta o se produce un error, escriba un mensaje en el registro de JMP.
consulta<< Ejecutar fondo(
En ejecución completa (
resultado de la consulta<< Distribución( Distribución nominal( Columna( :título ) ) );
),
En marcha cancelada (Escribir( "\!N¡La consulta fue cancelada!" ) ),
En caso de error (Escribir( "\!NError al ejecutar la consulta!" ); )
);
- Ejecute la consulta en segundo plano. Tras completarla correctamente, invoque la función MyFunc. La tabla de datos del resultado de la consulta siempre se pasa como primer parámetro a la función.
::Mi función= Función({ dt },{ Local predeterminado },
Escribir( "\!nNúmero de alquileres: ", NRows(es ) );
dt<< Distribución( Distribución nominal( Columna( :título ) ) );
);
consulta<< Ejecutar fondo(En ejecución completa (::MiFunción ) );
Si abrir una consulta desde un archivo .jmpquery separado en su script JSL no es satisfactorio, puede obtener el script sin formato para crear un objeto de consulta programable desde el menú de triángulo rojo en la parte superior izquierda de la ventana del Generador de consultas :
Por ejemplo, al invocar Guardar secuencia de comandos de ejecución en la ventana de secuencia de comandos para una consulta simple, obtengo lo siguiente:
La nueva consulta SQL devuelve un objeto programable de consulta SQL tal como lo hace Abierto cuando abre un archivo .jmpquery y se puede utilizar de las mismas maneras.
¿Qué novedades hay en JMP 12.1?
Buscar
Muchas de las personas con las que hablamos tienen montañas de datos en cientos de esquemas, tablas y columnas en sus bases de datos. En la versión JMP 12.1 del Generador de Consultas, hemos añadido cuadros de búsqueda a las listas de esquemas, tablas y columnas para ayudarle a encontrar rápidamente lo que busca:
Todos estos cuadros de Búsqueda se actualizan a medida que escribes y tienes la opción de usar expresiones regulares para refinar tu búsqueda.
Agregar tablas relacionadas
En el cuadro de diálogo Seleccionar tablas para consulta , si una tabla que ha agregado a la consulta tiene referencias de clave externa a otras tablas, puede agregar todas esas tablas relacionadas a la consulta haciendo clic en el botón Agregar tablas relacionadas .
Abrir como plantilla
Con frecuencia, necesita crear una consulta muy similar a otra que creó y guardó en un archivo .jmpquery. Siempre puede abrir la consulta previamente guardada, modificarla y usar "Guardar como" para guardarla en otro archivo .jmpquery. Sin embargo, si accidentalmente accede a Ahorrar , su consulta original se sobrescribirá. La función "Abrir como plantilla" existe para evitar este problema. "Abrir como plantilla" está disponible en el menú contextual de la ventana de inicio para archivos .jmpquery tanto en Mac como en Windows.
Al abrir un archivo .jmpquery como plantilla, en realidad se abre una copia de la consulta original, que no tiene relación con el archivo .jmpquery en el disco. Si abrió una consulta como plantilla, realizó cambios y pulsó accidentalmente el botón Ahorrar , se le solicitará que proporcione un archivo para guardar la consulta.
También puede abrir un archivo .jmpquery como plantilla desde el lenguaje de script JMP (JSL):
abierto( "C:\Usuarios\Público\Temporal\rentals.jmpquery",Como plantilla );
Editar una fórmula de columna calculada
En JMP 12.0, se podía agregar una columna calculada a la consulta, la cual se calculaba en el servidor de base de datos y los valores resultantes se devolvían en la consulta. Sin embargo, si se necesitaba editar la fórmula de una columna calculada creada previamente, no era posible. En su lugar, se debía eliminar la columna calculada y agregar una nueva, lo que posiblemente requería recrear una fórmula compleja. En JMP 12.1, ahora se puede editar una columna calculada. Simplemente haga clic con el botón derecho en la columna en Columnas disponibles y seleccione Editar fórmula .
Corrección de errores
Como era de esperar, hemos corregido varios errores en el Generador de consultas de JMP 12.1, algunos de los cuales fueron reportados por los clientes. Si tiene algún problema con el Generador de consultas, por favor, infórmelo al Soporte técnico de SAS para que podamos solucionarlo.
Conclusión
El Generador de consultas es una nueva función de JMP 12 (actualizada en JMP 12.1) que le ayuda a crear consultas SQL multitabla altamente personalizadas en sus bases de datos relacionales. El objetivo es facilitar la introducción de sus datos en JMP listos para el análisis con mayor rapidez que antes y facilitar el intercambio seguro de sus consultas. Esperamos que la pruebe y nos envíe sus comentarios.eric.hill@jmp.com) sobre lo que funciona bien y lo que no funciona para usted.
