]>
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 | ||
115 | let dateWhere = '' | |
116 | ||
117 | if (startDate) { | |
118 | dateWhere += ' AND "localVideoViewer"."startDate" >= :startDate' | |
119 | queryOptions.replacements.startDate = startDate | |
120 | } | |
121 | ||
122 | if (endDate) { | |
123 | dateWhere += ' AND "localVideoViewer"."endDate" <= :endDate' | |
124 | queryOptions.replacements.endDate = endDate | |
b2111066 C |
125 | } |
126 | ||
127 | const watchTimeQuery = `SELECT ` + | |
305ec384 | 128 | `COUNT("localVideoViewer"."id") AS "totalViewers", ` + |
b2111066 C |
129 | `SUM("localVideoViewer"."watchTime") AS "totalWatchTime", ` + |
130 | `AVG("localVideoViewer"."watchTime") AS "averageWatchTime" ` + | |
131 | `FROM "localVideoViewer" ` + | |
132 | `INNER JOIN "video" ON "video"."id" = "localVideoViewer"."videoId" ` + | |
49f0468d | 133 | `WHERE "videoId" = :videoId ${dateWhere}` |
b2111066 | 134 | |
49f0468d | 135 | const watchTimePromise = LocalVideoViewerModel.sequelize.query<any>(watchTimeQuery, queryOptions) |
b2111066 C |
136 | |
137 | const watchPeakQuery = `WITH "watchPeakValues" AS ( | |
138 | SELECT "startDate" AS "dateBreakpoint", 1 AS "inc" | |
139 | FROM "localVideoViewer" | |
f40712ab | 140 | WHERE "videoId" = :videoId ${dateWhere} |
b2111066 C |
141 | UNION ALL |
142 | SELECT "endDate" AS "dateBreakpoint", -1 AS "inc" | |
143 | FROM "localVideoViewer" | |
49f0468d | 144 | WHERE "videoId" = :videoId ${dateWhere} |
b2111066 C |
145 | ) |
146 | SELECT "dateBreakpoint", "concurrent" | |
147 | FROM ( | |
148 | SELECT "dateBreakpoint", SUM(SUM("inc")) OVER (ORDER BY "dateBreakpoint") AS "concurrent" | |
149 | FROM "watchPeakValues" | |
150 | GROUP BY "dateBreakpoint" | |
151 | ) tmp | |
152 | ORDER BY "concurrent" DESC | |
153 | FETCH FIRST 1 ROW ONLY` | |
49f0468d | 154 | const watchPeakPromise = LocalVideoViewerModel.sequelize.query<any>(watchPeakQuery, queryOptions) |
b2111066 | 155 | |
b2111066 C |
156 | const countriesQuery = `SELECT country, COUNT(country) as viewers ` + |
157 | `FROM "localVideoViewer" ` + | |
49f0468d | 158 | `WHERE "videoId" = :videoId AND country IS NOT NULL ${dateWhere} ` + |
b2111066 C |
159 | `GROUP BY country ` + |
160 | `ORDER BY viewers DESC` | |
49f0468d | 161 | const countriesPromise = LocalVideoViewerModel.sequelize.query<any>(countriesQuery, queryOptions) |
b2111066 | 162 | |
f18a060a | 163 | const [ rowsWatchTime, rowsWatchPeak, rowsCountries ] = await Promise.all([ |
b2111066 C |
164 | watchTimePromise, |
165 | watchPeakPromise, | |
b2111066 C |
166 | countriesPromise |
167 | ]) | |
168 | ||
f40712ab C |
169 | const viewersPeak = rowsWatchPeak.length !== 0 |
170 | ? parseInt(rowsWatchPeak[0].concurrent) || 0 | |
171 | : 0 | |
172 | ||
b2111066 C |
173 | return { |
174 | totalWatchTime: rowsWatchTime.length !== 0 | |
175 | ? Math.round(rowsWatchTime[0].totalWatchTime) || 0 | |
176 | : 0, | |
177 | averageWatchTime: rowsWatchTime.length !== 0 | |
178 | ? Math.round(rowsWatchTime[0].averageWatchTime) || 0 | |
179 | : 0, | |
180 | ||
305ec384 C |
181 | totalViewers: rowsWatchTime.length !== 0 |
182 | ? Math.round(rowsWatchTime[0].totalViewers) || 0 | |
183 | : 0, | |
184 | ||
f40712ab C |
185 | viewersPeak, |
186 | viewersPeakDate: rowsWatchPeak.length !== 0 && viewersPeak !== 0 | |
b2111066 C |
187 | ? rowsWatchPeak[0].dateBreakpoint || null |
188 | : null, | |
189 | ||
b2111066 C |
190 | countries: rowsCountries.map(r => ({ |
191 | isoCode: r.country, | |
192 | viewers: r.viewers | |
193 | })) | |
194 | } | |
195 | } | |
196 | ||
197 | static async getRetentionStats (video: MVideo): Promise<VideoStatsRetention> { | |
198 | const step = Math.max(Math.round(video.duration / 100), 1) | |
199 | ||
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 ` + | |
205 | `AND EXISTS (` + | |
206 | `SELECT 1 FROM "localVideoViewerWatchSection" ` + | |
207 | `WHERE "localVideoViewer"."id" = "localVideoViewerWatchSection"."localVideoViewerId" ` + | |
208 | `AND serie >= "localVideoViewerWatchSection"."watchStart" ` + | |
209 | `AND serie <= "localVideoViewerWatchSection"."watchEnd"` + | |
210 | `)` + | |
211 | `GROUP BY serie ` + | |
212 | `ORDER BY serie ASC` | |
213 | ||
214 | const queryOptions = { | |
215 | type: QueryTypes.SELECT as QueryTypes.SELECT, | |
216 | replacements: { videoId: video.id } | |
217 | } | |
218 | ||
219 | const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions) | |
220 | ||
221 | return { | |
222 | data: rows.map(r => ({ | |
223 | second: r.second, | |
224 | retentionPercent: parseFloat(r.retention) * 100 | |
225 | })) | |
226 | } | |
227 | } | |
228 | ||
229 | static async getTimeserieStats (options: { | |
230 | video: MVideo | |
231 | metric: VideoStatsTimeserieMetric | |
901bcf5c C |
232 | startDate: string |
233 | endDate: string | |
b2111066 C |
234 | }): Promise<VideoStatsTimeserie> { |
235 | const { video, metric } = options | |
236 | ||
3eda9b77 | 237 | const { groupInterval, startDate, endDate } = buildGroupByAndBoundaries(options.startDate, options.endDate) |
901bcf5c | 238 | |
b2111066 C |
239 | const selectMetrics: { [ id in VideoStatsTimeserieMetric ]: string } = { |
240 | viewers: 'COUNT("localVideoViewer"."id")', | |
241 | aggregateWatchTime: 'SUM("localVideoViewer"."watchTime")' | |
242 | } | |
243 | ||
67ad83d0 C |
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"', | |
248 | ||
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"' | |
252 | } | |
253 | ||
901bcf5c C |
254 | const query = `WITH "intervals" AS ( |
255 | SELECT | |
3eda9b77 | 256 | "time" AS "startDate", "time" + :groupInterval::interval as "endDate" |
901bcf5c | 257 | FROM |
3eda9b77 | 258 | generate_series(:startDate::timestamptz, :endDate::timestamptz, :groupInterval::interval) serie("time") |
901bcf5c C |
259 | ) |
260 | SELECT "intervals"."startDate" as "date", COALESCE(${selectMetrics[metric]}, 0) AS value | |
261 | FROM | |
262 | intervals | |
263 | LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId | |
67ad83d0 | 264 | AND ${intervalWhere[metric]} |
901bcf5c C |
265 | GROUP BY |
266 | "intervals"."startDate" | |
267 | ORDER BY | |
268 | "intervals"."startDate"` | |
b2111066 C |
269 | |
270 | const queryOptions = { | |
271 | type: QueryTypes.SELECT as QueryTypes.SELECT, | |
901bcf5c C |
272 | replacements: { |
273 | startDate, | |
274 | endDate, | |
3eda9b77 | 275 | groupInterval, |
901bcf5c C |
276 | videoId: video.id |
277 | } | |
b2111066 C |
278 | } |
279 | ||
280 | const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions) | |
281 | ||
282 | return { | |
901bcf5c | 283 | groupInterval, |
b2111066 C |
284 | data: rows.map(r => ({ |
285 | date: r.date, | |
286 | value: parseInt(r.value) | |
287 | })) | |
288 | } | |
289 | } | |
290 | ||
291 | toActivityPubObject (this: MLocalVideoViewerWithWatchSections): WatchActionObject { | |
292 | const location = this.country | |
293 | ? { | |
294 | location: { | |
295 | addressCountry: this.country | |
296 | } | |
297 | } | |
298 | : {} | |
299 | ||
300 | return { | |
301 | id: this.url, | |
302 | type: 'WatchAction', | |
303 | duration: getActivityStreamDuration(this.watchTime), | |
304 | startTime: this.startDate.toISOString(), | |
305 | endTime: this.endDate.toISOString(), | |
306 | ||
307 | object: this.Video.url, | |
308 | uuid: this.uuid, | |
309 | actionStatus: 'CompletedActionStatus', | |
310 | ||
311 | watchSections: this.WatchSections.map(w => ({ | |
312 | startTimestamp: w.watchStart, | |
313 | endTimestamp: w.watchEnd | |
314 | })), | |
315 | ||
316 | ...location | |
317 | } | |
318 | } | |
319 | } |