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

Откуда: Москва
Сообщений: 23
Господа, доброго времени суток!
Мне нужна ваша помощь!

задача:
есть 2 таблицы

1. Unit (ключевые поля)
id int (айди юнита)
ShortName VARCHAR(10)

и

2. Position (ключевые поля)
MngUnit int not NULL
TimeStamp datatime
IPO int (два значения 0 или 1)

у каждого юнита есть трекинг по GPS соотв долгота и широта и
время контрольной точки (поле TimeStamp), так же есть статус онлайн или оффлайн (IPO)
нужно назначить курсор с перебором ВСЕХ айди начиная с 1 и по FEENCH=0 таким образом, чтобы:
1. найти первую строку(первое вхождение) где юнит оффлайн (IPO = 0) и назначить переменной @TP(--TimePast) значение времени TimeStamp.
2. далее если
а. следующая строка оффлайн (IPO = 0), то вычленить разницу времени таким образом
(@TR(--TimeReal) = TimeStamp) - @TP = @TS (--TimeSum)
б. следующая строка онлайн (IPO = 1) И уравнение ИСТИНА ((@TR(--TimeReal) = TimeStamp) - @TP > 15 (--seconds)) ТО ((@TR(--TimeReal) = TimeStamp) - @TP = @TS), ЕСЛИ разница менее 15 секунд, то (@TR = @TP ) или просто перейти на сл строку безобновлений переменных.

когда кончатся строки по данному айди юнита, нужно увидеть суммарное время оффлайна по выше написанным критериям.

и соответственно внести их в таблицу TimeOfflineUnit
поле unit_id = u.ShortName и TimeOfflane = @TS (для каждого юнита свое значение)

при переходе на сл айди соотв переменные @TS = 0, @TP = поля TimeStamp, первой строке с критерием поля IPO = 0

у меня пока (5-е сутки) получилось нечто не очень понятное...
но все же.

переменные:
@TP -- Прошлое время, поле TimeStamp, пред-идущая строка этого айди
@TR -- Реальное время, поле TimeStamp, текущая строка этого айди
@TS -- Суммарное время, которое пойдет в итоговую таблицу вывода.
@UID -- Айди юнита на текущий период поиска (от 1 и до n..)

должны быть еще переменные, но какие точно пока не могу понять

в итога должно получиться что то вроде прикрепил файл. 1 и 2 представлены, 3 итог.

-----------------------------------------------------------------------------

DECLARE @IDch int = 2104 --для теста взял конкретный айди

SELECT u.ShortName as Unit_ID, p.TimeStamp, p.IPO
from [dbo].[Unit] u INNER JOIN [dbo].[Position] p
ON p.MngUnit = u.id
WHERE u.id = @IDch

-----------------------------------------------------------------------------

в начале должно быть что то вида
CREATE TABLE TimeOfflineUnit
id int
Unit_Id VARCHAR (10)
TimeOffline time(2)
primary key ('id')

в конце дроп.
БУДУ ПРИМНОЖЕСТВЕННО благодарен за помощь в разборке или написания курсора.
8 янв 18, 21:04    [21088212]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
peskauskas
Member

Откуда: Москва
Сообщений: 23
Картинка с другого сайта.

К сообщению приложен файл. Размер - 72Kb
8 янв 18, 21:46    [21088297]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
iii2
Member

Откуда:
Сообщений: 202
Курсор то тут нафига? чтобы дольше работало?
Всё двумя запросами решается (т.к. 2 таблицы апдейтить).
9 янв 18, 08:17    [21088702]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
peskauskas
Member

Откуда: Москва
Сообщений: 23
iii2,

Будьте добры, подсказать, как это сделать правильно !?
9 янв 18, 12:18    [21089542]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
iii2
Member

Откуда:
Сообщений: 202
Какая версия сервера?
9 янв 18, 14:54    [21090220]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
iiyama
Member

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

погуглите => задача поиска островов
9 янв 18, 15:47    [21090490]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
+ Просьба убрать от экранов детей, беременных и кормящих женщин, а также психически нестабильных личностей

DECLARE @data TABLE ( [id] INT, [time] DATETIME2(0), [ipo] TINYINT )
INSERT INTO @data VALUES 
( 2344, '2017-05-01T12:10:08', 1 ),
( 2464, '2017-05-01T12:10:11', 1 ),
( 2304, '2017-05-01T12:10:13', 0 ),
( 2384, '2017-05-01T12:10:16', 1 ),
( 2344, '2017-05-01T12:10:18', 0 ),
( 2344, '2017-05-01T12:10:19', 0 ),
( 2344, '2017-05-01T12:10:20', 1 ),
( 2224, '2017-05-01T12:10:21', 0 )
;
/*Острова. Детская задачка по определению границы изменения значений*/
WITH
t0 AS (
  /* Выполняем свертку значений */
  /* Марлезонский балет. Часть первая */
  SELECT
    *,
    [gr] = ROW_NUMBER() OVER ( PARTITION BY [id] ORDER BY [time] )
         - ROW_NUMBER() OVER ( PARTITION BY [id], [ipo] ORDER BY [time] )
  FROM
    @data
),
t1 AS (
  /* Марлезонский балет. Часть вторая */
  SELECT
    [id],
    [time] = MIN( [time] ),
    [ipo]
  FROM
    t0
  GROUP BY
    [id],
    [ipo],
    [gr]
),
t2 AS (
  /* Ранжируем свертку по дате */
  /* Все это не нужно, если есть LAG/LEAD, но их у меня нет */
  SELECT
    [id],
    [time],
    [ipo],
    [rn] = ROW_NUMBER() OVER ( PARTITION BY [id] ORDER BY [time] )
  FROM
    t1
),
t3 AS (
  /* Здесь мы считаем, что первое считанное значение распространяется на весь день с его начала */
  SELECT
    [id],
    [time],
    [ipo],
    [rn] = [rn] + 1
  FROM
    t2
  UNION ALL
  SELECT
    [id],
    [time] = CONVERT( DATE, [time] ),
    [ipo],
    [rn] = 1
  FROM
    t2
  WHERE
    [rn] = 1
),
t4 AS (
  /* Здесь мы считаем, что последнее считанное значение распространяется на весь день до его окончания */
  SELECT
    c.[id],
    c.[ipo],
    [duration] = SUM( DATEDIFF( S, c.[time], ISNULL( n.[time], DATEADD( DAY, 1, CONVERT( DATE, c.[time] ) ) ) ) )
  FROM
    t3 c
    LEFT JOIN t3 n ON (
          n.[id] = c.[id]
      AND n.[rn] = c.[rn] + 1 )
  GROUP BY
    c.[id],
    c.[ipo]
)
/* Отбираем записи, у которых был офф-лайн */
SELECT
  *
FROM
  t4
WHERE
  [ipo] = 0
;

9 янв 18, 18:43    [21091277]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
peskauskas
Member

Откуда: Москва
Сообщений: 23
Руслан Дамирович, ОГОНЬ!
а теперь как это сделать с курсором ибо юнитов порядка 10к+ и временных точек тоже некислое количество, причем у каждого юнита...
9 янв 18, 19:21    [21091329]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
peskauskas
Member

Откуда: Москва
Сообщений: 23
iii2,
14,0 нужно сделать салюшн именно в трансакте
9 янв 18, 19:22    [21091331]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
iiyama
Member

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

если 14й, то можете еще прикрутить Lead/Lag, а вот зачем Вам курсор до меня не доходит, чтобы дольше обрабатывалось или начальник сказал?
9 янв 18, 20:12    [21091416]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
peskauskas
Member

Откуда: Москва
Сообщений: 23
peskauskas,
CREATE table test.dbo.test
(
id int,
name Varchar (10),
time time(2),
line int
)

--Создаем переменные
DECLARE @id int =1
DECLARE @MngUnit varchar(10)
DECLARE @IPO int
DECLARE @time int
DECLARE @timePast time (2)

--Создадим курсор
DECLARE cur1 CURSOR FOR 
SELECT top 10 id, MngUnit, IPO FROM test.dbo.Position

--Откроем курсор
OPEN cur1

--Выборка данных первой строки
FETCH NEXT FROM cur1 INTO @id, @MngUnit, @ipo

--Пока есть данные в курсоре - выборка циклом
WHILE @@FETCH_STATUS = 0
BEGIN 
    SELECT @id as ID, @MngUnit as UNIT, @ipo as 'TIME OFF'
        -------ВОТ ТУТ ДОЛЖЕН БЫТЬ ЕЩЕ ОДИН КУРСОР, КОТОРЫЕ ПЕРЕБИРАЕТ КРИТЕРИИ----
	--Выборка следующей строки
    FETCH NEXT FROM cur1 INTO @id, @MngUnit, @ipo
END

--Закрываем курсор
CLOSE cur1
--Уничтожаем курсор
DEALLOCATE cur1

drop table test.dbo.test



Примерный набросок....

----ЕСЛИ ПЕРЕМЕННАЯ UID ЕСТЬ В СПИСКЕ) , И ((есть разница между реальным и прошлым времинем более 15 секунд ) И ( IPO = 1))  ИЛИ (ipo = 0)
IF (( @UID = (SELECT id FROM Unit WHERE u.id = @UID ) AND ( @TR - @TP >= '00:00:15' ) AND ( p.IPO = 1 )) OR ( @UID = u.id ) AND ( p.IPO = 0 ) 
	BEGIN 	---- то! прибавить @TS разницу @TR - @TP) 
		SET @TS = @TS + (p.TimeStamp - @TP)
	END
ELSE ---- ЕСЛИ УСЛОВИЯ НЕ ВЫПОЛНЯЮТСЯ, ТО = @UID + 1 (соотв, сл ряд) 
	SET @UID = @UID + 1
9 янв 18, 21:19    [21091590]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36972
Какой прекрасный говнокод! Давайте еще!
9 янв 18, 21:24    [21091605]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
peskauskas
Member

Откуда: Москва
Сообщений: 23
Гавриленко Сергей Алексеевич,

Если не можете помочь, зачем показывать всем, что вы этого не можете ?
9 янв 18, 22:18    [21091714]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
peskauskas
Member

Откуда: Москва
Сообщений: 23
peskauskas,

Помогите это правильно записать пожалуйста.
9 янв 18, 23:38    [21091840]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
peskauskas
Member

Откуда: Москва
Сообщений: 23
iiyama,

Готов выслушать ваше решение, начальник упомянул про курсор причем 2-го уровня...
у меня в пятницу тестирование и скажем так, мне дали время и пример для того что бы подготовиться.
Я могу все это написать в Экселе, в котором я разбираюсь куда лучше, но задача сделать решение в трансакте и не факт что курсором.
В целом, от меня будут ждать результат, а как я его достигну это мое дело...
9 янв 18, 23:42    [21091844]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36972
peskauskas
Гавриленко Сергей Алексеевич,

Если не можете помочь, зачем показывать всем, что вы этого не можете ?
На "слабо" своих корешей в подъезде будете разводить. Прощайте.

Сообщение было отредактировано: 10 янв 18, 01:31
10 янв 18, 00:46    [21091890]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
uaggster
Member

Откуда:
Сообщений: 827
peskauskas
Руслан Дамирович, ОГОНЬ!
а теперь как это сделать с курсором ибо юнитов порядка 10к+ и временных точек тоже некислое количество, причем у каждого юнита...

Так оно и для 10к юнитов будет работать, не снижая производительности.
Курсор будет работать примерно в 1000 раз медленнее (это не фигура речи).

"Если ты знаешь синтаксис курсора - это уже повод относиться к тебе с подозрением" (С) Олонский, если мне память не изменяет.
10 янв 18, 09:29    [21092241]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
iiyama
Member

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

Ну если начальник сказал, делайте как он сказал, на то он и началнеГ. Но его хотелка делается запросом без всяких курсоров. См. пример Руслан Дамирович

пример Б. Вложенный курсор
10 янв 18, 11:05    [21092634]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
peskauskas
Member

Откуда: Москва
Сообщений: 23
uaggster,

Я его и не знаю, точнее понять не могу до конца.
он мне не впился никуда, но такая задача тестирования.
=(
у кого можно проконсультироваться в месенджере по синтаксису ? 89250437967
10 янв 18, 11:10    [21092652]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
peskauskas
Member

Откуда: Москва
Сообщений: 23
peskauskas,

считает, но не правильно... =(

сократил количество данных и подписал для наглядности

INSERT INTO @data VALUES
( 2224, '2017-05-01T12:10:03', 0 ), -- время 1
( 2224, '2017-05-01T12:10:19', 0 ), -- время 2 отличается на 16 секунд
( 2224, '2017-05-01T12:10:32', 1 ), -- онлайн, отличается от пред идущего на 23 секунды
( 2224, '2017-05-01T12:10:36', 1 ) -- итого должно показать итоговое время оффлай 16+23 = 39 секунд
;
/*Острова. Детская задачка по определению границы изменения значений*/
WITH

считает
ID IPO DURATION
2224 0 43832
10 янв 18, 12:04    [21092909]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
peskauskas
peskauskas,
считает, но не правильно... =(
сократил количество данных и подписал для наглядности

Дружище, научись читать комментарии; осознай, что именно делает код; модифицируй под свои нужды.
Подсказка: оно считает, что если 0 был первым, то он был на начало дня, он считает, что если последней была 1, то 1 была до конца дня.
10 янв 18, 12:09    [21092929]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
peskauskas
Member

Откуда: Москва
Сообщений: 23
Руслан Дамирович,

Пардон !!! слеп =( бьюсб несколько дней, мозги в кашу =(
10 янв 18, 12:48    [21093054]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
peskauskas
Member

Откуда: Москва
Сообщений: 23
peskauskas,

Кто может мне расжевать курсор ?! плз.
=(
10 янв 18, 13:51    [21093295]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
peskauskas
peskauskas,

Кто может мне расжевать курсор ?! плз.
=(
Так какие вопросы, конкретно?
Вы же показали тут код с курсором, там всё нормально.
10 янв 18, 13:55    [21093314]     Ответить | Цитировать Сообщить модератору
 Re: Transact-SQL @CURSOR  [new]
peskauskas
Member

Откуда: Москва
Сообщений: 23
alexeyvg,

Да, но!
в центре есть камент
-------ВОТ ТУТ ДОЛЖЕН БЫТЬ ЕЩЕ ОДИН КУРСОР, КОТОРЫЕ ПЕРЕБИРАЕТ КРИТЕРИИ----
в место которого теоретически должен быть

----ЕСЛИ ПЕРЕМЕННАЯ UID ЕСТЬ В СПИСКЕ) , И ((есть разница между реальным и прошлым времинем более 15 секунд ) И ( IPO = 1)) ИЛИ (ipo = 0)
IF (( @UID = (SELECT id FROM Unit WHERE u.id = @UID ) AND ( @TR - @TP >= '00:00:15' ) AND ( p.IPO = 1 )) OR ( @UID = u.id ) AND ( p.IPO = 0 )
BEGIN ---- то! прибавить @TS разницу @TR - @TP)
SET @TS = @TS + (p.TimeStamp - @TP)
END
ELSE ---- ЕСЛИ УСЛОВИЯ НЕ ВЫПОЛНЯЮТСЯ, ТО = @UID + 1 (соотв, сл ряд)
SET @UID = @UID + 1

но как правильно его записать я пока не понял =(
10 янв 18, 15:28    [21093769]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить