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