]>
Commit | Line | Data |
---|---|---|
b2111066 C |
1 | import { QueryTypes } from 'sequelize' |
2 | import { AllowNull, BelongsTo, Column, CreatedAt, DataType, Default, ForeignKey, HasMany, IsUUID, Model, Table } from 'sequelize-typescript' | |
b2111066 | 3 | import { getActivityStreamDuration } from '@server/lib/activitypub/activity' |
901bcf5c | 4 | import { buildGroupByAndBoundaries } from '@server/lib/timeserie' |
b2111066 C |
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' | |
10 | ||
dfbcefc2 C |
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 | ||
b2111066 C |
18 | @Table({ |
19 | tableName: 'localVideoViewer', | |
20 | updatedAt: false, | |
21 | indexes: [ | |
22 | { | |
23 | fields: [ 'videoId' ] | |
f008e9f3 C |
24 | }, |
25 | { | |
26 | fields: [ 'url' ], | |
27 | unique: true | |
b2111066 C |
28 | } |
29 | ] | |
30 | }) | |
31 | export 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 | ||
49f0468d C |
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 = { | |
b2111066 | 115 | type: QueryTypes.SELECT as QueryTypes.SELECT, |
49f0468d C |
116 | replacements: { videoId: video.id } as any |
117 | } | |
118 | ||
624ea01b C |
119 | if (startDate) queryOptions.replacements.startDate = startDate |
120 | if (endDate) queryOptions.replacements.endDate = endDate | |
49f0468d | 121 | |
fd7581b0 C |
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 | ||
624ea01b C |
136 | const buildWatchTimePromise = () => { |
137 | let watchTimeDateWhere = '' | |
138 | ||
fd7581b0 C |
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 | |
624ea01b C |
141 | if (startDate) watchTimeDateWhere += ' AND "localVideoViewer"."startDate" >= :startDate' |
142 | if (endDate) watchTimeDateWhere += ' AND "localVideoViewer"."endDate" <= :endDate' | |
143 | ||
144 | const watchTimeQuery = `SELECT ` + | |
624ea01b C |
145 | `SUM("localVideoViewer"."watchTime") AS "totalWatchTime", ` + |
146 | `AVG("localVideoViewer"."watchTime") AS "averageWatchTime" ` + | |
147 | `FROM "localVideoViewer" ` + | |
624ea01b C |
148 | `WHERE "videoId" = :videoId ${watchTimeDateWhere}` |
149 | ||
150 | return LocalVideoViewerModel.sequelize.query<any>(watchTimeQuery, queryOptions) | |
49f0468d C |
151 | } |
152 | ||
624ea01b C |
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) | |
b2111066 C |
194 | } |
195 | ||
624ea01b C |
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 | } | |
b2111066 | 210 | |
fd7581b0 C |
211 | const [ rowsTotalViewers, rowsWatchTime, rowsWatchPeak, rowsCountries ] = await Promise.all([ |
212 | buildTotalViewersPromise(), | |
624ea01b C |
213 | buildWatchTimePromise(), |
214 | buildWatchPeakPromise(), | |
215 | buildCountriesPromise() | |
b2111066 C |
216 | ]) |
217 | ||
f40712ab C |
218 | const viewersPeak = rowsWatchPeak.length !== 0 |
219 | ? parseInt(rowsWatchPeak[0].concurrent) || 0 | |
220 | : 0 | |
221 | ||
b2111066 C |
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 | ||
fd7581b0 C |
230 | totalViewers: rowsTotalViewers.length !== 0 |
231 | ? Math.round(rowsTotalViewers[0].totalViewers) || 0 | |
305ec384 C |
232 | : 0, |
233 | ||
f40712ab C |
234 | viewersPeak, |
235 | viewersPeakDate: rowsWatchPeak.length !== 0 && viewersPeak !== 0 | |
b2111066 C |
236 | ? rowsWatchPeak[0].dateBreakpoint || null |
237 | : null, | |
238 | ||
b2111066 C |
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 | |
901bcf5c C |
281 | startDate: string |
282 | endDate: string | |
b2111066 C |
283 | }): Promise<VideoStatsTimeserie> { |
284 | const { video, metric } = options | |
285 | ||
3eda9b77 | 286 | const { groupInterval, startDate, endDate } = buildGroupByAndBoundaries(options.startDate, options.endDate) |
901bcf5c | 287 | |
b2111066 C |
288 | const selectMetrics: { [ id in VideoStatsTimeserieMetric ]: string } = { |
289 | viewers: 'COUNT("localVideoViewer"."id")', | |
290 | aggregateWatchTime: 'SUM("localVideoViewer"."watchTime")' | |
291 | } | |
292 | ||
67ad83d0 C |
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 | ||
901bcf5c C |
303 | const query = `WITH "intervals" AS ( |
304 | SELECT | |
3eda9b77 | 305 | "time" AS "startDate", "time" + :groupInterval::interval as "endDate" |
901bcf5c | 306 | FROM |
3eda9b77 | 307 | generate_series(:startDate::timestamptz, :endDate::timestamptz, :groupInterval::interval) serie("time") |
901bcf5c C |
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 | |
67ad83d0 | 313 | AND ${intervalWhere[metric]} |
901bcf5c C |
314 | GROUP BY |
315 | "intervals"."startDate" | |
316 | ORDER BY | |
317 | "intervals"."startDate"` | |
b2111066 C |
318 | |
319 | const queryOptions = { | |
320 | type: QueryTypes.SELECT as QueryTypes.SELECT, | |
901bcf5c C |
321 | replacements: { |
322 | startDate, | |
323 | endDate, | |
3eda9b77 | 324 | groupInterval, |
901bcf5c C |
325 | videoId: video.id |
326 | } | |
b2111066 C |
327 | } |
328 | ||
329 | const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions) | |
330 | ||
331 | return { | |
901bcf5c | 332 | groupInterval, |
b2111066 C |
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 | } |