Нахождение максимального количества строк, созданных за час?

Question

У меня есть таблица для хранения отзывов о товаре, как это:

Id -int
ProductId -int
Timestamp -datetime
Comments -text

Есть ли простой способ подсчитать и определить частоту отзывов продукта за 60 минут? то есть. Widget1 максимальное количество отзывов / час 55.

работа с sql05.

Answer 1

Я не знаю простого способа вычисления этой метрики, но, надеюсь, это поможет. Без какого-либо курсора SQL я бы сгенерировал таблицу SQL возможных интервалов с отметками времени начала и конца (2009-09-02T00:00 до 2009-09-02T00:59, 2009-09-02T00:01 до 2009-09-02T01:00 и т. Д.), А затем перекрестное соединение с использованием LINQ to SQL:

var rates = from r in db.Reviews
            from i in db.Intervals
            where i.Begin <= r.Timestamp && r.Timestamp <= i.End
            group r by i.Begin into reviews
            select reviews.Count();
var maxRate = rates.Max();

Я не пробовал код, но он должен помочь вам начать. Вы можете улучшить производительность, ограничив время проверки (последние 7 дней, 30 дней и т. Д.) Или сгенерировав меньше интервалов (возможно, начиная с четверть часа).

Answer 2

Другой вариант - использовать функции SQL DATEPART, например:

SELECT
    DAY(TimeStamp), MONTH(TimeStamp), YEAR(TimeStamp),
    DATEPART(HOUR, TimeStamp),
    COUNT(*)
FROM    
    dbo.Products
GROUP BY
    DAY(TimeStamp), MONTH(TimeStamp), YEAR(TimeStamp),
    DATEPART(HOUR, TimeStamp)
ORDER BY
    COUNT(*) DESC

Это дает вам не только максимальное количество просмотров за определенный час, но и все они, отсортированные по частоте. Имейте в виду: кроме решения RexM, это основано на "часовой" части вашей "метки времени" - так что, если у вас есть довольно много просмотров в 7:59 и еще один взрыв в 8:01, в моем решении, эти не могут быть показаны вместе (так как один час =7, а другой час =8).

Если вам нужен подход "любой 60-минутный интервал", используйте основную идею RexM (DATEDIFF с минутами <= 60).

Марк

Answer 3

Если вместо этого вы хотите "какой продукт получил наибольшее количество отзывов в 60-минутном блоке между dateX и dateY", то это немного сложнее.

Один из способов думать об этом - "для каждого отзыва за тот же период времени, сколько других обзоров для того же идентификатора продукта существует в следующие 60 минут". Если у вас есть такая логика, запрос становится более понятным:

SELECT TOP 1
    PR.ProductID,
    -- start of 60 minute block
    PR.Timestamp,
    ReviewCount = (
        SELECT COUNT(*)
        FROM ProductReviews PR1
        -- from parent time
        WHERE PR1.Timestamp >= PR.Timestamp
        -- until 60 mins later
        AND PR1.Timestamp <= DATEADD( minute, 60, PR.Timestamp )
        -- that matches ProductID
        AND PR1.ProductID = PR.ProductID
    )
FROM ProductReviews PR
-- in the last 24 hours
WHERE PR.Timestamp > ( GETDATE() - 1 )
ORDER BY ReviewCount DESC

Как это?

Answer 4

Если вы работаете с установленным 60-минутным блоком времени (например, последние 60 минут с этого момента), то это достаточно просто:

SELECT TOP 1
    PR.ProductID,
    COUNT(*)
FROM ProductReviews PR
WHERE PR.Timestamp BETWEEN DATEADD( minute, -60, GETDATE() ) AND GETDATE()
GROUP BY PR.ProductID
ORDER BY COUNT(*) DESC

Если вы хотите его в течение любого 60-минутного интервала, тогда это становится более сложным!

Answer 5

Если вы были довольны фиксированными часами для своих окон, я мог бы подумать о том, чтобы сделать это немного умнее, используя триггер. Триггер вставляет / обновляет в таблицу 'log' и просто различает вставку или обновление в зависимости от текущего времени.

Вы можете сочетать с этим любой другой подход, это добавит хороший слой кэширования.