aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/view/local-video-viewer.ts
diff options
context:
space:
mode:
Diffstat (limited to 'server/models/view/local-video-viewer.ts')
-rw-r--r--server/models/view/local-video-viewer.ts368
1 files changed, 0 insertions, 368 deletions
diff --git a/server/models/view/local-video-viewer.ts b/server/models/view/local-video-viewer.ts
deleted file mode 100644
index c7ac51a03..000000000
--- a/server/models/view/local-video-viewer.ts
+++ /dev/null
@@ -1,368 +0,0 @@
1import { QueryTypes } from 'sequelize'
2import { AllowNull, BelongsTo, Column, CreatedAt, DataType, Default, ForeignKey, HasMany, IsUUID, Model, Table } from 'sequelize-typescript'
3import { getActivityStreamDuration } from '@server/lib/activitypub/activity'
4import { buildGroupByAndBoundaries } from '@server/lib/timeserie'
5import { MLocalVideoViewer, MLocalVideoViewerWithWatchSections, MVideo } from '@server/types/models'
6import { VideoStatsOverall, VideoStatsRetention, VideoStatsTimeserie, VideoStatsTimeserieMetric, WatchActionObject } from '@shared/models'
7import { AttributesOnly } from '@shared/typescript-utils'
8import { VideoModel } from '../video/video'
9import { LocalVideoViewerWatchSectionModel } from './local-video-viewer-watch-section'
10
11/**
12 *
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
15 *
16 */
17
18@Table({
19 tableName: 'localVideoViewer',
20 updatedAt: false,
21 indexes: [
22 {
23 fields: [ 'videoId' ]
24 },
25 {
26 fields: [ 'url' ],
27 unique: true
28 }
29 ]
30})
31export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVideoViewerModel>>> {
32 @CreatedAt
33 createdAt: Date
34
35 @AllowNull(false)
36 @Column(DataType.DATE)
37 startDate: Date
38
39 @AllowNull(false)
40 @Column(DataType.DATE)
41 endDate: Date
42
43 @AllowNull(false)
44 @Column
45 watchTime: number
46
47 @AllowNull(true)
48 @Column
49 country: string
50
51 @AllowNull(false)
52 @Default(DataType.UUIDV4)
53 @IsUUID(4)
54 @Column(DataType.UUID)
55 uuid: string
56
57 @AllowNull(false)
58 @Column
59 url: string
60
61 @ForeignKey(() => VideoModel)
62 @Column
63 videoId: number
64
65 @BelongsTo(() => VideoModel, {
66 foreignKey: {
67 allowNull: false
68 },
69 onDelete: 'CASCADE'
70 })
71 Video: VideoModel
72
73 @HasMany(() => LocalVideoViewerWatchSectionModel, {
74 foreignKey: {
75 allowNull: false
76 },
77 onDelete: 'cascade'
78 })
79 WatchSections: LocalVideoViewerWatchSectionModel[]
80
81 static loadByUrl (url: string): Promise<MLocalVideoViewer> {
82 return this.findOne({
83 where: {
84 url
85 }
86 })
87 }
88
89 static loadFullById (id: number): Promise<MLocalVideoViewerWithWatchSections> {
90 return this.findOne({
91 include: [
92 {
93 model: VideoModel.unscoped(),
94 required: true
95 },
96 {
97 model: LocalVideoViewerWatchSectionModel.unscoped(),
98 required: true
99 }
100 ],
101 where: {
102 id
103 }
104 })
105 }
106
107 static async getOverallStats (options: {
108 video: MVideo
109 startDate?: string
110 endDate?: string
111 }): Promise<VideoStatsOverall> {
112 const { video, startDate, endDate } = options
113
114 const queryOptions = {
115 type: QueryTypes.SELECT as QueryTypes.SELECT,
116 replacements: { videoId: video.id } as any
117 }
118
119 if (startDate) queryOptions.replacements.startDate = startDate
120 if (endDate) queryOptions.replacements.endDate = endDate
121
122 const buildTotalViewersPromise = () => {
123 let totalViewersDateWhere = ''
124
125 if (startDate) totalViewersDateWhere += ' AND "localVideoViewer"."endDate" >= :startDate'
126 if (endDate) totalViewersDateWhere += ' AND "localVideoViewer"."startDate" <= :endDate'
127
128 const totalViewersQuery = `SELECT ` +
129 `COUNT("localVideoViewer"."id") AS "totalViewers" ` +
130 `FROM "localVideoViewer" ` +
131 `WHERE "videoId" = :videoId ${totalViewersDateWhere}`
132
133 return LocalVideoViewerModel.sequelize.query<any>(totalViewersQuery, queryOptions)
134 }
135
136 const buildWatchTimePromise = () => {
137 let watchTimeDateWhere = ''
138
139 // We know this where is not exact
140 // But we prefer to take into account only watch section that started and ended **in** the interval
141 if (startDate) watchTimeDateWhere += ' AND "localVideoViewer"."startDate" >= :startDate'
142 if (endDate) watchTimeDateWhere += ' AND "localVideoViewer"."endDate" <= :endDate'
143
144 const watchTimeQuery = `SELECT ` +
145 `SUM("localVideoViewer"."watchTime") AS "totalWatchTime", ` +
146 `AVG("localVideoViewer"."watchTime") AS "averageWatchTime" ` +
147 `FROM "localVideoViewer" ` +
148 `WHERE "videoId" = :videoId ${watchTimeDateWhere}`
149
150 return LocalVideoViewerModel.sequelize.query<any>(watchTimeQuery, queryOptions)
151 }
152
153 const buildWatchPeakPromise = () => {
154 let watchPeakDateWhereStart = ''
155 let watchPeakDateWhereEnd = ''
156
157 if (startDate) {
158 watchPeakDateWhereStart += ' AND "localVideoViewer"."startDate" >= :startDate'
159 watchPeakDateWhereEnd += ' AND "localVideoViewer"."endDate" >= :startDate'
160 }
161
162 if (endDate) {
163 watchPeakDateWhereStart += ' AND "localVideoViewer"."startDate" <= :endDate'
164 watchPeakDateWhereEnd += ' AND "localVideoViewer"."endDate" <= :endDate'
165 }
166
167 // Add viewers that were already here, before our start date
168 const beforeWatchersQuery = startDate
169 // eslint-disable-next-line max-len
170 ? `SELECT COUNT(*) AS "total" FROM "localVideoViewer" WHERE "localVideoViewer"."startDate" < :startDate AND "localVideoViewer"."endDate" >= :startDate`
171 : `SELECT 0 AS "total"`
172
173 const watchPeakQuery = `WITH
174 "beforeWatchers" AS (${beforeWatchersQuery}),
175 "watchPeakValues" AS (
176 SELECT "startDate" AS "dateBreakpoint", 1 AS "inc"
177 FROM "localVideoViewer"
178 WHERE "videoId" = :videoId ${watchPeakDateWhereStart}
179 UNION ALL
180 SELECT "endDate" AS "dateBreakpoint", -1 AS "inc"
181 FROM "localVideoViewer"
182 WHERE "videoId" = :videoId ${watchPeakDateWhereEnd}
183 )
184 SELECT "dateBreakpoint", "concurrent"
185 FROM (
186 SELECT "dateBreakpoint", SUM(SUM("inc")) OVER (ORDER BY "dateBreakpoint") + (SELECT "total" FROM "beforeWatchers") AS "concurrent"
187 FROM "watchPeakValues"
188 GROUP BY "dateBreakpoint"
189 ) tmp
190 ORDER BY "concurrent" DESC
191 FETCH FIRST 1 ROW ONLY`
192
193 return LocalVideoViewerModel.sequelize.query<any>(watchPeakQuery, queryOptions)
194 }
195
196 const buildCountriesPromise = () => {
197 let countryDateWhere = ''
198
199 if (startDate) countryDateWhere += ' AND "localVideoViewer"."endDate" >= :startDate'
200 if (endDate) countryDateWhere += ' AND "localVideoViewer"."startDate" <= :endDate'
201
202 const countriesQuery = `SELECT country, COUNT(country) as viewers ` +
203 `FROM "localVideoViewer" ` +
204 `WHERE "videoId" = :videoId AND country IS NOT NULL ${countryDateWhere} ` +
205 `GROUP BY country ` +
206 `ORDER BY viewers DESC`
207
208 return LocalVideoViewerModel.sequelize.query<any>(countriesQuery, queryOptions)
209 }
210
211 const [ rowsTotalViewers, rowsWatchTime, rowsWatchPeak, rowsCountries ] = await Promise.all([
212 buildTotalViewersPromise(),
213 buildWatchTimePromise(),
214 buildWatchPeakPromise(),
215 buildCountriesPromise()
216 ])
217
218 const viewersPeak = rowsWatchPeak.length !== 0
219 ? parseInt(rowsWatchPeak[0].concurrent) || 0
220 : 0
221
222 return {
223 totalWatchTime: rowsWatchTime.length !== 0
224 ? Math.round(rowsWatchTime[0].totalWatchTime) || 0
225 : 0,
226 averageWatchTime: rowsWatchTime.length !== 0
227 ? Math.round(rowsWatchTime[0].averageWatchTime) || 0
228 : 0,
229
230 totalViewers: rowsTotalViewers.length !== 0
231 ? Math.round(rowsTotalViewers[0].totalViewers) || 0
232 : 0,
233
234 viewersPeak,
235 viewersPeakDate: rowsWatchPeak.length !== 0 && viewersPeak !== 0
236 ? rowsWatchPeak[0].dateBreakpoint || null
237 : null,
238
239 countries: rowsCountries.map(r => ({
240 isoCode: r.country,
241 viewers: r.viewers
242 }))
243 }
244 }
245
246 static async getRetentionStats (video: MVideo): Promise<VideoStatsRetention> {
247 const step = Math.max(Math.round(video.duration / 100), 1)
248
249 const query = `WITH "total" AS (SELECT COUNT(*) AS viewers FROM "localVideoViewer" WHERE "videoId" = :videoId) ` +
250 `SELECT serie AS "second", ` +
251 `(COUNT("localVideoViewer".id)::float / (SELECT GREATEST("total"."viewers", 1) FROM "total")) AS "retention" ` +
252 `FROM generate_series(0, ${video.duration}, ${step}) serie ` +
253 `LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId ` +
254 `AND EXISTS (` +
255 `SELECT 1 FROM "localVideoViewerWatchSection" ` +
256 `WHERE "localVideoViewer"."id" = "localVideoViewerWatchSection"."localVideoViewerId" ` +
257 `AND serie >= "localVideoViewerWatchSection"."watchStart" ` +
258 `AND serie <= "localVideoViewerWatchSection"."watchEnd"` +
259 `)` +
260 `GROUP BY serie ` +
261 `ORDER BY serie ASC`
262
263 const queryOptions = {
264 type: QueryTypes.SELECT as QueryTypes.SELECT,
265 replacements: { videoId: video.id }
266 }
267
268 const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions)
269
270 return {
271 data: rows.map(r => ({
272 second: r.second,
273 retentionPercent: parseFloat(r.retention) * 100
274 }))
275 }
276 }
277
278 static async getTimeserieStats (options: {
279 video: MVideo
280 metric: VideoStatsTimeserieMetric
281 startDate: string
282 endDate: string
283 }): Promise<VideoStatsTimeserie> {
284 const { video, metric } = options
285
286 const { groupInterval, startDate, endDate } = buildGroupByAndBoundaries(options.startDate, options.endDate)
287
288 const selectMetrics: { [ id in VideoStatsTimeserieMetric ]: string } = {
289 viewers: 'COUNT("localVideoViewer"."id")',
290 aggregateWatchTime: 'SUM("localVideoViewer"."watchTime")'
291 }
292
293 const intervalWhere: { [ id in VideoStatsTimeserieMetric ]: string } = {
294 // Viewer is still in the interval. Overlap algorithm
295 viewers: '"localVideoViewer"."startDate" <= "intervals"."endDate" ' +
296 'AND "localVideoViewer"."endDate" >= "intervals"."startDate"',
297
298 // We do an aggregation, so only sum things once. Arbitrary we use the end date for that purpose
299 aggregateWatchTime: '"localVideoViewer"."endDate" >= "intervals"."startDate" ' +
300 'AND "localVideoViewer"."endDate" <= "intervals"."endDate"'
301 }
302
303 const query = `WITH "intervals" AS (
304 SELECT
305 "time" AS "startDate", "time" + :groupInterval::interval as "endDate"
306 FROM
307 generate_series(:startDate::timestamptz, :endDate::timestamptz, :groupInterval::interval) serie("time")
308 )
309 SELECT "intervals"."startDate" as "date", COALESCE(${selectMetrics[metric]}, 0) AS value
310 FROM
311 intervals
312 LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId
313 AND ${intervalWhere[metric]}
314 GROUP BY
315 "intervals"."startDate"
316 ORDER BY
317 "intervals"."startDate"`
318
319 const queryOptions = {
320 type: QueryTypes.SELECT as QueryTypes.SELECT,
321 replacements: {
322 startDate,
323 endDate,
324 groupInterval,
325 videoId: video.id
326 }
327 }
328
329 const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions)
330
331 return {
332 groupInterval,
333 data: rows.map(r => ({
334 date: r.date,
335 value: parseInt(r.value)
336 }))
337 }
338 }
339
340 toActivityPubObject (this: MLocalVideoViewerWithWatchSections): WatchActionObject {
341 const location = this.country
342 ? {
343 location: {
344 addressCountry: this.country
345 }
346 }
347 : {}
348
349 return {
350 id: this.url,
351 type: 'WatchAction',
352 duration: getActivityStreamDuration(this.watchTime),
353 startTime: this.startDate.toISOString(),
354 endTime: this.endDate.toISOString(),
355
356 object: this.Video.url,
357 uuid: this.uuid,
358 actionStatus: 'CompletedActionStatus',
359
360 watchSections: this.WatchSections.map(w => ({
361 startTimestamp: w.watchStart,
362 endTimestamp: w.watchEnd
363 })),
364
365 ...location
366 }
367 }
368}
generated by cgit v1.2.3 (git 2.25.1) at 2025-04-29 08:55:52 +0000