powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
25 сообщений из 26, страница 1 из 2
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38597376
Фотография SmeL_md
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Как объединить пересекающие и соприкасающиеся интервалы в один?
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
CREATE TABLE reserv
(
  res_id serial NOT NULL,
  room_id integer,
  drange daterange
)
INSERT INTO reserv (room_id,drange) VALUES (1,'[2014-01-01,2014-01-20)');
INSERT INTO reserv (room_id,drange) VALUES (1,'[2014-01-20,2014-02-10)');
INSERT INTO reserv (room_id,drange) VALUES (1,'[2014-02-10,2014-03-20)');
INSERT INTO reserv (room_id,drange) VALUES (2,'[2014-02-10,2014-03-20)');
INSERT INTO reserv (room_id,drange) VALUES (1,'[2014-03-22,2014-03-24)');

хочу получить похожее,
room_iddrange1[2014-01-01,2014-03-20)1[2014-03-22,2014-03-24)2[2014-02-10,2014-03-20)Поидеи нужно использовать RECURSIVE но ничего не выходит
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
WITH RECURSIVE t(room_id,dbeg,dend,drange) AS (
    SELECT room_id, lower(drange),upper(drange) + '1 day'::interval, drange FROM reserv as r
  UNION ALL
    SELECT r.room_id, lower(r.drange), upper(r.drange), r.drange FROM reserv as r, t
    --WHERE r.drange && daterange(t.dbeg::date,t.dend::date) /*AND isempty(r.drange * t.drange) = 'f'*/ 
    --WHERE upper(r.drange) > t.dbeg /*AND isempty(r.drange * t.drange) = 'f'*/ 
    WHERE lower(r.drange) >= t.dend 
    AND r.room_id = t.room_id
)
SELECT * FROM t;
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38597392
Lonepsycho
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
SmeL_mdПоидеи нужно использовать RECURSIVE но ничего не выходит
по идеи RECURSIVE не нужно использовать. джойните по res_id != res_id и drange && drange, селектите res_id, room_id, drange + drange , ну и пользуемся DISTINCT собственно. может есть вариант по лучше, но этот работать будет.
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38597423
Lonepsycho
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
SmeL_md,

да, может всётаки убрать критерий джойна res_id != res_id и оставить только drange && drange и может room_id = room_id (это уже по задаче как вам нужно), поскольку имея res_id != res_id как критерий можем получить не то что хотели, т.е. небудет единичных случаев в результсете.
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38597428
Фотография SmeL_md
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Lonepsycho,
так по идеи мы получим одно суммирование, т.е. интервал увеличится на 1 пересечение, а этого не достаточно нужно все что прилегает и пересекается объединить
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38597441
Lonepsycho
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
SmeL_md,
а, ну да. забыл что когда такое было нужно писал sum(...) для range type.
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38597495
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
with reserv (room_id, drange) as (values
    (1, daterange '[2014-01-01, 2014-01-20)'),
    (1, daterange '[2014-01-19, 2014-02-10)'),
    (1, daterange '[2014-02-10, 2014-03-20)'),
    (2, daterange '[2014-02-10, 2014-03-20)'),
    (1, daterange '[2014-03-22, 2014-03-24)'))
, reserv_drange_lag as (select *,
        lag(drange) over(partition by room_id order by lower(drange)) 
        as drange_lag
    from reserv)
, reserv_combine_flag as (select *,
        drange_lag && drange or drange_lag -|- drange 
        as combine_flag
    from reserv_drange_lag)
, reserv_combine_id as (select *,
        sum(case when combine_flag then 0 else 1 end)
        over(partition by room_id order by lower(drange))
        as combine_id
    from reserv_combine_flag)
select room_id, min(lower(drange)), max(upper(drange))
    from reserv_combine_id group by room_id, combine_id order by 1, 2;
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38597555
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
мой предыдущий вариант ошибается на наборе данных с дополнительной строкой (1, daterange '[2014-01-20, 2014-02-05)')

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
with recursive reserv (room_id, drange) as (
values
    (1, daterange '[2014-01-01, 2014-01-20)'),
    (1, daterange '[2014-01-19, 2014-02-10)'),
    (1, daterange '[2014-01-20, 2014-02-05)'),
    (1, daterange '[2014-02-10, 2014-03-20)'),
    (2, daterange '[2014-02-10, 2014-03-20)'),
    (1, daterange '[2014-03-22, 2014-03-24)'))
, reserv_combined as (
select array[drange] as cluster, room_id, drange
    from reserv
union all
select c.cluster||r.drange, c.room_id, c.drange+r.drange
    from reserv_combined as c
    join reserv as r using(room_id)
    where not(r.drange = any(c.cluster))
    and (c.drange && r.drange or c.drange -|- r.drange))
, reserv_contained as (select room_id, r1.drange,
        r1.drange <> r2.drange and r1.drange <@ r2.drange
        as contained_flag
    from reserv_combined as r1
    join reserv_combined as r2 using(room_id))
select room_id, drange from reserv_contained
except /*distinct*/
select room_id, drange from reserv_contained
    where contained_flag
order by 1, 2;
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38597605
SmeL_md,

я вот тут в соседней ветке приводил пример решения, даже с картинками. Думаю, в Postgre такой вариант тоже должен сработать... :)
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38597633
SmeL_md,

на вскидку в лоб переписал свое решение по PostgreSQL . Разбираться с преобразованием кончал/начал в диапазонный тип стало лень, если надо - доделаешь сам... :)
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38597637
Добрый Э - ЭхSmeL_md,

на вскидку в лоб переписал свое решение по PostgreSQL . Разбираться с преобразованием кончал/начал в диапазонный тип стало лень, если надо - доделаешь сам... :)
таки открыл на секунду хелп по постгри и сделал так: тынц ...
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38597639
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38597944
Фотография SmeL_md
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Спасибо LeXa NalBat и Добрый Э - Эх
на всякий случай зазеркалю ответ Добрый Э - Эх
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
select room_id, daterange(min(b), max(e)) as x_range
  from (
         select room_id, b, e, sum(sog) over(partition by room_id
                                                 order by b,e) as grp_id
           from (
                  select room_id, lower(r.drange) b, upper(r.drange) e, 
                         case 
                           when lower(r.drange) <= max(upper(r.drange)) 
                                                  over(partition by room_id
                                                           order by lower(r.drange),
                                                                    upper(r.drange) 
                                                       rows between unbounded preceding
                                                                and 1 preceding) 
                             then 0 
                           else 1 
                         end as sog -- sog = start_of_group :)
                    from reserv r
                )v0
       )v1
 group by room_id, grp_id
 order by room_id, min(b);
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38598010
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
SmeL_md,

мой вариант через cluster может оказаться медленным на большом объеме данных из-за почти декартового self-join, используйте способ от Добрый Э - Эх. обидно, что не додумался до b <= max(e) over(order by b, e rows between unbounded preceding and 1 preceding)
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38788263
Ismanov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Здравствуйте,

Как с помощью запроса объединить диапазоны таблицы (для примера, база содержит сотни записей)

ID MIN MAX
77777 1 3
77777 2 4
77777 3 5
77777 4 6

в один общий диапазон

ID MIN MAX
77777 1 6

?

Не могу правильно написать запрос SQL.
Заранее большое спасибо.
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38788317
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Ismanov,

Для новых вопросов надо заводить отдельную тему.

Код: sql
1.
SELECT id, min(min), max(max) FROM a_tab GROUP BY id
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38788804
vyegorov,

IMHO - магия данных. Думаю, твой вариант не решает задачи Ismanov-а.
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38788805
Ismanov,

а самостоятельно адаптировать это решение не получилось, что ли?
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38788914
Ismanov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Добрый Э - Эх,

не получается, я новичок в sql (
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38788985
Ismanov,
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
--
-- Тестовые данные:
with
  t as
    (
      select 77777 as id,  1 as min$,  3 as max$ union all
      select 77777 as id,  2 as min$,  4 as max$ union all
      select 77777 as id,  3 as min$,  5 as max$ union all
      select 77777 as id,  4 as min$,  6 as max$ union all
      select 77777 as id, 11 as min$, 13 as max$ union all
      select 77777 as id, 12 as min$, 14 as max$ union all
      select 77777 as id, 13 as min$, 15 as max$ union all
      select 77777 as id, 14 as min$, 16 as max$ union all
      select 88888 as id,  1 as min$,  3 as max$ union all
      select 88888 as id,  2 as min$,  4 as max$ union all
      select 88888 as id,  3 as min$,  5 as max$ union all
      select 88888 as id,  4 as min$,  6 as max$ union all
      select 88888 as id, 21 as min$, 23 as max$ union all
      select 88888 as id, 22 as min$, 24 as max$ union all
      select 88888 as id, 23 as min$, 25 as max$ union all
      select 88888 as id, 24 as min$, 26 as max$
    )
--
-- Основной запрос:
select id, min(b), max(e)
  from (
         select id, b, e, sum(sog) over(partition by id
                                                 order by b,e) as grp_id
           from (
                  select id, min$ b, max$ e, 
                         case 
                           when min$ <= max(max$) 
                                                  over(partition by id
                                                           order by min$,
                                                                    max$ 
                                                       rows between unbounded preceding
                                                                and 1 preceding) 
                             then 0 
                           else 1 
                         end as sog -- sog = start_of_group :)
                    from t
                )v0
       )v1
 group by id, grp_id
 order by id, min(b);

on-line проверка на sqlfiddle.com
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38789175
Ismanov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Добрый Э - Эх,

Спасибо большое, но выдает ошибку "Ошибка синтаксиса (пропущен оператор) в выражении запроса SUM(sog) OVER(partition by id
ORDER BY b,e)"
Я пытаюсь сделать это через MS Access 2013 через режим SQL при составлении запроса.
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38789178
IsmanovЯ пытаюсь сделать это через MS Access 2013 через режим SQL при составлении запроса.и при чем тут форум по Postgre, если ты на Access-e? По нему отдельная ветка на форуме есть. Но и под него решение написать можно.
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38789183
Ismanov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Добрый Э - Эх,

понял значит я заблудился ) иду публиковать проблему на access
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38789190
Лопата
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Ismanov,

вам нужен исключительно и только "запрос к серверу" ( Pass-Through query http://support.microsoft.com/kb/303968 -- он и только он пишется в синтаксисе СУБД--сервера) либо же писать запросы в SQL-диалекте самого аксесса [т.н. Jet-SQL] (там оконных ф-й скорее всего нет до сих пор)
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38789216
Ismanov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
лопата,

пожалуйста, можно подробно описать как написать этот запрос к серверу или через Jet SQL ?
я опубликовал вопрос на ветке Access http://www.sql.ru/forum/1123396-a/obedinit-neskolko-perekryvaushhihsya-diapazonov-v-odin
...
Рейтинг: 0 / 0
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
    #38789231
Лопата
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Ismanov,
запрос к серверу, если у вас данные - в постгресе, а как -- написано по ссылке. т.ч. не ленимся, улыбаемся и машем. сам запрос написан ээхом выше.

а в jet-SQL -- вам там и напишут. или ссылку на более раннее дадут. (я обычно в аксесс руками (в vba) открывал рекордсет, и клеил диапазоны вдоль прохода. это быстрее, чем чистый jet-SQL. вспоминать долго).
...
Рейтинг: 0 / 0
25 сообщений из 26, страница 1 из 2
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]