Блог

Главная - Вопросы по программированию - Как я могу рассчитать процент времени в среднем на эпизод в SQL?

Как я могу рассчитать процент времени в среднем на эпизод в SQL?

#sql

#sql

Вопрос:

У меня есть следующие таблицы:

 Episode
-----------------------------------
| Id | Name | Duration | SeasonId |
-----------------------------------
| 1  | ..   | 00:54:34 | 1        |
| 2  | ..   | 00:49:56 | 1        |
-----------------------------------

Profile
----------------------
| Id | Name | UserId |
----------------------
| 1  | ..   | 1      |
| 2  | ..   | 1      | 
----------------------

WatchHistory
------------------------------------
| ProfileId | EpisodeId | Progress |
------------------------------------
| 1         | 1         | 00:17:17 |
| 2         | 1         | 00:54:34 |
| 1         | 2         | 00:49:56 |
------------------------------------

Я хочу рассчитать средний процент профилей, просмотренных в каждом эпизоде, но, похоже, я не могу понять, как это сделать.

Для эпизода 1 профиль 1 просматривается примерно на 31,7 процента, профиль 2 просматривается на 100 процентов, поэтому эпизод 1 просматривается в среднем на 65,8 процента.

Результат должен выглядеть следующим образом:

 ------------------------------------------------------------------------
| EpisodeId | EpisodeName | EpisodeDuration | PercentageWatchedAverage |
------------------------------------------------------------------------
| 1         | ..          | 00:54:34        | 66                       |
| 2         | ..          | 00:49:56        | 100                      |
------------------------------------------------------------------------

Как я могу добиться этого результата?

Комментарии:

1. Что вы пробовали до сих пор? Где вы застряли? Какую СУБД вы используете?

2. Я пытался использовать предложение WITH , но это ни к чему не привело. Я отказался от всех своих предыдущих попыток. Я использую Sql Server Management Studio. Я могу сгенерировать все, что вы видите в вопросе, за исключением столбца процентов

3. Согласно вашей логике, если кто-то не смотрит ни одного эпизода, они не учитываются при расчете. Но если они смотрят 1 секунду эпизода, они. Вы уверены, что это та логика, которая вам нужна?

4. К какому типу данных относятся ваши столбцы Duration и Progress? Нужно ли учитывать, что люди пропускают части эпизода, используют быструю перемотку или пропускают, повторно просматривают одну и ту же часть эпизода более одного раза, смотрят весь эпизод, за исключением финальных титров, И т. Д.? (Или вы предполагаете, что все это учитывается в таблице WatchHistory?)

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

Ответ №1:

Вот начало… https://dbfiddle.uk/?rdbms=sqlserver_2019amp;fiddle=12f0a734716d16e666318ec54a705fbd

 SELECT
  Episode.id,
  AvgProgress.seconds * 100.0
  /
  DATEDIFF(second, 0, Episode.duration)
FROM
  Episode
LEFT JOIN
(
  SELECT
    EpisodeID,
    AVG(
      DATEDIFF(second, 0, Progress)
    )
      AS seconds
  FROM
    WatchHistory
  GROUP BY
    EpisodeID
)
  AS AvgProgress
    ON AvgProgress.EpisodeID = Episode.id
Метки: