import { QueryTypes } from 'sequelize'
import { AllowNull, BelongsTo, Column, CreatedAt, DataType, Default, ForeignKey, HasMany, IsUUID, Model, Table } from 'sequelize-typescript'
-import { STATS_TIMESERIE } from '@server/initializers/constants'
import { getActivityStreamDuration } from '@server/lib/activitypub/activity'
+import { buildGroupByAndBoundaries } from '@server/lib/timeserie'
import { MLocalVideoViewer, MLocalVideoViewerWithWatchSections, MVideo } from '@server/types/models'
import { VideoStatsOverall, VideoStatsRetention, VideoStatsTimeserie, VideoStatsTimeserieMetric, WatchActionObject } from '@shared/models'
import { AttributesOnly } from '@shared/typescript-utils'
import { VideoModel } from '../video/video'
import { LocalVideoViewerWatchSectionModel } from './local-video-viewer-watch-section'
+/**
+ *
+ * Aggregate viewers of local videos only to display statistics to video owners
+ * A viewer is a user that watched one or multiple sections of a specific video inside a time window
+ *
+ */
+
@Table({
tableName: 'localVideoViewer',
updatedAt: false,
})
}
- static async getOverallStats (video: MVideo): Promise<VideoStatsOverall> {
- const options = {
+ static async getOverallStats (options: {
+ video: MVideo
+ startDate?: string
+ endDate?: string
+ }): Promise<VideoStatsOverall> {
+ const { video, startDate, endDate } = options
+
+ const queryOptions = {
type: QueryTypes.SELECT as QueryTypes.SELECT,
- replacements: { videoId: video.id }
+ replacements: { videoId: video.id } as any
+ }
+
+ let dateWhere = ''
+
+ if (startDate) {
+ dateWhere += ' AND "localVideoViewer"."startDate" >= :startDate'
+ queryOptions.replacements.startDate = startDate
+ }
+
+ if (endDate) {
+ dateWhere += ' AND "localVideoViewer"."endDate" <= :endDate'
+ queryOptions.replacements.endDate = endDate
}
const watchTimeQuery = `SELECT ` +
`AVG("localVideoViewer"."watchTime") AS "averageWatchTime" ` +
`FROM "localVideoViewer" ` +
`INNER JOIN "video" ON "video"."id" = "localVideoViewer"."videoId" ` +
- `WHERE "videoId" = :videoId`
+ `WHERE "videoId" = :videoId ${dateWhere}`
- const watchTimePromise = LocalVideoViewerModel.sequelize.query<any>(watchTimeQuery, options)
+ 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
+ WHERE "videoId" = :videoId ${dateWhere}
UNION ALL
SELECT "endDate" AS "dateBreakpoint", -1 AS "inc"
FROM "localVideoViewer"
- WHERE "videoId" = :videoId
+ WHERE "videoId" = :videoId ${dateWhere}
)
SELECT "dateBreakpoint", "concurrent"
FROM (
) tmp
ORDER BY "concurrent" DESC
FETCH FIRST 1 ROW ONLY`
- const watchPeakPromise = LocalVideoViewerModel.sequelize.query<any>(watchPeakQuery, options)
-
- const commentsQuery = `SELECT COUNT(*) AS comments FROM "videoComment" WHERE "videoId" = :videoId`
- const commentsPromise = LocalVideoViewerModel.sequelize.query<any>(commentsQuery, options)
+ 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 ` +
+ `WHERE "videoId" = :videoId AND country IS NOT NULL ${dateWhere} ` +
`GROUP BY country ` +
`ORDER BY viewers DESC`
- const countriesPromise = LocalVideoViewerModel.sequelize.query<any>(countriesQuery, options)
+ const countriesPromise = LocalVideoViewerModel.sequelize.query<any>(countriesQuery, queryOptions)
- const [ rowsWatchTime, rowsWatchPeak, rowsComment, rowsCountries ] = await Promise.all([
+ const [ rowsWatchTime, rowsWatchPeak, rowsCountries ] = await Promise.all([
watchTimePromise,
watchPeakPromise,
- commentsPromise,
countriesPromise
])
+ const viewersPeak = rowsWatchPeak.length !== 0
+ ? parseInt(rowsWatchPeak[0].concurrent) || 0
+ : 0
+
return {
totalWatchTime: rowsWatchTime.length !== 0
? Math.round(rowsWatchTime[0].totalWatchTime) || 0
? Math.round(rowsWatchTime[0].averageWatchTime) || 0
: 0,
- viewersPeak: rowsWatchPeak.length !== 0
- ? parseInt(rowsWatchPeak[0].concurrent) || 0
- : 0,
- viewersPeakDate: rowsWatchPeak.length !== 0
+ viewersPeak,
+ viewersPeakDate: rowsWatchPeak.length !== 0 && viewersPeak !== 0
? rowsWatchPeak[0].dateBreakpoint || null
: null,
- views: video.views,
- likes: video.likes,
- dislikes: video.dislikes,
-
- comments: rowsComment.length !== 0
- ? parseInt(rowsComment[0].comments) || 0
- : 0,
-
countries: rowsCountries.map(r => ({
isoCode: r.country,
viewers: r.viewers
static async getTimeserieStats (options: {
video: MVideo
metric: VideoStatsTimeserieMetric
+ startDate: string
+ endDate: string
}): Promise<VideoStatsTimeserie> {
const { video, metric } = options
+ const { groupInterval, startDate, endDate } = buildGroupByAndBoundaries(options.startDate, options.endDate)
+
const selectMetrics: { [ id in VideoStatsTimeserieMetric ]: string } = {
viewers: 'COUNT("localVideoViewer"."id")',
aggregateWatchTime: 'SUM("localVideoViewer"."watchTime")'
}
- const query = `WITH days AS ( ` +
- `SELECT (current_date::timestamp - (serie || ' days')::interval)::timestamptz AS day
- FROM generate_series(0, ${STATS_TIMESERIE.MAX_DAYS - 1}) serie` +
- `) ` +
- `SELECT days.day AS date, COALESCE(${selectMetrics[metric]}, 0) AS value ` +
- `FROM days ` +
- `LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId ` +
- `AND date_trunc('day', "localVideoViewer"."startDate") = date_trunc('day', days.day) ` +
- `GROUP BY day ` +
- `ORDER BY day `
+ const query = `WITH "intervals" AS (
+ SELECT
+ "time" AS "startDate", "time" + :groupInterval::interval as "endDate"
+ FROM
+ generate_series(:startDate::timestamptz, :endDate::timestamptz, :groupInterval::interval) serie("time")
+ )
+ SELECT "intervals"."startDate" as "date", COALESCE(${selectMetrics[metric]}, 0) AS value
+ FROM
+ intervals
+ LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId
+ AND "localVideoViewer"."startDate" >= "intervals"."startDate" AND "localVideoViewer"."startDate" <= "intervals"."endDate"
+ GROUP BY
+ "intervals"."startDate"
+ ORDER BY
+ "intervals"."startDate"`
const queryOptions = {
type: QueryTypes.SELECT as QueryTypes.SELECT,
- replacements: { videoId: video.id }
+ replacements: {
+ startDate,
+ endDate,
+ groupInterval,
+ videoId: video.id
+ }
}
const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions)
return {
+ groupInterval,
data: rows.map(r => ({
date: r.date,
value: parseInt(r.value)
projects / github / Chocobozzz / PeerTube.git / blobdiff