Гость
Целевая тема:
Создать новую тему:
Автор:
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один / 25 сообщений из 26, страница 1 из 2
26.03.2014, 19:07:17
    #38597376
SmeL_md
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
Как объединить пересекающие и соприкасающиеся интервалы в один?
Код: 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
26.03.2014, 19:31:13
    #38597392
Lonepsycho
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
SmeL_mdПоидеи нужно использовать RECURSIVE но ничего не выходит
по идеи RECURSIVE не нужно использовать. джойните по res_id != res_id и drange && drange, селектите res_id, room_id, drange + drange , ну и пользуемся DISTINCT собственно. может есть вариант по лучше, но этот работать будет.
...
Рейтинг: 0 / 0
26.03.2014, 20:08:14
    #38597423
Lonepsycho
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
SmeL_md,

да, может всётаки убрать критерий джойна res_id != res_id и оставить только drange && drange и может room_id = room_id (это уже по задаче как вам нужно), поскольку имея res_id != res_id как критерий можем получить не то что хотели, т.е. небудет единичных случаев в результсете.
...
Рейтинг: 0 / 0
26.03.2014, 20:17:29
    #38597428
SmeL_md
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
Lonepsycho,
так по идеи мы получим одно суммирование, т.е. интервал увеличится на 1 пересечение, а этого не достаточно нужно все что прилегает и пересекается объединить
...
Рейтинг: 0 / 0
26.03.2014, 20:37:15
    #38597441
Lonepsycho
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
SmeL_md,
а, ну да. забыл что когда такое было нужно писал sum(...) для range type.
...
Рейтинг: 0 / 0
26.03.2014, 22:21:48
    #38597495
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
Код: 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
27.03.2014, 00:20:12
    #38597555
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
мой предыдущий вариант ошибается на наборе данных с дополнительной строкой (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
27.03.2014, 05:19:22
    #38597605
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
SmeL_md,

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

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

на вскидку в лоб переписал свое решение по PostgreSQL . Разбираться с преобразованием кончал/начал в диапазонный тип стало лень, если надо - доделаешь сам... :)
таки открыл на секунду хелп по постгри и сделал так: тынц ...
...
Рейтинг: 0 / 0
27.03.2014, 07:54:39
    #38597639
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
...
Рейтинг: 0 / 0
27.03.2014, 12:31:22
    #38597944
SmeL_md
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
Спасибо 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
27.03.2014, 13:13:49
    #38598010
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
SmeL_md,

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

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

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

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

ID MIN MAX
77777 1 6

?

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

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

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

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

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

не получается, я новичок в sql (
...
Рейтинг: 0 / 0
28.10.2014, 10:33:43
    #38788985
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
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
28.10.2014, 12:40:56
    #38789175
Ismanov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
daterange && daterange объединить пересекающие и соприкасающиеся интервалы в один
Добрый Э - Эх,

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

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

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

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

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


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