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
118 dateWhere += ' AND "localVideoViewer"."startDate" >= :startDate'
119 queryOptions.replacements.startDate = startDate
123 dateWhere += ' AND "localVideoViewer"."endDate" <= :endDate'
124 queryOptions.replacements.endDate = endDate
127 const watchTimeQuery = `SELECT ` +
128 `COUNT("localVideoViewer"."id") AS "totalViewers", ` +
129 `SUM("localVideoViewer"."watchTime") AS "totalWatchTime", ` +
130 `AVG("localVideoViewer"."watchTime") AS "averageWatchTime" ` +
131 `FROM "localVideoViewer" ` +
132 `INNER JOIN "video" ON "video"."id" = "localVideoViewer"."videoId" ` +
133 `WHERE "videoId" = :videoId ${dateWhere}`
135 const watchTimePromise = LocalVideoViewerModel.sequelize.query<any>(watchTimeQuery, queryOptions)
137 const watchPeakQuery = `WITH "watchPeakValues" AS (
138 SELECT "startDate" AS "dateBreakpoint", 1 AS "inc"
139 FROM "localVideoViewer"
140 WHERE "videoId" = :videoId ${dateWhere}
142 SELECT "endDate" AS "dateBreakpoint", -1 AS "inc"
143 FROM "localVideoViewer"
144 WHERE "videoId" = :videoId ${dateWhere}
146 SELECT "dateBreakpoint", "concurrent"
148 SELECT "dateBreakpoint", SUM(SUM("inc")) OVER (ORDER BY "dateBreakpoint") AS "concurrent"
149 FROM "watchPeakValues"
150 GROUP BY "dateBreakpoint"
152 ORDER BY "concurrent" DESC
153 FETCH FIRST 1 ROW ONLY`
154 const watchPeakPromise = LocalVideoViewerModel.sequelize.query<any>(watchPeakQuery, queryOptions)
156 const countriesQuery = `SELECT country, COUNT(country) as viewers ` +
157 `FROM "localVideoViewer" ` +
158 `WHERE "videoId" = :videoId AND country IS NOT NULL ${dateWhere} ` +
159 `GROUP BY country ` +
160 `ORDER BY viewers DESC`
161 const countriesPromise = LocalVideoViewerModel.sequelize.query<any>(countriesQuery, queryOptions)
163 const [ rowsWatchTime, rowsWatchPeak, rowsCountries ] = await Promise.all([
169 const viewersPeak = rowsWatchPeak.length !== 0
170 ? parseInt(rowsWatchPeak[0].concurrent) || 0
174 totalWatchTime: rowsWatchTime.length !== 0
175 ? Math.round(rowsWatchTime[0].totalWatchTime) || 0
177 averageWatchTime: rowsWatchTime.length !== 0
178 ? Math.round(rowsWatchTime[0].averageWatchTime) || 0
181 totalViewers: rowsWatchTime.length !== 0
182 ? Math.round(rowsWatchTime[0].totalViewers) || 0
186 viewersPeakDate: rowsWatchPeak.length !== 0 && viewersPeak !== 0
187 ? rowsWatchPeak[0].dateBreakpoint || null
190 countries: rowsCountries.map(r => ({
197 static async getRetentionStats (video: MVideo): Promise<VideoStatsRetention> {
198 const step = Math.max(Math.round(video.duration / 100), 1)
200 const query = `WITH "total" AS (SELECT COUNT(*) AS viewers FROM "localVideoViewer" WHERE "videoId" = :videoId) ` +
201 `SELECT serie AS "second", ` +
202 `(COUNT("localVideoViewer".id)::float / (SELECT GREATEST("total"."viewers", 1) FROM "total")) AS "retention" ` +
203 `FROM generate_series(0, ${video.duration}, ${step}) serie ` +
204 `LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId ` +
206 `SELECT 1 FROM "localVideoViewerWatchSection" ` +
207 `WHERE "localVideoViewer"."id" = "localVideoViewerWatchSection"."localVideoViewerId" ` +
208 `AND serie >= "localVideoViewerWatchSection"."watchStart" ` +
209 `AND serie <= "localVideoViewerWatchSection"."watchEnd"` +
214 const queryOptions = {
215 type: QueryTypes.SELECT as QueryTypes.SELECT,
216 replacements: { videoId: video.id }
219 const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions)
222 data: rows.map(r => ({
224 retentionPercent: parseFloat(r.retention) * 100
229 static async getTimeserieStats (options: {
231 metric: VideoStatsTimeserieMetric
234 }): Promise<VideoStatsTimeserie> {
235 const { video, metric } = options
237 const { groupInterval, startDate, endDate } = buildGroupByAndBoundaries(options.startDate, options.endDate)
239 const selectMetrics: { [ id in VideoStatsTimeserieMetric ]: string } = {
240 viewers: 'COUNT("localVideoViewer"."id")',
241 aggregateWatchTime: 'SUM("localVideoViewer"."watchTime")'
244 const intervalWhere: { [ id in VideoStatsTimeserieMetric ]: string } = {
245 // Viewer is still in the interval. Overlap algorithm
246 viewers: '"localVideoViewer"."startDate" <= "intervals"."endDate" ' +
247 'AND "localVideoViewer"."endDate" >= "intervals"."startDate"',
249 // We do an aggregation, so only sum things once. Arbitrary we use the end date for that purpose
250 aggregateWatchTime: '"localVideoViewer"."endDate" >= "intervals"."startDate" ' +
251 'AND "localVideoViewer"."endDate" <= "intervals"."endDate"'
254 const query = `WITH "intervals" AS (
256 "time" AS "startDate", "time" + :groupInterval::interval as "endDate"
258 generate_series(:startDate::timestamptz, :endDate::timestamptz, :groupInterval::interval) serie("time")
260 SELECT "intervals"."startDate" as "date", COALESCE(${selectMetrics[metric]}, 0) AS value
263 LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId
264 AND ${intervalWhere[metric]}
266 "intervals"."startDate"
268 "intervals"."startDate"`
270 const queryOptions = {
271 type: QueryTypes.SELECT as QueryTypes.SELECT,
280 const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions)
284 data: rows.map(r => ({
286 value: parseInt(r.value)
291 toActivityPubObject (this: MLocalVideoViewerWithWatchSections): WatchActionObject {
292 const location = this.country
295 addressCountry: this.country
303 duration: getActivityStreamDuration(this.watchTime),
304 startTime: this.startDate.toISOString(),
305 endTime: this.endDate.toISOString(),
307 object: this.Video.url,
309 actionStatus: 'CompletedActionStatus',
311 watchSections: this.WatchSections.map(w => ({
312 startTimestamp: w.watchStart,
313 endTimestamp: w.watchEnd