})
}
- 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
}): Promise<VideoStatsTimeserie> {
const { video, metric } = options
- const { groupInterval, sqlInterval, startDate, endDate } = buildGroupByAndBoundaries(options.startDate, options.endDate)
+ const { groupInterval, startDate, endDate } = buildGroupByAndBoundaries(options.startDate, options.endDate)
const selectMetrics: { [ id in VideoStatsTimeserieMetric ]: string } = {
viewers: 'COUNT("localVideoViewer"."id")',
const query = `WITH "intervals" AS (
SELECT
- "time" AS "startDate", "time" + :sqlInterval::interval as "endDate"
+ "time" AS "startDate", "time" + :groupInterval::interval as "endDate"
FROM
- generate_series(:startDate::timestamptz, :endDate::timestamptz, :sqlInterval::interval) serie("time")
+ generate_series(:startDate::timestamptz, :endDate::timestamptz, :groupInterval::interval) serie("time")
)
SELECT "intervals"."startDate" as "date", COALESCE(${selectMetrics[metric]}, 0) AS value
FROM
replacements: {
startDate,
endDate,
- sqlInterval,
+ groupInterval,
videoId: video.id
}
}