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

Commitad362b5

Browse files
committed
rewrite query
1 parent6b0c9b7 commitad362b5

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
@@ -3280,7 +3280,7 @@ func (q *FakeQuerier) GetTemplateAppInsightsByTemplate(ctx context.Context, arg
32803280
for_,usageKey:=rangeusageKeys {
32813281
r:= database.GetTemplateAppInsightsByTemplateRow{
32823282
TemplateID:usageKey.TemplateID,
3283-
DisplayName:sql.NullString{String:usageKey.DisplayName,Valid:true},
3283+
DisplayName:usageKey.DisplayName,
32843284
SlugOrPort:usageKey.Slug,
32853285
}
32863286
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
@@ -294,51 +294,70 @@ GROUP BY
294294
t.template_ids,ai.app_name,ai.display_name,ai.icon,ai.is_app;
295295

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

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

343362
-- name: GetTemplateInsightsByInterval :many
344363
-- 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