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

Commit7fa004c

Browse files
committed
feat(coderd/database): rewriteGetTemplateAppInsights to usetemplate_usage_stats
1 parentf369461 commit7fa004c

File tree

4 files changed

+186
-114
lines changed

4 files changed

+186
-114
lines changed

‎coderd/database/dbmem/dbmem.go

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2988,8 +2988,8 @@ func (q *FakeQuerier) GetTemplateAppInsights(ctx context.Context, arg database.G
29882988
ActiveUserIDs:activeUserIDs,
29892989
AccessMethod:appKey.AccessMethod,
29902990
SlugOrPort:appKey.SlugOrPort,
2991-
DisplayName:sql.NullString{String:appKey.DisplayName,Valid:appKey.DisplayName!=""},
2992-
Icon:sql.NullString{String:appKey.Icon,Valid:appKey.Icon!=""},
2991+
DisplayName:appKey.DisplayName,
2992+
Icon:appKey.Icon,
29932993
IsApp:appKey.Slug!="",
29942994
UsageSeconds:usage,
29952995
})

‎coderd/database/queries.sql.go

Lines changed: 94 additions & 58 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: 84 additions & 48 deletions
Original file line numberDiff line numberDiff line change
@@ -182,57 +182,93 @@ GROUP BY template_id;
182182
-- GetTemplateAppInsights returns the aggregate usage of each app in a given
183183
-- timeframe. The result can be filtered on template_ids, meaning only user data
184184
-- from workspaces based on those templates will be included.
185-
WITH app_stats_by_user_and_agentAS (
186-
SELECT
187-
s.start_time,
188-
60as seconds,
189-
w.template_id,
190-
was.user_id,
191-
was.agent_id,
192-
was.access_method,
193-
was.slug_or_port,
194-
wa.display_name,
195-
wa.icon,
196-
(wa.slugIS NOT NULL)::booleanAS is_app
197-
FROM workspace_app_stats was
198-
JOIN workspaces wON (
199-
w.id=was.workspace_id
200-
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[],1),0)>0 THENw.template_id= ANY(@template_ids::uuid[]) ELSE TRUE END
201-
)
202-
-- We do a left join here because we want to include user IDs that have used
203-
-- e.g. ports when counting active users.
204-
LEFT JOIN workspace_apps waON (
205-
wa.agent_id=was.agent_id
206-
ANDwa.slug=was.slug_or_port
185+
WITH
186+
app_insightsAS (
187+
SELECT
188+
tus.user_id,
189+
array_agg(DISTINCTtus.template_id)::uuid[]AS template_ids,
190+
app_usage.key::textAS app_name,
191+
COALESCE(wa.display_name,'')AS display_name,
192+
COALESCE(wa.icon,'')AS icon,
193+
(wa.slugIS NOT NULL)::booleanAS is_app,
194+
LEAST(SUM(app_usage.value::int),30)AS app_usage_mins
195+
FROM
196+
template_usage_statsAS tus, jsonb_each(app_usage_mins)AS app_usage
197+
LEFT JOIN LATERAL (
198+
-- Fetch the latest app info for each app based on slug and template.
199+
SELECT
200+
app.display_name,
201+
app.icon,
202+
app.slug
203+
FROM
204+
workspace_appsAS app
205+
JOIN
206+
workspace_agentsAS agent
207+
ON
208+
agent.id=app.agent_id
209+
JOIN
210+
workspace_resourcesAS resource
211+
ON
212+
resource.id=agent.resource_id
213+
JOIN
214+
workspace_buildsAS build
215+
ON
216+
build.job_id=resource.job_id
217+
JOIN
218+
workspacesAS workspace
219+
ON
220+
workspace.id=build.workspace_id
221+
WHERE
222+
-- Requires lateral join.
223+
app.slug=app_usage.key
224+
ANDworkspace.owner_id=tus.user_id
225+
ANDworkspace.template_id=tus.template_id
226+
ORDER BY
227+
app.created_atDESC
228+
LIMIT1
229+
)AS wa
230+
ON
231+
true
232+
WHERE
233+
tus.start_time>= @start_time::timestamptz
234+
ANDtus.end_time<= @end_time::timestamptz
235+
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[],1),0)>0 THENtus.template_id= ANY(@template_ids::uuid[]) ELSE TRUE END
236+
GROUP BY
237+
tus.start_time,tus.user_id,app_usage.key::text,wa.display_name,wa.icon,wa.slug
238+
),
239+
templatesAS (
240+
SELECT
241+
app_name,
242+
display_name,
243+
icon,
244+
is_app,
245+
array_agg(DISTINCT template_id)::uuid[]AS template_ids
246+
FROM
247+
app_insights, unnest(template_ids)AS template_id
248+
GROUP BY
249+
app_name, display_name, icon, is_app
207250
)
208-
-- This table contains both 1 minute entries and >1 minute entries,
209-
-- to calculate this with our uniqueness constraints, we generate series
210-
-- for the longer intervals.
211-
CROSS JOIN LATERAL generate_series(
212-
date_trunc('minute',was.session_started_at),
213-
-- Subtract 1 microsecond to avoid creating an extra series.
214-
date_trunc('minute',was.session_ended_at-'1 microsecond'::interval),
215-
'1 minute'::interval
216-
) s(start_time)
217-
WHERE
218-
s.start_time>= @start_time::timestamptz
219-
-- Subtract one minute because the series only contains the start time.
220-
ANDs.start_time< (@end_time::timestamptz)-'1 minute'::interval
221-
GROUP BYs.start_time,w.template_id,was.user_id,was.agent_id,was.access_method,was.slug_or_port,wa.display_name,wa.icon,wa.slug
222-
)
223251

224252
SELECT
225-
array_agg(DISTINCT template_id)::uuid[]AS template_ids,
226-
-- Return IDs so we can combine this with GetTemplateInsights.
227-
array_agg(DISTINCT user_id)::uuid[]AS active_user_ids,
228-
access_method,
229-
slug_or_port,
230-
display_name,
231-
icon,
232-
is_app,
233-
SUM(seconds)AS usage_seconds
234-
FROM app_stats_by_user_and_agent
235-
GROUP BY access_method, slug_or_port, display_name, icon, is_app;
253+
t.template_ids,
254+
array_agg(DISTINCTai.user_id)::uuid[]AS active_user_ids,
255+
''::textAS access_method,-- TODO(mafredri): Remove.
256+
ai.app_nameAS slug_or_port,
257+
ai.display_name,
258+
ai.icon,
259+
ai.is_app,
260+
(SUM(ai.app_usage_mins)*60)::bigintAS usage_seconds
261+
FROM
262+
app_insightsAS ai
263+
JOIN
264+
templatesAS t
265+
ON
266+
ai.app_name=t.app_name
267+
ANDai.display_name=t.display_name
268+
ANDai.icon=t.icon
269+
ANDai.is_app=t.is_app
270+
GROUP BY
271+
t.template_ids,ai.app_name,ai.display_name,ai.icon,ai.is_app;
236272

237273
-- name: GetTemplateAppInsightsByTemplate :many
238274
WITH app_stats_by_user_and_agentAS (

‎coderd/insights.go

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -474,11 +474,11 @@ func convertTemplateInsightsApps(usage database.GetTemplateInsightsRow, appUsage
474474
ifa.SlugOrPort!=b.SlugOrPort {
475475
returnstrings.Compare(a.SlugOrPort,b.SlugOrPort)
476476
}
477-
ifa.DisplayName.String!=b.DisplayName.String {
478-
returnstrings.Compare(a.DisplayName.String,b.DisplayName.String)
477+
ifa.DisplayName!=b.DisplayName {
478+
returnstrings.Compare(a.DisplayName,b.DisplayName)
479479
}
480-
ifa.Icon.String!=b.Icon.String {
481-
returnstrings.Compare(a.Icon.String,b.Icon.String)
480+
ifa.Icon!=b.Icon {
481+
returnstrings.Compare(a.Icon,b.Icon)
482482
}
483483
if!a.IsApp&&b.IsApp {
484484
return-1
@@ -496,9 +496,9 @@ func convertTemplateInsightsApps(usage database.GetTemplateInsightsRow, appUsage
496496
apps=append(apps, codersdk.TemplateAppUsage{
497497
TemplateIDs:app.TemplateIDs,
498498
Type:codersdk.TemplateAppsTypeApp,
499-
DisplayName:app.DisplayName.String,
499+
DisplayName:app.DisplayName,
500500
Slug:app.SlugOrPort,
501-
Icon:app.Icon.String,
501+
Icon:app.Icon,
502502
Seconds:app.UsageSeconds,
503503
})
504504
}

0 commit comments

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