时间戳

首页 > 解决方案 > 如何计算 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                      |
------------------------------------------------------------------------

我怎样才能完成这个结果？

标签： sql

解决方案

这是一个初学者...... https://dbfiddle.uk/?rdbms=sqlserver_2019&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

推荐阅读