server/models/view/local-video-viewer.ts

   1 import { QueryTypes } from 'sequelize'
   2 import { AllowNull, BelongsTo, Column, CreatedAt, DataType, Default, ForeignKey, HasMany, IsUUID, Model, Table } from 'sequelize-typescript'
   3 import { getActivityStreamDuration } from '@server/lib/activitypub/activity'
   4 import { buildGroupByAndBoundaries } from '@server/lib/timeserie'
   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 /**
  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
  18 @Table({
  19   tableName: 'localVideoViewer',
  20   updatedAt: false,
  21   indexes: [
  22     {
  23       fields: [ 'videoId' ]
  24     },
  25     {
  26       fields: [ 'url' ],
  27       unique: true
  28     }
  29   ]
  30 })
  31 export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVideoViewerModel>>> {
  32   @CreatedAt
  33   createdAt: Date
  34
  35   @AllowNull(false)
  36   @Column(DataType.DATE)
  37   startDate: Date
  38
  39   @AllowNull(false)
  40   @Column(DataType.DATE)
  41   endDate: Date
  42
  43   @AllowNull(false)
  44   @Column
  45   watchTime: number
  46
  47   @AllowNull(true)
  48   @Column
  49   country: string
  50
  51   @AllowNull(false)
  52   @Default(DataType.UUIDV4)
  53   @IsUUID(4)
  54   @Column(DataType.UUID)
  55   uuid: string
  56
  57   @AllowNull(false)
  58   @Column
  59   url: string
  60
  61   @ForeignKey(() => VideoModel)
  62   @Column
  63   videoId: number
  64
  65   @BelongsTo(() => VideoModel, {
  66     foreignKey: {
  67       allowNull: false
  68     },
  69     onDelete: 'CASCADE'
  70   })
  71   Video: VideoModel
  72
  73   @HasMany(() => LocalVideoViewerWatchSectionModel, {
  74     foreignKey: {
  75       allowNull: false
  76     },
  77     onDelete: 'cascade'
  78   })
  79   WatchSections: LocalVideoViewerWatchSectionModel[]
  80
  81   static loadByUrl (url: string): Promise<MLocalVideoViewer> {
  82     return this.findOne({
  83       where: {
  84         url
  85       }
  86     })
  87   }
  88
  89   static loadFullById (id: number): Promise<MLocalVideoViewerWithWatchSections> {
  90     return this.findOne({
  91       include: [
  92         {
  93           model: VideoModel.unscoped(),
  94           required: true
  95         },
  96         {
  97           model: LocalVideoViewerWatchSectionModel.unscoped(),
  98           required: true
  99         }
 100       ],
 101       where: {
 102         id
 103       }
 104     })
 105   }
 106
 107   static async getOverallStats (options: {
 108     video: MVideo
 109     startDate?: string
 110     endDate?: string
 111   }): Promise<VideoStatsOverall> {
 112     const { video, startDate, endDate } = options
 113
 114     const queryOptions = {
 115       type: QueryTypes.SELECT as QueryTypes.SELECT,
 116       replacements: { videoId: video.id } as any
 117     }
 118
 119     if (startDate) queryOptions.replacements.startDate = startDate
 120     if (endDate) queryOptions.replacements.endDate = endDate
 121
 122     const buildTotalViewersPromise = () => {
 123       let totalViewersDateWhere = ''
 124
 125       if (startDate) totalViewersDateWhere += ' AND "localVideoViewer"."endDate" >= :startDate'
 126       if (endDate) totalViewersDateWhere += ' AND "localVideoViewer"."startDate" <= :endDate'
 127
 128       const totalViewersQuery = `SELECT ` +
 129         `COUNT("localVideoViewer"."id") AS "totalViewers" ` +
 130         `FROM "localVideoViewer" ` +
 131         `WHERE "videoId" = :videoId ${totalViewersDateWhere}`
 132
 133       return LocalVideoViewerModel.sequelize.query<any>(totalViewersQuery, queryOptions)
 134     }
 135
 136     const buildWatchTimePromise = () => {
 137       let watchTimeDateWhere = ''
 138
 139       // We know this where is not exact
 140       // But we prefer to take into account only watch section that started and ended **in** the interval
 141       if (startDate) watchTimeDateWhere += ' AND "localVideoViewer"."startDate" >= :startDate'
 142       if (endDate) watchTimeDateWhere += ' AND "localVideoViewer"."endDate" <= :endDate'
 143
 144       const watchTimeQuery = `SELECT ` +
 145         `SUM("localVideoViewer"."watchTime") AS "totalWatchTime", ` +
 146         `AVG("localVideoViewer"."watchTime") AS "averageWatchTime" ` +
 147         `FROM "localVideoViewer" ` +
 148         `WHERE "videoId" = :videoId ${watchTimeDateWhere}`
 149
 150       return LocalVideoViewerModel.sequelize.query<any>(watchTimeQuery, queryOptions)
 151     }
 152
 153     const buildWatchPeakPromise = () => {
 154       let watchPeakDateWhereStart = ''
 155       let watchPeakDateWhereEnd = ''
 156
 157       if (startDate) {
 158         watchPeakDateWhereStart += ' AND "localVideoViewer"."startDate" >= :startDate'
 159         watchPeakDateWhereEnd += ' AND "localVideoViewer"."endDate" >= :startDate'
 160       }
 161
 162       if (endDate) {
 163         watchPeakDateWhereStart += ' AND "localVideoViewer"."startDate" <= :endDate'
 164         watchPeakDateWhereEnd += ' AND "localVideoViewer"."endDate" <= :endDate'
 165       }
 166
 167       // Add viewers that were already here, before our start date
 168       const beforeWatchersQuery = startDate
 169         // eslint-disable-next-line max-len
 170         ? `SELECT COUNT(*) AS "total" FROM "localVideoViewer" WHERE "localVideoViewer"."startDate" < :startDate AND "localVideoViewer"."endDate" >= :startDate`
 171         : `SELECT 0 AS "total"`
 172
 173       const watchPeakQuery = `WITH
 174         "beforeWatchers" AS (${beforeWatchersQuery}),
 175         "watchPeakValues" AS (
 176           SELECT "startDate" AS "dateBreakpoint", 1 AS "inc"
 177           FROM "localVideoViewer"
 178           WHERE "videoId" = :videoId ${watchPeakDateWhereStart}
 179           UNION ALL
 180           SELECT "endDate" AS "dateBreakpoint", -1 AS "inc"
 181           FROM "localVideoViewer"
 182           WHERE "videoId" = :videoId ${watchPeakDateWhereEnd}
 183         )
 184         SELECT "dateBreakpoint", "concurrent"
 185         FROM (
 186           SELECT "dateBreakpoint", SUM(SUM("inc")) OVER (ORDER BY "dateBreakpoint") + (SELECT "total" FROM "beforeWatchers") AS "concurrent"
 187           FROM "watchPeakValues"
 188           GROUP BY "dateBreakpoint"
 189         ) tmp
 190         ORDER BY "concurrent" DESC
 191         FETCH FIRST 1 ROW ONLY`
 192
 193       return LocalVideoViewerModel.sequelize.query<any>(watchPeakQuery, queryOptions)
 194     }
 195
 196     const buildCountriesPromise = () => {
 197       let countryDateWhere = ''
 198
 199       if (startDate) countryDateWhere += ' AND "localVideoViewer"."endDate" >= :startDate'
 200       if (endDate) countryDateWhere += ' AND "localVideoViewer"."startDate" <= :endDate'
 201
 202       const countriesQuery = `SELECT country, COUNT(country) as viewers ` +
 203         `FROM "localVideoViewer" ` +
 204         `WHERE "videoId" = :videoId AND country IS NOT NULL ${countryDateWhere} ` +
 205         `GROUP BY country ` +
 206         `ORDER BY viewers DESC`
 207
 208       return LocalVideoViewerModel.sequelize.query<any>(countriesQuery, queryOptions)
 209     }
 210
 211     const [ rowsTotalViewers, rowsWatchTime, rowsWatchPeak, rowsCountries ] = await Promise.all([
 212       buildTotalViewersPromise(),
 213       buildWatchTimePromise(),
 214       buildWatchPeakPromise(),
 215       buildCountriesPromise()
 216     ])
 217
 218     const viewersPeak = rowsWatchPeak.length !== 0
 219       ? parseInt(rowsWatchPeak[0].concurrent) || 0
 220       : 0
 221
 222     return {
 223       totalWatchTime: rowsWatchTime.length !== 0
 224         ? Math.round(rowsWatchTime[0].totalWatchTime) || 0
 225         : 0,
 226       averageWatchTime: rowsWatchTime.length !== 0
 227         ? Math.round(rowsWatchTime[0].averageWatchTime) || 0
 228         : 0,
 229
 230       totalViewers: rowsTotalViewers.length !== 0
 231         ? Math.round(rowsTotalViewers[0].totalViewers) || 0
 232         : 0,
 233
 234       viewersPeak,
 235       viewersPeakDate: rowsWatchPeak.length !== 0 && viewersPeak !== 0
 236         ? rowsWatchPeak[0].dateBreakpoint || null
 237         : null,
 238
 239       countries: rowsCountries.map(r => ({
 240         isoCode: r.country,
 241         viewers: r.viewers
 242       }))
 243     }
 244   }
 245
 246   static async getRetentionStats (video: MVideo): Promise<VideoStatsRetention> {
 247     const step = Math.max(Math.round(video.duration / 100), 1)
 248
 249     const query = `WITH "total" AS (SELECT COUNT(*) AS viewers FROM "localVideoViewer" WHERE "videoId" = :videoId) ` +
 250       `SELECT serie AS "second", ` +
 251         `(COUNT("localVideoViewer".id)::float / (SELECT GREATEST("total"."viewers", 1) FROM "total")) AS "retention" ` +
 252       `FROM generate_series(0, ${video.duration}, ${step}) serie ` +
 253       `LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId ` +
 254         `AND EXISTS (` +
 255           `SELECT 1 FROM "localVideoViewerWatchSection" ` +
 256           `WHERE "localVideoViewer"."id" = "localVideoViewerWatchSection"."localVideoViewerId" ` +
 257           `AND serie >= "localVideoViewerWatchSection"."watchStart" ` +
 258           `AND serie <= "localVideoViewerWatchSection"."watchEnd"` +
 259         `)` +
 260       `GROUP BY serie ` +
 261       `ORDER BY serie ASC`
 262
 263     const queryOptions = {
 264       type: QueryTypes.SELECT as QueryTypes.SELECT,
 265       replacements: { videoId: video.id }
 266     }
 267
 268     const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions)
 269
 270     return {
 271       data: rows.map(r => ({
 272         second: r.second,
 273         retentionPercent: parseFloat(r.retention) * 100
 274       }))
 275     }
 276   }
 277
 278   static async getTimeserieStats (options: {
 279     video: MVideo
 280     metric: VideoStatsTimeserieMetric
 281     startDate: string
 282     endDate: string
 283   }): Promise<VideoStatsTimeserie> {
 284     const { video, metric } = options
 285
 286     const { groupInterval, startDate, endDate } = buildGroupByAndBoundaries(options.startDate, options.endDate)
 287
 288     const selectMetrics: { [ id in VideoStatsTimeserieMetric ]: string } = {
 289       viewers: 'COUNT("localVideoViewer"."id")',
 290       aggregateWatchTime: 'SUM("localVideoViewer"."watchTime")'
 291     }
 292
 293     const intervalWhere: { [ id in VideoStatsTimeserieMetric ]: string } = {
 294       // Viewer is still in the interval. Overlap algorithm
 295       viewers: '"localVideoViewer"."startDate" <= "intervals"."endDate" ' +
 296         'AND "localVideoViewer"."endDate" >= "intervals"."startDate"',
 297
 298       // We do an aggregation, so only sum things once. Arbitrary we use the end date for that purpose
 299       aggregateWatchTime: '"localVideoViewer"."endDate" >= "intervals"."startDate" ' +
 300         'AND "localVideoViewer"."endDate" <= "intervals"."endDate"'
 301     }
 302
 303     const query = `WITH "intervals" AS (
 304       SELECT
 305         "time" AS "startDate", "time" + :groupInterval::interval as "endDate"
 306       FROM
 307         generate_series(:startDate::timestamptz, :endDate::timestamptz, :groupInterval::interval) serie("time")
 308     )
 309     SELECT "intervals"."startDate" as "date", COALESCE(${selectMetrics[metric]}, 0) AS value
 310     FROM
 311       intervals
 312       LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId
 313         AND ${intervalWhere[metric]}
 314     GROUP BY
 315       "intervals"."startDate"
 316     ORDER BY
 317       "intervals"."startDate"`
 318
 319     const queryOptions = {
 320       type: QueryTypes.SELECT as QueryTypes.SELECT,
 321       replacements: {
 322         startDate,
 323         endDate,
 324         groupInterval,
 325         videoId: video.id
 326       }
 327     }
 328
 329     const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions)
 330
 331     return {
 332       groupInterval,
 333       data: rows.map(r => ({
 334         date: r.date,
 335         value: parseInt(r.value)
 336       }))
 337     }
 338   }
 339
 340   toActivityPubObject (this: MLocalVideoViewerWithWatchSections): WatchActionObject {
 341     const location = this.country
 342       ? {
 343         location: {
 344           addressCountry: this.country
 345         }
 346       }
 347       : {}
 348
 349     return {
 350       id: this.url,
 351       type: 'WatchAction',
 352       duration: getActivityStreamDuration(this.watchTime),
 353       startTime: this.startDate.toISOString(),
 354       endTime: this.endDate.toISOString(),
 355
 356       object: this.Video.url,
 357       uuid: this.uuid,
 358       actionStatus: 'CompletedActionStatus',
 359
 360       watchSections: this.WatchSections.map(w => ({
 361         startTimestamp: w.watchStart,
 362         endTimestamp: w.watchEnd
 363       })),
 364
 365       ...location
 366     }
 367   }
 368 }