sql - 用于对连续数字进行分组的 SQL 查询
问题描述
可以说我有下表:
| ... | orderId | serialNumber | type | ... |
|-----|---------|--------------|---------------|-----|
| ... | 1 | 01 | someType | ... |
| ... | 1 | 02 | someType | ... |
| ... | 1 | 03 | someOtherType | ... |
| ... | 1 | 04 | someOtherType | ... |
| ... | 1 | 05 | someType | ... |
| ... | 1 | 06 | someType | ... |
| ... | 2 | 07 | someType | ... |
| ... | 2 | 08 | someType | ... |
我希望我的查询生成以下结果:
| orderId | count | min | max | type |
|---------|-------|-----|-----|---------------|
| 1 | 2 | 01 | 02 | someType |
| 1 | 2 | 03 | 04 | someOtherType |
| 1 | 2 | 05 | 06 | someType |
| 2 | 4 | 07 | 08 | someType |
我想要按类型和 orderId 分组。当 serialNumber 不进行时,应创建一个新条目。
这是我当前的查询:
SELECT
orderId,
count(*) AS count,
min(serialNumber) AS min,
max(serialNumber) AS max,
type
FROM tblMyTable
group by type, orderId
order by orderId
但它产生的结果是错误的:
| orderId | count | min | max | type |
|---------|-------|-----|-----|---------------|
| 1 | 4 | 01 | 06 | someType | <-- this should be 2 entries
| 1 | 2 | 03 | 04 | someOtherType |
| 2 | 2 | 07 | 08 | someType |
如您所见,它没有检测到 serialNumber 03 和 04 属于另一个描述。这会导致不正确的计数和最大值。
我不知道如何添加一个检查连续序列号的标准。
编辑:也可能出现单个记录,例如:
| orderId | count | min | max | type |
|---------|-------|-----|-----|---------------|
| 1337 | 1 | 10 | 10 | someNewType |
解决方案
这是一个间隙和孤岛问题,但我会使用行数差异方法:
select orderid, count(*), min(serialNumber), max(serialNumber), type
from (select t.*,
row_number() over (partition by orderid order by serialnumber) as seqnum,
row_number() over (partition by orderid, type order by serialnumber) as seqnum_type
from t
) t
group by orderid, type, (seqnum - seqnum_type)
order by orderid, min(serialNumber);
你serialnumber
看起来像一个字符串,但它有数值。如果您可以信任它的顺序,您甚至不需要两个行号值:
select orderid, count(*), min(serialNumber), max(serialNumber), type
from (select t.*,
row_number() over (partition by orderid, type order by serialnumber) as seqnum_type
from t
) t
group by orderid, type, (serialnumber - seqnum_type)
order by orderid, min(serialNumber);
这个工作的原因有点难以解释,但如果你运行子查询就很明显了。您将看到行号之间的差异如何识别您要识别的组。
推荐阅读
- c++ - 类中的 C++ 辅助函数,如何使用它们?
- python - Python小于语句,计算小于多少,并将其分配给新列
- javascript - 在导入依赖项之前开玩笑模拟窗口对象
- github - 通过 github 登录 Azure DevOps 失败
- mysql - 获取 BIRT 报告数据的正确方法
- autodesk-forge - 使用 BIM 360 和 Forge 进行 Web 开发 - 后端
- here-api - 是否有用于 HERE 地图路由的批量请求 API?
- python - 即使密钥存在,也检查 dict 中的密钥是否返回 false
- c++ - 串行读取更改输入并且不读取整行
- azure-devops - 我可以用值列表实例化模板管道吗?