X-Git-Url: https://git.immae.eu/?a=blobdiff_plain;f=server%2Fmodels%2Fview%2Flocal-video-viewer.ts;h=c7ac51a034269e71ca36a4bd821e08df8fe6237e;hb=f383c7c881baf7dae97c69ac4bcba5ab6916d35b;hp=2862f8b96454651821b8f9fb1621d1eaf7d06357;hpb=49f0468d44468528c2fb2c8b0efd19cdaeeec43d;p=github%2FChocobozzz%2FPeerTube.git

diff --git a/server/models/view/local-video-viewer.ts b/server/models/view/local-video-viewer.ts
index 2862f8b96..c7ac51a03 100644
--- a/server/models/view/local-video-viewer.ts
+++ b/server/models/view/local-video-viewer.ts
@@ -21,6 +21,10 @@ import { LocalVideoViewerWatchSectionModel } from './local-video-viewer-watch-se
   indexes: [
     {
       fields: [ 'videoId' ]
+    },
+    {
+      fields: [ 'url' ],
+      unique: true
     }
   ]
 })
@@ -112,59 +116,109 @@ export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVid
       replacements: { videoId: video.id } as any
     }
 
-    let dateWhere = ''
+    if (startDate) queryOptions.replacements.startDate = startDate
+    if (endDate) queryOptions.replacements.endDate = endDate
+
+    const buildTotalViewersPromise = () => {
+      let totalViewersDateWhere = ''
+
+      if (startDate) totalViewersDateWhere += ' AND "localVideoViewer"."endDate" >= :startDate'
+      if (endDate) totalViewersDateWhere += ' AND "localVideoViewer"."startDate" <= :endDate'
 
-    if (startDate) {
-      dateWhere += ' AND "localVideoViewer"."startDate" >= :startDate'
-      queryOptions.replacements.startDate = startDate
+      const totalViewersQuery = `SELECT ` +
+        `COUNT("localVideoViewer"."id") AS "totalViewers" ` +
+        `FROM "localVideoViewer" ` +
+        `WHERE "videoId" = :videoId ${totalViewersDateWhere}`
+
+      return LocalVideoViewerModel.sequelize.query<any>(totalViewersQuery, queryOptions)
     }
 
-    if (endDate) {
-      dateWhere += ' AND "localVideoViewer"."endDate" <= :endDate'
-      queryOptions.replacements.endDate = endDate
+    const buildWatchTimePromise = () => {
+      let watchTimeDateWhere = ''
+
+      // We know this where is not exact
+      // But we prefer to take into account only watch section that started and ended **in** the interval
+      if (startDate) watchTimeDateWhere += ' AND "localVideoViewer"."startDate" >= :startDate'
+      if (endDate) watchTimeDateWhere += ' AND "localVideoViewer"."endDate" <= :endDate'
+
+      const watchTimeQuery = `SELECT ` +
+        `SUM("localVideoViewer"."watchTime") AS "totalWatchTime", ` +
+        `AVG("localVideoViewer"."watchTime") AS "averageWatchTime" ` +
+        `FROM "localVideoViewer" ` +
+        `WHERE "videoId" = :videoId ${watchTimeDateWhere}`
+
+      return LocalVideoViewerModel.sequelize.query<any>(watchTimeQuery, queryOptions)
     }
 
-    const watchTimeQuery = `SELECT ` +
-      `SUM("localVideoViewer"."watchTime") AS "totalWatchTime", ` +
-      `AVG("localVideoViewer"."watchTime") AS "averageWatchTime" ` +
-      `FROM "localVideoViewer" ` +
-      `INNER JOIN "video" ON "video"."id" = "localVideoViewer"."videoId" ` +
-      `WHERE "videoId" = :videoId ${dateWhere}`
-
-    const watchTimePromise = LocalVideoViewerModel.sequelize.query<any>(watchTimeQuery, queryOptions)
-
-    const watchPeakQuery = `WITH "watchPeakValues" AS (
-        SELECT "startDate" AS "dateBreakpoint", 1 AS "inc"
-        FROM "localVideoViewer"
-        WHERE "videoId" = :videoId
-        UNION ALL
-        SELECT "endDate" AS "dateBreakpoint", -1 AS "inc"
-        FROM "localVideoViewer"
-        WHERE "videoId" = :videoId ${dateWhere}
-      )
-      SELECT "dateBreakpoint", "concurrent"
-      FROM (
-        SELECT "dateBreakpoint", SUM(SUM("inc")) OVER (ORDER BY "dateBreakpoint") AS "concurrent"
-        FROM "watchPeakValues"
-        GROUP BY "dateBreakpoint"
-      ) tmp
-      ORDER BY "concurrent" DESC
-      FETCH FIRST 1 ROW ONLY`
-    const watchPeakPromise = LocalVideoViewerModel.sequelize.query<any>(watchPeakQuery, queryOptions)
-
-    const countriesQuery = `SELECT country, COUNT(country) as viewers ` +
-      `FROM "localVideoViewer" ` +
-      `WHERE "videoId" = :videoId AND country IS NOT NULL ${dateWhere} ` +
-      `GROUP BY country ` +
-      `ORDER BY viewers DESC`
-    const countriesPromise = LocalVideoViewerModel.sequelize.query<any>(countriesQuery, queryOptions)
-
-    const [ rowsWatchTime, rowsWatchPeak, rowsCountries ] = await Promise.all([
-      watchTimePromise,
-      watchPeakPromise,
-      countriesPromise
+    const buildWatchPeakPromise = () => {
+      let watchPeakDateWhereStart = ''
+      let watchPeakDateWhereEnd = ''
+
+      if (startDate) {
+        watchPeakDateWhereStart += ' AND "localVideoViewer"."startDate" >= :startDate'
+        watchPeakDateWhereEnd += ' AND "localVideoViewer"."endDate" >= :startDate'
+      }
+
+      if (endDate) {
+        watchPeakDateWhereStart += ' AND "localVideoViewer"."startDate" <= :endDate'
+        watchPeakDateWhereEnd += ' AND "localVideoViewer"."endDate" <= :endDate'
+      }
+
+      // Add viewers that were already here, before our start date
+      const beforeWatchersQuery = startDate
+        // eslint-disable-next-line max-len
+        ? `SELECT COUNT(*) AS "total" FROM "localVideoViewer" WHERE "localVideoViewer"."startDate" < :startDate AND "localVideoViewer"."endDate" >= :startDate`
+        : `SELECT 0 AS "total"`
+
+      const watchPeakQuery = `WITH
+        "beforeWatchers" AS (${beforeWatchersQuery}),
+        "watchPeakValues" AS (
+          SELECT "startDate" AS "dateBreakpoint", 1 AS "inc"
+          FROM "localVideoViewer"
+          WHERE "videoId" = :videoId ${watchPeakDateWhereStart}
+          UNION ALL
+          SELECT "endDate" AS "dateBreakpoint", -1 AS "inc"
+          FROM "localVideoViewer"
+          WHERE "videoId" = :videoId ${watchPeakDateWhereEnd}
+        )
+        SELECT "dateBreakpoint", "concurrent"
+        FROM (
+          SELECT "dateBreakpoint", SUM(SUM("inc")) OVER (ORDER BY "dateBreakpoint") + (SELECT "total" FROM "beforeWatchers") AS "concurrent"
+          FROM "watchPeakValues"
+          GROUP BY "dateBreakpoint"
+        ) tmp
+        ORDER BY "concurrent" DESC
+        FETCH FIRST 1 ROW ONLY`
+
+      return LocalVideoViewerModel.sequelize.query<any>(watchPeakQuery, queryOptions)
+    }
+
+    const buildCountriesPromise = () => {
+      let countryDateWhere = ''
+
+      if (startDate) countryDateWhere += ' AND "localVideoViewer"."endDate" >= :startDate'
+      if (endDate) countryDateWhere += ' AND "localVideoViewer"."startDate" <= :endDate'
+
+      const countriesQuery = `SELECT country, COUNT(country) as viewers ` +
+        `FROM "localVideoViewer" ` +
+        `WHERE "videoId" = :videoId AND country IS NOT NULL ${countryDateWhere} ` +
+        `GROUP BY country ` +
+        `ORDER BY viewers DESC`
+
+      return LocalVideoViewerModel.sequelize.query<any>(countriesQuery, queryOptions)
+    }
+
+    const [ rowsTotalViewers, rowsWatchTime, rowsWatchPeak, rowsCountries ] = await Promise.all([
+      buildTotalViewersPromise(),
+      buildWatchTimePromise(),
+      buildWatchPeakPromise(),
+      buildCountriesPromise()
     ])
 
+    const viewersPeak = rowsWatchPeak.length !== 0
+      ? parseInt(rowsWatchPeak[0].concurrent) || 0
+      : 0
+
     return {
       totalWatchTime: rowsWatchTime.length !== 0
         ? Math.round(rowsWatchTime[0].totalWatchTime) || 0
@@ -173,10 +227,12 @@ export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVid
         ? Math.round(rowsWatchTime[0].averageWatchTime) || 0
         : 0,
 
-      viewersPeak: rowsWatchPeak.length !== 0
-        ? parseInt(rowsWatchPeak[0].concurrent) || 0
+      totalViewers: rowsTotalViewers.length !== 0
+        ? Math.round(rowsTotalViewers[0].totalViewers) || 0
         : 0,
-      viewersPeakDate: rowsWatchPeak.length !== 0
+
+      viewersPeak,
+      viewersPeakDate: rowsWatchPeak.length !== 0 && viewersPeak !== 0
         ? rowsWatchPeak[0].dateBreakpoint || null
         : null,
 
@@ -234,6 +290,16 @@ export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVid
       aggregateWatchTime: 'SUM("localVideoViewer"."watchTime")'
     }
 
+    const intervalWhere: { [ id in VideoStatsTimeserieMetric ]: string } = {
+      // Viewer is still in the interval. Overlap algorithm
+      viewers: '"localVideoViewer"."startDate" <= "intervals"."endDate" ' +
+        'AND "localVideoViewer"."endDate" >= "intervals"."startDate"',
+
+      // We do an aggregation, so only sum things once. Arbitrary we use the end date for that purpose
+      aggregateWatchTime: '"localVideoViewer"."endDate" >= "intervals"."startDate" ' +
+        'AND "localVideoViewer"."endDate" <= "intervals"."endDate"'
+    }
+
     const query = `WITH "intervals" AS (
       SELECT
         "time" AS "startDate", "time" + :groupInterval::interval as "endDate"
@@ -244,7 +310,7 @@ export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVid
     FROM
       intervals
       LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId
-        AND "localVideoViewer"."startDate" >= "intervals"."startDate" AND "localVideoViewer"."startDate" <= "intervals"."endDate"
+        AND ${intervalWhere[metric]}
     GROUP BY
       "intervals"."startDate"
     ORDER BY