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',
31 export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVideoViewerModel>>> {
36 @Column(DataType.DATE)
40 @Column(DataType.DATE)
52 @Default(DataType.UUIDV4)
54 @Column(DataType.UUID)
61 @ForeignKey(() => VideoModel)
65 @BelongsTo(() => VideoModel, {
73 @HasMany(() => LocalVideoViewerWatchSectionModel, {
79 WatchSections: LocalVideoViewerWatchSectionModel[]
81 static loadByUrl (url: string): Promise<MLocalVideoViewer> {
89 static loadFullById (id: number): Promise<MLocalVideoViewerWithWatchSections> {
93 model: VideoModel.unscoped(),
97 model: LocalVideoViewerWatchSectionModel.unscoped(),
107 static async getOverallStats (options: {
111 }): Promise<VideoStatsOverall> {
112 const { video, startDate, endDate } = options
114 const queryOptions = {
115 type: QueryTypes.SELECT as QueryTypes.SELECT,
116 replacements: { videoId: video.id } as any
119 if (startDate) queryOptions.replacements.startDate = startDate
120 if (endDate) queryOptions.replacements.endDate = endDate
122 const buildWatchTimePromise = () => {
123 let watchTimeDateWhere = ''
125 if (startDate) watchTimeDateWhere += ' AND "localVideoViewer"."startDate" >= :startDate'
126 if (endDate) watchTimeDateWhere += ' AND "localVideoViewer"."endDate" <= :endDate'
128 const watchTimeQuery = `SELECT ` +
129 `COUNT("localVideoViewer"."id") AS "totalViewers", ` +
130 `SUM("localVideoViewer"."watchTime") AS "totalWatchTime", ` +
131 `AVG("localVideoViewer"."watchTime") AS "averageWatchTime" ` +
132 `FROM "localVideoViewer" ` +
133 `INNER JOIN "video" ON "video"."id" = "localVideoViewer"."videoId" ` +
134 `WHERE "videoId" = :videoId ${watchTimeDateWhere}`
136 return LocalVideoViewerModel.sequelize.query<any>(watchTimeQuery, queryOptions)
139 const buildWatchPeakPromise = () => {
140 let watchPeakDateWhereStart = ''
141 let watchPeakDateWhereEnd = ''
144 watchPeakDateWhereStart += ' AND "localVideoViewer"."startDate" >= :startDate'
145 watchPeakDateWhereEnd += ' AND "localVideoViewer"."endDate" >= :startDate'
149 watchPeakDateWhereStart += ' AND "localVideoViewer"."startDate" <= :endDate'
150 watchPeakDateWhereEnd += ' AND "localVideoViewer"."endDate" <= :endDate'
153 // Add viewers that were already here, before our start date
154 const beforeWatchersQuery = startDate
155 // eslint-disable-next-line max-len
156 ? `SELECT COUNT(*) AS "total" FROM "localVideoViewer" WHERE "localVideoViewer"."startDate" < :startDate AND "localVideoViewer"."endDate" >= :startDate`
157 : `SELECT 0 AS "total"`
159 const watchPeakQuery = `WITH
160 "beforeWatchers" AS (${beforeWatchersQuery}),
161 "watchPeakValues" AS (
162 SELECT "startDate" AS "dateBreakpoint", 1 AS "inc"
163 FROM "localVideoViewer"
164 WHERE "videoId" = :videoId ${watchPeakDateWhereStart}
166 SELECT "endDate" AS "dateBreakpoint", -1 AS "inc"
167 FROM "localVideoViewer"
168 WHERE "videoId" = :videoId ${watchPeakDateWhereEnd}
170 SELECT "dateBreakpoint", "concurrent"
172 SELECT "dateBreakpoint", SUM(SUM("inc")) OVER (ORDER BY "dateBreakpoint") + (SELECT "total" FROM "beforeWatchers") AS "concurrent"
173 FROM "watchPeakValues"
174 GROUP BY "dateBreakpoint"
176 ORDER BY "concurrent" DESC
177 FETCH FIRST 1 ROW ONLY`
179 return LocalVideoViewerModel.sequelize.query<any>(watchPeakQuery, queryOptions)
182 const buildCountriesPromise = () => {
183 let countryDateWhere = ''
185 if (startDate) countryDateWhere += ' AND "localVideoViewer"."endDate" >= :startDate'
186 if (endDate) countryDateWhere += ' AND "localVideoViewer"."startDate" <= :endDate'
188 const countriesQuery = `SELECT country, COUNT(country) as viewers ` +
189 `FROM "localVideoViewer" ` +
190 `WHERE "videoId" = :videoId AND country IS NOT NULL ${countryDateWhere} ` +
191 `GROUP BY country ` +
192 `ORDER BY viewers DESC`
194 return LocalVideoViewerModel.sequelize.query<any>(countriesQuery, queryOptions)
197 const [ rowsWatchTime, rowsWatchPeak, rowsCountries ] = await Promise.all([
198 buildWatchTimePromise(),
199 buildWatchPeakPromise(),
200 buildCountriesPromise()
203 const viewersPeak = rowsWatchPeak.length !== 0
204 ? parseInt(rowsWatchPeak[0].concurrent) || 0
208 totalWatchTime: rowsWatchTime.length !== 0
209 ? Math.round(rowsWatchTime[0].totalWatchTime) || 0
211 averageWatchTime: rowsWatchTime.length !== 0
212 ? Math.round(rowsWatchTime[0].averageWatchTime) || 0
215 totalViewers: rowsWatchTime.length !== 0
216 ? Math.round(rowsWatchTime[0].totalViewers) || 0
220 viewersPeakDate: rowsWatchPeak.length !== 0 && viewersPeak !== 0
221 ? rowsWatchPeak[0].dateBreakpoint || null
224 countries: rowsCountries.map(r => ({
231 static async getRetentionStats (video: MVideo): Promise<VideoStatsRetention> {
232 const step = Math.max(Math.round(video.duration / 100), 1)
234 const query = `WITH "total" AS (SELECT COUNT(*) AS viewers FROM "localVideoViewer" WHERE "videoId" = :videoId) ` +
235 `SELECT serie AS "second", ` +
236 `(COUNT("localVideoViewer".id)::float / (SELECT GREATEST("total"."viewers", 1) FROM "total")) AS "retention" ` +
237 `FROM generate_series(0, ${video.duration}, ${step}) serie ` +
238 `LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId ` +
240 `SELECT 1 FROM "localVideoViewerWatchSection" ` +
241 `WHERE "localVideoViewer"."id" = "localVideoViewerWatchSection"."localVideoViewerId" ` +
242 `AND serie >= "localVideoViewerWatchSection"."watchStart" ` +
243 `AND serie <= "localVideoViewerWatchSection"."watchEnd"` +
248 const queryOptions = {
249 type: QueryTypes.SELECT as QueryTypes.SELECT,
250 replacements: { videoId: video.id }
253 const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions)
256 data: rows.map(r => ({
258 retentionPercent: parseFloat(r.retention) * 100
263 static async getTimeserieStats (options: {
265 metric: VideoStatsTimeserieMetric
268 }): Promise<VideoStatsTimeserie> {
269 const { video, metric } = options
271 const { groupInterval, startDate, endDate } = buildGroupByAndBoundaries(options.startDate, options.endDate)
273 const selectMetrics: { [ id in VideoStatsTimeserieMetric ]: string } = {
274 viewers: 'COUNT("localVideoViewer"."id")',
275 aggregateWatchTime: 'SUM("localVideoViewer"."watchTime")'
278 const intervalWhere: { [ id in VideoStatsTimeserieMetric ]: string } = {
279 // Viewer is still in the interval. Overlap algorithm
280 viewers: '"localVideoViewer"."startDate" <= "intervals"."endDate" ' +
281 'AND "localVideoViewer"."endDate" >= "intervals"."startDate"',
283 // We do an aggregation, so only sum things once. Arbitrary we use the end date for that purpose
284 aggregateWatchTime: '"localVideoViewer"."endDate" >= "intervals"."startDate" ' +
285 'AND "localVideoViewer"."endDate" <= "intervals"."endDate"'
288 const query = `WITH "intervals" AS (
290 "time" AS "startDate", "time" + :groupInterval::interval as "endDate"
292 generate_series(:startDate::timestamptz, :endDate::timestamptz, :groupInterval::interval) serie("time")
294 SELECT "intervals"."startDate" as "date", COALESCE(${selectMetrics[metric]}, 0) AS value
297 LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId
298 AND ${intervalWhere[metric]}
300 "intervals"."startDate"
302 "intervals"."startDate"`
304 const queryOptions = {
305 type: QueryTypes.SELECT as QueryTypes.SELECT,
314 const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions)
318 data: rows.map(r => ({
320 value: parseInt(r.value)
325 toActivityPubObject (this: MLocalVideoViewerWithWatchSections): WatchActionObject {
326 const location = this.country
329 addressCountry: this.country
337 duration: getActivityStreamDuration(this.watchTime),
338 startTime: this.startDate.toISOString(),
339 endTime: this.endDate.toISOString(),
341 object: this.Video.url,
343 actionStatus: 'CompletedActionStatus',
345 watchSections: this.WatchSections.map(w => ({
346 startTimestamp: w.watchStart,
347 endTimestamp: w.watchEnd