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