Группировать по значениям, которые находятся в последовательности
У меня есть таблица вроде этого
row chequeNo
1 15
2 19
3 20
4 35
5 16
и мне нужно получить такой результат
row from to
1 15 16
2 19 20
3 35 35
поэтому мне нужны группы chequeNo, где значения будут последовательными без каких-либо перерывов. chequeNo - уникальный столбец. Кроме того, это нужно сделать с помощью одного запроса на выбор sql, потому что у меня нет прав на создание любых структур sql, кроме отдельных запросов.
Так возможно?
Был бы благодарен за любую помощь
Ответы
Ответ 1
Вы можете использовать технику Aketi Jyuuzou под названием Tabibitosan здесь:
SQL> create table mytable (id,chequeno)
2 as
3 select 1, 15 from dual union all
4 select 2, 19 from dual union all
5 select 3, 20 from dual union all
6 select 4, 35 from dual union all
7 select 5, 16 from dual
8 /
Table created.
SQL> with tabibitosan as
2 ( select chequeno
3 , chequeno - row_number() over (order by chequeno) grp
4 from mytable
5 )
6 select row_number() over (order by grp) "row"
7 , min(chequeno) "from"
8 , max(chequeno) "to"
9 from tabibitosan
10 group by grp
11 /
row from to
---------- ---------- ----------
1 15 16
2 19 20
3 35 35
3 rows selected.
С уважением,
Роб.
Ответ 2
Это должно работать с Oracle 10 (только с Oracle 11)
select group_nr + 1,
min(chequeno) as start_value,
max(chequeno) as end_value
from (
select chequeno,
sum(group_change_flag) over (order by rn) as group_nr
from (
select row_number() over (order by chequeno) as rn,
chequeno,
case
when chequeno - lag(chequeno,1,chequeno) over (order by chequeno) <= 1 then 0
else 1
end as group_change_flag
from foo
) t1
) t2
group by group_nr
order by group_nr
(он должен работать с любой СУБД, поддерживающей стандартные функции оконного окна SQL, например PostgreSQL, DB2, SQL Server 2012)
Ответ 3
Вот подход "plain vanilla":
SELECT T1.chequeNo, T2.chequeNo
FROM Table1 AS T1 INNER JOIN Table1 AS T2 ON T2.chequeNo >= T1.chequeNo
WHERE
NOT EXISTS (SELECT T0.chequeNo FROM Table1 T0 WHERE T0.chequeNo IN ((T1.chequeNo-1), (T2.chequeNo+1)))
AND (SELECT COUNT(*) FROM Table1 T0 WHERE T0.chequeNo BETWEEN T1.chequeNo AND T2.chequeNo)=(T2.chequeNo - T1.chequeNo + 1)
ORDER BY 1,2
Пожалуйста, дайте мне знать, если он слишком неэффективен для больших наборов данных.
Ответ 4
CREATE TABLE YOUR_TABLE (
chequeNo NUMBER PRIMARY KEY
);
INSERT INTO YOUR_TABLE VALUES (15);
INSERT INTO YOUR_TABLE VALUES (19);
INSERT INTO YOUR_TABLE VALUES (20);
INSERT INTO YOUR_TABLE VALUES (35);
INSERT INTO YOUR_TABLE VALUES (16);
SELECT T1.chequeNo "from", T2.chequeNo "to"
FROM
(
SELECT chequeNo, ROW_NUMBER() OVER (ORDER BY chequeNo) RN
FROM (
SELECT chequeNo, LAG(chequeNo) OVER (ORDER BY chequeNo) PREV
FROM YOUR_TABLE
)
WHERE PREV IS NULL OR chequeNo > PREV + 1
) T1
JOIN
(
SELECT chequeNo, ROW_NUMBER() OVER (ORDER BY chequeNo) RN
FROM (
SELECT chequeNo, LEAD(chequeNo) OVER (ORDER BY chequeNo) NEXT
FROM YOUR_TABLE
)
WHERE NEXT IS NULL OR chequeNo < NEXT - 1
) T2
USING (RN);
Результат:
from to
---------------------- ----------------------
15 16
19 20
35 35
Если мы немного подправляем вещи...
INSERT INTO YOUR_TABLE VALUES (17);
INSERT INTO YOUR_TABLE VALUES (18);
... получаем:
from to
---------------------- ----------------------
15 20
35 35
