1 import { QueryTypes } from 'sequelize'
2 import { AllowNull, BelongsTo, Column, CreatedAt, DataType, Default, ForeignKey, HasMany, IsUUID, Model, Table } from 'sequelize-typescript'
3 import { getActivityStreamDuration } from '@server/lib/activitypub/activity'
4 import { buildGroupByAndBoundaries } from '@server/lib/timeserie'
5 import { MLocalVideoViewer, MLocalVideoViewerWithWatchSections, MVideo } from '@server/types/models'
6 import { VideoStatsOverall, VideoStatsRetention, VideoStatsTimeserie, VideoStatsTimeserieMetric, WatchActionObject } from '@shared/models'
7 import { AttributesOnly } from '@shared/typescript-utils'
8 import { VideoModel } from '../video/video'
9 import { LocalVideoViewerWatchSectionModel } from './local-video-viewer-watch-section'
13 * Aggregate viewers of local videos only to display statistics to video owners
14 * A viewer is a user that watched one or multiple sections of a specific video inside a time window
19 tableName: 'localVideoViewer',
27 export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVideoViewerModel>>> {
32 @Column(DataType.DATE)
36 @Column(DataType.DATE)
48 @Default(DataType.UUIDV4)
50 @Column(DataType.UUID)
57 @ForeignKey(() => VideoModel)
61 @BelongsTo(() => VideoModel, {
69 @HasMany(() => LocalVideoViewerWatchSectionModel, {
75 WatchSections: LocalVideoViewerWatchSectionModel[]
77 static loadByUrl (url: string): Promise<MLocalVideoViewer> {
85 static loadFullById (id: number): Promise<MLocalVideoViewerWithWatchSections> {
89 model: VideoModel.unscoped(),
93 model: LocalVideoViewerWatchSectionModel.unscoped(),
103 static async getOverallStats (video: MVideo): Promise<VideoStatsOverall> {
105 type: QueryTypes.SELECT as QueryTypes.SELECT,
106 replacements: { videoId: video.id }
109 const watchTimeQuery = `SELECT ` +
110 `SUM("localVideoViewer"."watchTime") AS "totalWatchTime", ` +
111 `AVG("localVideoViewer"."watchTime") AS "averageWatchTime" ` +
112 `FROM "localVideoViewer" ` +
113 `INNER JOIN "video" ON "video"."id" = "localVideoViewer"."videoId" ` +
114 `WHERE "videoId" = :videoId`
116 const watchTimePromise = LocalVideoViewerModel.sequelize.query<any>(watchTimeQuery, options)
118 const watchPeakQuery = `WITH "watchPeakValues" AS (
119 SELECT "startDate" AS "dateBreakpoint", 1 AS "inc"
120 FROM "localVideoViewer"
121 WHERE "videoId" = :videoId
123 SELECT "endDate" AS "dateBreakpoint", -1 AS "inc"
124 FROM "localVideoViewer"
125 WHERE "videoId" = :videoId
127 SELECT "dateBreakpoint", "concurrent"
129 SELECT "dateBreakpoint", SUM(SUM("inc")) OVER (ORDER BY "dateBreakpoint") AS "concurrent"
130 FROM "watchPeakValues"
131 GROUP BY "dateBreakpoint"
133 ORDER BY "concurrent" DESC
134 FETCH FIRST 1 ROW ONLY`
135 const watchPeakPromise = LocalVideoViewerModel.sequelize.query<any>(watchPeakQuery, options)
137 const commentsQuery = `SELECT COUNT(*) AS comments FROM "videoComment" WHERE "videoId" = :videoId`
138 const commentsPromise = LocalVideoViewerModel.sequelize.query<any>(commentsQuery, options)
140 const countriesQuery = `SELECT country, COUNT(country) as viewers ` +
141 `FROM "localVideoViewer" ` +
142 `WHERE "videoId" = :videoId AND country IS NOT NULL ` +
143 `GROUP BY country ` +
144 `ORDER BY viewers DESC`
145 const countriesPromise = LocalVideoViewerModel.sequelize.query<any>(countriesQuery, options)
147 const [ rowsWatchTime, rowsWatchPeak, rowsComment, rowsCountries ] = await Promise.all([
155 totalWatchTime: rowsWatchTime.length !== 0
156 ? Math.round(rowsWatchTime[0].totalWatchTime) || 0
158 averageWatchTime: rowsWatchTime.length !== 0
159 ? Math.round(rowsWatchTime[0].averageWatchTime) || 0
162 viewersPeak: rowsWatchPeak.length !== 0
163 ? parseInt(rowsWatchPeak[0].concurrent) || 0
165 viewersPeakDate: rowsWatchPeak.length !== 0
166 ? rowsWatchPeak[0].dateBreakpoint || null
171 dislikes: video.dislikes,
173 comments: rowsComment.length !== 0
174 ? parseInt(rowsComment[0].comments) || 0
177 countries: rowsCountries.map(r => ({
184 static async getRetentionStats (video: MVideo): Promise<VideoStatsRetention> {
185 const step = Math.max(Math.round(video.duration / 100), 1)
187 const query = `WITH "total" AS (SELECT COUNT(*) AS viewers FROM "localVideoViewer" WHERE "videoId" = :videoId) ` +
188 `SELECT serie AS "second", ` +
189 `(COUNT("localVideoViewer".id)::float / (SELECT GREATEST("total"."viewers", 1) FROM "total")) AS "retention" ` +
190 `FROM generate_series(0, ${video.duration}, ${step}) serie ` +
191 `LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId ` +
193 `SELECT 1 FROM "localVideoViewerWatchSection" ` +
194 `WHERE "localVideoViewer"."id" = "localVideoViewerWatchSection"."localVideoViewerId" ` +
195 `AND serie >= "localVideoViewerWatchSection"."watchStart" ` +
196 `AND serie <= "localVideoViewerWatchSection"."watchEnd"` +
201 const queryOptions = {
202 type: QueryTypes.SELECT as QueryTypes.SELECT,
203 replacements: { videoId: video.id }
206 const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions)
209 data: rows.map(r => ({
211 retentionPercent: parseFloat(r.retention) * 100
216 static async getTimeserieStats (options: {
218 metric: VideoStatsTimeserieMetric
221 }): Promise<VideoStatsTimeserie> {
222 const { video, metric } = options
224 const { groupInterval, startDate, endDate } = buildGroupByAndBoundaries(options.startDate, options.endDate)
226 const selectMetrics: { [ id in VideoStatsTimeserieMetric ]: string } = {
227 viewers: 'COUNT("localVideoViewer"."id")',
228 aggregateWatchTime: 'SUM("localVideoViewer"."watchTime")'
231 const query = `WITH "intervals" AS (
233 "time" AS "startDate", "time" + :groupInterval::interval as "endDate"
235 generate_series(:startDate::timestamptz, :endDate::timestamptz, :groupInterval::interval) serie("time")
237 SELECT "intervals"."startDate" as "date", COALESCE(${selectMetrics[metric]}, 0) AS value
240 LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId
241 AND "localVideoViewer"."startDate" >= "intervals"."startDate" AND "localVideoViewer"."startDate" <= "intervals"."endDate"
243 "intervals"."startDate"
245 "intervals"."startDate"`
247 const queryOptions = {
248 type: QueryTypes.SELECT as QueryTypes.SELECT,
257 const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions)
261 data: rows.map(r => ({
263 value: parseInt(r.value)
268 toActivityPubObject (this: MLocalVideoViewerWithWatchSections): WatchActionObject {
269 const location = this.country
272 addressCountry: this.country
280 duration: getActivityStreamDuration(this.watchTime),
281 startTime: this.startDate.toISOString(),
282 endTime: this.endDate.toISOString(),
284 object: this.Video.url,
286 actionStatus: 'CompletedActionStatus',
288 watchSections: this.WatchSections.map(w => ({
289 startTimestamp: w.watchStart,
290 endTimestamp: w.watchEnd