]>
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' ] | |
24 | } | |
25 | ] | |
26 | }) | |
27 | export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVideoViewerModel>>> { | |
28 | @CreatedAt | |
29 | createdAt: Date | |
30 | ||
31 | @AllowNull(false) | |
32 | @Column(DataType.DATE) | |
33 | startDate: Date | |
34 | ||
35 | @AllowNull(false) | |
36 | @Column(DataType.DATE) | |
37 | endDate: Date | |
38 | ||
39 | @AllowNull(false) | |
40 | @Column | |
41 | watchTime: number | |
42 | ||
43 | @AllowNull(true) | |
44 | @Column | |
45 | country: string | |
46 | ||
47 | @AllowNull(false) | |
48 | @Default(DataType.UUIDV4) | |
49 | @IsUUID(4) | |
50 | @Column(DataType.UUID) | |
51 | uuid: string | |
52 | ||
53 | @AllowNull(false) | |
54 | @Column | |
55 | url: string | |
56 | ||
57 | @ForeignKey(() => VideoModel) | |
58 | @Column | |
59 | videoId: number | |
60 | ||
61 | @BelongsTo(() => VideoModel, { | |
62 | foreignKey: { | |
63 | allowNull: false | |
64 | }, | |
65 | onDelete: 'CASCADE' | |
66 | }) | |
67 | Video: VideoModel | |
68 | ||
69 | @HasMany(() => LocalVideoViewerWatchSectionModel, { | |
70 | foreignKey: { | |
71 | allowNull: false | |
72 | }, | |
73 | onDelete: 'cascade' | |
74 | }) | |
75 | WatchSections: LocalVideoViewerWatchSectionModel[] | |
76 | ||
77 | static loadByUrl (url: string): Promise<MLocalVideoViewer> { | |
78 | return this.findOne({ | |
79 | where: { | |
80 | url | |
81 | } | |
82 | }) | |
83 | } | |
84 | ||
85 | static loadFullById (id: number): Promise<MLocalVideoViewerWithWatchSections> { | |
86 | return this.findOne({ | |
87 | include: [ | |
88 | { | |
89 | model: VideoModel.unscoped(), | |
90 | required: true | |
91 | }, | |
92 | { | |
93 | model: LocalVideoViewerWatchSectionModel.unscoped(), | |
94 | required: true | |
95 | } | |
96 | ], | |
97 | where: { | |
98 | id | |
99 | } | |
100 | }) | |
101 | } | |
102 | ||
49f0468d C |
103 | static async getOverallStats (options: { |
104 | video: MVideo | |
105 | startDate?: string | |
106 | endDate?: string | |
107 | }): Promise<VideoStatsOverall> { | |
108 | const { video, startDate, endDate } = options | |
109 | ||
110 | const queryOptions = { | |
b2111066 | 111 | type: QueryTypes.SELECT as QueryTypes.SELECT, |
49f0468d C |
112 | replacements: { videoId: video.id } as any |
113 | } | |
114 | ||
624ea01b C |
115 | if (startDate) queryOptions.replacements.startDate = startDate |
116 | if (endDate) queryOptions.replacements.endDate = endDate | |
49f0468d | 117 | |
624ea01b C |
118 | const buildWatchTimePromise = () => { |
119 | let watchTimeDateWhere = '' | |
120 | ||
121 | if (startDate) watchTimeDateWhere += ' AND "localVideoViewer"."startDate" >= :startDate' | |
122 | if (endDate) watchTimeDateWhere += ' AND "localVideoViewer"."endDate" <= :endDate' | |
123 | ||
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}` | |
131 | ||
132 | return LocalVideoViewerModel.sequelize.query<any>(watchTimeQuery, queryOptions) | |
49f0468d C |
133 | } |
134 | ||
624ea01b C |
135 | const buildWatchPeakPromise = () => { |
136 | let watchPeakDateWhereStart = '' | |
137 | let watchPeakDateWhereEnd = '' | |
138 | ||
139 | if (startDate) { | |
140 | watchPeakDateWhereStart += ' AND "localVideoViewer"."startDate" >= :startDate' | |
141 | watchPeakDateWhereEnd += ' AND "localVideoViewer"."endDate" >= :startDate' | |
142 | } | |
143 | ||
144 | if (endDate) { | |
145 | watchPeakDateWhereStart += ' AND "localVideoViewer"."startDate" <= :endDate' | |
146 | watchPeakDateWhereEnd += ' AND "localVideoViewer"."endDate" <= :endDate' | |
147 | } | |
148 | ||
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"` | |
154 | ||
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} | |
161 | UNION ALL | |
162 | SELECT "endDate" AS "dateBreakpoint", -1 AS "inc" | |
163 | FROM "localVideoViewer" | |
164 | WHERE "videoId" = :videoId ${watchPeakDateWhereEnd} | |
165 | ) | |
166 | SELECT "dateBreakpoint", "concurrent" | |
167 | FROM ( | |
168 | SELECT "dateBreakpoint", SUM(SUM("inc")) OVER (ORDER BY "dateBreakpoint") + (SELECT "total" FROM "beforeWatchers") AS "concurrent" | |
169 | FROM "watchPeakValues" | |
170 | GROUP BY "dateBreakpoint" | |
171 | ) tmp | |
172 | ORDER BY "concurrent" DESC | |
173 | FETCH FIRST 1 ROW ONLY` | |
174 | ||
175 | return LocalVideoViewerModel.sequelize.query<any>(watchPeakQuery, queryOptions) | |
b2111066 C |
176 | } |
177 | ||
624ea01b C |
178 | const buildCountriesPromise = () => { |
179 | let countryDateWhere = '' | |
180 | ||
181 | if (startDate) countryDateWhere += ' AND "localVideoViewer"."endDate" >= :startDate' | |
182 | if (endDate) countryDateWhere += ' AND "localVideoViewer"."startDate" <= :endDate' | |
183 | ||
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` | |
189 | ||
190 | return LocalVideoViewerModel.sequelize.query<any>(countriesQuery, queryOptions) | |
191 | } | |
b2111066 | 192 | |
f18a060a | 193 | const [ rowsWatchTime, rowsWatchPeak, rowsCountries ] = await Promise.all([ |
624ea01b C |
194 | buildWatchTimePromise(), |
195 | buildWatchPeakPromise(), | |
196 | buildCountriesPromise() | |
b2111066 C |
197 | ]) |
198 | ||
f40712ab C |
199 | const viewersPeak = rowsWatchPeak.length !== 0 |
200 | ? parseInt(rowsWatchPeak[0].concurrent) || 0 | |
201 | : 0 | |
202 | ||
b2111066 C |
203 | return { |
204 | totalWatchTime: rowsWatchTime.length !== 0 | |
205 | ? Math.round(rowsWatchTime[0].totalWatchTime) || 0 | |
206 | : 0, | |
207 | averageWatchTime: rowsWatchTime.length !== 0 | |
208 | ? Math.round(rowsWatchTime[0].averageWatchTime) || 0 | |
209 | : 0, | |
210 | ||
305ec384 C |
211 | totalViewers: rowsWatchTime.length !== 0 |
212 | ? Math.round(rowsWatchTime[0].totalViewers) || 0 | |
213 | : 0, | |
214 | ||
f40712ab C |
215 | viewersPeak, |
216 | viewersPeakDate: rowsWatchPeak.length !== 0 && viewersPeak !== 0 | |
b2111066 C |
217 | ? rowsWatchPeak[0].dateBreakpoint || null |
218 | : null, | |
219 | ||
b2111066 C |
220 | countries: rowsCountries.map(r => ({ |
221 | isoCode: r.country, | |
222 | viewers: r.viewers | |
223 | })) | |
224 | } | |
225 | } | |
226 | ||
227 | static async getRetentionStats (video: MVideo): Promise<VideoStatsRetention> { | |
228 | const step = Math.max(Math.round(video.duration / 100), 1) | |
229 | ||
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 ` + | |
235 | `AND EXISTS (` + | |
236 | `SELECT 1 FROM "localVideoViewerWatchSection" ` + | |
237 | `WHERE "localVideoViewer"."id" = "localVideoViewerWatchSection"."localVideoViewerId" ` + | |
238 | `AND serie >= "localVideoViewerWatchSection"."watchStart" ` + | |
239 | `AND serie <= "localVideoViewerWatchSection"."watchEnd"` + | |
240 | `)` + | |
241 | `GROUP BY serie ` + | |
242 | `ORDER BY serie ASC` | |
243 | ||
244 | const queryOptions = { | |
245 | type: QueryTypes.SELECT as QueryTypes.SELECT, | |
246 | replacements: { videoId: video.id } | |
247 | } | |
248 | ||
249 | const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions) | |
250 | ||
251 | return { | |
252 | data: rows.map(r => ({ | |
253 | second: r.second, | |
254 | retentionPercent: parseFloat(r.retention) * 100 | |
255 | })) | |
256 | } | |
257 | } | |
258 | ||
259 | static async getTimeserieStats (options: { | |
260 | video: MVideo | |
261 | metric: VideoStatsTimeserieMetric | |
901bcf5c C |
262 | startDate: string |
263 | endDate: string | |
b2111066 C |
264 | }): Promise<VideoStatsTimeserie> { |
265 | const { video, metric } = options | |
266 | ||
3eda9b77 | 267 | const { groupInterval, startDate, endDate } = buildGroupByAndBoundaries(options.startDate, options.endDate) |
901bcf5c | 268 | |
b2111066 C |
269 | const selectMetrics: { [ id in VideoStatsTimeserieMetric ]: string } = { |
270 | viewers: 'COUNT("localVideoViewer"."id")', | |
271 | aggregateWatchTime: 'SUM("localVideoViewer"."watchTime")' | |
272 | } | |
273 | ||
67ad83d0 C |
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"', | |
278 | ||
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"' | |
282 | } | |
283 | ||
901bcf5c C |
284 | const query = `WITH "intervals" AS ( |
285 | SELECT | |
3eda9b77 | 286 | "time" AS "startDate", "time" + :groupInterval::interval as "endDate" |
901bcf5c | 287 | FROM |
3eda9b77 | 288 | generate_series(:startDate::timestamptz, :endDate::timestamptz, :groupInterval::interval) serie("time") |
901bcf5c C |
289 | ) |
290 | SELECT "intervals"."startDate" as "date", COALESCE(${selectMetrics[metric]}, 0) AS value | |
291 | FROM | |
292 | intervals | |
293 | LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId | |
67ad83d0 | 294 | AND ${intervalWhere[metric]} |
901bcf5c C |
295 | GROUP BY |
296 | "intervals"."startDate" | |
297 | ORDER BY | |
298 | "intervals"."startDate"` | |
b2111066 C |
299 | |
300 | const queryOptions = { | |
301 | type: QueryTypes.SELECT as QueryTypes.SELECT, | |
901bcf5c C |
302 | replacements: { |
303 | startDate, | |
304 | endDate, | |
3eda9b77 | 305 | groupInterval, |
901bcf5c C |
306 | videoId: video.id |
307 | } | |
b2111066 C |
308 | } |
309 | ||
310 | const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions) | |
311 | ||
312 | return { | |
901bcf5c | 313 | groupInterval, |
b2111066 C |
314 | data: rows.map(r => ({ |
315 | date: r.date, | |
316 | value: parseInt(r.value) | |
317 | })) | |
318 | } | |
319 | } | |
320 | ||
321 | toActivityPubObject (this: MLocalVideoViewerWithWatchSections): WatchActionObject { | |
322 | const location = this.country | |
323 | ? { | |
324 | location: { | |
325 | addressCountry: this.country | |
326 | } | |
327 | } | |
328 | : {} | |
329 | ||
330 | return { | |
331 | id: this.url, | |
332 | type: 'WatchAction', | |
333 | duration: getActivityStreamDuration(this.watchTime), | |
334 | startTime: this.startDate.toISOString(), | |
335 | endTime: this.endDate.toISOString(), | |
336 | ||
337 | object: this.Video.url, | |
338 | uuid: this.uuid, | |
339 | actionStatus: 'CompletedActionStatus', | |
340 | ||
341 | watchSections: this.WatchSections.map(w => ({ | |
342 | startTimestamp: w.watchStart, | |
343 | endTimestamp: w.watchEnd | |
344 | })), | |
345 | ||
346 | ...location | |
347 | } | |
348 | } | |
349 | } |