Pivote con / sin Tablefunc

Una tabla dinámica es una herramienta poderosa para estimar, compilar y revisar datos para encontrar patrones y tendencias aún más fácil. Las tablas dinámicas se pueden usar para agregar, ordenar, ordenar, reorganizar, agrupar, totalizar o promediar datos en un conjunto de datos para comprender verdaderamente las asociaciones y dependencias de datos. Usar una tabla dinámica como ilustración es la forma más fácil de demostrar cómo funciona este método. PostgreSQL 8.3 se lanzó hace unos años, y una nueva versión llamada 'tablefunc' fue añadido. Tablefunc es un componente que contiene varios métodos que producen tablas (es decir, varias filas). Esta modificación viene con una gran variedad de características. El método de tabla de referencias cruzadas, que se utilizará para crear tablas dinámicas, se encuentra entre ellos. El método de tabla de referencias cruzadas toma un argumento textual: un comando SQL que devuelve datos sin procesar en el primer diseño y devuelve una tabla en el diseño posterior.

Ejemplo de tabla dinámica sin TableFunc:

Para comenzar a trabajar en el pivote de PostgreSQL con el módulo 'tablefunc', debe intentar hacer una tabla dinámica sin él. Entonces, abramos el shell de línea de comandos de PostgreSQL y proporcionemos los valores de los parámetros para el servidor, la base de datos, el número de puerto, el nombre de usuario y la contraseña requeridos. Deje estos parámetros vacíos si desea utilizar los parámetros seleccionados por defecto.

Crearemos una nueva tabla llamada 'Prueba' en la base de datos 'prueba' con algunos campos en ella, como se muestra a continuación.

>> CREAR TABLA Prueba (Id int, nombre varchar (20), sal int, trabajo varchar (20));

Después de crear una tabla, es hora de insertar algunos valores en la tabla, como se muestra en la siguiente consulta.

>> INSERT INTO Test (Id, name, sal, job) VALUES (11, 'Aqsa', 45000, 'Writer'), (11, 'Aqsa', 48000, 'Officer'), (11, 'Aqsa', 50000, 'Doctor'), (12, 'Raza', 40000, 'Oficial'), (11, 'Raza', 60000, 'Doctor'), (12, 'Raza', 67000, 'Oficial'), ( 13, 'Saeed', 85000, 'Escritor'), (13, 'Saeed', 69000, 'Oficial'), (13, 'Saeed', 90000, 'Doctor');

Puede ver que los datos relevantes se han insertado correctamente. Puede ver que esta tabla tiene más de 1 de los mismos valores para id, name y job.

>> SELECCIONAR * DESDE la entrada;

Creemos una tabla dinámica, que resumirá el registro de la tabla 'Prueba' utilizando la siguiente consulta. El comando fusiona los mismos valores de la columna 'Id' y 'nombre' en una fila mientras toma la suma de los valores de la columna 'salario' para los mismos datos de acuerdo con 'Id' y 'nombre'. También indica cuántas veces se ha producido un valor en el conjunto de valores en particular.

>> SELECCIONAR Id, nombre, suma (sal) sal, suma ((trabajo = 'Doctor') :: int) Doctor, suma ((trabajo = 'Escritor') :: int) Escritor, suma ((trabajo = 'Oficial ') :: int) "Oficial" DEL GRUPO DE PRUEBA POR Id, nombre;

Ejemplo de tabla dinámica con TableFunc:

Comenzaremos explicando nuestro punto principal desde un punto de vista realista, y luego describiremos la creación de la tabla dinámica en los pasos que nos gustan. Entonces, en primer lugar, debe agregar tres tablas para trabajar en un pivote. La primera tabla que vamos a crear es 'Maquillaje', que almacenará información sobre los básicos del maquillaje. Pruebe la siguiente consulta en el shell de la línea de comandos para crear esta tabla.

>> CREAR TABLA SI NO EXISTE Maquillaje (make_Id int PRIMARY KEY, p_name VARCHAR (100) NOT NULL);

Después de la creación de la tabla 'Maquillaje', agreguemos algunos registros. Ejecutaremos la consulta que se muestra a continuación en el shell para agregar 10 registros a esta tabla.

Necesitamos crear otra tabla llamada 'usuarios' que mantendrá los registros de los usuarios que usan esos productos. Ejecute la consulta que se indica a continuación en el shell para crear esta tabla.

>> CREAR TABLA SI NO EXISTE usuarios (user_id int PRIMARY KEY, u_name varchar (100) NOT NULL);

Hemos insertado los 20 registros para la tabla 'usuarios' como se muestra en la imagen a continuación.

Tenemos otra tabla, 'makeup_user', que mantendrá los registros mutuos de la tabla 'Makeup' y 'users'. Tiene otro campo, 'precio', que ahorrará el precio del producto. La tabla se ha generado mediante la consulta que se indica a continuación.

>> CREAR TABLA SI NO EXISTE makeup_user (ID int PRIMARY KEY, Mid int NOT NULL REFERENCES Makeup (make_Id), Uid int NOT NULL REFERENCES users (user_id), price decimal (18,2));

Hemos insertado un total de 56 registros en esta tabla, como se muestra en la imagen.

Creemos una vista adicional para usarla para la generación de una tabla dinámica. Esta vista usa INNER Join para hacer coincidir los valores de la columna de clave principal de las tres tablas y obtener el 'nombre', 'nombre_producto' y el 'costo' de un producto de una tabla 'clientes'

>> CREAR VISTA v_makeup_users COMO SELECCIONAR c.u_name, p.p_name, pc.precio DESDE los usuarios c INNER JOIN makeup_user pc ON c.user_id = pc.Uid INNER JOIN Makeup p ON pc.Medio = p.make_Id;

Para usar esto, primero debe instalar el paquete tablefunc para la base de datos que desea usar. Este paquete está integrado en PostgreSQL 9.1 y versiones posteriores ejecutando el comando que se indica a continuación. El paquete tablefunc se ha habilitado para usted ahora.

>> CREAR EXTENSIÓN SI NO EXISTE tablefunc;

Después de crear la extensión, es hora de usar la función Crosstab () para crear una tabla dinámica. Entonces usaremos la siguiente consulta en el shell de la línea de comandos para hacerlo. Esta consulta primero obtiene el registro de la 'Vista' recién creada. Estos registros se ordenarán y agruparán por el orden ascendente de las columnas 'u_name' y 'p_name'. Hemos enumerado su nombre de maquillaje para cada cliente, que han comprado, y el costo total de los productos comprados en la tabla. Hemos aplicado el operador UNION ALL en la columna 'p_name' para resumir todos los productos comprados por un cliente por separado. Esto resumirá todos los costos de los productos comprados por un usuario en un valor.

Nuestra tabla dinámica ha sido lista y mostrada en la imagen. Puede ver claramente que algunos espacios de columna están vacíos debajo de cada p_name porque no han comprado ese producto en particular.

Conclusión:

Ahora hemos aprendido brillantemente cómo crear una tabla dinámica para resumir los resultados de las tablas con y sin el uso del paquete Tablefunc.