Подсчёт количества записей принадлежащих промежутку времени

Question

Есть таблица, в которой хранятся просмотры:

+----+------------------+---------+----------------------------+
| id | media_content_id | user_id |                   view_date|
+----+------------------+---------+----------------------------+
|  1 |                1 |       6 | 2020-11-11 02:25:48.166839 |
|  2 |                1 |       3 | 2020-11-11 02:25:48.167086 |
|  3 |                1 |       9 | 2020-11-10 23:25:48.167342 |
+----+------------------+---------+----------------------------+

Класс используемый в EF:

[Table("media_views")]
public class MediaView
{
    [Key]
    [Column("id")]
    public int Id { get; set; }

    [ForeignKey("media_contents")]
    [Column("media_content_id")]
    public int MediaContentId {get; set;}

    [ForeignKey("user")]
    [Column("user_id")]
    public int UserId { get; set; }

    [Column("view_date")]
    public DateTime DateTime { get; set; }
}

Нужно получить список данных в виде промежутков времени и количества просмотров в них.

Я решил эту задачу следующим образом:

var iteratingDateTime = videoViews.OrderBy(vv => vv.DateTime).First().DateTime;
var currentDateTime = this.dateTimeProvider.Now();
var viewsAtSpan = new Dictionary<DateTime, int>();
var hoursSpan = 1;

while (iteratingDateTime < currentDateTime)
{
    var views = videoViews.Where(
        vv => vv.DateTime > iteratingDateTime && 
              vv.DateTime < iteratingDateTime.AddHours(hoursSpan));
    viewsAtSpan.Add(iteratingDateTime, views.Count());
    iteratingDateTime = iteratingDateTime.AddHours(hoursSpan);
}

Но очевидно, что это очень плохое решение, потому что будет огромное количество обращений к базе данных. Я могу получить данные с помощью SQL-запроса:

SELECT media_content_id, DATE(view_date), HOUR(view_date), COUNT(*) FROM media_views
WHERE media_content_id = 1
GROUP BY media_content_id, Date(view_date), HOUR(view_date);

+------------------+-----------------+-----------------+---------+
| media_content_id | Date(view_date) | Hour(view_date) | COUNT(*)|
+------------------+-----------------+-----------------+---------+
|                1 |      2020-11-09 |              13 |       1 |
|                1 |      2020-11-09 |              14 |       3 |
|                1 |      2020-11-09 |              15 |       1 |
+------------------+-----------------+-----------------+---------+

Но никак не могу сообразить как получить похожий результат с помощью Entity framework core.

Answer 1

Есть решение для ситуации, когда временные промежутки равны целому дню:

Я создал дополнительный класс, который буду заполнять данными:

public class AggregatedMediaViewsOverTime
{
    public int MediaContentId { get; set; }

    public int Views { get; set; }

    public DateTime Date { get; set; }
}

Получить данные можно c помощью такого LINQ запроса:

var test = from mv in db.MediaViews
           where mv.MediaContentId == mediaContentId
           group mv by mv.DateTime.Date into grp
           select new AggregatedMediaViewsOverTime()
           {
               Views = grp.Count(),
               MediaContentId = mediaContentId,
               Date = grp.Key.Date
           };

или в функциональной форме:

var test2 = db.MediaViews
    .Where(mv => mv.MediaContentId == mediaContentId)
    .GroupBy(mv => mv.DateTime.Date)
    .Select(grp => new AggregatedMediaViewsOverTime()
    {
        Views = grp.Count(),
        MediaContentId = mediaContentId,
        Date = grp.Key.Date
    });

Данный запрос конвертируется в следующий SQL запрос:

SELECT COUNT(*) AS `Views`, @__mediaContentId_0 AS `MediaContentId`, CONVERT(CONVERT(`m`.`view_date`, date), date) AS `Date`
FROM `media_views` AS `m`
WHERE `m`.`media_content_id` = @__mediaContentId_0
GROUP BY CONVERT(`m`.`view_date`, date)

БЛОГ НА HUSL

Подсчёт количества записей принадлежащих промежутку времени

Ответы (1 шт):