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

Commitc2965f4

Browse files
committed
feat(coderd/database): rewriteGetUserActivityInsights to usetemplate_usage_stats
1 parent83f3b78 commitc2965f4

8 files changed

+100
-146
lines changed

‎coderd/database/querier.go

Lines changed: 5 additions & 5 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: 46 additions & 69 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: 44 additions & 67 deletions
Original file line numberDiff line numberDiff line change
@@ -28,80 +28,57 @@ ORDER BY
2828

2929
-- name: GetUserActivityInsights :many
3030
-- GetUserActivityInsights returns the ranking with top active users.
31-
-- The result can be filtered on template_ids, meaning only user data from workspaces
32-
-- based on those templates will be included.
33-
-- Note:When selecting data from multiple templates or the entire deployment,
34-
--be aware that it may lead to an increase in "usage" numbers (cumulative). In such cases,
35-
--users may be counted multiple times for the same time intervalifthey have used multiple templates
31+
-- The result can be filtered on template_ids, meaning only user data
32+
--from workspacesbased on those templates will be included.
33+
-- Note:The usage_seconds and usage_seconds_cumulative differ only when
34+
--requesting deployment-wide (or multiple template) data. Cumulative
35+
--produces a bloated valueifa user has used multiple templates
3636
-- simultaneously.
37-
WITH app_statsAS (
38-
SELECT
39-
s.start_time,
40-
was.user_id,
41-
w.template_id,
42-
60as seconds
43-
FROM workspace_app_stats was
44-
JOIN workspaces wON (
45-
w.id=was.workspace_id
46-
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[],1),0)>0 THENw.template_id= ANY(@template_ids::uuid[]) ELSE TRUE END
47-
)
48-
-- This table contains both 1 minute entries and >1 minute entries,
49-
-- to calculate this with our uniqueness constraints, we generate series
50-
-- for the longer intervals.
51-
CROSS JOIN LATERAL generate_series(
52-
date_trunc('minute',was.session_started_at),
53-
-- Subtract 1 microsecond to avoid creating an extra series.
54-
date_trunc('minute',was.session_ended_at-'1 microsecond'::interval),
55-
'1 minute'::interval
56-
) s(start_time)
57-
WHERE
58-
s.start_time>= @start_time::timestamptz
59-
-- Subtract one minute because the series only contains the start time.
60-
ANDs.start_time< (@end_time::timestamptz)-'1 minute'::interval
61-
GROUP BYs.start_time,w.template_id,was.user_id
62-
), session_statsAS (
63-
SELECT
64-
date_trunc('minute',was.created_at)as start_time,
65-
was.user_id,
66-
was.template_id,
67-
CASE WHEN
68-
SUM(was.session_count_vscode)>0OR
69-
SUM(was.session_count_jetbrains)>0OR
70-
SUM(was.session_count_reconnecting_pty)>0OR
71-
SUM(was.session_count_ssh)>0
72-
THEN60 ELSE0 ENDas seconds
73-
FROM workspace_agent_stats was
74-
WHERE
75-
was.created_at>= @start_time::timestamptz
76-
ANDwas.created_at< @end_time::timestamptz
77-
ANDwas.connection_count>0
78-
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[],1),0)>0 THENwas.template_id= ANY(@template_ids::uuid[]) ELSE TRUE END
79-
GROUP BY date_trunc('minute',was.created_at),was.user_id,was.template_id
80-
), combined_statsAS (
37+
WITH deployment_statsAS (
8138
SELECT
82-
user_id,
83-
template_id,
8439
start_time,
85-
seconds
86-
FROM app_stats
87-
UNION
40+
user_id,
41+
array_agg(template_id)AS template_ids,
42+
-- See motivation in GetTemplateInsights for LEAST(SUM(n), 30).
43+
LEAST(SUM(usage_mins),30)AS usage_mins
44+
FROM
45+
template_usage_stats
46+
WHERE
47+
start_time>= @start_time::timestamptz
48+
AND end_time<= @end_time::timestamptz
49+
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[],1),0)>0 THEN template_id= ANY(@template_ids::uuid[]) ELSE TRUE END
50+
GROUP BY
51+
start_time, user_id
52+
), template_idsAS (
8853
SELECT
8954
user_id,
90-
template_id,
91-
start_time,
92-
seconds
93-
FROM session_stats
55+
array_agg(DISTINCT template_id)AS ids
56+
FROM
57+
deployment_stats, unnest(template_ids) template_id
58+
GROUP BY
59+
user_id
9460
)
61+
9562
SELECT
96-
users.idas user_id,
97-
users.username,
98-
users.avatar_url,
99-
array_agg(DISTINCT template_id)::uuid[]AS template_ids,
100-
SUM(seconds)AS usage_seconds
101-
FROM combined_stats
102-
JOIN usersON (users.id=combined_stats.user_id)
103-
GROUP BYusers.id, username, avatar_url
104-
ORDER BY user_idASC;
63+
ds.user_id,
64+
u.username,
65+
u.avatar_url,
66+
t.ids::uuid[]AS template_ids,
67+
(SUM(ds.usage_mins)*60)::bigintAS usage_seconds
68+
FROM
69+
deployment_stats ds
70+
JOIN
71+
users u
72+
ON
73+
u.id=ds.user_id
74+
JOIN
75+
template_ids t
76+
ON
77+
ds.user_id=t.user_id
78+
GROUP BY
79+
ds.user_id,u.username,u.avatar_url,t.ids
80+
ORDER BY
81+
ds.user_idASC;
10582

10683
-- name: GetTemplateInsights :one
10784
-- GetTemplateInsights returns the aggregate user-produced usage of all

‎coderd/testdata/insights/user-activity/multiple_users_and_workspaces_week_all_templates.json.golden

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,7 @@
1616
"user_id": "00000000-0000-0000-0000-000000000004",
1717
"username": "user1",
1818
"avatar_url": "",
19-
"seconds":30540
19+
"seconds":26820
2020
},
2121
{
2222
"template_ids": [

‎coderd/testdata/insights/user-activity/multiple_users_and_workspaces_week_deployment_wide.json.golden

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,7 @@
1616
"user_id": "00000000-0000-0000-0000-000000000004",
1717
"username": "user1",
1818
"avatar_url": "",
19-
"seconds":30540
19+
"seconds":26820
2020
},
2121
{
2222
"template_ids": [

‎coderd/testdata/insights/user-activity/multiple_users_and_workspaces_week_other_timezone_(São_Paulo).json.golden

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,7 @@
1616
"user_id": "00000000-0000-0000-0000-000000000004",
1717
"username": "user1",
1818
"avatar_url": "",
19-
"seconds":23280
19+
"seconds":23160
2020
},
2121
{
2222
"template_ids": [

‎coderd/testdata/insights/user-activity/multiple_users_and_workspaces_weekly_aggregated_deployment_wide.json.golden

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,7 @@
1616
"user_id": "00000000-0000-0000-0000-000000000004",
1717
"username": "user1",
1818
"avatar_url": "",
19-
"seconds":29820
19+
"seconds":26100
2020
},
2121
{
2222
"template_ids": [

‎coderd/testdata/insights/user-activity/multiple_users_and_workspaces_weekly_aggregated_templates.json.golden

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,7 @@
1616
"user_id": "00000000-0000-0000-0000-000000000004",
1717
"username": "user1",
1818
"avatar_url": "",
19-
"seconds":29820
19+
"seconds":26100
2020
},
2121
{
2222
"template_ids": [

0 commit comments

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