Tengo una mesa que se parece a esto:

+----+-------+
| id | col2  |
+----+-------+
|  1 | a     |
|  2 | b     |
|  3 | ,b    |
|  4 | c     |
|  5 | d,a   |
|  6 | e,a,b |
+----+-------+

¿Cuál es la forma más eficiente de consultarlo y devolver lo siguiente?

+------+----------+
| col1 | count_id |
+------+----------+
| a    |        3 |
| b    |        3 |
| c    |        1 |
| d    |        1 |
| e    |        1 |
+------+----------+

Estaba pensando en usar las declaraciones case when pero parece desordenado.

3
CuriousConfucius 14 feb. 2020 a las 04:30

2 respuestas

La mejor respuesta

En Presto, puede dividir la lista delimitada en una matriz y luego anular la matriz. Esto le da un registro por elemento en cada lista. El resto es solo agregación:

select s.colx, count(*) cnt 
from mytable t
cross join unnest(split(t.col2, ',')) as s(colx)
group by s.colx

Si desea el recuento de distintos id s (en caso de que haya duplicados dentro de las listas delimitadas):

select s.colx, count(distinct t.id) cnt 
from mytable t
cross join unnest(split(t.col2, ',')) as s(colx)
group by s.colx
1
GMB 14 feb. 2020 a las 01:38

Bueno, es posible que necesite una tabla de caracteres que contenga todos los caracteres que pueden aparecer en la segunda columna. Suponiendo que tuviera eso, podría intentar un enfoque de combinación:

WITH letters AS (
    SELECT 'a' AS col1 UNION ALL
    SELECT 'b' UNION ALL
    SELECT 'c' UNION ALL
    ...
    SELECT 'z'
)

SELECT
    t1.col1,
    COUNT(t2.col2) AS count_id
FROM letters t1
LEFT JOIN yourTable t2
    ON ',' || t2.col2 || ',' LIKE '%,' || t1.col1 || ',%'
GROUP BY
    t1.col1
ORDER BY
   t1.col1;

Tenga en cuenta que la respuesta aceptada solo funciona si col2 tiene todas las letras que queremos informar. De lo contrario, faltaría información en la salida.

1
Tim Biegeleisen 14 feb. 2020 a las 02:22