Jira отчеты в Grafana
Это отчеты для Jira v9.12.5
Важно для себя: SQL – актуальные, но SQL в json могут быть неактуальные, как и сами json.
Кто без задач (datepicker не используется)
Пояснение: remaining_days_kpi показывает, реально ли данному человеку выдать внеплановых задач или нет. Данное поле содержит кол-во рабочих дней которые требуются чтобы закрыть КПЭ задачи. Важно не забывать про отпуска, они здесь не учтены.
SELECT
cu.display_name AS user_name,
COUNT(*) FILTER (
WHERE ist.statuscategory = 2
AND ist.pname <> 'Postponed'
) AS open_issues,
COUNT(*) FILTER (WHERE ist.pname = 'In Progress') AS in_progress,
COUNT(*) FILTER (WHERE ist.pname = 'In Review') AS in_review,
COUNT(*) FILTER (
WHERE ist.pname != 'In Progress'
AND ist.statuscategory NOT IN (2,3) -- 2: Open, 3: Closed
) AS in_other_status,
COUNT(*) FILTER (
WHERE ist.statuscategory = 2
AND ist.pname <> 'Postponed'
AND (ji.timeoriginalestimate IS NULL OR ji.timeoriginalestimate = 0)
) AS open_without_estimate,
COUNT(*) FILTER (
WHERE ist.pname = 'In Progress'
AND (ji.timeoriginalestimate IS NULL OR ji.timeoriginalestimate = 0)
) AS in_progress_without_estimate,
-- 🔢 Remaining Estimate (в часах, с учётом списаний)
-- 3. open + in progress + in review
ROUND(
SUM(
GREATEST(
COALESCE(ji.timeoriginalestimate, 0)
- COALESCE(wl.logged_seconds, 0),
0
)
) FILTER (
WHERE (ist.statuscategory = 2 AND ist.pname <> 'Postponed')
OR ist.pname = 'In Progress'
OR ist.pname = 'In Review'
) / (3600 * 8),
0
) AS remaining_days,
-- Remaining Estimate по задачам с label = kpi_2026_4 (в рабочих днях)
ROUND(
SUM(
GREATEST(
COALESCE(ji.timeoriginalestimate, 0)
- COALESCE(wl.logged_seconds, 0),
0
)
) FILTER (
WHERE (
(ist.statuscategory = 2 AND ist.pname <> 'Postponed')
OR ist.pname = 'In Progress'
OR ist.pname = 'In Review'
)
AND EXISTS (
SELECT 1
FROM label l
WHERE l.issue = ji.id
AND l.label = 'kpi_2026_4'
)
) / (3600 * 8),
0
) AS remaining_days_kpi
FROM jiraissue ji
JOIN issuetype it ON it.id = ji.issuetype AND it.pname <> 'Epic'
JOIN issuestatus ist ON ist.id::text = ji.issuestatus
JOIN cwd_user cu
ON cu.id = REPLACE(ji.assignee, 'JIRAUSER', '')::bigint
AND cu.user_name IN ($jira_login)
LEFT JOIN (
SELECT
issueid,
SUM(timeworked) AS logged_seconds
FROM worklog
GROUP BY issueid
) wl ON wl.issueid = ji.id
GROUP BY cu.display_name
ORDER BY
remaining_days,
in_progress,
in_review DESC;
Старая версия без автоподстановки имен пользователей + без поля remaining_days_kpi:
SELECT
cu.display_name AS user_name,
COUNT(*) FILTER (
WHERE ist.statuscategory = 2
AND ist.pname <> 'Postponed'
) AS open_issues,
COUNT(*) FILTER (WHERE ist.pname = 'In Progress') AS in_progress,
COUNT(*) FILTER (WHERE ist.pname = 'In Review') AS in_review,
COUNT(*) FILTER (
WHERE ist.pname != 'In Progress'
AND ist.statuscategory NOT IN (2,3) -- 2: Open, 3: Closed
) AS in_other_status,
COUNT(*) FILTER (
WHERE ist.statuscategory = 2
AND ist.pname <> 'Postponed'
AND (ji.timeoriginalestimate IS NULL OR ji.timeoriginalestimate = 0)
) AS open_without_estimate,
COUNT(*) FILTER (
WHERE ist.pname = 'In Progress'
AND (ji.timeoriginalestimate IS NULL OR ji.timeoriginalestimate = 0)
) AS in_progress_without_estimate,
-- 🔢 Remaining Estimate (в часах, с учётом списаний)
-- 3. open + in progress + in review
ROUND(
SUM(
GREATEST(
COALESCE(ji.timeoriginalestimate, 0)
- COALESCE(wl.logged_seconds, 0),
0
)
) FILTER (
WHERE (ist.statuscategory = 2 AND ist.pname <> 'Postponed')
OR ist.pname = 'In Progress'
OR ist.pname = 'In Review'
) / 3600.0,
2
) AS remaining_hours
FROM jiraissue ji
JOIN issuetype it ON it.id = ji.issuetype AND it.pname <> 'Epic'
JOIN issuestatus ist ON ist.id::text = ji.issuestatus
JOIN cwd_user cu
ON cu.id = REPLACE(ji.assignee, 'JIRAUSER', '')::bigint
AND cu.user_name IN (
'MikhailMoskovkin',
'vburlak',
'agordienko',
'adanilkov',
'atsarev',
'dmurzin',
'atumanov',
'nikitakazachkin',
'kfedorov',
'igorryazanov',
'vsemina'
-- 'NikolayLebedenko',
-- eskripaleva
)
LEFT JOIN (
SELECT
issueid,
SUM(timeworked) AS logged_seconds
FROM worklog
GROUP BY issueid
) wl ON wl.issueid = ji.id
GROUP BY cu.display_name
ORDER BY
remaining_hours,
in_progress,
in_review DESC;2:
WITH last_estimate_change AS (
SELECT
cg.issueid,
MAX(cg.created) AS estimate_changed_at
FROM changegroup cg
JOIN changeitem ci
ON ci.groupid = cg.id
AND ci.field = 'timeoriginalestimate'
GROUP BY cg.issueid
),
last_worklog AS (
SELECT
issueid,
MAX(startdate) AS last_worklog_at,
SUM(timeworked) AS total_logged_seconds
FROM worklog
GROUP BY issueid
)
SELECT
p.pkey || '-' || ji.issuenum AS issue_key,
ji.summary,
assignee.display_name AS assignee,
-- указал оценку:
lec.estimate_changed_at,
-- дата последнего списания:
lw.last_worklog_at,
-- но через ⏱ - время между указанием оценки И последним списанием (секунд между estimate_changed_at и last_worklog_at)
EXTRACT(EPOCH FROM (lw.last_worklog_at - lec.estimate_changed_at)) / 3600 AS diff_in_hours,
-- запланировал часов:
ji.timeoriginalestimate / 3600 AS estimated_hours,
-- ⌛ всего списано
lw.total_logged_seconds / 3600 AS logged_hours
FROM jiraissue ji
JOIN project p ON p.id = ji.project
JOIN last_estimate_change lec ON lec.issueid = ji.id
JOIN last_worklog lw ON lw.issueid = ji.id
LEFT JOIN cwd_user assignee ON assignee.id = REPLACE(ji.assignee, 'JIRAUSER', '')::bigint
WHERE
$__timeFilter(ji.created)
AND ji.timeoriginalestimate > 0
AND lw.total_logged_seconds > 0
-- 1.2 это мы даем 20% времени на случаи, когда запланировали 2 часа, и списали 2 часа, но через 1 час 36 минут:
AND lw.total_logged_seconds > (EXTRACT(EPOCH FROM (lw.last_worklog_at - lec.estimate_changed_at)) * 1.2)
-- не будем обращать внимание на мелкие задачи (менее 30 минут, ведь это могут быть просто хотфиксы):
AND lw.total_logged_seconds > 1800
ORDER BY diff_in_hours;{
"id": 7,
"type": "table",
"title": "Задачи с оценкой выставленной перед списанием или в которых сначала было списание, а потом оценка",
"description": "Находим тех, кто хитрит (минус в diff_in_hours означает, что списано раньше указания оценки)",
"gridPos": {
"x": 0,
"y": 26,
"h": 14,
"w": 24
},
"fieldConfig": {
"defaults": {
"custom": {
"align": "auto",
"footer": {
"reducers": [
"sum"
]
},
"cellOptions": {
"type": "auto"
},
"inspect": false
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
},
"color": {
"mode": "thresholds"
}
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "issue_key"
},
"properties": [
{
"id": "custom.width",
"value": 103
}
]
},
{
"matcher": {
"id": "byName",
"options": "assignee"
},
"properties": [
{
"id": "custom.width",
"value": 210
}
]
},
{
"matcher": {
"id": "byName",
"options": "estimate_changed_at"
},
"properties": [
{
"id": "custom.width",
"value": 175
}
]
},
{
"matcher": {
"id": "byName",
"options": "last_worklog_at"
},
"properties": [
{
"id": "custom.width",
"value": 172
}
]
},
{
"matcher": {
"id": "byName",
"options": "diff_in_hours"
},
"properties": [
{
"id": "custom.width",
"value": 113
}
]
},
{
"matcher": {
"id": "byName",
"options": "logged_hours"
},
"properties": [
{
"id": "custom.width",
"value": 116
}
]
},
{
"matcher": {
"id": "byName",
"options": "estimated_hours"
},
"properties": [
{
"id": "custom.width",
"value": 143
}
]
}
]
},
"pluginVersion": "12.3.0",
"targets": [
{
"dataset": "jira",
"datasource": {
"type": "grafana-postgresql-datasource",
"uid": "df798g5q84h6oc"
},
"editorMode": "code",
"format": "table",
"rawQuery": true,
"rawSql": "WITH last_estimate_change AS (\n SELECT\n cg.issueid,\n MAX(cg.created) AS estimate_changed_at\n FROM changegroup cg\n JOIN changeitem ci\n ON ci.groupid = cg.id\n AND ci.field = 'timeoriginalestimate'\n GROUP BY cg.issueid\n),\n\nlast_worklog AS (\n SELECT\n issueid,\n MAX(startdate) AS last_worklog_at,\n SUM(timeworked) AS total_logged_seconds\n FROM worklog\n GROUP BY issueid\n)\n\nSELECT\n p.pkey || '-' || ji.issuenum AS issue_key,\n ji.summary,\n assignee.display_name AS assignee,\n -- указал оценку:\n lec.estimate_changed_at,\n -- дата последнего списания:\n lw.last_worklog_at,\n -- но через ⏱ - время между указанием оценки И последним списанием (секунд между estimate_changed_at и last_worklog_at)\n EXTRACT(EPOCH FROM (lw.last_worklog_at - lec.estimate_changed_at)) / 3600 AS diff_in_hours,\n -- запланировал часов:\n ji.timeoriginalestimate / 3600 AS estimated_hours,\n -- ⌛ всего списано\n lw.total_logged_seconds / 3600 AS logged_hours\n\nFROM jiraissue ji\nJOIN project p ON p.id = ji.project\nJOIN last_estimate_change lec ON lec.issueid = ji.id\nJOIN last_worklog lw ON lw.issueid = ji.id\nLEFT JOIN cwd_user assignee ON assignee.id = REPLACE(ji.assignee, 'JIRAUSER', '')::bigint\nWHERE\n $__timeFilter(ji.created)\n AND ji.timeoriginalestimate > 0\n AND lw.total_logged_seconds > 0\n -- 1.2 это мы даем 20% времени на случаи, когда запланировали 2 часа, и списали 2 часа, но через 1 час 36 минут:\n AND lw.total_logged_seconds > (EXTRACT(EPOCH FROM (lw.last_worklog_at - lec.estimate_changed_at)) * 1.2)\n -- не будем обращать внимание на мелкие задачи (менее 30 минут, ведь это могут быть просто хотфиксы):\n AND lw.total_logged_seconds > 1800\nORDER BY diff_in_hours;\n",
"refId": "A",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
},
"table": "jiraissue"
}
],
"datasource": {
"type": "grafana-postgresql-datasource",
"uid": "df798g5q84h6oc"
},
"options": {
"showHeader": true,
"cellHeight": "sm",
"sortBy": [
{
"displayName": "assignee",
"desc": false
}
]
}
}3:
WITH user_issue_logged AS (
SELECT
w.issueid,
w.author AS user_key,
SUM(w.timeworked) AS user_logged
FROM worklog w
GROUP BY w.issueid, w.author
)
SELECT
cu.display_name,
ROUND(SUM(ji.timeoriginalestimate) / 3600, 2) || ' h' AS estimated,
ROUND(SUM(uil.user_logged) / 3600, 2) || ' h' AS logged,
ROUND((SUM(uil.user_logged) - SUM(ji.timeoriginalestimate)) / 3600,2) || ' h' AS diff,
ROUND((SUM(uil.user_logged) * 100) / SUM(ji.timeoriginalestimate), 2) || ' %' AS logged_percentage,
ROUND(((SUM(uil.user_logged) * 100) / SUM(ji.timeoriginalestimate)) - 100, 2) || ' %' AS overruns_percentage,
ROUND(100 / ((SUM(uil.user_logged) * 100) / SUM(ji.timeoriginalestimate)), 2) AS coefficient
FROM user_issue_logged uil
JOIN jiraissue ji ON ji.id = uil.issueid AND $__timeFilter(ji.resolutiondate)
JOIN issuestatus ist ON ist.id = ji.issuestatus AND ist.statuscategory = 3 -- только закрытые задачи
JOIN cwd_user cu ON cu.id = REPLACE(uil.user_key, 'JIRAUSER', '')::bigint
WHERE ji.timeoriginalestimate > 0 AND uil.user_logged > 0
GROUP BY cu.display_name
ORDER BY coefficient DESC;{
"id": 1,
"type": "table",
"title": "Успешность оценки по закрытым задачам (плюс в diff означает перерасход)",
"gridPos": {
"x": 0,
"y": 54,
"h": 8,
"w": 24
},
"fieldConfig": {
"defaults": {
"custom": {
"align": "auto",
"footer": {
"reducers": []
},
"cellOptions": {
"type": "auto"
},
"inspect": false
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
},
"color": {
"mode": "thresholds"
}
},
"overrides": []
},
"pluginVersion": "12.3.0",
"targets": [
{
"dataset": "jira",
"datasource": {
"type": "grafana-postgresql-datasource",
"uid": "df798g5q84h6oc"
},
"editorMode": "code",
"format": "table",
"rawQuery": true,
"rawSql": "WITH user_issue_logged AS (\n SELECT\n w.issueid,\n w.author AS user_key,\n SUM(w.timeworked) AS user_logged\n FROM worklog w\n GROUP BY w.issueid, w.author\n)\nSELECT \n cu.display_name,\n ROUND(SUM(ji.timeoriginalestimate) / 3600, 2) || ' h' AS estimated,\n ROUND(SUM(uil.user_logged) / 3600, 2) || ' h' AS logged,\n ROUND((SUM(uil.user_logged) - SUM(ji.timeoriginalestimate)) / 3600,2) || ' h' AS diff,\n ROUND((SUM(uil.user_logged) * 100) / SUM(ji.timeoriginalestimate), 2) || ' %' AS logged_percentage,\n ROUND(((SUM(uil.user_logged) * 100) / SUM(ji.timeoriginalestimate)) - 100, 2) || ' %' AS overruns_percentage,\n ROUND(100 / ((SUM(uil.user_logged) * 100) / SUM(ji.timeoriginalestimate)), 2) AS coefficient\nFROM user_issue_logged uil\nJOIN jiraissue ji ON ji.id = uil.issueid AND $__timeFilter(ji.resolutiondate)\nJOIN issuestatus ist ON ist.id = ji.issuestatus AND ist.statuscategory = 3 -- только закрытые задачи\nJOIN cwd_user cu ON cu.id = REPLACE(uil.user_key, 'JIRAUSER', '')::bigint\nWHERE ji.timeoriginalestimate > 0 AND uil.user_logged > 0\nGROUP BY cu.display_name\nORDER BY coefficient DESC;\n",
"refId": "A",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
},
"table": "jiraissue"
}
],
"datasource": {
"type": "grafana-postgresql-datasource",
"uid": "df798g5q84h6oc"
},
"options": {
"showHeader": true,
"cellHeight": "sm"
}
}Списание времени по пользователям (дейлики - meeting_hours)
-- Чтобы расчитать untracked_hours находим количество дней в выбранном периоде Grafana
-- $__from / $__to приходят в миллисекундах, а нам нужны дни:
WITH period AS (
SELECT
(
EXTRACT(
day FROM (
to_timestamp($__to / 1000)
- to_timestamp($__from / 1000)
)
)::int + 1
) AS days_total
),
-- Считаем, сколько часов каждый пользователь реально списал за период:
user_logged AS (
SELECT
cu.user_name,
SUM(w.timeworked) / 3600.0 AS hours_logged
FROM worklog w
JOIN cwd_user cu
-- В worklog.author хранится строка вида JIRAUSER12345
-- поэтому извлекаем числовой id пользователя
ON cu.id = REPLACE(w.author, 'JIRAUSER', '')::bigint
WHERE $__timeFilter(w.startdate)
GROUP BY cu.user_name
),
-- Списанное время на дейлики по RA-190
meeting_logged AS (
SELECT
cu.user_name,
SUM(w.timeworked) / 3600.0 AS daily_meeting_hours
FROM worklog w
JOIN jiraissue ji
ON ji.id = w.issueid
JOIN project p
ON p.id = ji.project
JOIN cwd_user cu
ON cu.id = REPLACE(w.author, 'JIRAUSER', '')::bigint
WHERE p.pkey || '-' || ji.issuenum = 'RA-190'
AND $__timeFilter(w.startdate)
GROUP BY cu.user_name
)
SELECT
cu.display_name,
-- Реально списанные часы без дейликов
ROUND(
COALESCE(ul.hours_logged, 0)
- COALESCE(r.daily_meeting_hours, 0),
2
) AS tasks_hours,
-- Часы по RA-190
ROUND(
COALESCE(r.daily_meeting_hours, 0),
2
) AS meeting_hours,
-- Несписанные часы:
-- 1) считаем количество рабочих дней:
-- days_total - (floor(days_total / 7) * 2)
-- 2) умножаем на 8 часов
-- 3) вычитаем реально списанные часы
ROUND(
(
(p.days_total - FLOOR(p.days_total / 7.0) * 2) * 8
) - COALESCE(ul.hours_logged, 0),
2
) AS untracked_hours,
-- Процент несписанного времени
ROUND(
(
(
(p.days_total - FLOOR(p.days_total / 7.0) * 2) * 8
- COALESCE(ul.hours_logged, 0)
)
/
NULLIF(
(p.days_total - FLOOR(p.days_total / 7.0) * 2) * 8,
0
)
) * 100,
2
) AS untracked_percent
FROM cwd_user cu
CROSS JOIN period p
LEFT JOIN user_logged ul ON ul.user_name = cu.user_name
LEFT JOIN meeting_logged r ON r.user_name = cu.user_name
WHERE cu.user_name IN ($jira_login) AND cu.display_name NOT IN ('...', '...')
ORDER BY tasks_hours DESC;{
"kind": "Panel",
"spec": {
"id": 2,
"title": "Списание времени с учетом времени на ревью и дейлики",
"description": "Списание времени на дейлики ИЛИ консультирование внешних команд за рамками своих задач https://task.ttk-svyaz.ru/browse/RA-190",
"links": [],
"data": {
"kind": "QueryGroup",
"spec": {
"queries": [
{
"kind": "PanelQuery",
"spec": {
"query": {
"kind": "DataQuery",
"version": "v0",
"group": "grafana-postgresql-datasource",
"datasource": {
"name": "df798g5q84h6oc"
},
"spec": {
"dataset": "jira",
"editorMode": "code",
"format": "table",
"rawQuery": true,
"rawSql": "-- Чтобы расчитать untracked_hours находим количество дней в выбранном периоде Grafana\n-- $__from / $__to приходят в миллисекундах, а нам нужны дни:\nWITH period AS (\n SELECT\n (\n EXTRACT(\n day FROM (\n to_timestamp($__to / 1000)\n - to_timestamp($__from / 1000)\n )\n )::int + 1\n ) AS days_total\n),\n-- Считаем, сколько часов каждый пользователь реально списал за период:\nuser_logged AS (\n SELECT\n cu.user_name,\n SUM(w.timeworked) / 3600.0 AS hours_logged\n FROM worklog w\n JOIN cwd_user cu\n -- В worklog.author хранится строка вида JIRAUSER12345\n -- поэтому извлекаем числовой id пользователя\n ON cu.id = REPLACE(w.author, 'JIRAUSER', '')::bigint\n WHERE $__timeFilter(w.startdate)\n GROUP BY cu.user_name\n),\n\n-- Списанное время на дейлики по RA-190\nmeeting_logged AS (\n SELECT\n cu.user_name,\n SUM(w.timeworked) / 3600.0 AS daily_meeting_hours\n FROM worklog w\n JOIN jiraissue ji\n ON ji.id = w.issueid\n JOIN project p\n ON p.id = ji.project\n JOIN cwd_user cu\n ON cu.id = REPLACE(w.author, 'JIRAUSER', '')::bigint\n WHERE p.pkey || '-' || ji.issuenum = 'RA-190'\n AND $__timeFilter(w.startdate)\n GROUP BY cu.user_name\n)\n\nSELECT\n cu.display_name,\n\n -- Реально списанные часы без дейликов\n ROUND(\n COALESCE(ul.hours_logged, 0)\n - COALESCE(r.daily_meeting_hours, 0),\n 2\n ) AS tasks_hours,\n\n -- Часы по RA-190\n ROUND(\n COALESCE(r.daily_meeting_hours, 0),\n 2\n ) AS meeting_hours,\n\n -- Несписанные часы:\n -- 1) считаем количество рабочих дней:\n -- days_total - (floor(days_total / 7) * 2)\n -- 2) умножаем на 8 часов\n -- 3) вычитаем реально списанные часы\n ROUND(\n (\n (p.days_total - FLOOR(p.days_total / 7.0) * 2) * 8\n ) - COALESCE(ul.hours_logged, 0),\n 2\n ) AS untracked_hours,\n\n -- Процент несписанного времени\n ROUND(\n (\n (\n (p.days_total - FLOOR(p.days_total / 7.0) * 2) * 8\n - COALESCE(ul.hours_logged, 0)\n )\n /\n NULLIF(\n (p.days_total - FLOOR(p.days_total / 7.0) * 2) * 8,\n 0\n )\n ) * 100,\n 2\n ) AS untracked_percent\n\nFROM cwd_user cu\nCROSS JOIN period p\nLEFT JOIN user_logged ul ON ul.user_name = cu.user_name\nLEFT JOIN meeting_logged r ON r.user_name = cu.user_name\nWHERE cu.user_name IN ($jira_login) AND cu.display_name NOT IN ('Туманов Александр Михайлович', 'Скрипалева Евгения Aлександровна')\n\nORDER BY tasks_hours DESC;\n",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
},
"table": "jiraissue"
}
},
"refId": "A",
"hidden": false
}
}
],
"transformations": [],
"queryOptions": {}
}
},
"vizConfig": {
"kind": "VizConfig",
"group": "table",
"version": "13.2.3",
"spec": {
"options": {
"showHeader": true,
"cellHeight": "sm",
"sortBy": [
{
"desc": true,
"displayName": "untracked_hours"
}
]
},
"fieldConfig": {
"defaults": {
"custom": {
"align": "auto",
"footer": {
"reducers": []
},
"cellOptions": {
"type": "auto"
},
"inspect": false
},
"thresholds": {
"mode": "absolute",
"steps": [
{
"value": null,
"color": "green"
},
{
"value": 80,
"color": "red"
}
]
},
"color": {
"mode": "thresholds"
}
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "untracked_hours"
},
"properties": [
{
"id": "custom.width",
"value": 166
}
]
},
{
"matcher": {
"id": "byName",
"options": "untracked_percent"
},
"properties": [
{
"id": "custom.width",
"value": 143
}
]
},
{
"matcher": {
"id": "byName",
"options": "meeting_hours"
},
"properties": [
{
"id": "custom.width",
"value": 121
}
]
},
{
"matcher": {
"id": "byName",
"options": "tasks_hours",
"scope": "series"
},
"properties": [
{
"id": "custom.width",
"value": 138
}
]
}
]
}
}
}
}
}
SELECT
$__timeGroupAlias(w.startdate, '1d'), -- Grafana подставит alias "time"
cu.display_name AS metric,
SUM(w.timeworked) / 3600.0 AS value
FROM worklog w
JOIN cwd_user cu
ON cu.id = REPLACE(w.author, 'JIRAUSER', '')::bigint
WHERE
$__timeFilter(w.startdate)
GROUP BY 1, 2
ORDER BY 1, 2;{
"id": 3,
"type": "timeseries",
"title": "График своевременного списания времени",
"gridPos": {
"x": 12,
"y": 60,
"h": 12,
"w": 12
},
"fieldConfig": {
"defaults": {
"custom": {
"drawStyle": "line",
"lineInterpolation": "linear",
"barAlignment": 0,
"barWidthFactor": 0.6,
"lineWidth": 1,
"fillOpacity": 0,
"gradientMode": "none",
"spanNulls": false,
"insertNulls": false,
"showPoints": "auto",
"showValues": false,
"pointSize": 5,
"stacking": {
"mode": "none",
"group": "A"
},
"axisPlacement": "auto",
"axisLabel": "",
"axisColorMode": "text",
"axisBorderShow": false,
"scaleDistribution": {
"type": "linear"
},
"axisCenteredZero": false,
"hideFrom": {
"tooltip": false,
"viz": false,
"legend": false
},
"thresholdsStyle": {
"mode": "off"
}
},
"color": {
"mode": "palette-classic"
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": []
},
"pluginVersion": "12.3.0",
"targets": [
{
"dataset": "jira",
"datasource": {
"type": "grafana-postgresql-datasource",
"uid": "df798g5q84h6oc"
},
"editorMode": "code",
"format": "time_series",
"rawQuery": true,
"rawSql": "SELECT\n $__timeGroupAlias(w.startdate, '1d'), -- Grafana подставит alias \"time\"\n cu.display_name AS metric,\n SUM(w.timeworked) / 3600.0 AS value\nFROM worklog w\nJOIN cwd_user cu\n ON cu.id = REPLACE(w.author, 'JIRAUSER', '')::bigint\nWHERE\n $__timeFilter(w.startdate)\nGROUP BY 1, 2\nORDER BY 1, 2;\n",
"refId": "A",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
}
],
"datasource": {
"type": "grafana-postgresql-datasource",
"uid": "df798g5q84h6oc"
},
"options": {
"tooltip": {
"mode": "single",
"sort": "none",
"hideZeros": false
},
"legend": {
"showLegend": true,
"displayMode": "list",
"placement": "right",
"calcs": []
}
}
}Внеплановые задачи за период (сравниваем с квартальными значениями)
quarter_capacity_days = "количество работников в группе" умноженное на 12 (кол-во рабочих дней за квартал выделенных на внеплановые работы, в нашем случае 12 дней это 20%)
WITH closed_issues AS (
SELECT DISTINCT
ji.id,
ji.assignee
FROM jiraissue ji
JOIN changegroup cg
ON cg.issueid = ji.id
JOIN changeitem ci
ON ci.groupid = cg.id
AND ci.field = 'status'
AND ci.newstring = 'Closed'
WHERE $__timeFilter(cg.created)
AND NOT EXISTS (
SELECT 1
FROM label l
WHERE l.issue = ji.id
AND l.label LIKE 'kpi\_%' ESCAPE '\' -- символ _ в LIKE означает любой одиночный символ, а ESCAPE '\' трактует его буквально
)
),
issue_worklogs AS (
SELECT
issueid,
SUM(timeworked) AS logged_seconds
FROM worklog
GROUP BY issueid
),
user_roles AS (
SELECT DISTINCT
cu.id AS user_id,
cg.group_name AS user_group
FROM cwd_user cu
JOIN cwd_membership cm
ON cm.child_id = cu.id
JOIN cwd_group cg
ON cg.id = cm.parent_id
WHERE cg.group_name IN (
'analyst_role',
'bi_analyst_role',
'developer_role'
)
),
group_capacity AS (
SELECT
user_group,
COUNT(DISTINCT user_id) * 12 AS quarter_capacity_days
FROM user_roles
GROUP BY user_group
),
group_metrics AS (
SELECT
ur.user_group,
COUNT(DISTINCT ci.id) AS issue_count,
ROUND(
COALESCE(SUM(iw.logged_seconds), 0)
/ (3600.0 * 8),
2
) AS logged_days
FROM closed_issues ci
JOIN cwd_user cu
ON cu.id = REPLACE(ci.assignee, 'JIRAUSER', '')::bigint
JOIN user_roles ur
ON ur.user_id = cu.id
LEFT JOIN issue_worklogs iw
ON iw.issueid = ci.id
GROUP BY ur.user_group
)
SELECT
gc.user_group,
gc.quarter_capacity_days,
COALESCE(gm.logged_days, 0) AS logged_days,
ROUND(
COALESCE(gm.logged_days, 0) * 100.0
/ NULLIF(gc.quarter_capacity_days, 0),
2
) AS logged_days_percent,
COALESCE(gm.issue_count, 0) AS closed_issues
FROM group_capacity gc
LEFT JOIN group_metrics gm
ON gm.user_group = gc.user_group
ORDER BY gc.user_group;Эпики
WITH epic_list AS (
SELECT
ji.id AS epic_id,
p.pkey || '-' || ji.issuenum AS epic_key,
ji.summary AS epic_summary,
u.display_name AS assignee,
ji.duedate AS due_date
FROM jiraissue ji
JOIN project p ON p.id = ji.project
JOIN issuetype it ON it.id = ji.issuetype
JOIN issuestatus ist ON ist.id::text = ji.issuestatus
LEFT JOIN cwd_user u
ON u.id = REPLACE(ji.assignee, 'JIRAUSER', '')::bigint
WHERE it.pname = 'Epic'
AND ist.statuscategory <> 3 -- только незакрытые эпики
AND ist.pname <> 'Postponed' -- отложенные эпики
),
worklog_sum AS (
SELECT
issueid,
SUM(timeworked) AS logged_seconds
FROM worklog
GROUP BY issueid
)
SELECT
e.epic_key,
e.epic_summary,
e.assignee,
-- 🔢 open без оценки
COUNT(DISTINCT t.id) FILTER (
WHERE tist.statuscategory = 2
AND (t.timeoriginalestimate IS NULL OR t.timeoriginalestimate = 0)
) AS open_without_estimate,
-- 🔢 все незакрытые задачи эпика
COUNT(DISTINCT t.id) AS not_closed_tasks,
-- ⏱ open + in progress remaining (часы)
CEIL(
SUM(
GREATEST(
COALESCE(t.timeoriginalestimate, 0)
- COALESCE(wl.logged_seconds, 0),
0
)
) FILTER (
WHERE tist.statuscategory = 2
OR tist.pname = 'In Progress'
OR tist.pname = 'In Review'
) / 3600.0 / 8
) AS remaining_days,
TO_CHAR(e.due_date, 'YYYY-MM-DD') as due_date
FROM epic_list e
JOIN issuelink el
ON (el.destination = e.epic_id OR el.source = e.epic_id)
AND el.linktype = 10200 -- только задачи эпика
JOIN jiraissue t
-- Потому что AI подумала что бд часть связей “эпик-задача” записана в обратную сторону.
-- Из-за этого обычный сделан JOIN независимым от направления.
ON t.id = CASE
-- Если эпик находится в source, значит связанная задача находится в destination:
WHEN el.source = e.epic_id THEN el.destination
-- Иначе (эпик в destination), значит задача в source:
ELSE el.source
END
JOIN issuestatus tist
ON tist.id::text = t.issuestatus
LEFT JOIN worklog_sum wl
ON wl.issueid = t.id
WHERE tist.statuscategory <> 3 -- исключаем закрытые задачи
GROUP BY
e.epic_key,
e.epic_summary,
e.assignee,
e.due_date
ORDER BY
e.due_date,
remaining_days DESC,
open_without_estimate DESC;Куда пользователь списал время
Пользователь указывается в SQL-запросе:
SELECT
reporter.display_name AS reporter,
assignee.display_name AS assignee,
project.pkey || '-' || ji.issuenum AS issue_key,
ji.summary,
ROUND(SUM(w.timeworked)/3600.0, 2) AS logged_hours
FROM worklog w
-- пользователь, который списывал время
JOIN cwd_user cu
ON cu.id = REPLACE(w.author, 'JIRAUSER', '')::bigint
-- задача
JOIN jiraissue ji
ON ji.id = w.issueid
-- проект
JOIN project
ON project.id = ji.project
-- пользователи задачи
LEFT JOIN cwd_user reporter
ON reporter.id = REPLACE(ji.reporter, 'JIRAUSER', '')::bigint
LEFT JOIN cwd_user assignee
ON assignee.id = REPLACE(ji.assignee, 'JIRAUSER', '')::bigint
WHERE
cu.display_name = 'Имя пользователя'
AND $__timeFilter(w.startdate)
GROUP BY
project.pkey,
ji.issuenum,
ji.summary,
reporter.display_name,
assignee.display_name
ORDER BY logged_hours DESC;Нагрузка по ролям
Показано рабочее время, не календарное!!!
В примере выше сделана выборка только по всем эпикам, имена которых начинаются с "Внедрение NBSS"
WITH epic_list AS (
SELECT
ji.id AS epic_id,
p.pkey || '-' || ji.issuenum AS epic_key,
ji.summary AS epic_summary,
u.display_name AS assignee,
ji.duedate AS due_date
FROM jiraissue ji
JOIN project p ON p.id = ji.project
JOIN issuetype it ON it.id = ji.issuetype
JOIN issuestatus ist ON ist.id::text = ji.issuestatus
LEFT JOIN cwd_user u
ON u.id = REPLACE(ji.assignee, 'JIRAUSER', '')::bigint
WHERE it.pname = 'Epic'
AND ist.statuscategory <> 3 -- только незакрытые эпики
AND ist.pname <> 'Postponed' -- отложенные эпики
),
worklog_sum AS (
SELECT
issueid,
SUM(timeworked) AS logged_seconds
FROM worklog
GROUP BY issueid
)
SELECT
COALESCE(teams.team_name, 'Other') AS team_name,
-- e.epic_key,
-- e.epic_summary,
-- e.assignee,
-- всего задач
COUNT(DISTINCT t.id) AS tasks,
-- не закрытые без оценки
COUNT(DISTINCT t.id) FILTER (
WHERE tist.statuscategory IN (2,4)
AND (t.timeoriginalestimate IS NULL OR t.timeoriginalestimate = 0)
) AS not_closed_without_estimate,
CEIL(SUM(COALESCE(t.timeoriginalestimate, 0)) / 3600.0 / 8) AS estimated_days,
-- кол-во дней по несделанным задачам:
CEIL(
SUM(
GREATEST(
COALESCE(t.timeoriginalestimate, 0)
- COALESCE(wl.logged_seconds, 0),
0
)
) FILTER (
-- исключаем со статусом "Сделано"
WHERE tist.statuscategory != 3
) / 3600.0 / 8
) AS remaining_days
-- TO_CHAR(e.due_date, 'YYYY-MM-DD') as due_date
FROM epic_list e
JOIN issuelink el
ON (el.destination = e.epic_id OR el.source = e.epic_id)
AND el.linktype = 10200 -- только задачи эпика
JOIN jiraissue t
-- Потому что AI подумала что бд часть связей “эпик-задача” записана в обратную сторону.
-- Из-за этого обычный сделан JOIN независимым от направления.
ON t.id = CASE
-- Если эпик находится в source, значит связанная задача находится в destination:
WHEN el.source = e.epic_id THEN el.destination
-- Иначе (эпик в destination), значит задача в source:
ELSE el.source
END
JOIN issuestatus tist ON tist.id::text = t.issuestatus
LEFT JOIN worklog_sum wl ON wl.issueid = t.id
-- Связываем с пользователями
LEFT JOIN cwd_user assignee ON assignee.id = REPLACE(t.assignee, 'JIRAUSER', '')::bigint
-- Справочник команд на VALUES
LEFT JOIN (
VALUES
('agordienko', 'frontend'),
('MikhailMoskovkin', 'frontend'),
('vsemina', 'backend'),
('adanilkov', 'backend'),
('vsamodelkin', 'backend'),
('dmurzin', 'backend'),
('omuzychenko', 'backend'),
('AlekseyErdyakov', 'backend'),
('atsarev', 'backend'),
('vburlak', 'analysts'),
('igorryazanov', 'analysts'),
('AleksandrKuznetsov', 'analysts'),
('AlekseyUlyashov', 'analysts'),
('DanilShimanskiy', 'analysts'),
('nikitakazachkin', 'testers'),
('kfedorov', 'reports'),
('atumanov', 'reports'),
('SergeyLokhtin', 'reports'),
('eskripaleva', 'devops')
) AS teams(user_name, team_name) ON teams.user_name = assignee.user_name
WHERE
-- исключаем задачи, которые в статусе "Отложено"
tist.pname != 'Postponed' AND
(e.epic_summary LIKE 'WFM:%' OR e.epic_summary LIKE 'WFM :%' OR e.epic_summary LIKE 'Внедрение NBSS%')
GROUP BY team_name
ORDER BY estimated_days DESC;Еще лучше вынести сопоставление "логин" - "роль" в Grafana -> dashboard -> Variables -> Query
SELECT '
SELECT *
FROM (
VALUES
(''agordienko'', ''frontend''),
(''MikhailMoskovkin'', ''frontend''),
(''vsemina'', ''backend''),
(''adanilkov'', ''backend''),
(''vsamodelkin'', ''backend''),
(''dmurzin'', ''backend''),
(''omuzychenko'', ''backend''),
(''AlekseyErdyakov'', ''backend''),
(''atsarev'', ''backend''),
(''vburlak'', ''analysts''),
(''igorryazanov'', ''analysts''),
(''AleksandrKuznetsov'', ''analysts''),
(''AlekseyUlyashov'', ''analysts''),
(''DanilShimanskiy'', ''analysts''),
(''nikitakazachkin'', ''testers''),
(''kfedorov'', ''reports''),
(''atumanov'', ''reports''),
(''SergeyLokhtin'', ''reports''),
(''eskripaleva'', ''devops'')
) AS teams(user_name, team_name)
' AS sql;В итоге в основном запросе использовать:
LEFT JOIN (${teams_sql:raw}) teams ON teams.user_name = assignee.user_nameСпринт – отчет по ролям текущего спринта
ВАЖНО: запрос берет только задачи с эпиком (можно исключить, если из запроса убрать работу с epic_list и issuelink
WITH epic_list AS (
SELECT
ji.id AS epic_id,
p.pkey || '-' || ji.issuenum AS epic_key,
ji.summary AS epic_summary,
u.display_name AS assignee,
ji.duedate AS due_date
FROM jiraissue ji
JOIN project p ON p.id = ji.project
JOIN issuetype it ON it.id = ji.issuetype
JOIN issuestatus ist ON ist.id::text = ji.issuestatus
LEFT JOIN cwd_user u
ON u.id = REPLACE(ji.assignee, 'JIRAUSER', '')::bigint
WHERE it.pname = 'Epic'
AND ist.statuscategory <> 3 -- только незакрытые эпики
AND ist.pname <> 'Postponed' -- отложенные эпики
),
worklog_sum AS (
SELECT
issueid,
SUM(timeworked) AS logged_seconds
FROM worklog
GROUP BY issueid
)
SELECT
COALESCE(teams.team_name, 'Other') AS team_name,
-- e.epic_key,
-- e.epic_summary,
-- e.assignee,
-- всего задач
COUNT(DISTINCT t.id) AS tasks,
-- не закрытые без оценки
COUNT(DISTINCT t.id) FILTER (
WHERE tist.statuscategory IN (2,4)
AND (t.timeoriginalestimate IS NULL OR t.timeoriginalestimate = 0)
) AS not_closed_without_estimate,
CEIL(SUM(COALESCE(t.timeoriginalestimate, 0)) / 3600.0 / 8) AS estimated_days,
-- кол-во дней по несделанным задачам:
CEIL(
SUM(
GREATEST(
COALESCE(t.timeoriginalestimate, 0)
- COALESCE(wl.logged_seconds, 0),
0
)
) FILTER (
-- исключаем со статусом "Сделано"
WHERE tist.statuscategory != 3
) / 3600.0 / 8
) AS remaining_days
-- TO_CHAR(e.due_date, 'YYYY-MM-DD') as due_date
FROM epic_list e
JOIN issuelink el
ON (el.destination = e.epic_id OR el.source = e.epic_id)
AND el.linktype = 10200 -- только задачи эпика
JOIN jiraissue t
-- Потому что AI подумала что бд часть связей “эпик-задача” записана в обратную сторону.
-- Из-за этого обычный сделан JOIN независимым от направления.
ON t.id = CASE
-- Если эпик находится в source, значит связанная задача находится в destination:
WHEN el.source = e.epic_id THEN el.destination
-- Иначе (эпик в destination), значит задача в source:
ELSE el.source
END
JOIN issuestatus tist ON tist.id::text = t.issuestatus
LEFT JOIN worklog_sum wl ON wl.issueid = t.id
-- Связываем с пользователями
LEFT JOIN cwd_user assignee ON assignee.id = REPLACE(t.assignee, 'JIRAUSER', '')::bigint
-- Справочник команд на VALUES
LEFT JOIN (${teams_sql:raw}) teams ON teams.user_name = assignee.user_name
-- чтобы ниже сделать фильтрацию только по активному спринту для команды:
JOIN customfieldvalue cfv
ON cfv.issue = t.id
AND cfv.customfield = (
SELECT id
FROM customfield
WHERE cfname = 'Sprint'
)
JOIN "AO_60DB71_SPRINT" s ON s."ID" = cfv.stringvalue::bigint
WHERE
-- исключаем задачи, которые в статусе "Отложено"
tist.pname != 'Postponed'
-- нужен только один текущий открытый спринт, а не несколько открытых team_3_*
AND s."ID" = (
SELECT MAX("ID")
FROM "AO_60DB71_SPRINT"
WHERE "CLOSED" = false
AND "NAME" LIKE 'team_3_%'
)
GROUP BY team_name
ORDER BY estimated_days DESC;Спринт - загруженность людей в команде (v.1)
ВАЖНО: запрос берет только задачи с эпиком (можно исключить, если из запроса убрать работу с epic_list и issuelink
WITH epic_list AS (
SELECT
ji.id AS epic_id,
p.pkey || '-' || ji.issuenum AS epic_key,
ji.summary AS epic_summary,
u.display_name AS assignee,
ji.duedate AS due_date
FROM jiraissue ji
JOIN project p ON p.id = ji.project
JOIN issuetype it ON it.id = ji.issuetype
JOIN issuestatus ist ON ist.id::text = ji.issuestatus
LEFT JOIN cwd_user u
ON u.id = REPLACE(ji.assignee, 'JIRAUSER', '')::bigint
WHERE it.pname = 'Epic'
AND ist.statuscategory <> 3 -- только незакрытые эпики
AND ist.pname <> 'Postponed' -- отложенные эпики
),
worklog_sum AS (
SELECT
issueid,
SUM(timeworked) AS logged_seconds
FROM worklog
GROUP BY issueid
)
SELECT
assignee.user_name,
-- всего задач
COUNT(DISTINCT t.id) AS tasks,
-- не закрытые без оценки
COUNT(DISTINCT t.id) FILTER (
WHERE tist.statuscategory IN (2,4)
AND (t.timeoriginalestimate IS NULL OR t.timeoriginalestimate = 0)
) AS not_closed_without_estimate,
CEIL(SUM(COALESCE(t.timeoriginalestimate, 0)) / 3600.0 / 8) AS estimated_days,
-- кол-во дней по несделанным задачам:
CEIL(
SUM(
GREATEST(
COALESCE(t.timeoriginalestimate, 0)
- COALESCE(wl.logged_seconds, 0),
0
)
) FILTER (
-- исключаем со статусом "Сделано"
WHERE tist.statuscategory != 3
) / 3600.0 / 8
) AS remaining_days
-- TO_CHAR(e.due_date, 'YYYY-MM-DD') as due_date
FROM epic_list e
JOIN issuelink el
ON (el.destination = e.epic_id OR el.source = e.epic_id)
AND el.linktype = 10200 -- только задачи эпика
JOIN jiraissue t
-- Потому что AI подумала что бд часть связей “эпик-задача” записана в обратную сторону.
-- Из-за этого обычный сделан JOIN независимым от направления.
ON t.id = CASE
-- Если эпик находится в source, значит связанная задача находится в destination:
WHEN el.source = e.epic_id THEN el.destination
-- Иначе (эпик в destination), значит задача в source:
ELSE el.source
END
JOIN issuestatus tist ON tist.id::text = t.issuestatus
LEFT JOIN worklog_sum wl ON wl.issueid = t.id
-- Связываем с пользователями
LEFT JOIN cwd_user assignee ON assignee.id = REPLACE(t.assignee, 'JIRAUSER', '')::bigint
-- чтобы ниже сделать фильтрацию только по активному спринту для команды:
JOIN customfieldvalue cfv
ON cfv.issue = t.id
AND cfv.customfield = (
SELECT id
FROM customfield
WHERE cfname = 'Sprint'
)
JOIN "AO_60DB71_SPRINT" s ON s."ID" = cfv.stringvalue::bigint
WHERE
-- исключаем задачи, которые в статусе "Отложено"
tist.pname != 'Postponed'
-- нужен только один текущий открытый спринт, а не несколько открытых team_3_*
AND s."ID" = (
SELECT MAX("ID")
FROM "AO_60DB71_SPRINT"
WHERE "CLOSED" = false
AND "NAME" LIKE 'team_3_%'
)
GROUP BY assignee.user_name
ORDER BY estimated_days DESC;Спринт - загруженность людей в команде (v.2)
ВАЖНО: запрос берет даже задачи без эпиков
WITH worklog_sum AS (
SELECT
issueid,
SUM(timeworked) AS logged_seconds
FROM worklog
GROUP BY issueid
)
SELECT
assignee.user_name,
-- всего задач
COUNT(DISTINCT t.id) AS tasks,
-- не закрытые без оценки
COUNT(DISTINCT t.id) FILTER (
WHERE tist.statuscategory IN (2,4)
AND (t.timeoriginalestimate IS NULL OR t.timeoriginalestimate = 0)
) AS not_closed_without_estimate,
CEIL(SUM(COALESCE(t.timeoriginalestimate, 0)) / 3600.0 / 8) AS estimated_days,
-- ROUND(COALESCE(SUM(wl.logged_seconds),0)/3600.0,2) AS logged_hours,
ROUND(COALESCE(SUM(wl.logged_seconds),0)/3600.0 / 8, 2) AS logged_days,
-- кол-во дней по несделанным задачам:
CEIL(
SUM(
GREATEST(
COALESCE(t.timeoriginalestimate, 0) - COALESCE(wl.logged_seconds, 0),
0
)
) FILTER (
-- исключаем со статусом "Сделано"
WHERE tist.statuscategory != 3
) / 3600.0 / 8
) AS remaining_days
-- TO_CHAR(e.due_date, 'YYYY-MM-DD') as due_date
FROM jiraissue t
JOIN issuestatus tist ON tist.id::text = t.issuestatus
LEFT JOIN worklog_sum wl ON wl.issueid = t.id
-- Связываем с пользователями
LEFT JOIN cwd_user assignee ON assignee.id = REPLACE(t.assignee, 'JIRAUSER', '')::bigint
-- чтобы ниже сделать фильтрацию только по активному спринту для команды:
JOIN customfieldvalue cfv
ON cfv.issue = t.id
AND cfv.customfield = (
SELECT id
FROM customfield
WHERE cfname = 'Sprint'
)
JOIN "AO_60DB71_SPRINT" s ON s."ID" = cfv.stringvalue::bigint
WHERE
-- исключаем задачи, которые в статусе "Отложено"
tist.pname != 'Postponed'
-- нужен только один текущий открытый спринт, а не несколько открытых team_3_*
AND s."ID" = (
SELECT MAX("ID")
FROM "AO_60DB71_SPRINT"
WHERE "CLOSED" = false
AND "NAME" LIKE 'team_1_%'
)
GROUP BY assignee.user_name
ORDER BY estimated_days DESC;
11.09.2021 07:08
Комментарии
Комментариев пока нет.