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 (options: {
107 }): Promise<VideoStatsOverall> {
108 const { video, startDate, endDate } = options
110 const queryOptions = {
111 type: QueryTypes.SELECT as QueryTypes.SELECT,
112 replacements: { videoId: video.id } as any
115 if (startDate) queryOptions.replacements.startDate = startDate
116 if (endDate) queryOptions.replacements.endDate = endDate
118 const buildWatchTimePromise = () => {
119 let watchTimeDateWhere = ''
121 if (startDate) watchTimeDateWhere += ' AND "localVideoViewer"."startDate" >= :startDate'
122 if (endDate) watchTimeDateWhere += ' AND "localVideoViewer"."endDate" <= :endDate'
124 const watchTimeQuery = `SELECT ` +
125 `COUNT("localVideoViewer"."id") AS "totalViewers", ` +
126 `SUM("localVideoViewer"."watchTime") AS "totalWatchTime", ` +
127 `AVG("localVideoViewer"."watchTime") AS "averageWatchTime" ` +
128 `FROM "localVideoViewer" ` +
129 `INNER JOIN "video" ON "video"."id" = "localVideoViewer"."videoId" ` +
130 `WHERE "videoId" = :videoId ${watchTimeDateWhere}`
132 return LocalVideoViewerModel.sequelize.query<any>(watchTimeQuery, queryOptions)
135 const buildWatchPeakPromise = () => {
136 let watchPeakDateWhereStart = ''
137 let watchPeakDateWhereEnd = ''
140 watchPeakDateWhereStart += ' AND "localVideoViewer"."startDate" >= :startDate'
141 watchPeakDateWhereEnd += ' AND "localVideoViewer"."endDate" >= :startDate'
145 watchPeakDateWhereStart += ' AND "localVideoViewer"."startDate" <= :endDate'
146 watchPeakDateWhereEnd += ' AND "localVideoViewer"."endDate" <= :endDate'
149 // Add viewers that were already here, before our start date
150 const beforeWatchersQuery = startDate
151 // eslint-disable-next-line max-len
152 ? `SELECT COUNT(*) AS "total" FROM "localVideoViewer" WHERE "localVideoViewer"."startDate" < :startDate AND "localVideoViewer"."endDate" >= :startDate`
153 : `SELECT 0 AS "total"`
155 const watchPeakQuery = `WITH
156 "beforeWatchers" AS (${beforeWatchersQuery}),
157 "watchPeakValues" AS (
158 SELECT "startDate" AS "dateBreakpoint", 1 AS "inc"
159 FROM "localVideoViewer"
160 WHERE "videoId" = :videoId ${watchPeakDateWhereStart}
162 SELECT "endDate" AS "dateBreakpoint", -1 AS "inc"
163 FROM "localVideoViewer"
164 WHERE "videoId" = :videoId ${watchPeakDateWhereEnd}
166 SELECT "dateBreakpoint", "concurrent"
168 SELECT "dateBreakpoint", SUM(SUM("inc")) OVER (ORDER BY "dateBreakpoint") + (SELECT "total" FROM "beforeWatchers") AS "concurrent"
169 FROM "watchPeakValues"
170 GROUP BY "dateBreakpoint"
172 ORDER BY "concurrent" DESC
173 FETCH FIRST 1 ROW ONLY`
175 return LocalVideoViewerModel.sequelize.query<any>(watchPeakQuery, queryOptions)
178 const buildCountriesPromise = () => {
179 let countryDateWhere = ''
181 if (startDate) countryDateWhere += ' AND "localVideoViewer"."endDate" >= :startDate'
182 if (endDate) countryDateWhere += ' AND "localVideoViewer"."startDate" <= :endDate'
184 const countriesQuery = `SELECT country, COUNT(country) as viewers ` +
185 `FROM "localVideoViewer" ` +
186 `WHERE "videoId" = :videoId AND country IS NOT NULL ${countryDateWhere} ` +
187 `GROUP BY country ` +
188 `ORDER BY viewers DESC`
190 return LocalVideoViewerModel.sequelize.query<any>(countriesQuery, queryOptions)
193 const [ rowsWatchTime, rowsWatchPeak, rowsCountries ] = await Promise.all([
194 buildWatchTimePromise(),
195 buildWatchPeakPromise(),
196 buildCountriesPromise()
199 const viewersPeak = rowsWatchPeak.length !== 0
200 ? parseInt(rowsWatchPeak[0].concurrent) || 0
204 totalWatchTime: rowsWatchTime.length !== 0
205 ? Math.round(rowsWatchTime[0].totalWatchTime) || 0
207 averageWatchTime: rowsWatchTime.length !== 0
208 ? Math.round(rowsWatchTime[0].averageWatchTime) || 0
211 totalViewers: rowsWatchTime.length !== 0
212 ? Math.round(rowsWatchTime[0].totalViewers) || 0
216 viewersPeakDate: rowsWatchPeak.length !== 0 && viewersPeak !== 0
217 ? rowsWatchPeak[0].dateBreakpoint || null
220 countries: rowsCountries.map(r => ({
227 static async getRetentionStats (video: MVideo): Promise<VideoStatsRetention> {
228 const step = Math.max(Math.round(video.duration / 100), 1)
230 const query = `WITH "total" AS (SELECT COUNT(*) AS viewers FROM "localVideoViewer" WHERE "videoId" = :videoId) ` +
231 `SELECT serie AS "second", ` +
232 `(COUNT("localVideoViewer".id)::float / (SELECT GREATEST("total"."viewers", 1) FROM "total")) AS "retention" ` +
233 `FROM generate_series(0, ${video.duration}, ${step}) serie ` +
234 `LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId ` +
236 `SELECT 1 FROM "localVideoViewerWatchSection" ` +
237 `WHERE "localVideoViewer"."id" = "localVideoViewerWatchSection"."localVideoViewerId" ` +
238 `AND serie >= "localVideoViewerWatchSection"."watchStart" ` +
239 `AND serie <= "localVideoViewerWatchSection"."watchEnd"` +
244 const queryOptions = {
245 type: QueryTypes.SELECT as QueryTypes.SELECT,
246 replacements: { videoId: video.id }
249 const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions)
252 data: rows.map(r => ({
254 retentionPercent: parseFloat(r.retention) * 100
259 static async getTimeserieStats (options: {
261 metric: VideoStatsTimeserieMetric
264 }): Promise<VideoStatsTimeserie> {
265 const { video, metric } = options
267 const { groupInterval, startDate, endDate } = buildGroupByAndBoundaries(options.startDate, options.endDate)
269 const selectMetrics: { [ id in VideoStatsTimeserieMetric ]: string } = {
270 viewers: 'COUNT("localVideoViewer"."id")',
271 aggregateWatchTime: 'SUM("localVideoViewer"."watchTime")'
274 const intervalWhere: { [ id in VideoStatsTimeserieMetric ]: string } = {
275 // Viewer is still in the interval. Overlap algorithm
276 viewers: '"localVideoViewer"."startDate" <= "intervals"."endDate" ' +
277 'AND "localVideoViewer"."endDate" >= "intervals"."startDate"',
279 // We do an aggregation, so only sum things once. Arbitrary we use the end date for that purpose
280 aggregateWatchTime: '"localVideoViewer"."endDate" >= "intervals"."startDate" ' +
281 'AND "localVideoViewer"."endDate" <= "intervals"."endDate"'
284 const query = `WITH "intervals" AS (
286 "time" AS "startDate", "time" + :groupInterval::interval as "endDate"
288 generate_series(:startDate::timestamptz, :endDate::timestamptz, :groupInterval::interval) serie("time")
290 SELECT "intervals"."startDate" as "date", COALESCE(${selectMetrics[metric]}, 0) AS value
293 LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId
294 AND ${intervalWhere[metric]}
296 "intervals"."startDate"
298 "intervals"."startDate"`
300 const queryOptions = {
301 type: QueryTypes.SELECT as QueryTypes.SELECT,
310 const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions)
314 data: rows.map(r => ({
316 value: parseInt(r.value)
321 toActivityPubObject (this: MLocalVideoViewerWithWatchSections): WatchActionObject {
322 const location = this.country
325 addressCountry: this.country
333 duration: getActivityStreamDuration(this.watchTime),
334 startTime: this.startDate.toISOString(),
335 endTime: this.endDate.toISOString(),
337 object: this.Video.url,
339 actionStatus: 'CompletedActionStatus',
341 watchSections: this.WatchSections.map(w => ({
342 startTimestamp: w.watchStart,
343 endTimestamp: w.watchEnd