Hay muchos datos provenientes de múltiples fuentes que necesito agrupar según la prioridad, pero la calidad de los datos de esas fuentes es diferente; es posible que falten algunos datos. La tarea es agrupar esos datos en una tabla separada, de la forma más completa posible.
Por ejemplo:
create table grouped_data ( id serial primary key, type text, a text, b text, c int ); create table raw_data ( id serial primary key, type text, a text, b text, c int, priority int ); insert into raw_data (type, a, b, c, priority) values ('one', null, '', 123, 1), ('one', 'foo', '', 456, 2), ('one', 'bar', 'baz', 789, 3), ('two', null, 'two-b', 11, 3), ('two', '', '', 33, 2), ('two', null, 'two-bbb', 22, 1); Ahora necesito agrupar registros por type , ordenar por priority , tomar el primer valor que no sea nulo ni vacío, y ponerlo en grouped_data . En este caso, el valor de a para el grupo one sería foo porque la fila que contiene ese valor tiene una prioridad más alta que la que tiene la bar . Y c debería ser 123 , ya que tiene el prio más alto. Lo mismo para el grupo two , para cada columna tomamos los datos que no son nulos, no están vacíos y tienen la prioridad más alta, o recurrimos a null si no hay datos reales presentes.
Al final, se espera que grouped_data tenga el siguiente contenido:
('one', 'foo', 'baz', 123), ('two', null, 'two-bbb', 22)He intentado agrupar, subseleccionar, MERGE, uniones cruzadas... Por desgracia, mi conocimiento de PostgreSQL no es lo suficientemente bueno para que funcione. Una cosa que también me gustaría evitar es revisar las columnas una por una, ya que en el mundo real hay pocas docenas de columnas con las que trabajar...
Un enlace a un violín que he estado usando para jugar con esto: http://sqlfiddle.com/#!17/76699/1
UPD:
¡Gracias a todos! La solución de Oleksii Tambovtsev es la más rápida. En un conjunto de datos que se parece mucho a un caso del mundo real (2 millones de registros, ~30 campos), se necesitan solo 20 segundos para producir exactamente el mismo conjunto de datos, que anteriormente se generaba mediante programación y tomaba más de 20 minutos.
La solución de eshirvana hace lo mismo en 95s, la de Steve Kass en 125s y Stefanov.sm - 308s (¡que sigue siendo muchísimo más rápida que programáticamente!)
Gracias a todos :)
puede usar la función de ventana first_value :
select distinct type , first_value(a) over (partition by type order by nullif(a,'') is null, priority) as a , first_value(b) over (partition by type order by nullif(b,'') is null, priority) as b , first_value(c) over (partition by type order by priority) as c from raw_dataDeberías probar esto:
SELECT type, (array_agg(a ORDER BY priority ASC) FILTER (WHERE a IS NOT NULL AND a != ''))[1] as a, (array_agg(b ORDER BY priority ASC) FILTER (WHERE b IS NOT NULL AND b != ''))[1] as b, (array_agg(c ORDER BY priority ASC) FILTER (WHERE c IS NOT NULL))[1] as c FROM raw_data GROUP BY type ORDER BY type;select distinct on (type) type, first_value(a) over (partition by type order by (nullif(a, '') is null), priority) a, first_value(b) over (partition by type order by (nullif(b, '') is null), priority) b, first_value(c) over (partition by type order by (c is null), priority) c from raw_data;Esto también debería funcionar.
WITH types(type) AS ( SELECT DISTINCT type FROM raw_data ) SELECT type, (SELECT a FROM raw_data WHERE a > '' AND raw_data.type = types.type ORDER BY priority LIMIT 1) AS a, (SELECT b FROM raw_data WHERE b > '' AND raw_data.type = types.type ORDER BY priority LIMIT 1) AS b, (SELECT c FROM raw_data WHERE c IS NOT NULL AND raw_data.type = types.type ORDER BY priority LIMIT 1) AS c FROM types ORDER BY type;