# SQL 使用说明

URL: https://docs.ones.cn/ones-project/performance-management/widget-configuration/sql-guide

效能管理提供了内置的 SQL 能力，支持用户在现成的指标之外自行制作更多指标。使用位置在卡片编辑页中⬇️

![SQL 使用说明 配图 1](https://docs.ones.cn/assets/images/a8f806e9163ad592-d0742e216e8429e2cf951dd4d3daa804.png)

使用流程分为 3 个步骤：

1. 获取字段
2. 写计算公式
3. 应用

## 1.获取字段

Performance 中的数据源来源有 2 种：自动同步的项目管理中的工作项属性，预处理过的属性相关的数据。可以从下图中红圈位置处查看⬇️，每个数据集均有说明。

![SQL 使用说明 配图 2](https://docs.ones.cn/assets/images/01fa839d140cec93-2b595a2526f9ee5328795810d85acc96.png)

其中亦提供了用于写计算公式的sql表达式，点击图中按钮即可直接复制使用⬇️

![SQL 使用说明 配图 3](https://docs.ones.cn/assets/images/27f4f69b8b66823c-45120fd75568499bb95dbe7fd7671a9b.png)

##### 所有数据集的预处理内容说明⬇️

1.工作项

| 维度 | 计算方式 | 说明 |
| --- | --- | --- |
| 完成时间 | 工作项状态类型最后一次切换为「已完成」的时间 |  |
| 是否逾期 | 状态类型为已完成的工作项，完成时间晚于截止日期，则为"是"，否则为"否" | "是"或"否" |
| 故事点完成量 | 工作项状态类型非「已完成」时，值为0；状态类型为「已完成」时，该值等于「故事点」 |  |
| 生存时长 | 工作项完成时间 - 工作项创建时间 |  |
| 响应时长 | 工作项「进行中」状态类型最早出现时间 - 工作项创建时间 |  |

| 指标 | 计算方式 | 说明 |
| --- | --- | --- |
| 工作项按时交付数量 | 「是否逾期」为"否"的工作项数量 |  |
| 工作项按时交付率 | 「是否逾期」为"否"的工作项数量 / 状态类型为已完成的工作项数量 |  |
| 内部发现的缺陷数 | 「是否为线上缺陷」= 否的工作项数量 | 有使用「是否为线上缺陷」属性的工作项才会被计算 |
| 外部发现的缺陷数 | 「是否为线上缺陷」= 是的工作项数量 | 有使用「是否为线上缺陷」属性的工作项才会被计算 |
| 缺陷探测率 | 内部发现的缺陷数占全部缺陷比重。内部发现的缺陷数量 / [（内部发现的缺陷数量 +外部发现的缺陷数量）] * 100% | 使用该指标时，需要增加筛选条件：工作项类型 == 有使用「是否为线上缺陷」字段的工作项类型 |
| 缺陷逃逸率 | 外部发现的缺陷数占全部缺陷比重。外部发现的缺陷数量 / [（内部发现的缺陷数量 +外部发现的缺陷数量）] * 100% | 使用该指标时，需要增加筛选条件：工作项类型 == 有使用「是否为线上缺陷」字段的工作项类型 |
| 工作项完成进度 | 状态类型为「已完成」的工作项数量占全部工作项数量的比重 | 「全部工作项数量」取决于筛选范围 |
| 线上缺陷密度 | 线上缺陷个数 / 需求个数 |  |

2.项目

| 维度 | 计算方式 | 说明 |
| --- | --- | --- |
| 是否归档 |  | "是"或"否" |
| 延期率 | [（当前日期 - 计划结束日期）/（计划结束日期 - 计划开始日期）] * 100% | 只在计算进行中的项目时有意义；需要在筛选条件中限制状态类型为进行中的项目 |

3.迭代

| 维度 | 计算方式 | 说明 |
| --- | --- | --- |
| 迭代实际完成日期 | 迭代阶段类型最后一次切换为「已完成」的日期 |  |
| 是否按时交付 | 「迭代实际完成日期」早于等于「计划完成日期」则为"是"，否则则为"否" | "是"或"否" |

| 指标 | 计算方式 | 说明 |
| --- | --- | --- |
| 按时交付数量 | 「是否按时交付」为"是"的迭代数量 |  |
| 按时交付率 | 迭代按时交付数量 / 迭代阶段类型为已完成的迭代数量 |  |

4.工时

| 维度 | 计算方式 | 说明 |
| --- | --- | --- |
| 工时 | 登记或预估工时的时长 |  |
| 工时类型 |  | "登记"或"预估" |
| 成员 | 添加登记或预估工时时选择的成员 |  |
| 开始时间 | 工时的开始时间（不是创建时间） |  |

| 指标 | 计算方式 | 说明 |
| --- | --- | --- |
| 总登记工时 | 登记工时的总和 | 根据筛选范围计算。如按项目、迭代、成员，按每天、每周、每月筛选 |
| 总预估工时 | 预估工时的总和 | 根据筛选范围计算。如按项目、迭代、成员，按每天、每周、每月筛选 |
| 预估偏差率 | （总登记工时 - 总预估工时）/ 总预估工时 * 100% | 根据筛选范围计算 |
| 日饱和度 | 用户每日登记工时 / 团队标准工作时长 * 100% | 使用折线图查看时，「时间单位」需设置成"天" |
| 周饱和度 | 每日饱和度按周求和 / 每周工作日 * 100% | 使用折线图查看时，「时间单位」需设置成"周" |

## 2.写计算公式

- 首先了解常用函数：

| 符号 | 意义 |
| --- | --- |
| + | 加 |
| - | 减 |
| * | 乘 |
| / | 除 |
| AVG | 取平均值 |
| MAX | 取最大值 |
| MIN | 取最小值 |
| COUNT | 计数 |
| COUNTIF | 有条件的计数 |
| SUM | 求和 |
| DISTINCT | 去重 |
| DATE_DIFF | 计算时间差 |
| > | 大于 |
| < | 小于 |
| >= | 大于等于 |
| <= | 小于等于 |
| = | 等于 |
| != | 不等于 |
| IN | 包含在某范围内 |
| NOT IN | 不包含在某范围内 |
| LIKE"%内容%" | 包含某内容 |
| IS NULL | 为空 |
| IS NOT NULL | 不为空 |

全量可用函数参见 [clickhouse函数教程](https://clickhouse.com/docs/zh/sql-reference/functions/arithmetic-functions)

- 具体使用方法示例
当你已选择「工作项」数据集：

计数——此时在sql输入框中输入： `count(*)` ，你会得到：工作项总数。

有条件的计数——输入： `countIf(状态类型sql表达式='未开始')` ，你会得到：状态类型为"未开始"的工作项总数。

有多个条件的计数——输入： `countIf(状态类型sql表达式='未开始' or 状态类型='进行中')` ，你会得到：状态类型为"未开始"或"进行中"的工作项总数。

求和——输入： `sum(成本sql表达式)` ，你会得到：总成本。

求比率： `countIf(状态类型sql表达式='未开始')/count(*)` ，你会得到：状态类型为"未开始"的工作项数占总数的比，简称未开始率。

求时间差： `date_diff('day',startdate,enddate)` ，你会得到：开始日期与结束日期相差的天数。

求平均值： `avg(表达式)` ，比如对时间差求平均，可以代入上一行表达式： `avg(date_diff('day',startdate,enddate))`

以上函数可以灵活组合使用，按需选择即可。

- 特殊值处理说明
自定义的状态值可以直接写中文，例如：状态表达式 = '审批进行中'。 对于系统状态值的写法，以未开始为例，应写：状态表达式 =' `{{status.todo}}` '。所有需要特殊处理的系统状态值如下： `'status.todo': '未开始', 'status.inactive': '未激活', 'status.reopen': '重新打开', 'status.unpublished': '未发布', 'status.to_be_processed': '待处理', 'status.pending': '待办', 'status.fixed': '已修复', 'status.in_progress': '进行中', 'status.resolving': '处理中',`

`'status.in_implementation': '实现中', 'status.implemented': '已实现', 'status.done': '已完成', 'status.published': '已发布', 'status.rejected': '已拒绝', 'status.form_closed': '已关单', 'status.solved': '已解决', 'status.confirmed': '已确认', "status.verified": '已验证', 'status.checked': '完成', 'status.close': '关闭',`

另外，自定义的工作项类型可以直接写中文，例如：工作项类型表达式 = '合同'。

对于系统自带工作项类型的写法，以任务为例，应写：工作项类型表达式 =' `{{issue_type.task}}` '。所有需要特殊处理的类型如下：

`'issue_type.needs': '需求',`

`'issue_type.defect': '缺陷',`

`'issue_type.android_defect': 'Android缺陷',`

`'issue_type.web_defect': 'Web缺陷',`

`'issue_type.task': '任务',`

`'issue_type.work_order': '工单',`

`'issue_type.user_story': '用户故事',`

`'issue_type.sub_task': '子任务',`

`'issue_type.sub_needs': '子需求',`

`'issue_type.sub_check_item': '子检查项',`

`'issue_type.release': '发布',`

## 3.应用

点击右下角按钮即可应用到图表⬇️

![SQL 使用说明 配图 4](https://docs.ones.cn/assets/images/424231ce59a6b47a-b228eccd3c40911107f698248c739408.png)

确认输出结果后，可以使用样式设置中的内容调整图表外观，以增加美观度，如下图。

![SQL 使用说明 配图 5](https://docs.ones.cn/assets/images/3c91637af9c51a80-eff183c68d04369fdaf8cf002cd8a4d4.png)