Te damos la bienvenida a nuestro espacio, ahora hallarás la respuesta de lo que estabas buscando.
Solución:
No queda realmente claro a partir de la pregunta cuál es el papel de la call_type_id columna. Lo ignoraré hasta que lo aclares.
Sin funciones de ventana
Aquí hay una variante simple que no usa funciones de ventana en absoluto.
Asegúrese de que haya un índice en (call_dt_key, aes_raw).
CTE_Dates devuelve una lista de todas las fechas de la tabla y calcula el promedio de cada día. Esta average_current_day será necesario para el primer día. El servidor escaneará todo el índice de cualquier forma, por lo que calcular dicho promedio es barato.
Luego, para cada día distinto, utilizo una autounión para calcular el promedio de los 40 días anteriores. Esto volverá NULL para el primer día, que se reemplaza con average_current_day en la consulta principal.
No tiene que usar CTE aquí, solo hace que la consulta sea más fácil de leer.
WITH
CTE_Dates
AS
(
SELECT
call_dt_key
,call_dt_key - INTERVAL '41 day' AS dt_from
,call_dt_key - INTERVAL '1 day' AS dt_to
,AVG(test_aes.aes_raw) AS average_current_day
FROM test_aes
GROUP BY call_dt_key
)
SELECT
CTE_Dates.call_dt_key
,COALESCE(prev40.average_40, CTE_Dates.average_current_day) AS average_40
FROM
CTE_Dates
LEFT JOIN LATERAL
(
SELECT AVG(test_aes.aes_raw) AS average_40
FROM test_aes
WHERE
test_aes.call_dt_key >= CTE_Dates.dt_from
AND test_aes.call_dt_key <= CTE_Dates.dt_to
) AS prev40 ON true
ORDER BY call_dt_key;
Resultado
| call_dt_key | average_40 |
|----------------------------|--------------------|
| January, 01 2016 00:00:00 | 15 |
| January, 05 2016 00:00:00 | 15 |
| January, 10 2016 00:00:00 | 15.333333333333334 |
| January, 15 2016 00:00:00 | 15.294117647058824 |
| January, 16 2016 00:00:00 | 15.5 |
| January, 20 2016 00:00:00 | 15.652173913043478 |
| January, 21 2016 00:00:00 | 15.6 |
| January, 31 2016 00:00:00 | 15.555555555555555 |
| February, 01 2016 00:00:00 | 15.517241379310345 |
| February, 10 2016 00:00:00 | 15.483870967741936 |
| February, 15 2016 00:00:00 | 15.652173913043478 |
| February, 26 2016 00:00:00 | 15.333333333333334 |
| March, 04 2016 00:00:00 | 15 |
| March, 18 2016 00:00:00 | 15 |
Aquí está SQL Fiddle.
Con el índice recomendado, esta solución no debería ser tan mala.
Hay una pregunta similar, pero para SQL Server (Suma móvil de rango de fechas usando funciones de ventana). Postgres parece apoyar RANGE con una ventana de tamaño especificado, mientras que SQL Server no lo hace en este momento. Por lo tanto, es probable que la solución para Postgres sea un poco más simple.
los key parte sería:
AVG(...) OVER (ORDER BY call_dt_key RANGE BETWEEN 41 PRECEDING AND 1 PRECEDING)
Para calcular el promedio móvil usando estas funciones de ventana, probablemente primero tendrá que llenar los espacios vacíos en las fechas, de modo que la tabla tenga al menos una fila para cada día (con NULL valores para aes_raw en estas filas ficticias).
...
Como Erwin Brandstetter señaló correctamente en su respuesta, en este momento (a partir de Postgres 9.5) el RANGE La cláusula en Postgres todavía tiene limitaciones similares a SQL Server. Los doctores dicen:
los valor PRECEDENTES y valor Actualmente, los casos SIGUIENTES solo están permitidos en el modo FILAS.
Entonces, este método con el RANGE anterior no funcionaría para usted incluso si usara Postgres 9.5.
Usar funciones de ventana
Puede utilizar los enfoques descritos en la pregunta para SQL Server anterior. Por ejemplo, agrupe sus datos en sumas diarias, agregue filas para los días que faltan, calcule el movimiento SUM y COUNT utilizando OVER con ROWS y luego calcular la media móvil.
Algo en estas líneas:
WITH
CTE_Dates
AS
(
SELECT
call_dt_key
,SUM(test_aes.aes_raw) AS sum_daily
,COUNT(*) AS cnt_daily
,AVG(test_aes.aes_raw) AS avg_daily
,LEAD(call_dt_key) OVER(ORDER BY call_dt_key) - INTERVAL '1 day' AS next_date
FROM test_aes
GROUP BY call_dt_key
)
,CTE_AllDates
AS
(
SELECT
CASE WHEN call_dt_key = dt THEN call_dt_key ELSE NULL END AS final_dt
,avg_daily
,SUM(CASE WHEN call_dt_key = dt THEN sum_daily ELSE NULL END)
OVER (ORDER BY dt ROWS BETWEEN 41 PRECEDING AND 1 PRECEDING)
/SUM(CASE WHEN call_dt_key = dt THEN cnt_daily ELSE NULL END)
OVER (ORDER BY dt ROWS BETWEEN 41 PRECEDING AND 1 PRECEDING) AS avg_40
FROM
CTE_Dates
INNER JOIN LATERAL
generate_series(call_dt_key, COALESCE(next_date, call_dt_key), '1 day')
AS all_dates(dt) ON true
)
SELECT
final_dt
,COALESCE(avg_40, avg_daily) AS final_avg
FROM CTE_AllDates
WHERE final_dt IS NOT NULL
ORDER BY final_dt;
El resultado es el mismo que en la primera variante. Consulte SQL Fiddle.
Nuevamente, esto podría escribirse con subconsultas en línea sin CTE.
Vale la pena verificar en datos reales el rendimiento de diferentes variantes.
La gran recompensa hace que la respuesta aceptada actualmente parezca ejemplar, pero no estoy del todo satisfecho con varios detalles. Por lo tanto, agregué esta respuesta.
Definición de tabla
Debería haber proporcionado una definición de tabla real para facilitar esto.
A juzgar por los datos de la muestra, call_dt_tm es tipo timestamp with time zone (timestamptz). La columna call_dt_key no es completamente funcionalmente dependiente, ya que la fecha coincidente depende de la zona horaria. Pero si define eso (no solo un desplazamiento, ¡cuidado con el horario de verano!), La fecha se puede derivar fácil y confiablemente de un timestamptz y debería no almacenarse de forma redundante. Para hacerlo bien, use una expresión como:
(call_dt_tm AT TIME ZONE 'Asia/Hong_Kong')::date -- use your time zone name
Detalles:
- Ignorar las zonas horarias por completo en Rails y PostgreSQL
Podrías agregar un MATERIALIZED VIEW con la columna de fecha derivada para facilitar su uso ...
Para el propósito de esta pregunta, me ceñiré a la tabla dada.
40 días
La pregunta y la respuesta cuentan 41 días en lugar de 40 según el requisito. Se incluyen los límites inferior y superior, lo que resulta en un error de uno por uno (bastante común).
En consecuencia, obtengo resultados diferentes en dos filas a continuación.
date, interval, timestamp
Restando un interval a partir de una date produce un timestamp (como en call_dt_key - INTERVAL '41 day'). Para el propósito de esta consulta, es más eficiente restar un integer, produciendo otro date (igual que call_dt_key - 41).
No posible con un RANGE cláusula
@Vladimir sugirió (ahora arreglado) una solución con el RANGE cláusula en la definición de marco de funciones de ventana en Postgres 9.5.
De hecho, nada ha cambiado entre Postgres 9.4 y 9.5 a este respecto, ni siquiera el texto del manual. La definición de marco de las funciones de ventana solo permite RANGE UNBOUNDED PRECEDING y RANGE UNBOUNDED FOLLOWING - no con valores.
Respuesta
Por supuesto, puede utilizar un CTE para calcular la suma / recuento / promedio diarios sobre la marcha. Pero tu mesa ...
almacena la información sobre las llamadas de los usuarios en un centro de llamadas
Este tipo de información no cambia más tarde. Así que calcule los agregados diarios una vez en una vista materializada y construir sobre eso.
CREATE MATERIALIZED VIEW mv_test_aes AS
SELECT call_dt_key AS day
, sum(aes_raw)::int AS day_sum
, count(*)::int AS day_ct
FROM test_aes
WHERE call_dt_key < (now() AT TIME ZONE 'Asia/Hong_Kong')::date -- see above
GROUP BY call_dt_key
ORDER BY call_dt_key;
El día actual siempre falta, pero eso es un característica. Los resultados serían incorrectos antes de que termine el día.
El MV necesita ser actualizado una vez por día, antes de ejecutar la consulta o faltan los últimos días.
Un índice en la tabla subyacente es no es necesario para esto, ya que toda la tabla se lee de todos modos.
CREATE INDEX test_aes_day_val ON test_aes (call_dt_key, aes_raw);
Puede crear una vista materializada más inteligente a mano y solo agregue nuevos días de forma incremental en lugar de recrear todo con MV estándar. Pero eso está más allá del alcance de la pregunta ...
Sin embargo, sugiero encarecidamente un índice en el MV:
CREATE INDEX foo ON mv_test_aes (day, day_sum, day_ct);
Yo solo agregué day_sum y day_ct esperando escaneos de solo índice. Si no los ve en sus consultas, no necesita las columnas en el índice.
SELECT t.day
, round(COALESCE(sum(t1.day_sum) * 1.0 / sum(t1.day_ct) -- * 1.0 to avoid int division
, t.day_sum * 1.0 / t.day_ct), 4) AS avg_40days
FROM mv_test_aes t
LEFT JOIN mv_test_aes t1 ON t1.day < t.day
AND t1.day >= t.day - 40 -- not 41
GROUP BY t.day, t.day_sum, t.day_ct
ORDER BY t.day;
Resultado:
day | avg_40days -----------+------------ 2016-01-01 | 15.0000 2016-01-05 | 15.0000 2016-01-10 | 15.3333 2016-01-15 | 15.2941 2016-01-16 | 15.5000 2016-01-20 | 15.6522 2016-01-21 | 15.6000 2016-01-31 | 15.5556 2016-02-01 | 15.5172 2016-02-10 | 15.4839 2016-02-15 | 15.5556 -- correct results 2016-02-26 | 15.0000 2016-03-04 | 15.0000 2016-03-18 | 15.0000
Violín SQL.
Si ejecuta esto a menudo, envolvería todo el asunto en un MV para evitar cálculos repetidos.
Una solución con funciones de ventana y una cláusula de marco. ROWS BETWEEN .... también sería posible. Pero los datos de su ejemplo sugieren que no tiene valores para la mayoría de los días en el rango (muchos más espacios que islas), por lo que no espero que sea más rápido. Relacionado:
- Suma móvil / recuento / promedio durante el intervalo de fechas
Más adelante puedes encontrar las críticas de otros usuarios, tú aún puedes dejar el tuyo si te apetece.