Agregando los loggings unidos más recientes por semana

Tengo una tabla de updates en Postgres que es 9.4.5 como esta:

 goal_id | created_at | status 1 | 2016-01-01 | green 1 | 2016-01-02 | networking 2 | 2016-01-02 | amber 

Y una tabla de goals como esta:

 id | company_id 1 | 1 2 | 2 

Quiero crear un gráfico para cada compañía que muestre el estado de todos sus objectives, por semana.

tabla de ejemplo

I image esto requeriría generar una serie de las últimas 8 semanas, encontrar la actualización más reciente para cada objective que vino antes de esa semana, y luego contar los diferentes estados de las actualizaciones encontradas.

Lo que tengo hasta ahora

 SELECT EXTRACT(year from generate_series) AS year, EXTRACT(week from generate_series) AS week, u.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(NOW() - INTERVAL '2 MONTHS', NOW(), '1 week') LEFT OUTER JOIN ( SELECT DISTINCT ON(year, week) goals.company_id, updates.status, EXTRACT(week from updates.created_at) week, EXTRACT(year from updates.created_at) AS year, updates.created_at FROM updates JOIN goals ON goals.id = updates.goal_id ORDER BY year, week, updates.created_at DESC ) u ON u.week = week AND u.year = year GROUP BY 1,2,3 

Pero esto tiene dos problemas. Parece que el join on u no está funcionando como pensé. Parece join en cada fila (?) Devuelta desde la consulta interna, y esto solo selecciona la actualización más reciente que ocurrió desde esa semana. Debería get la actualización más reciente de antes de esa semana si es necesario.

Este es un SQL bastante complicado y me encanta la información sobre cómo llevarlo a cabo.

Estructuras de tabla e información

La tabla de objectives tiene alnetworkingedor de ~ 1000 objectives ATM y está creciendo aproximadamente ~ 100 por semana:

  Table "goals" Column | Type | Modifiers -----------------+-----------------------------+----------------------------------------------------------- id | integer | not null default nextval('goals_id_seq'::regclass) company_id | integer | not null name | text | not null created_at | timestamp without time zone | not null default timezone('utc'::text, now()) updated_at | timestamp without time zone | not null default timezone('utc'::text, now()) Indexes: "goals_pkey" PRIMARY KEY, btree (id) "entity_goals_company_id_fkey" btree (company_id) Foreign-key constraints: "goals_company_id_fkey" FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE RESTRICT 

La tabla de updates tiene alnetworkingedor de ~ 1000 y está creciendo alnetworkingedor de ~ 100 a la semana:

  Table "updates" Column | Type | Modifiers ------------+-----------------------------+------------------------------------------------------------------ id | integer | not null default nextval('updates_id_seq'::regclass) status | entity.goalstatus | not null goal_id | integer | not null created_at | timestamp without time zone | not null default timezone('utc'::text, now()) updated_at | timestamp without time zone | not null default timezone('utc'::text, now()) Indexes: "goal_updates_pkey" PRIMARY KEY, btree (id) "entity_goal_updates_goal_id_fkey" btree (goal_id) Foreign-key constraints: "updates_goal_id_fkey" FOREIGN KEY (goal_id) REFERENCES goals(id) ON DELETE CASCADE Schema | Name | Internal name | Size | Elements | Access privileges | Description --------+-------------------+---------------+------+----------+-------------------+------------- entity | entity.goalstatus | goalstatus | 4 | green +| | | | | | amber +| | | | | | networking | | 

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.

Estructura de datos

Inserté los siguientes loggings;

Empresa

Registros de la compañía

Metas

Registros de objetivos

Actualizaciones

Registros de 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; Resultado de Sql interno

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

Resultado final

Tenga un buen día.