Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit5f3be62

Browse files
authored
feat(coderd/database): usetemplate_usage_stats inGetTemplateInsightsByInterval query (#12667)
This PR updates the `GetTemplateInsightsByInterval` query to use rolled up `template_usage_stats` instead of raw agent and app stats.
1 parent35d0843 commit5f3be62

File tree

3 files changed

+125
-176
lines changed

3 files changed

+125
-176
lines changed

‎coderd/database/dbmem/dbmem.go

Lines changed: 71 additions & 64 deletions
Original file line numberDiff line numberDiff line change
@@ -3407,7 +3407,7 @@ func (q *FakeQuerier) GetTemplateInsights(_ context.Context, arg database.GetTem
34073407
returnrow,nil
34083408
}
34093409

3410-
func (q*FakeQuerier)GetTemplateInsightsByInterval(ctx context.Context,arg database.GetTemplateInsightsByIntervalParams) ([]database.GetTemplateInsightsByIntervalRow,error) {
3410+
func (q*FakeQuerier)GetTemplateInsightsByInterval(_ context.Context,arg database.GetTemplateInsightsByIntervalParams) ([]database.GetTemplateInsightsByIntervalRow,error) {
34113411
err:=validateDatabaseType(arg)
34123412
iferr!=nil {
34133413
returnnil,err
@@ -3416,82 +3416,89 @@ func (q *FakeQuerier) GetTemplateInsightsByInterval(ctx context.Context, arg dat
34163416
q.mutex.RLock()
34173417
deferq.mutex.RUnlock()
34183418

3419-
typestatByIntervalstruct {
3420-
startTime,endTime time.Time
3421-
userSetmap[uuid.UUID]struct{}
3422-
templateIDSetmap[uuid.UUID]struct{}
3423-
}
3419+
/*
3420+
WITH
3421+
ts AS (
3422+
SELECT
3423+
d::timestamptz AS from_,
3424+
CASE
3425+
WHEN (d::timestamptz + (@interval_days::int || ' day')::interval) <= @end_time::timestamptz
3426+
THEN (d::timestamptz + (@interval_days::int || ' day')::interval)
3427+
ELSE @end_time::timestamptz
3428+
END AS to_
3429+
FROM
3430+
-- Subtract 1 microsecond from end_time to avoid including the next interval in the results.
3431+
generate_series(@start_time::timestamptz, (@end_time::timestamptz) - '1 microsecond'::interval, (@interval_days::int || ' day')::interval) AS d
3432+
)
34243433
3425-
statsByInterval:= []statByInterval{{arg.StartTime,arg.StartTime.AddDate(0,0,int(arg.IntervalDays)),make(map[uuid.UUID]struct{}),make(map[uuid.UUID]struct{})}}
3426-
forstatsByInterval[len(statsByInterval)-1].endTime.Before(arg.EndTime) {
3427-
statsByInterval=append(statsByInterval,statByInterval{statsByInterval[len(statsByInterval)-1].endTime,statsByInterval[len(statsByInterval)-1].endTime.AddDate(0,0,int(arg.IntervalDays)),make(map[uuid.UUID]struct{}),make(map[uuid.UUID]struct{})})
3428-
}
3429-
ifstatsByInterval[len(statsByInterval)-1].endTime.After(arg.EndTime) {
3430-
statsByInterval[len(statsByInterval)-1].endTime=arg.EndTime
3431-
}
3434+
SELECT
3435+
ts.from_ AS start_time,
3436+
ts.to_ AS end_time,
3437+
array_remove(array_agg(DISTINCT tus.template_id), NULL)::uuid[] AS template_ids,
3438+
COUNT(DISTINCT tus.user_id) AS active_users
3439+
FROM
3440+
ts
3441+
LEFT JOIN
3442+
template_usage_stats AS tus
3443+
ON
3444+
tus.start_time >= ts.from_
3445+
AND tus.end_time <= ts.to_
3446+
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN tus.template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
3447+
GROUP BY
3448+
ts.from_, ts.to_;
3449+
*/
34323450

3433-
for_,s:=rangeq.workspaceAgentStats {
3434-
ifs.CreatedAt.Before(arg.StartTime)||s.CreatedAt.Equal(arg.EndTime)||s.CreatedAt.After(arg.EndTime) {
3435-
continue
3436-
}
3437-
iflen(arg.TemplateIDs)>0&&!slices.Contains(arg.TemplateIDs,s.TemplateID) {
3438-
continue
3439-
}
3440-
ifs.ConnectionCount==0 {
3441-
continue
3451+
typeintervalstruct {
3452+
From time.Time
3453+
To time.Time
3454+
}
3455+
varts []interval
3456+
ford:=arg.StartTime;d.Before(arg.EndTime);d=d.AddDate(0,0,int(arg.IntervalDays)) {
3457+
to:=d.AddDate(0,0,int(arg.IntervalDays))
3458+
ifto.After(arg.EndTime) {
3459+
to=arg.EndTime
34423460
}
3461+
ts=append(ts,interval{From:d,To:to})
3462+
}
34433463

3444-
for_,ds:=rangestatsByInterval {
3445-
ifs.CreatedAt.Before(ds.startTime)||s.CreatedAt.Equal(ds.endTime)||s.CreatedAt.After(ds.endTime) {
3464+
typegroupedstruct {
3465+
TemplateIDsmap[uuid.UUID]struct{}
3466+
UserIDsmap[uuid.UUID]struct{}
3467+
}
3468+
groupedByInterval:=make(map[interval]grouped)
3469+
for_,tus:=rangeq.templateUsageStats {
3470+
for_,t:=rangets {
3471+
iftus.StartTime.Before(t.From)||tus.EndTime.After(t.To) {
34463472
continue
34473473
}
3448-
ds.userSet[s.UserID]=struct{}{}
3449-
ds.templateIDSet[s.TemplateID]=struct{}{}
3450-
}
3451-
}
3452-
3453-
for_,s:=rangeq.workspaceAppStats {
3454-
w,err:=q.getWorkspaceByIDNoLock(ctx,s.WorkspaceID)
3455-
iferr!=nil {
3456-
returnnil,err
3457-
}
3458-
3459-
iflen(arg.TemplateIDs)>0&&!slices.Contains(arg.TemplateIDs,w.TemplateID) {
3460-
continue
3461-
}
3462-
3463-
for_,ds:=rangestatsByInterval {
3464-
// (was.session_started_at >= ts.from_ AND was.session_started_at < ts.to_)
3465-
// OR (was.session_ended_at > ts.from_ AND was.session_ended_at < ts.to_)
3466-
// OR (was.session_started_at < ts.from_ AND was.session_ended_at >= ts.to_)
3467-
if!(((s.SessionStartedAt.After(ds.startTime)||s.SessionStartedAt.Equal(ds.startTime))&&s.SessionStartedAt.Before(ds.endTime))||
3468-
(s.SessionEndedAt.After(ds.startTime)&&s.SessionEndedAt.Before(ds.endTime))||
3469-
(s.SessionStartedAt.Before(ds.startTime)&& (s.SessionEndedAt.After(ds.endTime)||s.SessionEndedAt.Equal(ds.endTime)))) {
3474+
iflen(arg.TemplateIDs)>0&&!slices.Contains(arg.TemplateIDs,tus.TemplateID) {
34703475
continue
34713476
}
3472-
3473-
ds.userSet[s.UserID]=struct{}{}
3474-
ds.templateIDSet[w.TemplateID]=struct{}{}
3477+
g,ok:=groupedByInterval[t]
3478+
if!ok {
3479+
g=grouped{
3480+
TemplateIDs:make(map[uuid.UUID]struct{}),
3481+
UserIDs:make(map[uuid.UUID]struct{}),
3482+
}
3483+
}
3484+
g.TemplateIDs[tus.TemplateID]=struct{}{}
3485+
g.UserIDs[tus.UserID]=struct{}{}
3486+
groupedByInterval[t]=g
34753487
}
34763488
}
34773489

3478-
varresult []database.GetTemplateInsightsByIntervalRow
3479-
for_,ds:=rangestatsByInterval {
3480-
templateIDs:=make([]uuid.UUID,0,len(ds.templateIDSet))
3481-
fortemplateID:=rangeds.templateIDSet {
3482-
templateIDs=append(templateIDs,templateID)
3490+
varrows []database.GetTemplateInsightsByIntervalRow
3491+
for_,t:=rangets {// Ordered by interval.
3492+
row:=database.GetTemplateInsightsByIntervalRow{
3493+
StartTime:t.From,
3494+
EndTime:t.To,
34833495
}
3484-
slices.SortFunc(templateIDs,func(a,b uuid.UUID)int {
3485-
returnslice.Ascending(a.String(),b.String())
3486-
})
3487-
result=append(result, database.GetTemplateInsightsByIntervalRow{
3488-
StartTime:ds.startTime,
3489-
EndTime:ds.endTime,
3490-
TemplateIDs:templateIDs,
3491-
ActiveUsers:int64(len(ds.userSet)),
3492-
})
3496+
row.TemplateIDs=uniqueSortedUUIDs(maps.Keys(groupedByInterval[t].TemplateIDs))
3497+
row.ActiveUsers=int64(len(groupedByInterval[t].UserIDs))
3498+
rows=append(rows,row)
34933499
}
3494-
returnresult,nil
3500+
3501+
returnrows,nil
34953502
}
34963503

34973504
func (q*FakeQuerier)GetTemplateInsightsByTemplate(_ context.Context,arg database.GetTemplateInsightsByTemplateParams) ([]database.GetTemplateInsightsByTemplateRow,error) {

‎coderd/database/queries.sql.go

Lines changed: 28 additions & 57 deletions
Some generated files are not rendered by default. Learn more aboutcustomizing how changed files appear on GitHub.

‎coderd/database/queries/insights.sql

Lines changed: 26 additions & 55 deletions
Original file line numberDiff line numberDiff line change
@@ -297,64 +297,35 @@ GROUP BY template_id, display_name, slug_or_port;
297297
-- time, if end time is a partial interval, it will be included in the results and
298298
-- that interval will be shorter than a full one. If there is no data for a selected
299299
-- interval/template, it will be included in the results with 0 active users.
300-
WITH tsAS (
301-
SELECT
302-
d::timestamptzAS from_,
303-
CASE
304-
WHEN (d::timestamptz+ (@interval_days::int||' day')::interval)<= @end_time::timestamptz
305-
THEN (d::timestamptz+ (@interval_days::int||' day')::interval)
306-
ELSE @end_time::timestamptz
307-
ENDAS to_
308-
FROM
309-
-- Subtract 1 microsecond from end_time to avoid including the next interval in the results.
310-
generate_series(@start_time::timestamptz, (@end_time::timestamptz)-'1 microsecond'::interval, (@interval_days::int||' day')::interval)AS d
311-
), unflattened_usage_by_intervalAS (
312-
-- We select data from both workspace agent stats and workspace app stats to
313-
-- get a complete picture of usage. This matches how usage is calculated by
314-
-- the combination of GetTemplateInsights and GetTemplateAppInsights. We use
315-
-- a union all to avoid a costly distinct operation.
316-
--
317-
-- Note that one query must perform a left join so that all intervals are
318-
-- present at least once.
319-
SELECT
320-
ts.*,
321-
was.template_id,
322-
was.user_id
323-
FROM ts
324-
LEFT JOIN workspace_agent_stats wasON (
325-
was.created_at>=ts.from_
326-
ANDwas.created_at<ts.to_
327-
ANDwas.connection_count>0
328-
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[],1),0)>0 THENwas.template_id= ANY(@template_ids::uuid[]) ELSE TRUE END
329-
)
330-
GROUP BYts.from_,ts.to_,was.template_id,was.user_id
331-
332-
UNION ALL
333-
334-
SELECT
335-
ts.*,
336-
w.template_id,
337-
was.user_id
338-
FROM ts
339-
JOIN workspace_app_stats wasON (
340-
(was.session_started_at>=ts.from_ANDwas.session_started_at<ts.to_)
341-
OR (was.session_ended_at>ts.from_ANDwas.session_ended_at<ts.to_)
342-
OR (was.session_started_at<ts.from_ANDwas.session_ended_at>=ts.to_)
343-
)
344-
JOIN workspaces wON (
345-
w.id=was.workspace_id
346-
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[],1),0)>0 THENw.template_id= ANY(@template_ids::uuid[]) ELSE TRUE END
300+
WITH
301+
tsAS (
302+
SELECT
303+
d::timestamptzAS from_,
304+
CASE
305+
WHEN (d::timestamptz+ (@interval_days::int||' day')::interval)<= @end_time::timestamptz
306+
THEN (d::timestamptz+ (@interval_days::int||' day')::interval)
307+
ELSE @end_time::timestamptz
308+
ENDAS to_
309+
FROM
310+
-- Subtract 1 microsecond from end_time to avoid including the next interval in the results.
311+
generate_series(@start_time::timestamptz, (@end_time::timestamptz)-'1 microsecond'::interval, (@interval_days::int||' day')::interval)AS d
347312
)
348-
GROUP BYts.from_,ts.to_,w.template_id,was.user_id
349-
)
350313

351314
SELECT
352-
from_AS start_time,
353-
to_AS end_time,
354-
array_remove(array_agg(DISTINCT template_id),NULL)::uuid[]AS template_ids,
355-
COUNT(DISTINCT user_id)AS active_users
356-
FROM unflattened_usage_by_interval
357-
GROUP BY from_, to_;
315+
ts.from_AS start_time,
316+
ts.to_AS end_time,
317+
array_remove(array_agg(DISTINCTtus.template_id),NULL)::uuid[]AS template_ids,
318+
COUNT(DISTINCTtus.user_id)AS active_users
319+
FROM
320+
ts
321+
LEFT JOIN
322+
template_usage_statsAS tus
323+
ON
324+
tus.start_time>=ts.from_
325+
ANDtus.end_time<=ts.to_
326+
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[],1),0)>0 THENtus.template_id= ANY(@template_ids::uuid[]) ELSE TRUE END
327+
GROUP BY
328+
ts.from_,ts.to_;
358329

359330
-- name: GetTemplateUsageStats :many
360331
SELECT

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp