Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 задачка по разнице множеств  [new]
Albatross
Member

Откуда:
Сообщений: 997
есть таблица A

(id, type, date_from, date_to) отрезков, которые могут пересекаться в рамках разных type.

Есть таблица B.

(id, date_from, date_to) отрезков, которые могут пересекаться.

Нужно найти разницу множеств с учётом type, а именно некоторую таблицу A'
например, если в таблице A есть интервал с типом 1 с 1го по 5 июля, а в таблице B есть интервал с 3 по 3 июля, то итого должны получиться два интервала типа 1 с первого по 2 июля и с 4 по 5 июля.
Никаких прочих ограничений не накладывается.

Можно ли это сделать на SQL вообще? Без курсоров.
5 авг 09, 10:38    [7498889]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
http://www.aspnetmania.com/Articles/Article.aspx?ID=41
5 авг 09, 10:43    [7498921]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]

Guest
уточнения в B они сами с собой не пересекаются?
пересечение A и B вхождение?
в A разница всегда больше 2?
что делать если отрезки из A и B полностью совпадают?
5 авг 09, 10:44    [7498935]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]
Albatross
Member

Откуда:
Сообщений: 997
`ё,

в B могут пересекаться как угодно. Могут даже все быть с одинаковыми датами.
Если отрезки совпадают, из результата их выкинуть.
"в A разница всегда больше 2?" - не понял.
5 авг 09, 10:47    [7498952]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]
Ramin Hashimzade
Member

Откуда: Азербайджан, Баку
Сообщений: 9979
Блог
чесно скажы, это у тебя дипломная? :)

----
www.hramin.jino-net.ru
Картинка с другого сайта.
5 авг 09, 10:49    [7498964]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]
Albatross
Member

Откуда:
Сообщений: 997
Ramin,

Нет. Грубо говоря, есть сбои устройств, которые снимаются датчиками - они в таблице А, причём type - это тип сбоя. Но иногда этот сбой не считается сбоем, если специальные люди ввели в систему отмазу, типа это электричество отрубило и мы не причём. Это таблица B.
Нужно из сбоев по датчикам вычесть отмазы.

Я уже делал решение, но тут выяснилось, что оно не работает, если один сбой по датчикам пересекается со многими отмазами сразу.
5 авг 09, 10:52    [7498980]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]
Albatross
Member

Откуда:
Сообщений: 997
tpg,

Там совсем не то, что нужно.
5 авг 09, 10:53    [7498986]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Albatross
Там совсем не то, что нужно.
это Вы имхо с выводами поспешили

1) генерируете два набора записей по Вашим интервалам (A и B), например путем join с таблицей-календарем
(результат - по одной записи для каждой даты)

2) находите "разницу множеств" (можно по рекомендованной tpg статье)

3) сливаете результат шага 2 обратно в интервалы - получаете "некоторую таблицу A'"
5 авг 09, 10:58    [7499024]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]
Albatross
Member

Откуда:
Сообщений: 997
Паганель,

Таблица календарь, конечно, хорошо, но увы (сорри, не написал), тут не покатит, поскольку у меня с точностью до минут.
Можно, конечно, таблицу минут создать...... но может попроще можно?
5 авг 09, 11:00    [7499038]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]

Guest
тогда примерно так
собираем "не пересекающиеся отрезки" в B так , что-бы пересекающихся отрезков не было.
а дальше думаю совсем просто .
5 авг 09, 11:03    [7499067]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]
Albatross
Member

Откуда:
Сообщений: 997
`ё,

пересекающихся из B я знаю как убрать, но как это поможет?
представим, что их нет.
5 авг 09, 11:05    [7499086]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Albatross
не покатит, поскольку у меня с точностью до минут.
Можно, конечно, таблицу минут создать...... но может попроще можно?
таблицу минут можно ж не постоянную иметь,
а временную создать (и данными набить) перед запуском запроса

но Вы уж договаривайте
какая будет разница между минимальной и максимальной датами в одном запросе?
если сутки, то таблица минут 1440 записей имхо приемлемо
если около недели, то таблица минут это 10 тыс записей, тоже не так страшно
но если 10 лет, то это порядка 5 млн минут, уже не то...
5 авг 09, 11:08    [7499102]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]

Guest
a.date_from,B.date_from 
where A.date_from < B.date_from and b.date_to<a.date_to
union

B.date_to,a.date_to
where A.date_from < B.date_from and b.date_to<a.date_to

так ?
5 авг 09, 11:08    [7499107]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]

Guest
и снова убираем пересекающиеся отрезки
5 авг 09, 11:10    [7499121]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]

Guest
с заменой их на не пересекающиеся
5 авг 09, 11:14    [7499165]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]
Albatross
Member

Откуда:
Сообщений: 997
a.date_from,B.date_from 
where A.date_from < B.date_from and b.date_to<a.date_to
union

B.date_to,a.date_to
where A.date_from < B.date_from and b.date_to<a.date_to

так ?

Вот я так и делал.
Вот посмотрите, если есть сбой по датчикам от первого до 30 августа.
И отмазы в 5, 6 , 7 числа
То вторая часть здесь помимо прочего даст сбой от первого до 6 числа, что неверно, так как должна быть дырка в 5, 6. И даже если получившиеся три сбоя объединить, получится неверно.
5 авг 09, 11:15    [7499172]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]

Guest
оставляем не пересекающиеся.

Делим пересекающиеся на не пересекающиеся (можно в цикле от внешних границ )
5 авг 09, 11:19    [7499211]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]
может так?
Guest
DECLARE @A TABLE (id int, type int, date_from datetime, date_to datetime)

DECLARE @B TABLE (id int, date_from datetime, date_to datetime)

INSERT INTO @A
SELECT 1, 1, '20090701', '20090707' UNION ALL
SELECT 2, 1, '20090601', '20090607' UNION ALL
SELECT 3, 1, '20090501', '20090507' UNION ALL
SELECT 4, 1, '20090401', '20090407' UNION ALL
SELECT 5, 1, '20090301', '20090307'

INSERT INTO @B
SELECT 1, '20090703', '20090706' UNION ALL
SELECT 2, '20090603', '20090605' UNION ALL
SELECT 5, '20090303', '20090304'

-- **************************************

SELECT A.id, A.type, A.date_from As Start, B.date_from As Finish
FROM @A A
INNER JOIN @B B
  ON    B.date_from BETWEEN A.date_from AND A.date_to
    AND B.date_to BETWEEN A.date_from AND A.date_to 

UNION ALL

SELECT A.id, A.type, B.date_to, A.date_to
FROM @A A
INNER JOIN @B B
  ON    B.date_from BETWEEN A.date_from AND A.date_to
    AND B.date_to BETWEEN A.date_from AND A.date_to 

ORDER BY type, Start, Finish
id          type        Start                                                  Finish                                                 
----------- ----------- ------------------------------------------------------ ------------------------------------------------------ 
5           1           2009-03-01 00:00:00.000                                2009-03-03 00:00:00.000
5           1           2009-03-04 00:00:00.000                                2009-03-07 00:00:00.000
2           1           2009-06-01 00:00:00.000                                2009-06-03 00:00:00.000
2           1           2009-06-05 00:00:00.000                                2009-06-07 00:00:00.000
1           1           2009-07-01 00:00:00.000                                2009-07-03 00:00:00.000
1           1           2009-07-06 00:00:00.000                                2009-07-07 00:00:00.000
5 авг 09, 11:20    [7499220]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]

Guest
извини что так отрывочно.
выделяем ближайшие к краем отрезки выделяем не пересекающиеся. их убираем и так далее сжимая внешние границы отрезков к друг другу
5 авг 09, 11:21    [7499234]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]
Albatross
Member

Откуда:
Сообщений: 997
может так?
DECLARE @A TABLE (id int, type int, date_from datetime, date_to datetime)

DECLARE @B TABLE (id int, date_from datetime, date_to datetime)

INSERT INTO @A
SELECT 1, 1, '20090701', '20090707' UNION ALL
SELECT 2, 1, '20090601', '20090607' UNION ALL
SELECT 3, 1, '20090501', '20090507' UNION ALL
SELECT 4, 1, '20090401', '20090407' UNION ALL
SELECT 5, 1, '20090301', '20090307'

INSERT INTO @B
SELECT 1, '20090703', '20090706' UNION ALL
SELECT 2, '20090603', '20090605' UNION ALL
SELECT 5, '20090303', '20090304'

-- **************************************

SELECT A.id, A.type, A.date_from As Start, B.date_from As Finish
FROM @A A
INNER JOIN @B B
  ON    B.date_from BETWEEN A.date_from AND A.date_to
    AND B.date_to BETWEEN A.date_from AND A.date_to 

UNION ALL

SELECT A.id, A.type, B.date_to, A.date_to
FROM @A A
INNER JOIN @B B
  ON    B.date_from BETWEEN A.date_from AND A.date_to
    AND B.date_to BETWEEN A.date_from AND A.date_to 

ORDER BY type, Start, Finish
id          type        Start                                                  Finish                                                 
----------- ----------- ------------------------------------------------------ ------------------------------------------------------ 
5           1           2009-03-01 00:00:00.000                                2009-03-03 00:00:00.000
5           1           2009-03-04 00:00:00.000                                2009-03-07 00:00:00.000
2           1           2009-06-01 00:00:00.000                                2009-06-03 00:00:00.000
2           1           2009-06-05 00:00:00.000                                2009-06-07 00:00:00.000
1           1           2009-07-01 00:00:00.000                                2009-07-03 00:00:00.000
1           1           2009-07-06 00:00:00.000                                2009-07-07 00:00:00.000


Это неправильно. Опять же, в случае, когда на сбой больше одной отмазы (внутри него).
Например:


 	

DECLARE @A TABLE (id int, type int, date_from datetime, date_to datetime)

DECLARE @B TABLE (id int, date_from datetime, date_to datetime)

INSERT INTO @A
SELECT 1, 1, '20090701', '20090707' 

INSERT INTO @B
SELECT 1, '20090703', '20090704' UNION ALL
SELECT 1, '20090705', '20090706' 

-- **************************************

SELECT A.id, A.type, A.date_from As Start, B.date_from As Finish
FROM @A A
INNER JOIN @B B
  ON    B.date_from BETWEEN A.date_from AND A.date_to
    AND B.date_to BETWEEN A.date_from AND A.date_to 

UNION ALL

SELECT A.id, A.type, B.date_to, A.date_to
FROM @A A
INNER JOIN @B B
  ON    B.date_from BETWEEN A.date_from AND A.date_to
    AND B.date_to BETWEEN A.date_from AND A.date_to 


Он выведет,

id type Start Finish
1 1 2009-07-01 00:00:00.000 2009-07-03 00:00:00.000
1 1 2009-07-01 00:00:00.000 2009-07-05 00:00:00.000
1 1 2009-07-04 00:00:00.000 2009-07-07 00:00:00.000
1 1 2009-07-06 00:00:00.000 2009-07-07 00:00:00.000

Но это неправильно, понятно, почему
Например, вторая строчка не учитывает отмазу с 3 по 4го число
5 авг 09, 11:39    [7499387]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5499
Блог
Перейти на 2008, освоить тип данных geometry. ;)

Посмотрите пример его "экзотического" использования.
5 авг 09, 12:04    [7499611]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]
может так?
Guest
...вот так можна получить все даты без "отмазок"
но как из них наклепать интервалов - ума неприложу
DECLARE @A TABLE (id int, type int, date_from datetime, date_to datetime)

DECLARE @B TABLE (id int, date_from datetime, date_to datetime)

INSERT INTO @A
SELECT 1, 1, '20090630', '20090707' --UNION ALL
--SELECT 1, 1, '20090801', '20090807' 

INSERT INTO @B
SELECT 1, '20090703', '20090704' UNION ALL
SELECT 1, '20090705', '20090706'

-- **************************************

SELECT DISTINCT type, DATEADD(d, D.num, A.date_from) As AllDate
FROM @A A, 
  (SELECT CAST(D1.digit + D0.digit As int) AS num
   FROM (
	SELECT '0' AS digit
	UNION All
	SELECT '1'
	UNION All 
	SELECT '2'
	UNION All
	SELECT '3'
	UNION All
	SELECT '4'
	UNION All
	SELECT '5'
	UNION All
	SELECT '6'
	UNION All
	SELECT '7'
	UNION All
	SELECT '8'
	UNION ALL 
	SELECT '9') D0,
	(SELECT '0' AS digit
	UNION All
	SELECT '1'
	UNION All 
	SELECT '2'
	UNION All
	SELECT '3'
	UNION All
	SELECT '4'
	UNION All
	SELECT '5'
	UNION All
	SELECT '6'
	UNION All
	SELECT '7'
	UNION All
	SELECT '8'
	UNION ALL 
	SELECT '9') D1) D
WHERE DATEADD(d, D.num, A.date_from) <= A.date_to
  AND DATEADD(d, D.num, A.date_from) NOT IN
(SELECT DISTINCT DATEADD(d, D.num, B.date_from)
FROM @B B, 
  (SELECT CAST(D1.digit + D0.digit As int) AS num
   FROM (
	SELECT '0' AS digit
	UNION All
	SELECT '1'
	UNION All 
	SELECT '2'
	UNION All
	SELECT '3'
	UNION All
	SELECT '4'
	UNION All
	SELECT '5'
	UNION All
	SELECT '6'
	UNION All
	SELECT '7'
	UNION All
	SELECT '8'
	UNION ALL 
	SELECT '9') D0,
	(SELECT '0' AS digit
	UNION All
	SELECT '1'
	UNION All 
	SELECT '2'
	UNION All
	SELECT '3'
	UNION All
	SELECT '4'
	UNION All
	SELECT '5'
	UNION All
	SELECT '6'
	UNION All
	SELECT '7'
	UNION All
	SELECT '8'
	UNION ALL 
	SELECT '9') D1) D
WHERE DATEADD(d, D.num, B.date_from) <= B.date_to)
type        AllDate                                                
----------- ------------------------------------------------------ 
1           2009-06-30 00:00:00.000
1           2009-07-01 00:00:00.000
1           2009-07-02 00:00:00.000
1           2009-07-07 00:00:00.000
5 авг 09, 12:12    [7499688]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
может так?
как из них наклепать интервалов - ума неприложу
можно как-то так
declare @t table(dt smalldatetime)
insert into @t(dt)
select '20090630' union all
select '20090701' union all
select '20090702' union all
select '20090704' union all
select '20090705' union all
select '20090707'

select min(dt) as begin_, max(dt) as end_
  from (select t.dt, datediff(day, m.dt, t.dt) - row_number() over(order by t.dt) as grp
          from @t as t
          cross join (select min(dt) as dt from @t) as m
       ) as r
 group by grp


begin_                  end_
----------------------- -----------------------
2009-06-30 00:00:00     2009-07-02 00:00:00
2009-07-04 00:00:00     2009-07-05 00:00:00
2009-07-07 00:00:00     2009-07-07 00:00:00

(3 row(s) affected)
5 авг 09, 12:24    [7499781]     Ответить | Цитировать Сообщить модератору
 Re: задачка по разнице множеств  [new]
totoshka-83
Member

Откуда:
Сообщений: 61
Ramin,

Прошу прощения за оффтоп :)
Мне понравился дизайн вашего сайта не подскажите какой шаблон для джумлы использовали?
5 авг 09, 13:04    [7500171]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить