indexes: [
{
fields: [ 'videoId' ]
+ },
+ {
+ fields: [ 'url' ],
+ unique: true
}
]
})
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
? 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,
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"
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
projects / github / Chocobozzz / PeerTube.git / blobdiff