I need to find a way to determine the most common substring from within an array in PostgreSQL.
I've got a single dimension array in a column in PostgreSQL that is storing CPV values (a nested classification vocabulary - https://simap.ted.europa.eu/cpv). The codes made up of numeric characters, but stored as varchar as some records have a leading zero, like this:
["45331110", "50721000", "45251250", "42160000", "39715000", "45315000", "09323000", "71321200", "45331100", "50720000"]
I want to extract the most common leading two digits from this array using PostgreSQL, which in the example case would be 45.
If you want to get the most common leading two digits per row, then you can use:
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 true
returns
+--+------------------+
|id|leading_two_digits|
+--+------------------+
|1 |45 |
|2 |50 |
+--+------------------+
If you want to get the most common leading two digits across all rows, you can use:
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 1
This query does what you need.
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;
Result:
Name|Value|
----|-----|
mc |45 |
You may use thie above as a subquery to extract the most common substring per row.