Consulta de distribución de frecuencia SQL para contar ranges con grupos e include 0 conteos

Dado:

table 'thing': age --- 3.4 3.4 10.1 40 45 49 

Quiero contar el número de cosas para cada range de 10 años, por ejemplo,

 age_range | count ----------+------- 0 | 2 10| 1 20| 0 30| 0 40| 3 

Esta consulta se acerca:

 SELECT FLOOR(age / 10) as age_range, COUNT(*) FROM thing GROUP BY FLOOR(age / 10) ORDER BY FLOOR(age / 10); 

Salida:

  age_range | count -----------+------- 0 | 1 1 | 2 4 | 3 

Sin embargo, no me muestra los ranges que tienen 0 conteos. ¿Cómo puedo modificar la consulta para que también muestre los intervalos intermedios con 0 conteos?

Encontré preguntas similares de stackoverflow para recuento de ranges, algunas para 0 recuentos, pero implican tener que especificar cada range (ya sea codificando los ranges en la consulta o colocando los ranges en una tabla). Preferiría usar una consulta genérica como la anterior, donde no tengo que especificar explícitamente cada range (por ejemplo, 0-10, 10-20, 20-30, …). Estoy usando PostgreSQL 9.1.3.

¿Hay alguna manera de modificar la consulta simple anterior para include 0 conteos?

Similar:
Oracle: ¿cómo "agrupar por" en un range?
Obtener distribución de frecuencia de un range decimal en MySQL

generate_series al rescate:

 select 10 * sd, count(t.age) from generate_series(0, 10) s(d) left outer join thing t on sd = floor(t.age / 10) group by sd order by sd 

Calcular el límite superior para generate_series debería ser trivial con una consulta separada, solo usé 10 como marcador de position.

Esta:

 generate_series(0, 10) s(d) 

esencialmente genera una tabla en línea llamada s con una sola columna d que contiene los valores de 0 a 10 (inclusive).

Puede envolver las dos consultas (una para calcular el range, una para calcular los recuentos) en una function si es necesario.

Necesitas alguna forma de devise la tabla de ranges de edad. El número de fila generalmente funciona bien. Haga un producto cartesiano contra una gran table para get muchos numbers.

 WITH RANGES AS ( SELECT (rownum - 1) * 10 AS age_range FROM ( SELECT row_number() OVER() as rownum FROM pg_tables ) n ,( SELECT ceil( max(age) / 10 ) range_end FROM thing ) m WHERE n. rownum <= range_end ) SELECT r.age_range, COUNT(t.age) AS count FROM ranges r LEFT JOIN thing t ON r.age_range = FLOOR(t.age / 10) * 10 GROUP BY r.age_range ORDER BY r.age_range; 

EDITAR: mu es demasiado corto tiene una respuesta mucho más elegante, pero si no tienes una function generate_series en el db, … 🙂