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