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

Commite15161f

Browse files
committed
rewrite query
1 parente4f1de7 commite15161f

File tree

5 files changed

+130
-91
lines changed

5 files changed

+130
-91
lines changed

‎coderd/database/dbmem/dbmem.go

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3278,7 +3278,7 @@ func (q *FakeQuerier) GetTemplateAppInsightsByTemplate(ctx context.Context, arg
32783278
for_,usageKey:=rangeusageKeys {
32793279
r:= database.GetTemplateAppInsightsByTemplateRow{
32803280
TemplateID:usageKey.TemplateID,
3281-
DisplayName:sql.NullString{String:usageKey.DisplayName,Valid:true},
3281+
DisplayName:usageKey.DisplayName,
32823282
SlugOrPort:usageKey.Slug,
32833283
}
32843284
for_,mUserUsage:=rangeusageByTemplateAppUser[usageKey] {

‎coderd/database/querier.go

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

‎coderd/database/queries.sql.go

Lines changed: 66 additions & 48 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: 60 additions & 41 deletions
Original file line numberDiff line numberDiff line change
@@ -293,51 +293,70 @@ GROUP BY
293293
t.template_ids,ai.app_name,ai.display_name,ai.icon,ai.is_app;
294294

295295
-- name: GetTemplateAppInsightsByTemplate :many
296-
WITH app_stats_by_user_and_agentAS (
297-
SELECT
298-
s.start_time,
299-
60as seconds,
300-
w.template_id,
301-
was.user_id,
302-
was.agent_id,
303-
was.slug_or_port,
304-
wa.display_name,
305-
(wa.slugIS NOT NULL)::booleanAS is_app
306-
FROM workspace_app_stats was
307-
JOIN workspaces wON (
308-
w.id=was.workspace_id
309-
)
310-
-- We do a left join here because we want to include user IDs that have used
311-
-- e.g. ports when counting active users.
312-
LEFT JOIN workspace_apps waON (
313-
wa.agent_id=was.agent_id
314-
ANDwa.slug=was.slug_or_port
296+
-- GetTemplateAppInsightsByTemplate is used for Prometheus metrics. Keep
297+
-- in sync with GetTemplateAppInsights and UpsertTemplateUsageStats.
298+
WITH
299+
-- This CTE is used to explode app usage into minute buckets, then
300+
-- flatten the users app usage within the template so that usage in
301+
-- multiple workspaces under one template is only counted once for
302+
-- every minute.
303+
app_insightsAS (
304+
SELECT
305+
w.template_id,
306+
was.user_id,
307+
-- Both app stats and agent stats track web terminal usage, but
308+
-- by different means. The app stats value should be more
309+
-- accurate so we don't want to discard it just yet.
310+
CASE
311+
WHENwas.access_method='terminal'
312+
THEN'[terminal]'-- Unique name, app names can't contain brackets.
313+
ELSEwas.slug_or_port
314+
END::textAS app_name,
315+
COALESCE(wa.display_name,'')AS display_name,
316+
(wa.slugIS NOT NULL)::booleanAS is_app,
317+
COUNT(DISTINCTs.minute_bucket)AS app_minutes
318+
FROM
319+
workspace_app_statsAS was
320+
JOIN
321+
workspacesAS w
322+
ON
323+
w.id=was.workspace_id
324+
-- We do a left join here because we want to include user IDs that have used
325+
-- e.g. ports when counting active users.
326+
LEFT JOIN
327+
workspace_apps wa
328+
ON
329+
wa.agent_id=was.agent_id
330+
ANDwa.slug=was.slug_or_port
331+
-- Generate a series of minute buckets for each session for computing the
332+
-- mintes/bucket.
333+
CROSS JOIN
334+
generate_series(
335+
date_trunc('minute',was.session_started_at),
336+
-- Subtract 1 microsecond to avoid creating an extra series.
337+
date_trunc('minute',was.session_ended_at-'1 microsecond'::interval),
338+
'1 minute'::interval
339+
)AS s(minute_bucket)
340+
WHERE
341+
s.minute_bucket>= @start_time::timestamptz
342+
ANDs.minute_bucket< @end_time::timestamptz
343+
GROUP BY
344+
w.template_id,was.user_id,was.access_method,was.slug_or_port,wa.display_name,wa.slug
315345
)
316-
-- This table contains both 1 minute entries and >1 minute entries,
317-
-- to calculate this with our uniqueness constraints, we generate series
318-
-- for the longer intervals.
319-
CROSS JOIN LATERAL generate_series(
320-
date_trunc('minute',was.session_started_at),
321-
-- Subtract 1 microsecond to avoid creating an extra series.
322-
date_trunc('minute',was.session_ended_at-'1 microsecond'::interval),
323-
'1 minute'::interval
324-
) s(start_time)
325-
WHERE
326-
s.start_time>= @start_time::timestamptz
327-
-- Subtract one minute because the series only contains the start time.
328-
ANDs.start_time< (@end_time::timestamptz)-'1 minute'::interval
329-
GROUP BYs.start_time,w.template_id,was.user_id,was.agent_id,was.slug_or_port,wa.display_name,wa.slug
330-
)
331346

332347
SELECT
333348
template_id,
334-
display_name,
335-
slug_or_port,
336-
COALESCE(COUNT(DISTINCT user_id))::bigintAS active_users,
337-
SUM(seconds)AS usage_seconds
338-
FROM app_stats_by_user_and_agent
339-
WHERE is_app IS TRUE
340-
GROUP BY template_id, display_name, slug_or_port;
349+
app_nameAS slug_or_port,
350+
display_nameAS display_name,
351+
COUNT(DISTINCT user_id)::bigintAS active_users,
352+
(SUM(app_minutes)*60)::bigintAS usage_seconds
353+
FROM
354+
app_insights
355+
WHERE
356+
is_app IS TRUE
357+
GROUP BY
358+
template_id, slug_or_port, display_name;
359+
341360

342361
-- name: GetTemplateInsightsByInterval :many
343362
-- GetTemplateInsightsByInterval returns all intervals between start and end

‎coderd/prometheusmetrics/insights/metricscollector.go

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -194,7 +194,7 @@ func (mc *MetricsCollector) Collect(metricsCh chan<- prometheus.Metric) {
194194
// Custom apps
195195
for_,appRow:=rangedata.apps {
196196
metricsCh<-prometheus.MustNewConstMetric(applicationsUsageSecondsDesc,prometheus.GaugeValue,float64(appRow.UsageSeconds),data.templateNames[appRow.TemplateID],
197-
appRow.DisplayName.String,appRow.SlugOrPort)
197+
appRow.DisplayName,appRow.SlugOrPort)
198198
}
199199

200200
// Built-in apps

0 commit comments

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