Ejemplo de tipo de tabla Mariadb CONNECT PIVOT

Este tipo de tabla se puede utilizar para transformar el resultado de otra tabla o vista (llamada tabla de origen) en una tabla pivotada a lo largo de columnas “pivote” y “hechos”. Una tabla dinámica es una excelente herramienta de informes que ordena y suma (de forma predeterminada) independientemente del diseño de datos original en la tabla de origen.

Por ejemplo, supongamos que tiene la siguiente tabla de “Gastos”:

Girando el contenido de la tabla usando los campos ‘Quién’ y ‘Semana’ para las columnas de la izquierda, y el campo ‘Qué’ para el encabezado superior y sumando los campos ‘Cantidad’ para cada celda en la nueva tabla, se obtiene el siguiente resultado deseado:

Tenga en cuenta que SQL le permite obtener el mismo resultado presentado de manera diferente mediante el uso de la cláusula “agrupar por”, a saber:

select who, week, what,sum(amount)from expenses
       groupby who, week, what;

Sin embargo, no hay forma de obtener el diseño pivotado que se muestra arriba simplemente usando SQL. Incluso el uso de la programación de SQL incorporado para algunos DBMS no es tan simple y automático.

El tipo de tabla dinámica de CONNECT hace que esto sea mucho más sencillo.

Uso del tipo de tablas PIVOT

Para obtener el resultado que se muestra en el ejemplo anterior, simplemente defínalo como una nueva tabla con la declaración:

createtable pivex
engine=connect table_type=pivot tabname=expenses;

Ahora puede usarlo como cualquier otra tabla, por ejemplo, para mostrar el resultado que se muestra arriba, solo diga:

select*from pivex;

La implementación CONNECT del tipo de tabla PIVOT hace gran parte del trabajo necesario para transformar la tabla fuente:

1. Encontrar la columna “Hechos”, por defecto la última columna de la tabla fuente. La búsqueda de columnas “Facts” o “Pivot” funciona solo para tablas dinámicas basadas en tablas. No lo hacen para tablas dinámicas basadas en view o srcdef, para las cuales deben especificarse explícitamente.
2. Encontrar la columna “Pivote”, por defecto la última columna restante.
3. Elección de la función agregada a utilizar, “SUMA” por defecto.
4. Construyendo y ejecutando el “Agrupar por” en la columna “Hechos”, obteniendo su resultado en memoria.
5. Obtener todos los valores distintos en la columna “Pivot” y definir una columna de “Datos” para cada uno.
6. Difundir el resultado de la tabla de memoria intermedia en la tabla final.

La columna “Pivot” de la tabla de origen no debe ser anulable (no existen cosas como una columna “nula”). La creación será rechazada incluso si esta columna anulable en realidad no contiene valores nulos.

Si se desea un resultado diferente, las opciones de Crear tabla están disponibles para cambiar los valores predeterminados utilizados por Pivot. Por ejemplo, si queremos mostrar el gasto promedio de cada persona y producto, distribuidos en columnas para cada semana, use la siguiente declaración:

createtable pivex2
engine=connect table_type=pivot tabname=expenses
option_list='PivotCol=Week,Function=AVG';

Ahora diciendo:

select*from pivex2;

Mostrará la tabla resultante:

Restringir las columnas en una tabla dinámica

Supongamos que queremos una tabla dinámica de gastos que sume los gastos de todas las personas y productos, independientemente de la semana en que se compró. Podemos hacer esto simplemente eliminando de la tabla pivex la columna de la semana de la lista de columnas.

altertable pivex dropcolumn week;

El resultado que obtenemos de la nueva tabla es:

Sintaxis de creación de tabla de PIVOT

La instrucción Create Table para tablas PIVOT utiliza la siguiente sintaxis:

createtable pivot_table_name
[(column_definition)]engine=CONNECT table_type=PIVOT
 srcdef='source_table_def'
[option_list='pivot_table_option_list'];

La definición de columna tiene dos conjuntos de columnas:

1. Un conjunto de columnas que pertenecen a la tabla de origen, sin incluir las columnas “hechos” y “pivote”.
2. Columnas de “datos” que reciben los valores de las columnas de “hechos” agregadas nombradas a partir de los valores de la columna “pivote”. Se indican mediante la opción “bandera”.

los opciones y subopciones disponibles para tablas dinámicas son:

• : Estas opciones deben especificarse en OPTION_LIST.

Opciones de acceso adicionales

Hay cuatro casos en los que pivot debe llamar al servidor que contiene la tabla fuente o en los que se debe ejecutar la instrucción SrcDef:

1. La tabla de origen no es una tabla CONNECT. 2. Se especifica la opción SrcDef. 3. La tabla de origen está en otro servidor. 4. Las columnas no están especificadas.

De forma predeterminada, pivot intenta llamar al servidor que se usa actualmente usando host = localhost, user = root sin usar contraseña y port = 3306. Sin embargo, esto puede no ser lo que se necesita, en particular si el usuario root local tiene una contraseña, en cuyo caso puede recibir un mensaje de error de “acceso denegado” al crear o usar la tabla dinámica.

Especifique las opciones de host, usuario, contraseña y / o puerto en option_list para anular las opciones de conexión predeterminadas utilizadas para acceder a la tabla de origen, obtener especificaciones de columna, ejecutar el grupo generado por o la consulta SrcDef.

Definición de una tabla dinámica

Existen principalmente dos formas de definir una tabla PIVOT:

1. Desde una tabla o vista existente. 2. Dar directamente la instrucción SQL devolviendo el resultado a pivotar.

Definición de una tabla dinámica a partir de una tabla de origen

los nombre de la pestaña La opción de tabla estándar se utiliza para dar el nombre de la tabla o vista de origen.

Para las tablas, el grupo interno por se generará internamente, excepto cuando la opción GROUPBY se especifique como verdadera. Hágalo solo cuando la tabla o vista tenga un formato GRUPO POR válido.

Definir directamente la fuente de una tabla dinámica en SQL

Alternativamente, la fuente interna se puede definir directamente usando el SrcDef opción que debe tener el grupo adecuado por formato.

Como hemos visto anteriormente, una tabla dinámica adecuada se hace a partir de una tabla intermedia interna resultante de la ejecución de un GROUP BY declaración. En muchos casos, es más sencillo o deseable especificar esto directamente al crear la tabla dinámica. Esto puede deberse a que la fuente es el resultado de un proceso complejo que incluye el filtrado y / o la unión de tablas.

Para hacer esto, use el SrcDef opción, que a menudo reemplaza todas las demás opciones. Por ejemplo, suponga que en el primer ejemplo estamos solo interesado en las semanas 4 y 5. Por supuesto, podríamos mostrarlo de la siguiente manera:

select*from pivex where week in(4,5);

Sin embargo, ¿qué pasa si esta mesa es una mesa enorme? En este caso, la forma correcta de hacerlo es definir la tabla dinámica como esta:

createtable pivex4
engine=connect table_type=pivot
option_list='PivotCol=what,FncCol=amount'
SrcDef='select who, week, what, sum(amount) from expenses
where week in (4,5) group by who, week, what';

Si su tabla de origen tiene millones de registros y planea pivotar solo un pequeño subconjunto de ella, hacerlo marcará una gran diferencia en cuanto al rendimiento. Además, tiene total libertad para usar expresiones, funciones escalares, alias, unirse, cláusulas where y have en su declaración SQL. La única restricción es que usted es responsable de que el resultado de esta declaración tenga el formato correcto para el procesamiento dinámico.

Usar SrcDef también permite usar expresiones y / o funciones escalares. Por ejemplo:

createtable xpivot (
Who char(10)notnull,
What char(12)notnull,Firstdouble(8,2) flag=1,
Middle double(8,2) flag=1,Lastdouble(8,2) flag=1)engine=connect table_type=PIVOT
option_list='PivotCol=wk,FncCol=amnt'
Srcdef='select who, what, case when week=3 then ''First'' when
week=5 then ''Last'' else ''Middle'' end as wk, sum(amount) *
6.56 as amnt from expenses group by who, what, wk';

Ahora la declaración:

select*from xpivot;

Mostrará el resultado:

Nota 1: para evitar que varias líneas tengan los mismos valores de columna fijos, es obligatorio en SrcDef para colocar la columna dinámica al final del grupo por lista.

Nota 2: en la declaración de creación SrcDef, es obligatorio dar alias para las columnas que contienen expresiones para que sean reconocidas por las otras opciones.

Nota 3: en el SrcDef select, las comillas deben escaparse porque la declaración completa se pasa a MariaDB entre comillas. Alternativamente, especifíquelo entre comillas dobles.

Nota 4: Podríamos haber dejado CONNECT para las definiciones de columna. Sin embargo, debido a que se definen a partir de los nombres ordenados, la columna del medio se colocó al final de ellos.

Especificar las columnas correspondientes a la columna Pivot

Estas columnas deben ser nombradas a partir de los valores existentes en la columna “pivote”. Por ejemplo, suponiendo que tengamos lo siguiente mascota mesa:

Girarlo usando raza ya que la columna pivote se realiza con:

createtable pivet
engine=connect table_type=pivot tabname=pet
option_list='PivotCol=race,groupby=1';

Esto da el resultado:

Por cierto, ¿te suena esto? Muestra que, en cierto modo, las tablas PIVOT están haciendo lo contrario de lo que hacen las tablas OCCUR.

Alternativamente, podemos definir específicamente las columnas de la tabla, pero ¿qué sucede si la columna Pivot contiene valores que no coinciden con una columna de “datos”? Hay tres casos dependiendo de las opciones y banderas especificadas.

Primer caso: Si no se especifican opciones específicas, se trata de un error al intentar mostrar la tabla. La consulta se cancelará con un mensaje de error que indica que se alcanzó un valor no coincidente. Tenga en cuenta que debido a que la lista de columnas se establece al crear la tabla, es probable que esto ocurra si algunas filas que contienen nuevos valores para la columna dinámica se insertan en la tabla de origen. Si esto sucede, debe volver a crear la tabla o agregar manualmente las nuevas columnas a la tabla dinámica.

Segundo caso: Se especificó la opción de aceptar. Por ejemplo:

createtable xpivet2 (
name varchar(12)notnull,
dog intnotnulldefault0 flag=1,
cat intnotnulldefault0 flag=1)engine=connect table_type=pivot tabname=pet
option_list='PivotCol=race,groupby=1,Accept=1';

No se generará ningún error y se ignorarán los valores que no coincidan. Esta tabla se mostrará como:

Tercer caso: Se especificó una columna de “volcado” con el valor de la bandera igual a 2. Todos los valores que no coincidan se agregarán en esta columna. Por ejemplo:

createtable xpivet (
name varchar(12)notnull,
dog intnotnulldefault0 flag=1,
cat intnotnulldefault0 flag=1,
other intnotnulldefault0 flag=2)engine=connect table_type=pivot tabname=pet
option_list='PivotCol=race,groupby=1';

Esta tabla se mostrará como:

Es una buena idea proporcionar una columna de “volcado” si la tabla de origen es propensa a insertar nuevas filas que puedan tener un valor para la columna dinámica que no existía cuando se creó la tabla dinámica.

Pivotando tablas de fuentes grandes

A veces, esto puede resultar riesgoso. Si la columna dinámica contiene demasiados valores distintos, es posible que la tabla resultante tenga demasiadas columnas. En todos los casos, el proceso involucrado, encontrar valores distintos al crear la tabla o hacer el grupo al usarla, puede ser muy largo y, a veces, puede fallar debido a la memoria agotada.

Las restricciones de una cláusula where deben aplicarse a la tabla de origen al crear la tabla dinámica en lugar de a la tabla dinámica en sí. Esto se puede hacer creando una tabla intermedia o usando como fuente una vista o una opción srcdef.

Todas las tablas PIVOT son de solo lectura.

El contenido reproducido en este sitio es propiedad de sus respectivos dueños, y MariaDB no revisa este contenido con anticipación. Los puntos de vista, la información y las opiniones expresadas por este contenido no representan necesariamente las de MariaDB o de cualquier otra parte.