文档中心
搜索
Ask AI
全部文档
文档中心DeepModel功能DeepQL查询语句分组聚合（group by原理）

分组聚合（group by原理）

DeepQL查询语句支持分组聚合，其中分组语法如上图，与常见的SQL分组语法（例如：select 字段 from 表 group by 字段）不同，包含以下部分

  • group：分组对象

  • by：指定分组字段，可通过using转换分组对象中属性/链接，以作为分组字段，注：暂不支持多值属性/链接作为分组字段；也支持通过ROLLUP、CUBE按分组字段的字段组合进行分组，以实现分组汇总

分组结果如上图，包含以下部分

  • key：分组字段值，可通过.key.分组字段获取分组字段值

  • grouping：分组字段

  • elements：按分组字段分组后的对象数据，与key值对应，可通过.elements.属性/链接编码获取非分组字段值

分组聚合例子

查询各组件、优先级下功能需求的总数、预估总人天，组件显示中文名称

with ReqInfo := (select Requirement
filter .req_type = 'func'),
# 按组件中文名称、优先级，对上述查询结果分组
ReqGroup := (group ReqInfo
using component_name := json_get(.component.name, 'zh-cn')
by component_name, .priority)
# 基于分组结果进行聚合，计算各分组下功能需求的总数、预估总人天
select ReqGroup {
    component_name := .key.component_name,
    priority := .key.priority,
    req_count := count(.elements),
    e_man_day := sum(.elements.<requirement[is Feature].<feature[is Task].e_man_day)
}

分组聚合 - 分组结果例子

通过上述例子中的分组结果，更直观地理解分组语法

with ReqInfo := (select Requirement
filter .req_type = 'func'),
# 按组件中文名称、优先级，对上述查询结果分组
ReqGroup := (group ReqInfo
using component_name := json_get(.component.name, 'zh-cn')
by component_name, .priority)
# 分组结果
select ReqGroup {
    key: {*},
    grouping,
    elements: {
        req_id,
        component: {name},
        priority,
        e_man_day := .<requirement[is Feature].<feature[is Task].e_man_day
    }
}
Copy
[
  {
    "key": {
      "id": "fc11f6cf-cc23-42f3-9a27-d2994d7d7fa8",
      "component_name": "DeepFlow",
      "priority": "high"
    },
    "grouping": [
      "component_name",
      "priority"
    ],
    "elements": [
      {
        "req_id": "REQ_202306_6",
        "component": {
          "name": {
            "en": "DeepFlow",
            "zh-cn": "DeepFlow"
          }
        },
        "priority": "high",
        "e_man_day": []
      },
      {
        "req_id": "REQ_202306_8",
        "component": {
          "name": {
            "en": "DeepFlow",
            "zh-cn": "DeepFlow"
          }
        },
        "priority": "high",
        "e_man_day": [
          2,
          2,
          1,
          1
        ]
      }
    ]
  },
  {
    "key": {
      "id": "05ab56c5-b333-4529-9b3d-6d9630fa6696",
      "component_name": "DeepModel",
      "priority": "high"
    },
    "grouping": [
      "component_name",
      "priority"
    ],
    "elements": [
      {
        "req_id": "REQ_202306_1",
        "component": {
          "name": {
            "en": "DeepModel",
            "zh-cn": "DeepModel"
          }
        },
        "priority": "high",
        "e_man_day": []
      },
      {
        "req_id": "REQ_202306_3",
        "component": {
          "name": {
            "en": "DeepModel",
            "zh-cn": "DeepModel"
          }
        },
        "priority": "high",
        "e_man_day": [
          2,
          2
        ]
      }
    ]
  }
]

分组聚合ROLLUP例子

可通过ROLLUP，按分组字段列表中所有前缀的字段组合进行分组，以实现分组汇总。例如：指定分组字段a、b、c，ROLLUP(a, b, c)等价于{(), (a), (a, b), (a, b, c)}，表示分别对4个字段组合进行分组，包括：不分组，按字段a分组，按字段a、b分组，按字段a、b、c分组

基于分组聚合例子，查询各组件、优先级（ROLLUP）下功能需求的总数、预估总人天，组件显示中文名称。其中，各组件、优先级（ROLLUP）即不分组，按组件分组，按组件、优先级分组

with ReqInfo := (select Requirement
filter .req_type = 'func'),
# 按组件中文名称、优先级，对上述查询结果分组（ROLLUP）
ReqGroup := (group ReqInfo
using component_name := json_get(.component.name, 'zh-cn')
by ROLLUP(component_name, .priority))
# 基于分组结果进行聚合，计算各分组下功能需求的总数、预估总人天
select ReqGroup {
    component_name := .key.component_name,
    priority := .key.priority,
    req_count := count(.elements),
    e_man_day := sum(.elements.<requirement[is Feature].<feature[is Task].e_man_day)
}

分组聚合ROLLUP - 分组结果例子

通过上述例子中的分组结果，更直观地理解分组语法

with ReqInfo := (select Requirement
filter .req_type = 'func'),
# 按组件中文名称、优先级，对上述查询结果分组（ROLLUP）
ReqGroup := (group ReqInfo
using component_name := json_get(.component.name, 'zh-cn')
by ROLLUP(component_name, .priority))
# 分组结果
select ReqGroup {
    key: {*},
    grouping,
    elements: {
        req_id,
        component: {name},
        priority,
        e_man_day := .<requirement[is Feature].<feature[is Task].e_man_day
    }
}
Copy
[
  {
    "key": {
      "id": "947de123-307b-40d1-a13a-68b20d6fcdae",
      "component_name": null,
      "priority": null
    },
    "grouping": [],
    "elements": [
      {
        "req_id": "REQ_202306_6",
        "component": {
          "name": {
            "en": "DeepFlow",
            "zh-cn": "DeepFlow"
          }
        },
        "priority": "high",
        "e_man_day": []
      },
      {
        "req_id": "REQ_202306_8",
        "component": {
          "name": {
            "en": "DeepFlow",
            "zh-cn": "DeepFlow"
          }
        },
        "priority": "high",
        "e_man_day": [
          2,
          2,
          1,
          1
        ]
      },
      {
        "req_id": "REQ_202306_1",
        "component": {
          "name": {
            "en": "DeepModel",
            "zh-cn": "DeepModel"
          }
        },
        "priority": "high",
        "e_man_day": []
      },
      {
        "req_id": "REQ_202306_3",
        "component": {
          "name": {
            "en": "DeepModel",
            "zh-cn": "DeepModel"
          }
        },
        "priority": "high",
        "e_man_day": [
          2,
          2
        ]
      }
    ]
  },
  {
    "key": {
      "id": "a0c0b781-123e-482a-b8a1-7bd0e3248d38",
      "component_name": "DeepModel",
      "priority": "high"
    },
    "grouping": [
      "component_name",
      "priority"
    ],
    "elements": [
      {
        "req_id": "REQ_202306_1",
        "component": {
          "name": {
            "en": "DeepModel",
            "zh-cn": "DeepModel"
          }
        },
        "priority": "high",
        "e_man_day": []
      },
      {
        "req_id": "REQ_202306_3",
        "component": {
          "name": {
            "en": "DeepModel",
            "zh-cn": "DeepModel"
          }
        },
        "priority": "high",
        "e_man_day": [
          2,
          2
        ]
      }
    ]
  },
  {
    "key": {
      "id": "821c3864-4514-40c3-9606-675773293cbe",
      "component_name": "DeepFlow",
      "priority": "high"
    },
    "grouping": [
      "component_name",
      "priority"
    ],
    "elements": [
      {
        "req_id": "REQ_202306_6",
        "component": {
          "name": {
            "en": "DeepFlow",
            "zh-cn": "DeepFlow"
          }
        },
        "priority": "high",
        "e_man_day": []
      },
      {
        "req_id": "REQ_202306_8",
        "component": {
          "name": {
            "en": "DeepFlow",
            "zh-cn": "DeepFlow"
          }
        },
        "priority": "high",
        "e_man_day": [
          2,
          2,
          1,
          1
        ]
      }
    ]
  },
  {
    "key": {
      "id": "30d3912a-3ed4-4b62-b3b7-ada6e70ecec8",
      "component_name": "DeepModel",
      "priority": null
    },
    "grouping": [
      "component_name"
    ],
    "elements": [
      {
        "req_id": "REQ_202306_1",
        "component": {
          "name": {
            "en": "DeepModel",
            "zh-cn": "DeepModel"
          }
        },
        "priority": "high",
        "e_man_day": []
      },
      {
        "req_id": "REQ_202306_3",
        "component": {
          "name": {
            "en": "DeepModel",
            "zh-cn": "DeepModel"
          }
        },
        "priority": "high",
        "e_man_day": [
          2,
          2
        ]
      }
    ]
  },
  {
    "key": {
      "id": "b22fec00-2166-461c-9746-9328b300db55",
      "component_name": "DeepFlow",
      "priority": null
    },
    "grouping": [
      "component_name"
    ],
    "elements": [
      {
        "req_id": "REQ_202306_6",
        "component": {
          "name": {
            "en": "DeepFlow",
            "zh-cn": "DeepFlow"
          }
        },
        "priority": "high",
        "e_man_day": []
      },
      {
        "req_id": "REQ_202306_8",
        "component": {
          "name": {
            "en": "DeepFlow",
            "zh-cn": "DeepFlow"
          }
        },
        "priority": "high",
        "e_man_day": [
          2,
          2,
          1,
          1
        ]
      }
    ]
  }
]

分组聚合CUBE例子

可通过CUBE，按分组字段列表中所有的字段组合进行分组，以实现分组汇总。例如：指定分组字段a、b，CUBE(a, b)等价于{(), (a), (b), (a, b)}，表示分别对4个字段组合进行分组，包括：不分组，按字段a分组，按字段b分组，按字段a、b分组

基于分组聚合例子，查询各组件、优先级（CUBE）下功能需求的总数、预估总人天，组件显示中文名称。其中，各组件、优先级（CUBE）即不分组，按组件分组，按优先级分组，按组件、优先级分组

with ReqInfo := (select Requirement
filter .req_type = 'func'),
# 按组件中文名称、优先级，对上述查询结果分组（CUBE）
ReqGroup := (group ReqInfo
using component_name := json_get(.component.name, 'zh-cn')
by CUBE(component_name, .priority))
# 基于分组结果进行聚合，计算各分组下功能需求的总数、预估总人天
select ReqGroup {
    component_name := .key.component_name,
    priority := .key.priority,
    req_count := count(.elements),
    e_man_day := sum(.elements.<requirement[is Feature].<feature[is Task].e_man_day)
}

分组聚合CUBE - 分组结果例子

通过上述例子中的分组结果，更直观地理解分组语法

with ReqInfo := (select Requirement
filter .req_type = 'func'),
# 按组件中文名称、优先级，对上述查询结果分组（CUBE）
ReqGroup := (group ReqInfo
using component_name := json_get(.component.name, 'zh-cn')
by CUBE(component_name, .priority))
# 分组结果
select ReqGroup {
    key: {*},
    grouping,
    elements: {
        req_id,
        component: {name},
        priority,
        e_man_day := .<requirement[is Feature].<feature[is Task].e_man_day
    }
}
Copy
[
  {
    "key": {
      "id": "147728b7-ec3c-4aa0-adfc-7bc0a1efcd07",
      "component_name": null,
      "priority": null
    },
    "grouping": [],
    "elements": [
      {
        "req_id": "REQ_202306_6",
        "component": {
          "name": {
            "en": "DeepFlow",
            "zh-cn": "DeepFlow"
          }
        },
        "priority": "high",
        "e_man_day": []
      },
      {
        "req_id": "REQ_202306_8",
        "component": {
          "name": {
            "en": "DeepFlow",
            "zh-cn": "DeepFlow"
          }
        },
        "priority": "high",
        "e_man_day": [
          2,
          2,
          1,
          1
        ]
      },
      {
        "req_id": "REQ_202306_1",
        "component": {
          "name": {
            "en": "DeepModel",
            "zh-cn": "DeepModel"
          }
        },
        "priority": "high",
        "e_man_day": []
      },
      {
        "req_id": "REQ_202306_3",
        "component": {
          "name": {
            "en": "DeepModel",
            "zh-cn": "DeepModel"
          }
        },
        "priority": "high",
        "e_man_day": [
          2,
          2
        ]
      }
    ]
  },
  {
    "key": {
      "id": "12c5015d-31d8-437c-8528-d22bfc685c4f",
      "component_name": "DeepModel",
      "priority": "high"
    },
    "grouping": [
      "component_name",
      "priority"
    ],
    "elements": [
      {
        "req_id": "REQ_202306_1",
        "component": {
          "name": {
            "en": "DeepModel",
            "zh-cn": "DeepModel"
          }
        },
        "priority": "high",
        "e_man_day": []
      },
      {
        "req_id": "REQ_202306_3",
        "component": {
          "name": {
            "en": "DeepModel",
            "zh-cn": "DeepModel"
          }
        },
        "priority": "high",
        "e_man_day": [
          2,
          2
        ]
      }
    ]
  },
  {
    "key": {
      "id": "94c0c42b-f6b1-4765-ac0c-fef0a8fc7093",
      "component_name": "DeepFlow",
      "priority": "high"
    },
    "grouping": [
      "component_name",
      "priority"
    ],
    "elements": [
      {
        "req_id": "REQ_202306_6",
        "component": {
          "name": {
            "en": "DeepFlow",
            "zh-cn": "DeepFlow"
          }
        },
        "priority": "high",
        "e_man_day": []
      },
      {
        "req_id": "REQ_202306_8",
        "component": {
          "name": {
            "en": "DeepFlow",
            "zh-cn": "DeepFlow"
          }
        },
        "priority": "high",
        "e_man_day": [
          2,
          2,
          1,
          1
        ]
      }
    ]
  },
  {
    "key": {
      "id": "86bd976d-e504-482e-82ff-4402e3d4adba",
      "component_name": "DeepModel",
      "priority": null
    },
    "grouping": [
      "component_name"
    ],
    "elements": [
      {
        "req_id": "REQ_202306_1",
        "component": {
          "name": {
            "en": "DeepModel",
            "zh-cn": "DeepModel"
          }
        },
        "priority": "high",
        "e_man_day": []
      },
      {
        "req_id": "REQ_202306_3",
        "component": {
          "name": {
            "en": "DeepModel",
            "zh-cn": "DeepModel"
          }
        },
        "priority": "high",
        "e_man_day": [
          2,
          2
        ]
      }
    ]
  },
  {
    "key": {
      "id": "c5417cc3-f7c7-43dc-abea-c98026fd6538",
      "component_name": "DeepFlow",
      "priority": null
    },
    "grouping": [
      "component_name"
    ],
    "elements": [
      {
        "req_id": "REQ_202306_6",
        "component": {
          "name": {
            "en": "DeepFlow",
            "zh-cn": "DeepFlow"
          }
        },
        "priority": "high",
        "e_man_day": []
      },
      {
        "req_id": "REQ_202306_8",
        "component": {
          "name": {
            "en": "DeepFlow",
            "zh-cn": "DeepFlow"
          }
        },
        "priority": "high",
        "e_man_day": [
          2,
          2,
          1,
          1
        ]
      }
    ]
  },
  {
    "key": {
      "id": "10b52f79-93c6-4701-ab84-61d21995cdb3",
      "component_name": null,
      "priority": "high"
    },
    "grouping": [
      "priority"
    ],
    "elements": [
      {
        "req_id": "REQ_202306_6",
        "component": {
          "name": {
            "en": "DeepFlow",
            "zh-cn": "DeepFlow"
          }
        },
        "priority": "high",
        "e_man_day": []
      },
      {
        "req_id": "REQ_202306_8",
        "component": {
          "name": {
            "en": "DeepFlow",
            "zh-cn": "DeepFlow"
          }
        },
        "priority": "high",
        "e_man_day": [
          2,
          2,
          1,
          1
        ]
      },
      {
        "req_id": "REQ_202306_1",
        "component": {
          "name": {
            "en": "DeepModel",
            "zh-cn": "DeepModel"
          }
        },
        "priority": "high",
        "e_man_day": []
      },
      {
        "req_id": "REQ_202306_3",
        "component": {
          "name": {
            "en": "DeepModel",
            "zh-cn": "DeepModel"
          }
        },
        "priority": "high",
        "e_man_day": [
          2,
          2
        ]
      }
    ]
  }
]

分组聚合 - 单选断言例子

暂不支持多值属性/链接作为分组字段，如果多值属性/链接数据实际为单值，可使用assert_single()即单选断言实现分组字段

assert_single()查询时执行，推断指定属性/链接数据是否为单选，如果数据为单选，则查询成功，否则查询失败

基于分组聚合例子，组件从单选链接component改为多选链接multi_component，未加assert_single()时，查询失败；加上assert_single()时，如果数据为单选，则查询成功，否则查询失败

with ReqInfo := (select Requirement
filter .req_type = 'func'),
# 按组件中文名称、优先级，对上述查询结果分组
ReqGroup := (group ReqInfo
using component_name := json_get(.multi_component.name, 'zh-cn')
by component_name, .priority)
# 基于分组结果进行聚合，计算各分组下功能需求的总数、预估总人天
select ReqGroup {
    component_name := .key.component_name,
    priority := .key.priority,
    req_count := count(.elements),
    e_man_day := sum(.elements.<requirement[is Feature].<feature[is Task].e_man_day)
}


with ReqInfo := (select Requirement
filter .req_type = 'func'),
# 按组件中文名称、优先级，对上述查询结果分组
ReqGroup := (group ReqInfo
using component_name := assert_single(json_get(.multi_component.name, 'zh-cn'))
by component_name, .priority)
# 基于分组结果进行聚合，计算各分组下功能需求的总数、预估总人天
select ReqGroup {
    component_name := .key.component_name,
    priority := .key.priority,
    req_count := count(.elements),
    e_man_day := sum(.elements.<requirement[is Feature].<feature[is Task].e_man_day)
}

上一篇：子查询
下一篇：递归函数（代替维度表达式）

回到顶部

咨询热线

400-821-9199

我们使用 ChatGPT，基于文档中心的内容以及对话上下文回答您的问题。

ctrl+Enter to send

© 2022 先胜云. All Rights Reserved.