plan - optimizar consultas sql server 2014

PostgreSQL: conteo de filas en ejecución para una consulta ''por minuto''

Question

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

Answer 1

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 quiere GROUP BY rodajas por minuto.

• count() se usa típicamente como función agregada simple. Agregar una cláusula OVER convierte en una función de ventana . Omita PARTITION 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 define ORDER BY . Cito el manual :

  La opción de marco predeterminada es RANGE UNBOUNDED PRECEDING , que es lo mismo que RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW . Con ORDER BY , establece que el marco sea todas las filas desde el inicio de la partición hasta el último par ORDER BY la fila actual.

  Y eso pasa a ser exactamente lo que necesitas.

• Use count(*) lugar de count(id) . Se ajusta mejor a su pregunta ("recuento de filas"). Por lo general, es un poco más rápido que count(id) . Y, aunque podemos suponer que id NOT NULL es NOT NULL , no se ha especificado en la pregunta, por lo que count(id) es incorrecto , estrictamente hablando, porque los valores NULL no se cuentan con count(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 ventana count(*) solo vería 1 fila por minuto de esta manera.
  Sin embargo, puede SELECT DISTINCT porque DISTINCT se aplica después de las funciones de ventana.

• ORDER BY 1 es solo una abreviatura de ORDER BY date_trunc(''minute'', "when") aquí.
  1 es una referencia de referencia de posición a la primera expresión en la lista SELECT .

• 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 el SELECT 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 valores NULL (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 devolver NULL . Valor predeterminado a 0 con COALESCE .

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;