aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/view/local-video-viewer.ts
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2022-04-07 10:53:35 +0200
committerChocobozzz <chocobozzz@cpy.re>2022-04-15 09:49:35 +0200
commit901bcf5c188ea79350fecd499ad76460b866617b (patch)
tree1e79f26cc3f2b952371d31bfa9b94a2b150be38a /server/models/view/local-video-viewer.ts
parentac907dc7c158056e9b6a5cb58acd27df5c7c2670 (diff)
downloadPeerTube-901bcf5c188ea79350fecd499ad76460b866617b.tar.gz
PeerTube-901bcf5c188ea79350fecd499ad76460b866617b.tar.zst
PeerTube-901bcf5c188ea79350fecd499ad76460b866617b.zip
Add ability to set start/end date to timeserie
Diffstat (limited to 'server/models/view/local-video-viewer.ts')
-rw-r--r--server/models/view/local-video-viewer.ts39
1 files changed, 27 insertions, 12 deletions
diff --git a/server/models/view/local-video-viewer.ts b/server/models/view/local-video-viewer.ts
index 1491acb9e..ad2ad35ca 100644
--- a/server/models/view/local-video-viewer.ts
+++ b/server/models/view/local-video-viewer.ts
@@ -1,7 +1,7 @@
1import { QueryTypes } from 'sequelize' 1import { QueryTypes } from 'sequelize'
2import { AllowNull, BelongsTo, Column, CreatedAt, DataType, Default, ForeignKey, HasMany, IsUUID, Model, Table } from 'sequelize-typescript' 2import { AllowNull, BelongsTo, Column, CreatedAt, DataType, Default, ForeignKey, HasMany, IsUUID, Model, Table } from 'sequelize-typescript'
3import { STATS_TIMESERIE } from '@server/initializers/constants'
4import { getActivityStreamDuration } from '@server/lib/activitypub/activity' 3import { getActivityStreamDuration } from '@server/lib/activitypub/activity'
4import { buildGroupByAndBoundaries } from '@server/lib/timeserie'
5import { MLocalVideoViewer, MLocalVideoViewerWithWatchSections, MVideo } from '@server/types/models' 5import { MLocalVideoViewer, MLocalVideoViewerWithWatchSections, MVideo } from '@server/types/models'
6import { VideoStatsOverall, VideoStatsRetention, VideoStatsTimeserie, VideoStatsTimeserieMetric, WatchActionObject } from '@shared/models' 6import { VideoStatsOverall, VideoStatsRetention, VideoStatsTimeserie, VideoStatsTimeserieMetric, WatchActionObject } from '@shared/models'
7import { AttributesOnly } from '@shared/typescript-utils' 7import { AttributesOnly } from '@shared/typescript-utils'
@@ -216,33 +216,48 @@ export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVid
216 static async getTimeserieStats (options: { 216 static async getTimeserieStats (options: {
217 video: MVideo 217 video: MVideo
218 metric: VideoStatsTimeserieMetric 218 metric: VideoStatsTimeserieMetric
219 startDate: string
220 endDate: string
219 }): Promise<VideoStatsTimeserie> { 221 }): Promise<VideoStatsTimeserie> {
220 const { video, metric } = options 222 const { video, metric } = options
221 223
224 const { groupInterval, sqlInterval, startDate, endDate } = buildGroupByAndBoundaries(options.startDate, options.endDate)
225
222 const selectMetrics: { [ id in VideoStatsTimeserieMetric ]: string } = { 226 const selectMetrics: { [ id in VideoStatsTimeserieMetric ]: string } = {
223 viewers: 'COUNT("localVideoViewer"."id")', 227 viewers: 'COUNT("localVideoViewer"."id")',
224 aggregateWatchTime: 'SUM("localVideoViewer"."watchTime")' 228 aggregateWatchTime: 'SUM("localVideoViewer"."watchTime")'
225 } 229 }
226 230
227 const query = `WITH days AS ( ` + 231 const query = `WITH "intervals" AS (
228 `SELECT (current_date::timestamp - (serie || ' days')::interval)::timestamptz AS day 232 SELECT
229 FROM generate_series(0, ${STATS_TIMESERIE.MAX_DAYS - 1}) serie` + 233 "time" AS "startDate", "time" + :sqlInterval::interval as "endDate"
230 `) ` + 234 FROM
231 `SELECT days.day AS date, COALESCE(${selectMetrics[metric]}, 0) AS value ` + 235 generate_series(:startDate::timestamptz, :endDate::timestamptz, :sqlInterval::interval) serie("time")
232 `FROM days ` + 236 )
233 `LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId ` + 237 SELECT "intervals"."startDate" as "date", COALESCE(${selectMetrics[metric]}, 0) AS value
234 `AND date_trunc('day', "localVideoViewer"."startDate") = date_trunc('day', days.day) ` + 238 FROM
235 `GROUP BY day ` + 239 intervals
236 `ORDER BY day ` 240 LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId
241 AND "localVideoViewer"."startDate" >= "intervals"."startDate" AND "localVideoViewer"."startDate" <= "intervals"."endDate"
242 GROUP BY
243 "intervals"."startDate"
244 ORDER BY
245 "intervals"."startDate"`
237 246
238 const queryOptions = { 247 const queryOptions = {
239 type: QueryTypes.SELECT as QueryTypes.SELECT, 248 type: QueryTypes.SELECT as QueryTypes.SELECT,
240 replacements: { videoId: video.id } 249 replacements: {
250 startDate,
251 endDate,
252 sqlInterval,
253 videoId: video.id
254 }
241 } 255 }
242 256
243 const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions) 257 const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions)
244 258
245 return { 259 return {
260 groupInterval,
246 data: rows.map(r => ({ 261 data: rows.map(r => ({
247 date: r.date, 262 date: r.date,
248 value: parseInt(r.value) 263 value: parseInt(r.value)