aboutsummaryrefslogtreecommitdiffhomepage
path: root/server
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2022-11-24 10:07:58 +0100
committerChocobozzz <me@florianbigard.com>2022-11-24 10:07:58 +0100
commit624ea01b1088d65fbdf41d8b8f723405046439d5 (patch)
tree37248ab432c092f3c914357bf65b4fcc0ed17b27 /server
parent72cd9f303ab592440027552ce8a869277f21a05b (diff)
downloadPeerTube-624ea01b1088d65fbdf41d8b8f723405046439d5.tar.gz
PeerTube-624ea01b1088d65fbdf41d8b8f723405046439d5.tar.zst
PeerTube-624ea01b1088d65fbdf41d8b8f723405046439d5.zip
Fix overall viewers stats with start/end dates
Diffstat (limited to 'server')
-rw-r--r--server/models/view/local-video-viewer.ts120
-rw-r--r--server/tests/api/views/video-views-overall-stats.ts66
2 files changed, 141 insertions, 45 deletions
diff --git a/server/models/view/local-video-viewer.ts b/server/models/view/local-video-viewer.ts
index 12350861b..9d0d89a59 100644
--- a/server/models/view/local-video-viewer.ts
+++ b/server/models/view/local-video-viewer.ts
@@ -112,58 +112,88 @@ export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVid
112 replacements: { videoId: video.id } as any 112 replacements: { videoId: video.id } as any
113 } 113 }
114 114
115 let dateWhere = '' 115 if (startDate) queryOptions.replacements.startDate = startDate
116 if (endDate) queryOptions.replacements.endDate = endDate
116 117
117 if (startDate) { 118 const buildWatchTimePromise = () => {
118 dateWhere += ' AND "localVideoViewer"."startDate" >= :startDate' 119 let watchTimeDateWhere = ''
119 queryOptions.replacements.startDate = startDate 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)
120 } 133 }
121 134
122 if (endDate) { 135 const buildWatchPeakPromise = () => {
123 dateWhere += ' AND "localVideoViewer"."endDate" <= :endDate' 136 let watchPeakDateWhereStart = ''
124 queryOptions.replacements.endDate = endDate 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)
125 } 176 }
126 177
127 const watchTimeQuery = `SELECT ` + 178 const buildCountriesPromise = () => {
128 `COUNT("localVideoViewer"."id") AS "totalViewers", ` + 179 let countryDateWhere = ''
129 `SUM("localVideoViewer"."watchTime") AS "totalWatchTime", ` + 180
130 `AVG("localVideoViewer"."watchTime") AS "averageWatchTime" ` + 181 if (startDate) countryDateWhere += ' AND "localVideoViewer"."endDate" >= :startDate'
131 `FROM "localVideoViewer" ` + 182 if (endDate) countryDateWhere += ' AND "localVideoViewer"."startDate" <= :endDate'
132 `INNER JOIN "video" ON "video"."id" = "localVideoViewer"."videoId" ` + 183
133 `WHERE "videoId" = :videoId ${dateWhere}` 184 const countriesQuery = `SELECT country, COUNT(country) as viewers ` +
134 185 `FROM "localVideoViewer" ` +
135 const watchTimePromise = LocalVideoViewerModel.sequelize.query<any>(watchTimeQuery, queryOptions) 186 `WHERE "videoId" = :videoId AND country IS NOT NULL ${countryDateWhere} ` +
136 187 `GROUP BY country ` +
137 const watchPeakQuery = `WITH "watchPeakValues" AS ( 188 `ORDER BY viewers DESC`
138 SELECT "startDate" AS "dateBreakpoint", 1 AS "inc" 189
139 FROM "localVideoViewer" 190 return LocalVideoViewerModel.sequelize.query<any>(countriesQuery, queryOptions)
140 WHERE "videoId" = :videoId ${dateWhere} 191 }
141 UNION ALL
142 SELECT "endDate" AS "dateBreakpoint", -1 AS "inc"
143 FROM "localVideoViewer"
144 WHERE "videoId" = :videoId ${dateWhere}
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`
154 const watchPeakPromise = LocalVideoViewerModel.sequelize.query<any>(watchPeakQuery, queryOptions)
155
156 const countriesQuery = `SELECT country, COUNT(country) as viewers ` +
157 `FROM "localVideoViewer" ` +
158 `WHERE "videoId" = :videoId AND country IS NOT NULL ${dateWhere} ` +
159 `GROUP BY country ` +
160 `ORDER BY viewers DESC`
161 const countriesPromise = LocalVideoViewerModel.sequelize.query<any>(countriesQuery, queryOptions)
162 192
163 const [ rowsWatchTime, rowsWatchPeak, rowsCountries ] = await Promise.all([ 193 const [ rowsWatchTime, rowsWatchPeak, rowsCountries ] = await Promise.all([
164 watchTimePromise, 194 buildWatchTimePromise(),
165 watchPeakPromise, 195 buildWatchPeakPromise(),
166 countriesPromise 196 buildCountriesPromise()
167 ]) 197 ])
168 198
169 const viewersPeak = rowsWatchPeak.length !== 0 199 const viewersPeak = rowsWatchPeak.length !== 0
diff --git a/server/tests/api/views/video-views-overall-stats.ts b/server/tests/api/views/video-views-overall-stats.ts
index 3aadc9689..ac636961e 100644
--- a/server/tests/api/views/video-views-overall-stats.ts
+++ b/server/tests/api/views/video-views-overall-stats.ts
@@ -4,6 +4,56 @@ import { expect } from 'chai'
4import { FfmpegCommand } from 'fluent-ffmpeg' 4import { FfmpegCommand } from 'fluent-ffmpeg'
5import { prepareViewsServers, prepareViewsVideos, processViewersStats } from '@server/tests/shared' 5import { prepareViewsServers, prepareViewsVideos, processViewersStats } from '@server/tests/shared'
6import { cleanupTests, PeerTubeServer, stopFfmpeg, waitJobs } from '@shared/server-commands' 6import { cleanupTests, PeerTubeServer, stopFfmpeg, waitJobs } from '@shared/server-commands'
7import { wait } from '@shared/core-utils'
8import { VideoStatsOverall } from '@shared/models'
9
10/**
11 *
12 * Simulate 5 sections of viewers
13 * * user0 started and ended before start date
14 * * user1 started before start date and ended in the interval
15 * * user2 started started in the interval and ended after end date
16 * * user3 started and ended in the interval
17 * * user4 started and ended after end date
18 */
19async function simulateComplexViewers (servers: PeerTubeServer[], videoUUID: string) {
20 const user0 = '8.8.8.8,127.0.0.1'
21 const user1 = '8.8.8.8,127.0.0.1'
22 const user2 = '8.8.8.9,127.0.0.1'
23 const user3 = '8.8.8.10,127.0.0.1'
24 const user4 = '8.8.8.11,127.0.0.1'
25
26 await servers[0].views.view({ id: videoUUID, currentTime: 0, xForwardedFor: user0 }) // User 0 starts
27 await wait(500)
28
29 await servers[0].views.view({ id: videoUUID, currentTime: 0, xForwardedFor: user1 }) // User 1 starts
30 await servers[0].views.view({ id: videoUUID, currentTime: 2, xForwardedFor: user0 }) // User 0 ends
31 await wait(500)
32
33 const startDate = new Date().toISOString()
34 await servers[0].views.view({ id: videoUUID, currentTime: 0, xForwardedFor: user2 }) // User 2 starts
35 await wait(500)
36
37 await servers[0].views.view({ id: videoUUID, currentTime: 0, xForwardedFor: user3 }) // User 3 starts
38 await wait(500)
39
40 await servers[0].views.view({ id: videoUUID, currentTime: 4, xForwardedFor: user1 }) // User 1 ends
41 await wait(500)
42
43 await servers[0].views.view({ id: videoUUID, currentTime: 3, xForwardedFor: user3 }) // User 3 ends
44 await wait(500)
45
46 const endDate = new Date().toISOString()
47 await servers[0].views.view({ id: videoUUID, currentTime: 0, xForwardedFor: user4 }) // User 4 starts
48 await servers[0].views.view({ id: videoUUID, currentTime: 5, xForwardedFor: user2 }) // User 2 ends
49 await wait(500)
50
51 await servers[0].views.view({ id: videoUUID, currentTime: 1, xForwardedFor: user4 }) // User 4 ends
52
53 await processViewersStats(servers)
54
55 return { startDate, endDate }
56}
7 57
8describe('Test views overall stats', function () { 58describe('Test views overall stats', function () {
9 let servers: PeerTubeServer[] 59 let servers: PeerTubeServer[]
@@ -237,6 +287,22 @@ describe('Test views overall stats', function () {
237 expect(new Date(stats.viewersPeakDate)).to.be.below(before2Watchers) 287 expect(new Date(stats.viewersPeakDate)).to.be.below(before2Watchers)
238 } 288 }
239 }) 289 })
290
291 it('Should complex filter peak viewers by date', async function () {
292 this.timeout(60000)
293
294 const { startDate, endDate } = await simulateComplexViewers(servers, videoUUID)
295
296 const expectCorrect = (stats: VideoStatsOverall) => {
297 expect(stats.viewersPeak).to.equal(3)
298 expect(new Date(stats.viewersPeakDate)).to.be.above(new Date(startDate)).and.below(new Date(endDate))
299 }
300
301 expectCorrect(await servers[0].videoStats.getOverallStats({ videoId: videoUUID, startDate, endDate }))
302 expectCorrect(await servers[0].videoStats.getOverallStats({ videoId: videoUUID, startDate }))
303 expectCorrect(await servers[0].videoStats.getOverallStats({ videoId: videoUUID, endDate }))
304 expectCorrect(await servers[0].videoStats.getOverallStats({ videoId: videoUUID }))
305 })
240 }) 306 })
241 307
242 describe('Test countries', function () { 308 describe('Test countries', function () {