Tengo dos columnas ind y tar que contienen matrices.
ind tar {10} {10} {6} {5,6} {4,5,6} {5,6} {5,6} {5,6} {7,8} {11} {11} {5,6,7} {11} {8} {9,10} {6} Quiero encontrar si existe un valor en ambas matrices, y si eso es cierto, quiero mantenerlo solo en la columna ind . Por ejemplo, en la primera fila tengo el valor 10 en ambas columnas. Quiero terminar con este valor solo en la columna ind y dejar la columna tar vacía. Este es el resultado esperado:
ind tar {10} {6} {5} {4,5,6} {5,6} {7,8} {11} {11} {5,6,7} {11} {8} {9,10} {6}¿Cómo puedo hacer eso en PostgreSQL?
Hasta ahora solo logré encontrar los elementos comunes, pero no sé cómo continuar manteniéndolos solo en la columna ind y eliminarlos de la columna tar .
with t1 as ( select distinct ind, tar from table_1 join table_2 using (id) limit 50 ), t2 as ( select ind & tar as common_el, ind , tar from t1 ) select * from t2lo que resulta en esto:
common_el ind tar {10} {10} {10} {6} {6} {5,6} {5,6} {4,5,6} {5,6} {5,6} {5,6} {5,6}Puedes hacerlo de esta manera ( violín ):
Creación de tablas:
CREATE TABLE t(x INTEGER[], y INTEGER[]);Rellene la tabla:
INSERT INTO t VALUES ('{10}', '{10}'), ('{6}', '{5,6}'), ('{4,5,6}', '{5,6}'), ('{5,6}', '{5,6}'), ('{7,8}', '{11}'), ('{11}', '{5,6,7}'), ('{11}', '{8}'), ('{9,10}', '{6}'), -- -- records below added for testing! -- ('{11}', '{5,8,10,11,133}'), ('{9,10}', '{4,5,6,8,9,10,11}'), ('{9,10}', '{4,5,6,8,9,10,11}'); Si no quiere o no puede, use INTARRAY .
SELECT tx, ARRAY((SELECT UNNEST(ty)) EXCEPT (SELECT UNNEST(tx))) FROM t;Resultado:
x array {10} {} {6} {5} {4,5,6} {} {5,6} {} {7,8} {11} {11} {7,5,6} {11} {8} {9,10} {6} {11} {8,10,133,5} {9,10} {11,8,5,4,6} {9,10} {11,8,5,4,6}Et voilà - ¡el resultado deseado! ¡Vea aquí un excelente hilo con muchos enfoques para esto y temas estrechamente relacionados discutidos!
El operador & que está utilizando es del módulo intarray que también le permite usar - para eliminar elementos en una matriz de otra.
Por ej.
select ind, tar, ind & tar as common_el, tar - (ind & tar) as new_tar from table_1| Indiana | alquitrán | common_el | nuevo_tar |
|---|---|---|---|
| {10} | {10} | {10} | {} |
| {6} | {5,6} | {6} | {5} |
| {4,5,6} | {5,6} | {5,6} | {} |
| {5,6} | {5,6} | {5,6} | {} |
| {7,8} | {11} | {} | {11} |
| {11} | {5,6,7} | {} | {5,6,7} |
| {11} | {8} | {} | {8} |
| {9,10} | {6} | {} | {6} |
o más simple
select ind, tar, ind & tar as common_el, tar - ind as new_tar from table_1Vea la demostración funcional de db fiddle aquí
Edición 1 : para usuarios de módulos que no intarray .
Usando UNNEST para transformar la matriz en múltiples filas, esto se puede resolver con múltiples enfoques de sql para identificar dónde los elementos de 1 conjunto no están en otro, por ejemplo.
select ind, array( select t1.val from unnest(tar) t1(val) where t1.val not in ( select val from unnest(ind) i1(val) ) ) as new_tar from table_1| Indiana | nuevo_tar |
|---|---|
| {10} | {} |
| {6} | {5} |
| {4,5,6} | {} |
| {5,6} | {} |
| {7,8} | {11} |
| {11} | {5,6,7} |
| {11} | {8} |
| {9,10} | {6} |
Esto también se puede hacer así (todo el código a continuación está disponible en el violín aquí ):
CREATE OR REPLACE FUNCTION array_diff (array1 ANYARRAY, array2 ANYARRAY) RETURNS ANYARRAY AS $$ SELECT COALESCE(ARRAY_AGG(elem), '{}') FROM UNNEST(array1) elem WHERE elem <> ALL (array2) $$ LANGUAGE SQL STRICT IMMUTABLE;y para usarlo (puse registros adicionales para probar, verifique el violín):
SELECT ROW_NUMBER() OVER (ORDER BY NULL) rn, x, y, array_diff(y, x) FROM t ORDER BY rn;Resultado:
rn xy array_diff 1 {10} {10} {} 2 {6} {5,6} {5} 3 {4,5,6} {5,6} {} 4 {5,6} {5,6} {} 5 {7,8} {11} {11} 6 {11} {5,6,7} {5,6,7} 7 {11} {8} {8} 8 {9,10} {6} {6} 9 {11} {5,8,10,11,133} {5,8,10,133} 10 {9,10} {4,5,6,8,9,10,11} {4,5,6,8,11} 11 {9,10} {4,5,6,8,9,10,11} {4,5,6,8,11}Vea el violín para la ACTUALIZACIÓN (trivial).