Necesito encontrar una manera de determinar la subcadena más común dentro de una matriz en PostgreSQL.
Tengo una matriz de una sola dimensión en una columna en PostgreSQL que almacena valores de CPV (un vocabulario de clasificación anidado: https://simap.ted.europa.eu/cpv ). Los códigos compuestos por caracteres numéricos, pero almacenados como varchar ya que algunos registros tienen un cero inicial, como este:
["45331110", "50721000", "45251250", "42160000", "39715000", "45315000", "09323000", "71321200", "45331100", "50720000"]
Quiero extraer los dos dígitos iniciales más comunes de esta matriz usando PostgreSQL, que en el caso del ejemplo sería 45 .
Si desea obtener los dos dígitos iniciales más comunes por fila, puede usar:
WITH data_rows(id, cpv_values) AS ( VALUES (1, ARRAY ['45331110', '50721000', '45251250','42160000','39715000','45315000', '09323000','71321200','45331100', '50720000']) , (2, ARRAY ['50721000']) -- second test case ) SELECT id, leading_two_digits FROM data_rows -- for every row in `data_rows` (your table), -- select the most common `leading_two_digits` (through GROUP BY/ORDER BY/LIMIT 1) JOIN LATERAL ( SELECT left(code, 2) AS leading_two_digits FROM unnest(cpv_values) AS f(code) GROUP BY left(code, 2) ORDER BY COUNT(*) DESC LIMIT 1 ) s ON truedevoluciones
+--+------------------+ |id|leading_two_digits| +--+------------------+ |1 |45 | |2 |50 | +--+------------------+Si desea obtener los dos dígitos principales más comunes en todas las filas, puede usar:
WITH data_rows(cpv_values) AS ( VALUES (ARRAY ['45331110', '50721000', '45251250','42160000','39715000','45315000', '09323000','71321200','45331100', '50720000']), (ARRAY ['45']) ) SELECT left(code, 2) AS leading_two_digits FROM data_rows, unnest(cpv_values) AS f(code) GROUP BY left(code, 2) ORDER BY COUNT(*) DESC LIMIT 1Esta consulta hace lo que necesita.
select substr(t, 1, 2) mc from unnest(array['45331110', '50721000', '45251250', '42160000', '39715000', '45315000', '09323000', '71321200', '45331100', '50720000']) t group by mc order by count(1) desc limit 1;Resultado:
Name|Value| ----|-----| mc |45 |Puede usar lo anterior como una subconsulta para extraer la subcadena más común por fila.