aboutsummaryrefslogtreecommitdiffhomepage
diff options
context:
space:
mode:
-rw-r--r--server/models/view/local-video-viewer.ts12
1 files changed, 11 insertions, 1 deletions
diff --git a/server/models/view/local-video-viewer.ts b/server/models/view/local-video-viewer.ts
index 2305c7262..b8c372040 100644
--- a/server/models/view/local-video-viewer.ts
+++ b/server/models/view/local-video-viewer.ts
@@ -236,6 +236,16 @@ export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVid
236 aggregateWatchTime: 'SUM("localVideoViewer"."watchTime")' 236 aggregateWatchTime: 'SUM("localVideoViewer"."watchTime")'
237 } 237 }
238 238
239 const intervalWhere: { [ id in VideoStatsTimeserieMetric ]: string } = {
240 // Viewer is still in the interval. Overlap algorithm
241 viewers: '"localVideoViewer"."startDate" <= "intervals"."endDate" ' +
242 'AND "localVideoViewer"."endDate" >= "intervals"."startDate"',
243
244 // We do an aggregation, so only sum things once. Arbitrary we use the end date for that purpose
245 aggregateWatchTime: '"localVideoViewer"."endDate" >= "intervals"."startDate" ' +
246 'AND "localVideoViewer"."endDate" <= "intervals"."endDate"'
247 }
248
239 const query = `WITH "intervals" AS ( 249 const query = `WITH "intervals" AS (
240 SELECT 250 SELECT
241 "time" AS "startDate", "time" + :groupInterval::interval as "endDate" 251 "time" AS "startDate", "time" + :groupInterval::interval as "endDate"
@@ -246,7 +256,7 @@ export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVid
246 FROM 256 FROM
247 intervals 257 intervals
248 LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId 258 LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId
249 AND "localVideoViewer"."startDate" >= "intervals"."startDate" AND "localVideoViewer"."startDate" <= "intervals"."endDate" 259 AND ${intervalWhere[metric]}
250 GROUP BY 260 GROUP BY
251 "intervals"."startDate" 261 "intervals"."startDate"
252 ORDER BY 262 ORDER BY