aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models
diff options
context:
space:
mode:
Diffstat (limited to 'server/models')
-rw-r--r--server/models/view/local-video-viewer.ts120
1 files changed, 75 insertions, 45 deletions
diff --git a/server/models/view/local-video-viewer.ts b/server/models/view/local-video-viewer.ts
index 12350861b..9d0d89a59 100644
--- a/server/models/view/local-video-viewer.ts
+++ b/server/models/view/local-video-viewer.ts
@@ -112,58 +112,88 @@ export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVid
112 replacements: { videoId: video.id } as any 112 replacements: { videoId: video.id } as any
113 } 113 }
114 114
115 let dateWhere = '' 115 if (startDate) queryOptions.replacements.startDate = startDate
116 if (endDate) queryOptions.replacements.endDate = endDate
116 117
117 if (startDate) { 118 const buildWatchTimePromise = () => {
118 dateWhere += ' AND "localVideoViewer"."startDate" >= :startDate' 119 let watchTimeDateWhere = ''
119 queryOptions.replacements.startDate = startDate 120
121 if (startDate) watchTimeDateWhere += ' AND "localVideoViewer"."startDate" >= :startDate'
122 if (endDate) watchTimeDateWhere += ' AND "localVideoViewer"."endDate" <= :endDate'
123
124 const watchTimeQuery = `SELECT ` +
125 `COUNT("localVideoViewer"."id") AS "totalViewers", ` +
126 `SUM("localVideoViewer"."watchTime") AS "totalWatchTime", ` +
127 `AVG("localVideoViewer"."watchTime") AS "averageWatchTime" ` +
128 `FROM "localVideoViewer" ` +
129 `INNER JOIN "video" ON "video"."id" = "localVideoViewer"."videoId" ` +
130 `WHERE "videoId" = :videoId ${watchTimeDateWhere}`
131
132 return LocalVideoViewerModel.sequelize.query<any>(watchTimeQuery, queryOptions)
120 } 133 }
121 134
122 if (endDate) { 135 const buildWatchPeakPromise = () => {
123 dateWhere += ' AND "localVideoViewer"."endDate" <= :endDate' 136 let watchPeakDateWhereStart = ''
124 queryOptions.replacements.endDate = endDate 137 let watchPeakDateWhereEnd = ''
138
139 if (startDate) {
140 watchPeakDateWhereStart += ' AND "localVideoViewer"."startDate" >= :startDate'
141 watchPeakDateWhereEnd += ' AND "localVideoViewer"."endDate" >= :startDate'
142 }
143
144 if (endDate) {
145 watchPeakDateWhereStart += ' AND "localVideoViewer"."startDate" <= :endDate'
146 watchPeakDateWhereEnd += ' AND "localVideoViewer"."endDate" <= :endDate'
147 }
148
149 // Add viewers that were already here, before our start date
150 const beforeWatchersQuery = startDate
151 // eslint-disable-next-line max-len
152 ? `SELECT COUNT(*) AS "total" FROM "localVideoViewer" WHERE "localVideoViewer"."startDate" < :startDate AND "localVideoViewer"."endDate" >= :startDate`
153 : `SELECT 0 AS "total"`
154
155 const watchPeakQuery = `WITH
156 "beforeWatchers" AS (${beforeWatchersQuery}),
157 "watchPeakValues" AS (
158 SELECT "startDate" AS "dateBreakpoint", 1 AS "inc"
159 FROM "localVideoViewer"
160 WHERE "videoId" = :videoId ${watchPeakDateWhereStart}
161 UNION ALL
162 SELECT "endDate" AS "dateBreakpoint", -1 AS "inc"
163 FROM "localVideoViewer"
164 WHERE "videoId" = :videoId ${watchPeakDateWhereEnd}
165 )
166 SELECT "dateBreakpoint", "concurrent"
167 FROM (
168 SELECT "dateBreakpoint", SUM(SUM("inc")) OVER (ORDER BY "dateBreakpoint") + (SELECT "total" FROM "beforeWatchers") AS "concurrent"
169 FROM "watchPeakValues"
170 GROUP BY "dateBreakpoint"
171 ) tmp
172 ORDER BY "concurrent" DESC
173 FETCH FIRST 1 ROW ONLY`
174
175 return LocalVideoViewerModel.sequelize.query<any>(watchPeakQuery, queryOptions)
125 } 176 }
126 177
127 const watchTimeQuery = `SELECT ` + 178 const buildCountriesPromise = () => {
128 `COUNT("localVideoViewer"."id") AS "totalViewers", ` + 179 let countryDateWhere = ''
129 `SUM("localVideoViewer"."watchTime") AS "totalWatchTime", ` + 180
130 `AVG("localVideoViewer"."watchTime") AS "averageWatchTime" ` + 181 if (startDate) countryDateWhere += ' AND "localVideoViewer"."endDate" >= :startDate'
131 `FROM "localVideoViewer" ` + 182 if (endDate) countryDateWhere += ' AND "localVideoViewer"."startDate" <= :endDate'
132 `INNER JOIN "video" ON "video"."id" = "localVideoViewer"."videoId" ` + 183
133 `WHERE "videoId" = :videoId ${dateWhere}` 184 const countriesQuery = `SELECT country, COUNT(country) as viewers ` +
134 185 `FROM "localVideoViewer" ` +
135 const watchTimePromise = LocalVideoViewerModel.sequelize.query<any>(watchTimeQuery, queryOptions) 186 `WHERE "videoId" = :videoId AND country IS NOT NULL ${countryDateWhere} ` +
136 187 `GROUP BY country ` +
137 const watchPeakQuery = `WITH "watchPeakValues" AS ( 188 `ORDER BY viewers DESC`
138 SELECT "startDate" AS "dateBreakpoint", 1 AS "inc" 189
139 FROM "localVideoViewer" 190 return LocalVideoViewerModel.sequelize.query<any>(countriesQuery, queryOptions)
140 WHERE "videoId" = :videoId ${dateWhere} 191 }
141 UNION ALL
142 SELECT "endDate" AS "dateBreakpoint", -1 AS "inc"
143 FROM "localVideoViewer"
144 WHERE "videoId" = :videoId ${dateWhere}
145 )
146 SELECT "dateBreakpoint", "concurrent"
147 FROM (
148 SELECT "dateBreakpoint", SUM(SUM("inc")) OVER (ORDER BY "dateBreakpoint") AS "concurrent"
149 FROM "watchPeakValues"
150 GROUP BY "dateBreakpoint"
151 ) tmp
152 ORDER BY "concurrent" DESC
153 FETCH FIRST 1 ROW ONLY`
154 const watchPeakPromise = LocalVideoViewerModel.sequelize.query<any>(watchPeakQuery, queryOptions)
155
156 const countriesQuery = `SELECT country, COUNT(country) as viewers ` +
157 `FROM "localVideoViewer" ` +
158 `WHERE "videoId" = :videoId AND country IS NOT NULL ${dateWhere} ` +
159 `GROUP BY country ` +
160 `ORDER BY viewers DESC`
161 const countriesPromise = LocalVideoViewerModel.sequelize.query<any>(countriesQuery, queryOptions)
162 192
163 const [ rowsWatchTime, rowsWatchPeak, rowsCountries ] = await Promise.all([ 193 const [ rowsWatchTime, rowsWatchPeak, rowsCountries ] = await Promise.all([
164 watchTimePromise, 194 buildWatchTimePromise(),
165 watchPeakPromise, 195 buildWatchPeakPromise(),
166 countriesPromise 196 buildCountriesPromise()
167 ]) 197 ])
168 198
169 const viewersPeak = rowsWatchPeak.length !== 0 199 const viewersPeak = rowsWatchPeak.length !== 0