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

Откуда:
Сообщений: 373
CREATE TABLE #t(
	[id] [int] NOT NULL,
	[c_Id] [char](3) NOT NULL,
	[dTime] [smalldatetime] NOT NULL,
	[is1] [bit] NOT NULL,
	[is2] [bit] NOT NULL,
	[is3] [bit] NOT NULL);
                        
INSERT #t ([id], [c_Id], [dTime], [is1], [is2], [is3]) VALUES 
   (1, N'111', CAST(N'2017-03-09 08:17:00' AS SmallDateTime), 0, 0, 0)
  ,(2, N'111', CAST(N'2017-03-09 08:10:00' AS SmallDateTime), 0, 0, 0)
  ,(3, N'111', CAST(N'2017-03-09 08:01:00' AS SmallDateTime), 0, 1, 0)
  ,(4, N'111', CAST(N'2017-03-08 20:05:00' AS SmallDateTime), 1, 1, 0)
  ,(5, N'111', CAST(N'2017-03-08 17:47:00' AS SmallDateTime), 0, 0, 0)
  ,(6, N'111', CAST(N'2017-03-08 15:25:00' AS SmallDateTime), 0, 0, 1)
  ,(7, N'111', CAST(N'2017-03-08 12:38:00' AS SmallDateTime), 0, 1, 0)
  ,(8, N'111', CAST(N'2017-03-08 07:06:00' AS SmallDateTime), 0, 1, 0)
  ,(9, N'111', CAST(N'2017-03-07 08:12:00' AS SmallDateTime), 1, 1, 0)
 ,(10, N'111', CAST(N'2017-03-07 05:33:00' AS SmallDateTime), 0, 0, 0)

 ,(11, N'112', CAST(N'2017-02-09 08:17:00' AS SmallDateTime), 0, 1, 0)
 ,(12, N'112', CAST(N'2017-02-09 08:10:00' AS SmallDateTime), 0, 1, 0)
 ,(13, N'112', CAST(N'2017-02-09 08:01:00' AS SmallDateTime), 0, 1, 0)
 ,(14, N'112', CAST(N'2017-02-08 20:05:00' AS SmallDateTime), 1, 1, 0)
 ,(15, N'112', CAST(N'2017-02-08 17:47:00' AS SmallDateTime), 0, 0, 0)
 ,(16, N'112', CAST(N'2017-02-08 15:25:00' AS SmallDateTime), 0, 0, 0)
 ,(17, N'112', CAST(N'2017-02-08 12:38:00' AS SmallDateTime), 0, 1, 0)
 ,(18, N'112', CAST(N'2017-02-08 07:06:00' AS SmallDateTime), 0, 1, 0)
 ,(19, N'112', CAST(N'2017-02-07 08:12:00' AS SmallDateTime), 1, 1, 0)
 ,(20, N'112', CAST(N'2017-02-07 05:33:00' AS SmallDateTime), 0, 0, 0)
 ,(21, N'112', CAST(N'2017-01-08 17:47:00' AS SmallDateTime), 0, 0, 0)
 ,(22, N'112', CAST(N'2017-01-08 15:25:00' AS SmallDateTime), 0, 0, 1)
 ,(23, N'112', CAST(N'2017-01-08 12:38:00' AS SmallDateTime), 0, 0, 0)
 ,(24, N'112', CAST(N'2017-01-08 07:06:00' AS SmallDateTime), 0, 1, 0)
 ,(25, N'112', CAST(N'2017-01-07 08:12:00' AS SmallDateTime), 1, 1, 0)
 ,(26, N'112', CAST(N'2017-01-07 05:33:00' AS SmallDateTime), 0, 0, 0)

SELECT * FROM #t ORDER BY dTime DESC;

DROP TABLE #t;
-- Дана упорядоченная последовательнось по c_Id asc и dTime desc
-- подмножество по c_Id считается правильным, если выполнены следующие условия,
-- при последовательном проходе снизу вверх - для is1(dTime1) = 1  
-- должно существовать is2(dTime2) = 1, где dTime2 >= dTime1 и is3(dTime3) = 1, где dTime3 > dTime1
--   либо набор завершается без is3(dTime3) = 1 
--  иными словами - всегда должны чередоваться 1 слева направо. И только полсле появления 1 в is3 
--  разрешается 1 в is1      

--В результирующий набор должна попасть строка нарушившая условия чередования 1 
--  ,(14, N'112', CAST(N'2017-02-08 20:05:00' AS SmallDateTime), 1, 1, 0)
31 май 17, 14:21    [20527466]     Ответить | Цитировать Сообщить модератору
 Re: Задачка однако  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
Не вижу ВАШЕГО варианта решения.
31 май 17, 15:05    [20527697]     Ответить | Цитировать Сообщить модератору
 Re: Задачка однако  [new]
Massa52
Member

Откуда:
Сообщений: 373
Cammomile,
У меня его сейчас нет. Вернее я его на работе состряпал - но он мне не понравился.
Вот пришел домой - набросал пример из реальной задачи - вдруг кого нить осенит решение.

Алгоритм был следующий
- убираются из набора строки - где все is1, is2, is3 = 0
- и затем использовал rank
в общем - получилось нагромождение with - ов.
31 май 17, 15:32    [20527835]     Ответить | Цитировать Сообщить модератору
 Re: Задачка однако  [new]
iiyama
Member

Откуда:
Сообщений: 642
Massa52,
Зачем цэтэе, если я правильно понял задачу, то OUTER APPLY для строк is1=1, в котором select по вашей гипотезе.
Если она не выполняется, то он вернет NULL
PS. Могу ошибаться, прочитал момоходом
31 май 17, 16:03    [20527982]     Ответить | Цитировать Сообщить модератору
 Re: Задачка однако  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
Поясните как в контексте разговора об SQL читать фразу: Ис Один ОТ ДиТайм Один равно единице? (is1(dTime1) = 1 )
31 май 17, 16:10    [20528016]     Ответить | Цитировать Сообщить модератору
 Re: Задачка однако  [new]
Massa52
Member

Откуда:
Сообщений: 373
Cammomile,
is1(dTime1) = 1 - прошу прощения - я не знал как это записать и придумал, получается, отсебятину.
Мне надо было выделить строку с is1 = 1
Когда поле is1 = 1 - ему соответствует значение времени в этой же строке.
31 май 17, 16:22    [20528077]     Ответить | Цитировать Сообщить модератору
 Re: Задачка однако  [new]
Massa52
Member

Откуда:
Сообщений: 373
iiyama,
Спасибо - получилось через OUTER APPLY.
Задачка жизненная. Я составил два фрагмента наблюдения за объектами - предположим мышами, за мышью '111' и '112'
где is1 = 1 соответствует началу наблюдения, is2 = 1 - мышь под наблюдением, is3 = 1 - наблюдение завершилось.
Должно выполняться правило мониторинга - пока наблюдение не завершится - нельзя начинать новое.
IF OBJECT_ID(N'tempdb..#t', N'U') IS NOT NULL   
   DROP TABLE #t;  

CREATE TABLE #t(
	[id] [int] NOT NULL,
	[c_Id] [char](3) NOT NULL,
	[dTime] [smalldatetime] NOT NULL,
	[is1] [bit] NOT NULL,
	[is2] [bit] NOT NULL,
	[is3] [bit] NOT NULL);
                        
INSERT #t ([id], [c_Id], [dTime], [is1], [is2], [is3]) VALUES 
   (1, N'111', CAST(N'2017-03-09 08:17:00' AS SmallDateTime), 0, 0, 0)
  ,(2, N'111', CAST(N'2017-03-09 08:10:00' AS SmallDateTime), 0, 0, 0)
  ,(3, N'111', CAST(N'2017-03-09 08:01:00' AS SmallDateTime), 0, 1, 0)
  ,(4, N'111', CAST(N'2017-03-08 20:05:00' AS SmallDateTime), 1, 1, 0)
  ,(5, N'111', CAST(N'2017-03-08 17:47:00' AS SmallDateTime), 0, 0, 0)
  ,(6, N'111', CAST(N'2017-03-08 15:25:00' AS SmallDateTime), 0, 0, 1)
  ,(7, N'111', CAST(N'2017-03-08 12:38:00' AS SmallDateTime), 0, 1, 0)
  ,(8, N'111', CAST(N'2017-03-08 07:06:00' AS SmallDateTime), 0, 1, 0)
  ,(9, N'111', CAST(N'2017-03-07 08:12:00' AS SmallDateTime), 1, 1, 0)
 ,(10, N'111', CAST(N'2017-03-07 05:33:00' AS SmallDateTime), 0, 0, 0)

 ,(11, N'112', CAST(N'2017-02-09 08:17:00' AS SmallDateTime), 0, 1, 0)
 ,(12, N'112', CAST(N'2017-02-09 08:10:00' AS SmallDateTime), 0, 1, 0)
 ,(13, N'112', CAST(N'2017-02-09 08:01:00' AS SmallDateTime), 0, 1, 0)
 ,(14, N'112', CAST(N'2017-02-08 20:05:00' AS SmallDateTime), 1, 1, 0)
 ,(15, N'112', CAST(N'2017-02-08 17:47:00' AS SmallDateTime), 0, 0, 0)
 ,(16, N'112', CAST(N'2017-02-08 15:25:00' AS SmallDateTime), 0, 0, 0)
 ,(17, N'112', CAST(N'2017-02-08 12:38:00' AS SmallDateTime), 0, 1, 0)
 ,(18, N'112', CAST(N'2017-02-08 07:06:00' AS SmallDateTime), 0, 1, 0)
 ,(19, N'112', CAST(N'2017-02-07 08:12:00' AS SmallDateTime), 1, 1, 0)
 ,(20, N'112', CAST(N'2017-02-07 05:33:00' AS SmallDateTime), 0, 0, 0)
 ,(21, N'112', CAST(N'2017-01-08 17:47:00' AS SmallDateTime), 0, 0, 0)
 ,(22, N'112', CAST(N'2017-01-08 15:25:00' AS SmallDateTime), 0, 0, 1)
 ,(23, N'112', CAST(N'2017-01-08 12:38:00' AS SmallDateTime), 0, 0, 0)
 ,(24, N'112', CAST(N'2017-01-08 07:06:00' AS SmallDateTime), 0, 1, 0)
 ,(25, N'112', CAST(N'2017-01-07 08:12:00' AS SmallDateTime), 1, 1, 0)
 ,(26, N'112', CAST(N'2017-01-07 05:33:00' AS SmallDateTime), 0, 0, 0);

;WITH c AS
(
 SELECT t.*, A.is3 AS p
 FROM #t t OUTER APPLY 
       (SELECT TOP(1) t1.id, t1.is3 FROM #t t1 WHERE t.c_Id = t1.c_Id AND t.dTime < t1.dTime AND t1.is3 = 1 AND t.is1 = 1 ORDER BY t1.c_Id, t1.dTime DESC) AS A
WHERE is1 = 1 AND A.is3 is NULL 
)
SELECT c_Id, MAX(dTime) tTime, COUNT(*) AS nn 
FROM c
GROUP BY c_Id
HAVING COUNT(*) > 1
1 июн 17, 07:32    [20529494]     Ответить | Цитировать Сообщить модератору
 Re: Задачка однако  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Massa52,

автор
где is1 = 1 соответствует началу наблюдения, is2 = 1 - мышь под наблюдением, is3 = 1 - наблюдение завершилось.

а зачем для этого аж 3 поля?
1 июн 17, 08:59    [20529687]     Ответить | Цитировать Сообщить модератору
 Re: Задачка однако  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
WITH
t0 AS (
  SELECT
    [id],
    [c_id],
    [dTime],
    [is1] = CONVERT( INT, [is1] ),
    [is2] = CONVERT( INT, [is2] ),
    [is3] = CONVERT( INT, [is3] ),
    [rn] = ROW_NUMBER() 
           OVER (
             PARTITION BY
               [c_id]
             ORDER BY
               [dTime] )
         - ROW_NUMBER()
           OVER (
             PARTITION BY
               [c_id],
               [is1],
               [is2],
               [is3]
             ORDER BY
               [dTime] )
  FROM
    #t
),
t AS (
  SELECT
    [id] = MIN( [id] ),
    [c_id],
    [dTime] = MIN( [dTime] ),
    [is1],
    [is2],
    [is3]
  FROM
    t0
  GROUP BY
    [c_id],
    [is1],
    [is2],
    [is3],
    [rn]
)
SELECT
  t1.*,
  ss.*
FROM
  t t1
  OUTER APPLY (
    SELECT
      [is] = SUM( t2.[is2] ) - SUM( t2.[is3] )
    FROM
      t t2
    WHERE
          t2.[c_id] = t1.[c_id]
      AND t2.[dTime] < t1.[dTime]
  ) ss
WHERE
  t1.[is1] = 1 AND ss.[is] > 1
1 июн 17, 09:11    [20529718]     Ответить | Цитировать Сообщить модератору
 Re: Задачка однако  [new]
Massa52
Member

Откуда:
Сообщений: 373
TaPaK
Massa52,

автор
где is1 = 1 соответствует началу наблюдения, is2 = 1 - мышь под наблюдением, is3 = 1 - наблюдение завершилось.

а зачем для этого аж 3 поля?



Какие варианты? Одно поле - is1 * 100 + is2 * 10 + is3?

Спасибо большое
Руслан Дамирович
.
Завтра прогоню на реальных данных и сообщу.
1 июн 17, 10:00    [20529980]     Ответить | Цитировать Сообщить модератору
 Re: Задачка однако  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Massa52,
автор
Одно поле - is1 * 100 + is2 * 10 + is3?

эээ ну начиная от битового, заканчивая 1-2-3...
1 июн 17, 10:01    [20529992]     Ответить | Цитировать Сообщить модератору
 Re: Задачка однако  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
Пытаемся решить нереляционную задачу реляционными методами. На самом деле надо добавить атрибут с номером экперимента.
Все эти спрекуляции на тему зависимости порядка строк запрещены для отношений.
1 июн 17, 11:04    [20530368]     Ответить | Цитировать Сообщить модератору
 Re: Задачка однако  [new]
Massa52
Member

Откуда:
Сообщений: 373
TaPaK,
Понятно.

Руслан Дамирович,
Прогнал на реальной базе - работает. Спасибо еще раз.
2 июн 17, 06:43    [20533466]     Ответить | Цитировать Сообщить модератору
 Re: Задачка однако  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Massa52
Руслан Дамирович,
Прогнал на реальной базе - работает. Спасибо еще раз.

Что работает, и ежу понятно. Результаты-то правильные дает? :)
2 июн 17, 09:01    [20533584]     Ответить | Цитировать Сообщить модератору
 Re: Задачка однако  [new]
Massa52
Member

Откуда:
Сообщений: 373
Руслан Дамирович,
То что надо.
2 июн 17, 09:03    [20533586]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить