Agregando los loggings unidos más recientes por semana

Necesita un elemento de datos por semana y objective (antes de agregar recuentos por empresa). Eso es una CROSS JOIN simple entre generate_series() y goals . La (posiblemente) costosa parte es get el state actual de las updates para cada uno. Como @Paul ya sugirió , una unión LATERAL parece ser la mejor herramienta. Solo hazlo para updates y utiliza una técnica más rápida con LIMIT 1 .

Y simplifique el event handling la date con date_trunc() .

 SELECT w_start , g.company_id , count(*) FILTER (WHERE u.status = 'green') AS green_count , count(*) FILTER (WHERE u.status = 'amber') AS amber_count , count(*) FILTER (WHERE u.status = 'networking') AS networking_count FROM generate_series(date_trunc('week', NOW() - interval '2 months') , date_trunc('week', NOW()) , interval '1 week') w_start CROSS JOIN goals g LEFT JOIN LATERAL ( SELECT status FROM updates WHERE goal_id = g.id AND created_at < w_start ORDER BY created_at DESC LIMIT 1 ) u ON true GROUP BY w_start, g.company_id ORDER BY w_start, g.company_id; 

Para hacerlo rápido necesitas un índice de columnas múltiples :

 CREATE INDEX updates_special_idx ON updates (goal_id, created_at DESC, status); 

El order descendente para created_at es el mejor, pero no estrictamente necesario. Postgres puede escanear los índices hacia atrás casi exactamente igual de rápido. ( No aplicable para el order de sorting invertido de columnas múltiples, sin embargo )

Columnas de índice en ese order. ¿Por qué?

• Índice y performance de múltiples columnas

Y el status la tercera columna solo se agrega para permitir exploraciones rápidas de solo índice en las updates . Caso relacionado:

• Escaneos de índice lento en una tabla grande

1k objectives por 9 semanas (su intervalo de 2 meses se solapa con al less 9 semanas) solo requieren búsquedas de índice 9k para la 2da tabla de solo 1k filas. Para las tablas pequeñas como esta, el performance no debería ser un gran problema. Pero una vez que tenga un par de miles más en cada tabla, el performance se deteriorará con escaneos secuenciales.

w_start representa el comienzo de cada semana. En consecuencia, los recuentos son para el comienzo de la semana. Todavía puede extraer año y semana (o cualquier otro detalle que represente su semana), si insiste:

  EXTRACT(isoyear from w_start) AS year , EXTRACT(week from w_start) AS week 

Mejor con ISOYEAR , como explicó @Paul.

SQL Fiddle.

Relacionado:

• ¿Cuál es la diferencia entre LATERAL y una subconsulta en PostgreSQL?
• Optimizar la consulta GROUP BY para recuperar el último logging por usuario
• Seleccione la primera fila en cada grupo GROUP BY?
• PostgreSQL: conteo de filas en ejecución para una consulta 'por minuto'

Esto parece ser un buen uso para las uniones LATERAL :

 SELECT EXTRACT(ISOYEAR FROM s) AS year, EXTRACT(WEEK FROM s) AS week, u.company_id, COUNT(u.goal_id) FILTER (WHERE u.status = 'green') AS green_count, COUNT(u.goal_id) FILTER (WHERE u.status = 'amber') AS amber_count, COUNT(u.goal_id) FILTER (WHERE u.status = 'networking') AS networking_count FROM generate_series(NOW() - INTERVAL '2 months', NOW(), '1 week') s(w) LEFT OUTER JOIN LATERAL ( SELECT DISTINCT ON (g.company_id, u2.goal_id) g.company_id, u2.goal_id, u2.status FROM updates u2 INNER JOIN goals g ON g.id = u2.goal_id WHERE u2.created_at <= sw ORDER BY g.company_id, u2.goal_id, u2.created_at DESC ) u ON true WHERE u.company_id IS NOT NULL GROUP BY year, week, u.company_id ORDER BY u.company_id, year, week ; 

Por cierto, estoy extrayendo ISOYEAR no YEAR para asegurarme de get resultados razonables a principios de enero. Por ejemplo EXTRACT(YEAR FROM '2016-01-01 08:49:56.734556-08') es 2016 pero EXTRACT(WEEK FROM '2016-01-01 08:49:56.734556-08') es 53 !

EDITAR: Debería probar sus datos reales, pero creo que esto debería ser más rápido:

 SELECT year, week, company_id, COUNT(goal_id) FILTER (WHERE last_status = 'green') AS green_count, COUNT(goal_id) FILTER (WHERE last_status = 'amber') AS amber_count, COUNT(goal_id) FILTER (WHERE last_status = 'networking') AS networking_count FROM ( SELECT EXTRACT(ISOYEAR FROM s) AS year, EXTRACT(WEEK FROM s) AS week, u.company_id, u.goal_id, (array_agg(u.status ORDER BY u.created_at DESC))[1] AS last_status FROM generate_series(NOW() - INTERVAL '2 months', NOW(), '1 week') s(t) LEFT OUTER JOIN ( SELECT g.company_id, u2.goal_id, u2.created_at, u2.status FROM updates u2 INNER JOIN goals g ON g.id = u2.goal_id ) u ON st >= u.created_at WHERE u.company_id IS NOT NULL GROUP BY year, week, u.company_id, u.goal_id ) x GROUP BY year, week, company_id ORDER BY company_id, year, week ; 

Sin embargo, todavía no hay funciones de window. 🙂 También puedes acelerarlo un poco más reemplazando (array_agg(...))[1] con una first function real. Tendrá que definirlo usted mismo, pero hay implementaciones en la wiki de Postgres que son fáciles de search para Google.

Yo uso PostgreSQL 9.3. Estoy interesado en tu pregunta. Examiné tu estructura de datos. Luego de crear las siguientes tablas.

Inserté los siguientes loggings;

Empresa

Metas

Actualizaciones

Después de eso escribí la siguiente consulta, para su corrección

 SELECT c.id company_id, c.name company_name, u.status goal_status, EXTRACT(week from u.created_at) goal_status_week, EXTRACT(year from u.created_at) AS goal_status_year FROM company c INNER JOIN goals g ON g.company_id = c.id INNER JOIN updates u ON u.goal_id = g.id ORDER BY goal_status_year DESC, goal_status_week DESC; 

Obtengo los siguientes resultados;

Por fin fusiono esta consulta con series de semanas

 SELECT gs.company_id, gs.company_name, gs.goal_status, EXTRACT(year from w) AS year, EXTRACT(week from w) AS week, COUNT(gs.*) cnt FROM generate_series(NOW() - INTERVAL '3 MONTHS', NOW(), '1 week') w LEFT JOIN( SELECT c.id company_id, c.name company_name, u.status goal_status, EXTRACT(week from u.created_at) goal_status_week, EXTRACT(year from u.created_at) AS goal_status_year FROM company c INNER JOIN goals g ON g.company_id = c.id INNER JOIN updates u ON u.goal_id = g.id ) gs ON gs.goal_status_week = EXTRACT(week from w) AND gs.goal_status_year = EXTRACT(year from w) GROUP BY company_id, company_name, goal_status, year, week ORDER BY year DESC, week DESC; 

Obtengo este resultado

Tenga un buen día.