]>
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 { STATS_TIMESERIE } from '@server/initializers/constants' | |
4 | import { getActivityStreamDuration } from '@server/lib/activitypub/activity' | |
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 | @Table({ | |
12 | tableName: 'localVideoViewer', | |
13 | updatedAt: false, | |
14 | indexes: [ | |
15 | { | |
16 | fields: [ 'videoId' ] | |
17 | } | |
18 | ] | |
19 | }) | |
20 | export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVideoViewerModel>>> { | |
21 | @CreatedAt | |
22 | createdAt: Date | |
23 | ||
24 | @AllowNull(false) | |
25 | @Column(DataType.DATE) | |
26 | startDate: Date | |
27 | ||
28 | @AllowNull(false) | |
29 | @Column(DataType.DATE) | |
30 | endDate: Date | |
31 | ||
32 | @AllowNull(false) | |
33 | @Column | |
34 | watchTime: number | |
35 | ||
36 | @AllowNull(true) | |
37 | @Column | |
38 | country: string | |
39 | ||
40 | @AllowNull(false) | |
41 | @Default(DataType.UUIDV4) | |
42 | @IsUUID(4) | |
43 | @Column(DataType.UUID) | |
44 | uuid: string | |
45 | ||
46 | @AllowNull(false) | |
47 | @Column | |
48 | url: string | |
49 | ||
50 | @ForeignKey(() => VideoModel) | |
51 | @Column | |
52 | videoId: number | |
53 | ||
54 | @BelongsTo(() => VideoModel, { | |
55 | foreignKey: { | |
56 | allowNull: false | |
57 | }, | |
58 | onDelete: 'CASCADE' | |
59 | }) | |
60 | Video: VideoModel | |
61 | ||
62 | @HasMany(() => LocalVideoViewerWatchSectionModel, { | |
63 | foreignKey: { | |
64 | allowNull: false | |
65 | }, | |
66 | onDelete: 'cascade' | |
67 | }) | |
68 | WatchSections: LocalVideoViewerWatchSectionModel[] | |
69 | ||
70 | static loadByUrl (url: string): Promise<MLocalVideoViewer> { | |
71 | return this.findOne({ | |
72 | where: { | |
73 | url | |
74 | } | |
75 | }) | |
76 | } | |
77 | ||
78 | static loadFullById (id: number): Promise<MLocalVideoViewerWithWatchSections> { | |
79 | return this.findOne({ | |
80 | include: [ | |
81 | { | |
82 | model: VideoModel.unscoped(), | |
83 | required: true | |
84 | }, | |
85 | { | |
86 | model: LocalVideoViewerWatchSectionModel.unscoped(), | |
87 | required: true | |
88 | } | |
89 | ], | |
90 | where: { | |
91 | id | |
92 | } | |
93 | }) | |
94 | } | |
95 | ||
96 | static async getOverallStats (video: MVideo): Promise<VideoStatsOverall> { | |
97 | const options = { | |
98 | type: QueryTypes.SELECT as QueryTypes.SELECT, | |
99 | replacements: { videoId: video.id } | |
100 | } | |
101 | ||
102 | const watchTimeQuery = `SELECT ` + | |
103 | `SUM("localVideoViewer"."watchTime") AS "totalWatchTime", ` + | |
104 | `AVG("localVideoViewer"."watchTime") AS "averageWatchTime" ` + | |
105 | `FROM "localVideoViewer" ` + | |
106 | `INNER JOIN "video" ON "video"."id" = "localVideoViewer"."videoId" ` + | |
107 | `WHERE "videoId" = :videoId` | |
108 | ||
109 | const watchTimePromise = LocalVideoViewerModel.sequelize.query<any>(watchTimeQuery, options) | |
110 | ||
111 | const watchPeakQuery = `WITH "watchPeakValues" AS ( | |
112 | SELECT "startDate" AS "dateBreakpoint", 1 AS "inc" | |
113 | FROM "localVideoViewer" | |
114 | WHERE "videoId" = :videoId | |
115 | UNION ALL | |
116 | SELECT "endDate" AS "dateBreakpoint", -1 AS "inc" | |
117 | FROM "localVideoViewer" | |
118 | WHERE "videoId" = :videoId | |
119 | ) | |
120 | SELECT "dateBreakpoint", "concurrent" | |
121 | FROM ( | |
122 | SELECT "dateBreakpoint", SUM(SUM("inc")) OVER (ORDER BY "dateBreakpoint") AS "concurrent" | |
123 | FROM "watchPeakValues" | |
124 | GROUP BY "dateBreakpoint" | |
125 | ) tmp | |
126 | ORDER BY "concurrent" DESC | |
127 | FETCH FIRST 1 ROW ONLY` | |
128 | const watchPeakPromise = LocalVideoViewerModel.sequelize.query<any>(watchPeakQuery, options) | |
129 | ||
130 | const commentsQuery = `SELECT COUNT(*) AS comments FROM "videoComment" WHERE "videoId" = :videoId` | |
131 | const commentsPromise = LocalVideoViewerModel.sequelize.query<any>(commentsQuery, options) | |
132 | ||
133 | const countriesQuery = `SELECT country, COUNT(country) as viewers ` + | |
134 | `FROM "localVideoViewer" ` + | |
135 | `WHERE "videoId" = :videoId AND country IS NOT NULL ` + | |
136 | `GROUP BY country ` + | |
137 | `ORDER BY viewers DESC` | |
138 | const countriesPromise = LocalVideoViewerModel.sequelize.query<any>(countriesQuery, options) | |
139 | ||
140 | const [ rowsWatchTime, rowsWatchPeak, rowsComment, rowsCountries ] = await Promise.all([ | |
141 | watchTimePromise, | |
142 | watchPeakPromise, | |
143 | commentsPromise, | |
144 | countriesPromise | |
145 | ]) | |
146 | ||
147 | return { | |
148 | totalWatchTime: rowsWatchTime.length !== 0 | |
149 | ? Math.round(rowsWatchTime[0].totalWatchTime) || 0 | |
150 | : 0, | |
151 | averageWatchTime: rowsWatchTime.length !== 0 | |
152 | ? Math.round(rowsWatchTime[0].averageWatchTime) || 0 | |
153 | : 0, | |
154 | ||
155 | viewersPeak: rowsWatchPeak.length !== 0 | |
156 | ? parseInt(rowsWatchPeak[0].concurrent) || 0 | |
157 | : 0, | |
158 | viewersPeakDate: rowsWatchPeak.length !== 0 | |
159 | ? rowsWatchPeak[0].dateBreakpoint || null | |
160 | : null, | |
161 | ||
162 | views: video.views, | |
163 | likes: video.likes, | |
164 | dislikes: video.dislikes, | |
165 | ||
166 | comments: rowsComment.length !== 0 | |
167 | ? parseInt(rowsComment[0].comments) || 0 | |
168 | : 0, | |
169 | ||
170 | countries: rowsCountries.map(r => ({ | |
171 | isoCode: r.country, | |
172 | viewers: r.viewers | |
173 | })) | |
174 | } | |
175 | } | |
176 | ||
177 | static async getRetentionStats (video: MVideo): Promise<VideoStatsRetention> { | |
178 | const step = Math.max(Math.round(video.duration / 100), 1) | |
179 | ||
180 | const query = `WITH "total" AS (SELECT COUNT(*) AS viewers FROM "localVideoViewer" WHERE "videoId" = :videoId) ` + | |
181 | `SELECT serie AS "second", ` + | |
182 | `(COUNT("localVideoViewer".id)::float / (SELECT GREATEST("total"."viewers", 1) FROM "total")) AS "retention" ` + | |
183 | `FROM generate_series(0, ${video.duration}, ${step}) serie ` + | |
184 | `LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId ` + | |
185 | `AND EXISTS (` + | |
186 | `SELECT 1 FROM "localVideoViewerWatchSection" ` + | |
187 | `WHERE "localVideoViewer"."id" = "localVideoViewerWatchSection"."localVideoViewerId" ` + | |
188 | `AND serie >= "localVideoViewerWatchSection"."watchStart" ` + | |
189 | `AND serie <= "localVideoViewerWatchSection"."watchEnd"` + | |
190 | `)` + | |
191 | `GROUP BY serie ` + | |
192 | `ORDER BY serie ASC` | |
193 | ||
194 | const queryOptions = { | |
195 | type: QueryTypes.SELECT as QueryTypes.SELECT, | |
196 | replacements: { videoId: video.id } | |
197 | } | |
198 | ||
199 | const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions) | |
200 | ||
201 | return { | |
202 | data: rows.map(r => ({ | |
203 | second: r.second, | |
204 | retentionPercent: parseFloat(r.retention) * 100 | |
205 | })) | |
206 | } | |
207 | } | |
208 | ||
209 | static async getTimeserieStats (options: { | |
210 | video: MVideo | |
211 | metric: VideoStatsTimeserieMetric | |
212 | }): Promise<VideoStatsTimeserie> { | |
213 | const { video, metric } = options | |
214 | ||
215 | const selectMetrics: { [ id in VideoStatsTimeserieMetric ]: string } = { | |
216 | viewers: 'COUNT("localVideoViewer"."id")', | |
217 | aggregateWatchTime: 'SUM("localVideoViewer"."watchTime")' | |
218 | } | |
219 | ||
220 | const query = `WITH days AS ( ` + | |
221 | `SELECT (current_date::timestamp - (serie || ' days')::interval)::timestamptz AS day | |
222 | FROM generate_series(0, ${STATS_TIMESERIE.MAX_DAYS - 1}) serie` + | |
223 | `) ` + | |
224 | `SELECT days.day AS date, COALESCE(${selectMetrics[metric]}, 0) AS value ` + | |
225 | `FROM days ` + | |
226 | `LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId ` + | |
227 | `AND date_trunc('day', "localVideoViewer"."startDate") = date_trunc('day', days.day) ` + | |
228 | `GROUP BY day ` + | |
229 | `ORDER BY day ` | |
230 | ||
231 | const queryOptions = { | |
232 | type: QueryTypes.SELECT as QueryTypes.SELECT, | |
233 | replacements: { videoId: video.id } | |
234 | } | |
235 | ||
236 | const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions) | |
237 | ||
238 | return { | |
239 | data: rows.map(r => ({ | |
240 | date: r.date, | |
241 | value: parseInt(r.value) | |
242 | })) | |
243 | } | |
244 | } | |
245 | ||
246 | toActivityPubObject (this: MLocalVideoViewerWithWatchSections): WatchActionObject { | |
247 | const location = this.country | |
248 | ? { | |
249 | location: { | |
250 | addressCountry: this.country | |
251 | } | |
252 | } | |
253 | : {} | |
254 | ||
255 | return { | |
256 | id: this.url, | |
257 | type: 'WatchAction', | |
258 | duration: getActivityStreamDuration(this.watchTime), | |
259 | startTime: this.startDate.toISOString(), | |
260 | endTime: this.endDate.toISOString(), | |
261 | ||
262 | object: this.Video.url, | |
263 | uuid: this.uuid, | |
264 | actionStatus: 'CompletedActionStatus', | |
265 | ||
266 | watchSections: this.WatchSections.map(w => ({ | |
267 | startTimestamp: w.watchStart, | |
268 | endTimestamp: w.watchEnd | |
269 | })), | |
270 | ||
271 | ...location | |
272 | } | |
273 | } | |
274 | } |