最小值和最大值按连续范围分组 [英] Min and Max values grouping by consecutive ranges
问题描述
我有一张表格,该表格通知我发生错误的错误类型和行号.(此过程目前不相关).我需要按错误类型分组,并显示每种错误类型的行首和行尾,这是每种错误类型的范围所导致的.我需要考虑线间距
我的表和查询是:
创建表错误(err_type varchar(10),行整数);插入错误值('type_A',1),('type_A',2),('type_A',3),('type_A',6),('type_A',7),('type_B',9),('type_B',10),('type_B',12),('type_B',13),('type_B',14),('type_B',15),('type_C',21);从错误中选择*;
我的数据:
err_type行----------------type_A 1type_A 2type_A 3type_A 6type_A 7B型9B型10B型12B型13B型14B型15type_C 21
我需要执行以下查询:
err_type line_start line_end-------------------------------A型1 3type_A 6 7B型9 10B型12 15type_C 21 21
我正在使用PostgreSQL,但是Oracle对于基于分区
功能具有类似的语法.
有什么建议吗?
这是一个空白问题.我认为最简单的方法是 row_number()
和 group by
:
选择err_type,min(line),max(line)从(选择e.*,row_number()到(按err_type分区按行排序)作为seqnum从错误e)e按err_type分组,(行-seqnum)按err_type排序,最小值(行);
此处是db< fiddle.
I have a table that informs me a error type and line number that error occurred. (The process is irrelevant at this moment). I need to group by error type and show line start and line end for each error type, resulting of a range of each error type. I need to consider gaps of lines
My table and queries was:
create table errors (
err_type varchar(10),
line integer);
insert into errors values
('type_A', 1),('type_A', 2),('type_A', 3),
('type_A', 6),('type_A', 7),
('type_B', 9),('type_B', 10),
('type_B', 12),('type_B', 13),('type_B', 14),('type_B', 15),
('type_C', 21);
select * from errors;
My data:
err_type line
----------------
type_A 1
type_A 2
type_A 3
type_A 6
type_A 7
type_B 9
type_B 10
type_B 12
type_B 13
type_B 14
type_B 15
type_C 21
I need a query to do this:
err_type line_start line_end
-------------------------------
type_A 1 3
type_A 6 7
type_B 9 10
type_B 12 15
type_C 21 21
I'm using PostgreSQL, but Oracle has a similar syntax for partitioning over
functionality.
Any suggestion?
This is a gaps-and-islands problem. I think the simplest method is row_number()
and group by
:
select err_type, min(line), max(line)
from (select e.*, row_number() over (partition by err_type order by line) as seqnum
from errors e
) e
group by err_type, (line - seqnum)
order by err_type, min(line);
Here is a db<>fiddle.
这篇关于最小值和最大值按连续范围分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!