database - tables - update statistics sql server

¿Cómo encontrar el período de una hora con la mayoría de los puntos de datos?

Question

Tengo una tabla de base de datos con cientos de miles de publicaciones en el foro, y me gustaría saber qué período de una hora contiene la mayor cantidad de publicaciones.

Podía avanzar lentamente un minuto a la vez, manteniendo una serie de marcas de tiempo y haciendo un seguimiento de qué hora tenía más, pero creo que hay una forma mucho mejor de hacerlo. Realizaré esta operación en un año de publicaciones, por lo que consultar cada minuto en un año parece bastante horrible.

Idealmente, habría una manera de hacer esto dentro de una única consulta de base de datos.

Answer 1

Aquí hay una pequeña variación en la implementación de Josh. Esto pasa de inmediato a la tabla y usa una autocomunicación en sí misma buscando cualquier publicación dentro de una hora de esa publicación.

select top 1 posts.DateCreated, count (posts.datecreated),
min(minutes.DateCreated) as MinPostDate,
max(minutes.datecreated) as MaxPostDate
from posts Minutes   
left join posts on posts.datecreated >= minutes.DateCreated 
AND posts.datecreated < dateadd(hour, 1, Minutes.DateCreated)
group by posts.DateCreated
order by count(posts.datecreated) desc

Desde una perspectiva de rendimiento en una tabla con solo 6 filas, su método, que utilizó la función para generar la tabla intermutada, tardó 16 segundos frente a esta, que fue subsecuente.

No estoy seguro si sería posible usar esto para perder un marco de tiempo válido ya que el intervalo de tiempo se basa en el desplazamiento de cada publicación.

Answer 2

Binning funcionará si desea ver intervalos como 10:00 - 11:00. Sin embargo, si tuvo una súbita oleada de interés de 10:30 a 11:30, se dividirá en dos intervalos y, por lo tanto, puede ocultarse por un número menor de coincidencias que se ajustaron por completo en una sola hora.

La única forma de evitar este problema es generar una lista ordenada por tiempo y paso por ella. Algo como esto:

max = 0; maxTime = 0
for each $item in the list:
   push $item onto queue
   while head of queue is more than an hour before $item
      drop queue head.
   if queue.count > max then max = queue.count; maxTime = $item.time

De esta forma, solo necesita mantener una ventana de 1 hora en memoria en lugar de toda la lista.

Answer 3

Esto funcionó en una pequeña base de datos MS-SQL de prueba.

SELECT TOP 1 id, date_entered,
  (SELECT COUNT(*)
   FROM   dbo.notes AS n2
   WHERE n2.date_entered >= n.date_entered 
   AND n2.date_entered < Dateadd(hh, 1, n.date_entered)) AS num
FROM  dbo.notes n
ORDER BY num DESC

Esto no es muy eficiente, los controles se basan en una hora de cada publicación.

For MYSQL 

SELECT ID,f.Date, (SELECT COUNT(*)
FROM Forum AS f2
WHERE f2.Date >= f.Date AND f2.Date < Date_ADD(f.Date, INTERVAL 1 HOUR)) As num
FROM Forum AS f
ORDER BY num
LIMIT 0,1

Answer 4

Si mysql:

select substr( timestamp, 1, 16 ) as hour, count(*) as count from forum_posts group by hour order by count desc limit 1;

editar: no estoy seguro si la pregunta original significa cualquier posible período de 60 minutos

Answer 5

Si usas MySQL:

SELECT DATE(postDate), HOUR(postDate), COUNT(*) AS n
FROM posts
GROUP BY DATE(postDate), HOUR(postDate)
ORDER BY n DESC
LIMIT 1

Answer 6

Esto da como resultado una consulta de base de datos O (n) y una búsqueda de mayor tiempo O (n), para una complejidad total de O (2n) (que, por supuesto, sigue siendo O (n)):

Use un comando count count en SQL que ''bin'' elementos para usted en incrementos de minutos.

Entonces ejecutarías la consulta de recuento en esta tabla:

time
1
2      
4
3
3
2
4
1
3
2

Y volvería:

0 1
1 1
2 3
3 3
4 2

Contando cada artículo.

Sospecho que puedes hacer lo mismo con tu mesa, y ponerlos por minuto, luego ejecutar un algoritmo sobre eso.

SELECT customer_name, COUNT(DISTINCT city) as "Distinct Cities"
FROM customers
GROUP BY customer_name;

De este tutorial en conteo: http://www.techonthenet.com/sql/count.php (cerca del final).

Aquí hay una página similar del manual de MySQL: http://dev.mysql.com/doc/refman/5.1/en/counting-rows.html

Entonces, si tiene una tabla con una fecha de tiempo en ella (al minuto, permitiendo que la agrupación ocurra por minutos):

datetime (yyyymmddhhmm)
200901121435
200901121538
200901121435
200901121538
200901121435
200901121538
200901121538
200901121435
200901121435
200901121538
200901121435
200901121435

Entonces el SQL

SELECT datetime, COUNT(DISTINCT datetime) as "Date Time"
FROM post
GROUP BY datetime;

debería regresar

200901121435 7
200901121538 5

Deberá seguir procesando esto, pero el trabajo arduo de agrupar y contar ya está hecho, y solo dará como resultado algo más de 500 mil filas por año (60 minutos, 24 horas, 365 días)

El procesamiento posterior sería:

Start at time T = first post time.
Set greatestTime = T
Sum all counts between T and T+one hour --> currentHourCount and greatestHourCount
While records exist past T+one hour
   Increment T by one minute.
   While the first element is prior to time T, subtract it
   while the last element is before time T+ one hour, add it
   If currentHourCount > greatestHourCount then
      greatestHourCount = currentHourCount
      greatestTime = T
end while

-Adán

Answer 7

Esto lo hará.

SELECCIONAR DateOfEvent HourBenence, DATEADD (hh, 1, DateOfEvent)) HourEnd, COUNT (*) AS NumEventsPerHour FROM tEvents COMO UNIRSE a eventos como B ON A.DateOfEvent> = B.DateOfEvents AND DATEADD (hh, 1, A.DateOfEvent) < = B.DateOfEvent GROUP POR A.DateOfEvent

Answer 8

SELECT DATEPART(hour, PostDateTime) AS HourOfDay, COUNT(*) AS ForumPosts FROM Posts GROUP BY DATEPART(hour, PostDateTime)

Answer 9

Trate la marca de tiempo de cada publicación como el inicio de dicha hora y cuente todas las demás publicaciones que caigan dentro de esa hora, incluida la publicación que la inició. Ordene las horas resultantes en orden descendente por la cantidad de publicaciones en cada una de ellas.

Una vez hecho esto, encontrará la "hora" más alta que tiene la mayor cantidad de publicaciones, pero este período de tiempo puede no ser exactamente de una hora, puede ser más corto (pero nunca más).

Para obtener un período "más bonito", puede calcular cuánto tiempo realmente es, dividir por dos, y ajustar el inicio del período en esa cantidad y el final hacia adelante, esto "centrará" las publicaciones dentro de la hora. Se garantiza que este ajuste no incluirá ninguna publicación nueva, por lo que el recuento sigue siendo válido. Si las publicaciones están lo suficientemente cerca como para incluirlas repentinamente en el período posterior a la expansión a una hora, un punto anterior tendría "la mayor cantidad de publicaciones" en lugar de la que seleccionó.

Si esta es una pregunta de SQL, puede reutilizar el SQL que Josh publicó aquí , simplemente reemplace la tabla de Minutos con otro enlace a su tabla de publicaciones.

Otro método que puede usar es usar una ventana deslizante.

Primero ordena todas las publicaciones de acuerdo con la marca de tiempo. Mantenga un registro de las publicaciones usando una lista, una lista vinculada podría ser utilizada para esto.

Ahora, para cada publicación, agréguela al final de la lista. Luego, para cada publicación desde el inicio de la lista, si esa publicación es más de una hora antes de la publicación que acaba de agregar, elimínela de la lista.

Después de hacer esa operación de 2 pasos para una nueva publicación en la lista, verifique si el número de publicaciones en la lista es más que un máximo anterior, y si lo es, haga una copia de la lista o al menos almacene la publicación. acabas de agregar

Una vez que haya terminado, obtendrá la "copia de la lista" con la mayor cantidad de publicaciones en una hora, o la publicación que corresponda al final de una ventana de 1 hora que contenga la mayor cantidad de publicaciones.

Pseudo-código:

initialize posts-window-list to empty list
for each post in sorted-posts-list:
    add post to end of posts-window-list
    for each other-post from start of posts-window-list:
        if other-post is more than one hour older than post, remove it
        otherwise, end this inner loop
    if number of posts in list is more than previous maximum:
        make copy of list, this is the new maximum

Answer 10

Con una tabla llena de cada minuto del año, le interesan los Minutes y una columna de Posts con una columna Time :

select top 1 minutes.time, count (posts.time)
from Minutes
   left join posts on posts.time >= minutes.time AND posts.time < dateadd(hour, 1, Minutes.Time)
group by minutes.time
order by count (posts.time) desc

Para resolver la generación de la tabla de minutos, puede usar una función como ufn_GenerateIntegers. Entonces la función se vuelve

select top 5 minutes.time, count (posts.time)
from (select dateadd(minute, IntValue, ''2008-01-01'') as Time from ufn_GenerateIntegers(525600)) Minutes
   left join posts on posts.time >= minutes.time AND posts.time < dateadd(hour, 1, Minutes.Time)
group by minutes.time
order by count(posts.time) desc

Acabo de hacer una prueba con alrededor de 5000 publicaciones al azar y tardé 16 segundos en mi máquina. Por lo tanto, no es trivial, pero no ridícula para la consulta puntual ocasional. Afortunadamente, este es un punto de datos que puede calcular uno al día o incluso una vez al mes y caché si desea mostrar el valor con frecuencia.

Eche un vistazo a la mejora de lassevk .