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

Откуда:
Сообщений: 16
Здравствуйте.
есть выборка в виде

id1id2in_movetime_stime_end
19152578016.01.2020 7:54:4016.01.2020 7:54:40
19152578016.01.2020 7:54:4016.01.2020 7:55:10
19152578016.01.2020 7:55:1016.01.2020 7:55:20
19152578016.01.2020 7:55:2016.01.2020 7:55:50
19152578116.01.2020 18:53:0016.01.2020 18:54:00
19152578016.01.2020 18:54:0016.01.2020 19:00:30
19152578016.01.2020 19:00:3016.01.2020 19:00:50
19152578016.01.2020 19:00:5016.01.2020 19:01:10
19152578016.01.2020 19:01:1016.01.2020 19:01:30


Нужно как то получить отдельные номера для красной и синей групп.
То есть что то типа
id1id2in_movetime_stime_endgroup
19152578016.01.2020 7:54:4016.01.2020 7:54:401
19152578016.01.2020 7:54:4016.01.2020 7:55:101
19152578016.01.2020 7:55:1016.01.2020 7:55:201
19152578016.01.2020 7:55:2016.01.2020 7:55:501
19152578116.01.2020 18:53:0016.01.2020 18:54:00не важно
19152578016.01.2020 18:54:0016.01.2020 19:00:302
19152578016.01.2020 19:00:3016.01.2020 19:00:502
19152578016.01.2020 19:00:5016.01.2020 19:01:102
19152578016.01.2020 19:01:1016.01.2020 19:01:302

пар id1,id2 очень много, отдельная нумерация нужна именно для каждой пары.

Или возможно есть другой способ получить финальный результат в виде
id1id2in_movetime_stime_end
19152578016.01.2020 7:54:4016.01.2020 7:55:50
19152578016.01.2020 18:54:0016.01.2020 19:01:30


уже всю голову сломал)
Помогите кто может, пожалуйста)
Спасибо.

Сообщение было отредактировано: 14 фев 20, 21:20
14 фев 20, 21:14    [22080373]     Ответить | Цитировать Сообщить модератору
 Re: Нумерация групп, разделенных строкой  [new]
invm
Member

Откуда: Москва
Сообщений: 9079
with t as
(
 select
  id1, id2, in_move, time_s, time_end,
  sum(in_move) over (partition by id1, id2 order by time_s, time_end) as g
 from
  ...
)
select
 id1, id2, in_move, min(time_s), max(time_end),
from
 t
where
 in_move = 0
group by
 g, id1, id2, in_move;
14 фев 20, 21:33    [22080377]     Ответить | Цитировать Сообщить модератору
 Re: Нумерация групп, разделенных строкой  [new]
dimonovych
Member

Откуда:
Сообщений: 16
Спасибо, но не то.
Таким образом в столбце g значение возрастает для каждой строчки и каждая строка получается отдельной группой.
17 фев 20, 11:10    [22081087]     Ответить | Цитировать Сообщить модератору
 Re: Нумерация групп, разделенных строкой  [new]
invm
Member

Откуда: Москва
Сообщений: 9079
dimonovych
Спасибо, но не то.
Да не за что.
Пока будете выполнять запросы в уме, а не на сервере, вам все будет "не то".
17 фев 20, 11:22    [22081103]     Ответить | Цитировать Сообщить модератору
 Re: Нумерация групп, разделенных строкой  [new]
dimonovych
Member

Откуда:
Сообщений: 16
Вот скриншот.
В реальных данных названия столбцов другие немного и in_move инвертировано - в движении -1, стоянка 0, но суть та же.

К сообщению приложен файл. Размер - 117Kb
17 фев 20, 13:11    [22081269]     Ответить | Цитировать Сообщить модератору
 Re: Нумерация групп, разделенных строкой  [new]
nullin
Member

Откуда: pullin
Сообщений: 113
dimonovych, так разинвертируете.
17 фев 20, 13:17    [22081281]     Ответить | Цитировать Сообщить модератору
 Re: Нумерация групп, разделенных строкой  [new]
invm
Member

Откуда: Москва
Сообщений: 9079
dimonovych
В реальных данных названия столбцов другие немного и in_move инвертировано - в движении -1, стоянка 0, но суть та же.

Картинка с другого сайта.
Мда... Даете неверные исходные данные, а потом удивляетесь, что запрос неправильно работает.
Если суть та же, инвертируйте в запросе так, чтобы стоянка - что угодно, движение = 0 и будет вам счастье.
17 фев 20, 13:17    [22081282]     Ответить | Цитировать Сообщить модератору
 Re: Нумерация групп, разделенных строкой  [new]
dimonovych
Member

Откуда:
Сообщений: 16
Скриншот уже для "разинвертированного" запроса.
может я и не шарю в sql но не совсем тупой уже.
17 фев 20, 17:10    [22081532]     Ответить | Цитировать Сообщить модератору
 Re: Нумерация групп, разделенных строкой  [new]
court
Member

Откуда:
Сообщений: 1932
dimonovych
Скриншот уже для "разинвертированного" запроса.
нет
вам же всё ясно сказали
invm
чтобы стоянка - что угодно, движение = 0 и будет вам счастье.

-- sum(case when in_move = 1 then 0 else -1 end)over(...
sum(in_move-1)over(...
17 фев 20, 17:26    [22081552]     Ответить | Цитировать Сообщить модератору
 Re: Нумерация групп, разделенных строкой  [new]
entrypoint
Member

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

SET DATEFORMAT DMY;
WITH a (id1, id2, in_move, time_s,	time_end)
AS (
SELECT 1915,	2578,	0,	CONVERT(DATETIME, '16.01.2020 7:54:40'),	CONVERT(DATETIME, '16.01.2020 7:54:40')  UNION ALL  
SELECT 1915,	2578,	0,	'16.01.2020 7:54:40',	'16.01.2020 7:55:10'  UNION ALL
SELECT 1915,	2578,	0,	'16.01.2020 7:55:10',	'16.01.2020 7:55:20'  UNION ALL
SELECT 1915,	2578,	0,	'16.01.2020 7:55:20',	'16.01.2020 7:55:50'  UNION ALL
SELECT 1915,	2578,	1,	'16.01.2020 18:53:00',	'16.01.2020 18:54:00' UNION ALL
SELECT 1915,	2578,	0,	'16.01.2020 18:54:00',	'16.01.2020 19:00:30' UNION ALL
SELECT 1915,	2578,	0,	'16.01.2020 19:00:30',	'16.01.2020 19:00:50' UNION ALL
SELECT 1915,	2578,	0,	'16.01.2020 19:00:50',	'16.01.2020 19:01:10' UNION ALL
SELECT 1915,	2578,	0,	'16.01.2020 19:01:10',	'16.01.2020 19:01:30'
), 
c AS ( 
SELECT 
       a.id1
     , a.id2
     , a.time_s
     , a.time_end
	, RANK() OVER (ORDER BY z.time_s) AS [group]
FROM 
     a
OUTER APPLY (SELECT TOP(1) b.time_s FROM a AS b WHERE b.time_s > a.time_s AND b.in_move = 1 ORDER BY b.time_s ASC) AS z
WHERE a.in_move = 0
)
SELECT 
       c.id1
     , c.id2
     , MIN(c.time_s) AS   time_s
     , MAX(c.time_end) AS time_end
FROM 
     c
GROUP BY 
         c.id1
       , c.id2
       , c.[group]
       ORDER BY 
                time_s
              , time_end;
 
17 фев 20, 18:02    [22081591]     Ответить | Цитировать Сообщить модератору
 Re: Нумерация групп, разделенных строкой  [new]
nullin
Member

Откуда: pullin
Сообщений: 113
entrypoint, в outer apply() явно не хватает условия о равенстве айдишников.
17 фев 20, 21:00    [22081694]     Ответить | Цитировать Сообщить модератору
 Re: Нумерация групп, разделенных строкой  [new]
entrypoint
Member

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

SET DATEFORMAT DMY;
WITH a (id1, id2, in_move, time_s,	time_end)
AS (
SELECT 1915,	2578,	0,	CONVERT(DATETIME, '16.01.2020 7:54:40'),	CONVERT(DATETIME, '16.01.2020 7:54:40')  UNION ALL  
SELECT 1915,	2578,	0,	'16.01.2020 7:54:40',	'16.01.2020 7:55:10'  UNION ALL
SELECT 1915,	2578,	0,	'16.01.2020 7:55:10',	'16.01.2020 7:55:20'  UNION ALL
SELECT 1915,	2578,	0,	'16.01.2020 7:55:20',	'16.01.2020 7:55:50'  UNION ALL
SELECT 1915,	2578,	1,	'16.01.2020 18:53:00',	'16.01.2020 18:54:00' UNION ALL
SELECT 1915,	2578,	0,	'16.01.2020 18:54:00',	'16.01.2020 19:00:30' UNION ALL
SELECT 1915,	2578,	0,	'16.01.2020 19:00:30',	'16.01.2020 19:00:50' UNION ALL
SELECT 1915,	2578,	0,	'16.01.2020 19:00:50',	'16.01.2020 19:01:10' UNION ALL
SELECT 1915,	2578,	0,	'16.01.2020 19:01:10',	'16.01.2020 19:01:30'
), 
c AS ( 
SELECT 
       a.id1
     , a.id2
     , a.time_s
     , a.time_end
	, RANK() OVER (ORDER BY z.time_s) AS [group]
FROM 
     a
OUTER APPLY (
    SELECT TOP (1) 
		 b.time_s
    FROM 
	    a AS b
    WHERE a.id1 = b.id1
		AND a.id2 = b.id2
		AND b.time_s > a.time_s
		AND b.in_move = 1
		 ORDER BY 
				b.time_s ASC) AS z
WHERE a.in_move = 0
)
SELECT 
       c.id1
     , c.id2
     , MIN(c.time_s) AS   time_s
     , MAX(c.time_end) AS time_end
FROM 
     c
GROUP BY 
         c.id1
       , c.id2
       , c.[group]
       ORDER BY 
                time_s
              , time_end;
18 фев 20, 09:50    [22081888]     Ответить | Цитировать Сообщить модератору
 Re: Нумерация групп, разделенных строкой  [new]
nullin
Member

Откуда: pullin
Сообщений: 113
entrypoint, вроде все здорово, однако: почему rank() без partition по тем же id, когда группировка с ними идет далее по порядку?
Для наглядности, набор для требуемого теста не могу подобрать.
18 фев 20, 12:07    [22082050]     Ответить | Цитировать Сообщить модератору
 Re: Нумерация групп, разделенных строкой  [new]
entrypoint
Member

Откуда:
Сообщений: 227
nullin
entrypoint, вроде все здорово, однако: почему rank() без partition по тем же id, когда группировка с ними идет далее по порядку?
Для наглядности, набор для требуемого теста не могу подобрать.

потому что мне нужно получить номер группы по всему списку, а не номер группы в пределах id

-- Тестовые данные 
DECLARE @t AS TABLE (id INT NOT NULL)
INSERT INTO @t(id)
SELECT 10 UNION ALL 
SELECT 10 UNION ALL 
SELECT 11 UNION ALL 
SELECT 11
  
-- Нужный результат
SELECT a.id, RANK() OVER (ORDER BY a.id) AS [group] FROM @t AS a;
-- Бессмысленный результат
SELECT a.id, RANK() OVER (PARTITION BY a.id ORDER BY a.id) AS [group] FROM @t AS a
18 фев 20, 13:38    [22082161]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить