plan - optimizar consultas sql server 2014
PostgreSQL: conteo de filas en ejecución para una consulta ''por minuto'' (1)
Necesito consultar cada minuto el recuento total de filas hasta ese minuto.
Lo mejor que pude lograr hasta ahora no funciona. Devuelve recuento por minuto, no el recuento total hasta cada minuto:
SELECT COUNT(id) AS count
, EXTRACT(hour from "when") AS hour
, EXTRACT(minute from "when") AS minute
FROM mytable
GROUP BY hour, minute
Solo minutos de devolución con actividad
Más corto
SELECT DISTINCT
date_trunc(''minute'', "when") AS minute
, count(*) OVER (ORDER BY date_trunc(''minute'', "when")) AS running_ct
FROM mytable
ORDER BY 1;
Use
date_trunc()
, devuelve exactamente lo que necesita.No incluya la
id
en la consulta, ya que quiereGROUP BY
rodajas por minuto.count()
se usa típicamente como función agregada simple. Agregar una cláusulaOVER
convierte en una función de ventana . OmitaPARTITION BY
en la definición de la ventana: desea un recuento total de todas las filas. De manera predeterminada, eso cuenta desde la primera fila hasta el último par de la fila actual, tal como lo defineORDER BY
. Cito el manual :La opción de marco predeterminada es
RANGE UNBOUNDED PRECEDING
, que es lo mismo queRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
. ConORDER BY
, establece que el marco sea todas las filas desde el inicio de la partición hasta el último parORDER BY
la fila actual.Y eso pasa a ser exactamente lo que necesitas.
Use
count(*)
lugar decount(id)
. Se ajusta mejor a su pregunta ("recuento de filas"). Por lo general, es un poco más rápido quecount(id)
. Y, aunque podemos suponer queid
NOT NULL
esNOT NULL
, no se ha especificado en la pregunta, por lo quecount(id)
es incorrecto , estrictamente hablando, porque los valores NULL no se cuentan concount(id)
.No puede agrupar en rodajas por minuto en el mismo nivel de consulta. Las funciones de agregación se aplican antes de las funciones de ventana, el
count(*)
función de ventanacount(*)
solo vería 1 fila por minuto de esta manera.
Sin embargo, puedeSELECT DISTINCT
porqueDISTINCT
se aplica después de las funciones de ventana.ORDER BY 1
es solo una abreviatura deORDER BY date_trunc(''minute'', "when")
aquí.
1
es una referencia de referencia de posición a la primera expresión en la listaSELECT
.Use
to_char()
si necesita formatear el resultado. Me gusta:
SELECT DISTINCT
to_char(date_trunc(''minute'', "when"), ''DD.MM.YYYY HH24:MI'') AS minute
, count(*) OVER (ORDER BY date_trunc(''minute'', "when")) AS running_ct
FROM mytable
ORDER BY date_trunc(''minute'', "when");
Lo más rápido
SELECT minute, sum(minute_ct) OVER (ORDER BY minute) AS running_ct
FROM (
SELECT date_trunc(''minute'', "when") AS minute
, count(*) AS minute_ct
FROM tbl
GROUP BY 1
) sub
ORDER BY 1;
Muy parecido al anterior, pero:
Utilizo una subconsulta para agregar y contar filas por minuto. De esta forma obtenemos 1 fila por minuto sin
DISTINCT
en elSELECT
externo.Use
sum()
como función agregada de ventana ahora para sumar los recuentos de la subconsulta.
Descubrí que esto es sustancialmente más rápido con muchas filas por minuto.
Incluye minutos sin actividad
Más corto
@GabiMe preguntó en un comentario cómo obtener una fila por cada minute
en el marco de tiempo, incluidos aquellos en los que no se produjo ningún evento (ninguna fila en la tabla base):
SELECT DISTINCT
minute, count(c.minute) OVER (ORDER BY minute) AS running_ct
FROM (
SELECT generate_series(date_trunc(''minute'', min("when"))
, max("when")
, interval ''1 min'')
FROM tbl
) m(minute)
LEFT JOIN (SELECT date_trunc(''minute'', "when") FROM tbl) c(minute) USING (minute)
ORDER BY 1;
Genere una fila por cada minuto en el marco de tiempo entre el primer y el último evento con
generate_series()
- aquí directamente basado en valores agregados de la subconsulta.LEFT JOIN
a todas las marcas de tiempo truncadas al minuto y al recuento.NULL
valoresNULL
(donde no existe una fila) no se agregan al conteo de ejecución.
Lo más rápido
Con CTE:
WITH cte AS (
SELECT date_trunc(''minute'', "when") AS minute, count(*) AS minute_ct
FROM tbl
GROUP BY 1
)
SELECT m.minute
, COALESCE(sum(cte.minute_ct) OVER (ORDER BY m.minute), 0) AS running_ct
FROM (
SELECT generate_series(min(minute), max(minute), interval ''1 min'')
FROM cte
) m(minute)
LEFT JOIN cte USING (minute)
ORDER BY 1;
Nuevamente, agregar y contar filas por minuto en el primer paso, omite la necesidad de
DISTINCT
posterior.Diferente de
count()
,sum()
puede devolverNULL
. Valor predeterminado a0
conCOALESCE
.
Con muchas filas y un índice sobre "when"
esta versión con una subconsulta fue la más rápida entre un par de variantes que probé con Postgres 9.1 - 9.4:
SELECT m.minute
, COALESCE(sum(c.minute_ct) OVER (ORDER BY m.minute), 0) AS running_ct
FROM (
SELECT generate_series(date_trunc(''minute'', min("when"))
, max("when")
, interval ''1 min'')
FROM tbl
) m(minute)
LEFT JOIN (
SELECT date_trunc(''minute'', "when") AS minute
, count(*) AS minute_ct
FROM tbl
GROUP BY 1
) c USING (minute)
ORDER BY 1;