Transact-SQL @CURSOR / Microsoft SQL Server

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Transact-SQL @CURSOR

36 сообщений из 36, показаны все 2 страниц

все

Transact-SQL @CURSOR

#39580689

peskauskas

Гость

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

задача:
есть 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')

в конце дроп.
БУДУ ПРИМНОЖЕСТВЕННО благодарен за помощь в разборке или написания курсора.

...

Рейтинг:

0 / 0

08.01.2018, 21:04

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39580708

peskauskas

Гость

...

Рейтинг:

0 / 0

08.01.2018, 21:46

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39580809

iii2

Гость

Курсор то тут нафига? чтобы дольше работало?
Всё двумя запросами решается (т.к. 2 таблицы апдейтить).

...

Рейтинг:

0 / 0

09.01.2018, 08:17

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39580955

peskauskas

Гость

iii2,

Будьте добры, подсказать, как это сделать правильно !?

...

Рейтинг:

0 / 0

09.01.2018, 12:18

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39581090

iii2

Гость

Какая версия сервера?

...

Рейтинг:

0 / 0

09.01.2018, 14:54

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39581125

iiyama

Участник

Сообщения: 628

Рейтинг: 0 / 0

peskauskas,

погуглите => задача поиска островов

...

Рейтинг:

0 / 0

09.01.2018, 15:47

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39581249

Руслан Дамирович

Участник

Откуда: Резиновая нерезиновая

Сообщения: 922

Рейтинг: 0 / 0

Просьба убрать от экранов детей, беременных и кормящих женщин, а также психически нестабильных личностей

Код: 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.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.

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
;

...

Рейтинг:

0 / 0

09.01.2018, 18:43

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39581258

peskauskas

Гость

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

...

Рейтинг:

0 / 0

09.01.2018, 19:21

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39581260

peskauskas

Гость

iii2,
14,0 нужно сделать салюшн именно в трансакте

...

Рейтинг:

0 / 0

09.01.2018, 19:22

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39581287

iiyama

Участник

Сообщения: 628

Рейтинг: 0 / 0

peskauskas,

если 14й, то можете еще прикрутить Lead/Lag, а вот зачем Вам курсор до меня не доходит, чтобы дольше обрабатывалось или начальник сказал?

...

Рейтинг:

0 / 0

09.01.2018, 20:12

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39581310

peskauskas

Гость

peskauskas,

Код: 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.

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

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

Код: sql

1.
2.
3.
4.
5.
6.
7.

----ЕСЛИ ПЕРЕМЕННАЯ 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

...

Рейтинг:

0 / 0

09.01.2018, 21:19

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39581314

Гавриленко Сергей Алексеевич

Участник

Откуда: Moscow

Сообщения: 37 171

Рейтинг: 0 / 0

Какой прекрасный говнокод! Давайте еще!

...

Рейтинг:

0 / 0

09.01.2018, 21:24

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39581339

peskauskas

Гость

Гавриленко Сергей Алексеевич,

Если не можете помочь, зачем показывать всем, что вы этого не можете ?

...

Рейтинг:

0 / 0

09.01.2018, 22:18

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39581367

peskauskas

Гость

peskauskas,

Помогите это правильно записать пожалуйста.

...

Рейтинг:

0 / 0

09.01.2018, 23:38

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39581370

peskauskas

Гость

iiyama,

Готов выслушать ваше решение, начальник упомянул про курсор причем 2-го уровня...
у меня в пятницу тестирование и скажем так, мне дали время и пример для того что бы подготовиться.
Я могу все это написать в Экселе, в котором я разбираюсь куда лучше, но задача сделать решение в трансакте и не факт что курсором.
В целом, от меня будут ждать результат, а как я его достигну это мое дело...

...

Рейтинг:

0 / 0

09.01.2018, 23:42

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39581385

Гавриленко Сергей Алексеевич

Участник

Откуда: Moscow

Сообщения: 37 171

Рейтинг: 0 / 0

peskauskasГавриленко Сергей Алексеевич,

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

...

Рейтинг:

0 / 0

10.01.2018, 00:46

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39581482

uaggster

Гость

peskauskasРуслан Дамирович, ОГОНЬ!
а теперь как это сделать с курсором ибо юнитов порядка 10к+ и временных точек тоже некислое количество, причем у каждого юнита...
Так оно и для 10к юнитов будет работать, не снижая производительности.
Курсор будет работать примерно в 1000 раз медленнее (это не фигура речи).

"Если ты знаешь синтаксис курсора - это уже повод относиться к тебе с подозрением" (С) Олонский, если мне память не изменяет.

...

Рейтинг:

0 / 0

10.01.2018, 09:29

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39581567

iiyama

Участник

Сообщения: 628

Рейтинг: 0 / 0

peskauskas,

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

пример Б. Вложенный курсор

...

Рейтинг:

0 / 0

10.01.2018, 11:05

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39581572

peskauskas

Гость

uaggster,

Я его и не знаю, точнее понять не могу до конца.
он мне не впился никуда, но такая задача тестирования.
=(
у кого можно проконсультироваться в месенджере по синтаксису ? 89250437967

...

Рейтинг:

0 / 0

10.01.2018, 11:10

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39581630

peskauskas

Гость

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

...

Рейтинг:

0 / 0

10.01.2018, 12:04

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39581639

Руслан Дамирович

Участник

Откуда: Резиновая нерезиновая

Сообщения: 922

Рейтинг: 0 / 0

peskauskaspeskauskas,
считает, но не правильно... =(
сократил количество данных и подписал для наглядности

Дружище, научись читать комментарии; осознай, что именно делает код; модифицируй под свои нужды.
Подсказка: оно считает, что если 0 был первым, то он был на начало дня, он считает, что если последней была 1, то 1 была до конца дня.

...

Рейтинг:

0 / 0

10.01.2018, 12:09

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39581670

peskauskas

Гость

Руслан Дамирович,

Пардон !!! слеп =( бьюсб несколько дней, мозги в кашу =(

...

Рейтинг:

0 / 0

10.01.2018, 12:48

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39581713

peskauskas

Гость

peskauskas,

Кто может мне расжевать курсор ?! плз.
=(

...

Рейтинг:

0 / 0

10.01.2018, 13:51

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39581717

alexeyvg

Участник

Откуда: Moscow

Сообщения: 26 926

Рейтинг: 0 / 0

peskauskaspeskauskas,

Кто может мне расжевать курсор ?! плз.
=(Так какие вопросы, конкретно?
Вы же показали тут код с курсором, там всё нормально.

...

Рейтинг:

0 / 0

10.01.2018, 13:55

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39581804

peskauskas

Гость

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

но как правильно его записать я пока не понял =(

...

Рейтинг:

0 / 0

10.01.2018, 15:28

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39581828

peskauskas

Гость

alexeyvgpeskauskaspeskauskas,

Кто может мне расжевать курсор ?! плз.
=(Так какие вопросы, конкретно?
Вы же показали тут код с курсором, там всё нормально.

как присвоить значение поля 3 переменной "Х"
SET @3 = (SELECT поле3 FROM TABLE1 WHERE ... )
и тут начинается сложность.
изначально есть рандомная таблица, которая выровнена по времени мой алгоритм заключается в том, что:

DECLARE @UID =1

0. переменная @TS = 0
1. находим 1 совпадение (если оно есть) по ID_unit = @UID, и IPO = 0, далее присваиваем переменной @TP значение поля TimeStamp (2001/03/01 10:00:01.02230 -- к примеру).
2. далее курсор идет по строкам и смотрит на ID_Unit пока не найдет cл. совпадение.
2.1. Если IPO = 1 и @TP - (значение поля TimeStamp) менее 15 секунд , ТО, идем по списку дальше.
2.2. Если IPO = 0, то присвоить переменной @TS = @TP - (значение поля TimeStamp) и идем по списку дальше.
2.3. Если совпадений больше нет, записываем значения в таблицу TABLE_S (создается в начале и дропается в конце):
2.3.1.INSER INTO TABLE_S (Unit_ID (@UID) , OfflineTime (@TS (Формат в секундах)))
3. переменная @UID = @UID + 1
3.1 Цикл с пункта 0.

Если вкратце...

...

Рейтинг:

0 / 0

10.01.2018, 15:48

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39582122

alexeyvg

Участник

Откуда: Moscow

Сообщения: 26 926

Рейтинг: 0 / 0

peskauskasи тут начинается сложность.Это сложность логики вашего кода, которая неразрывно связана с бизнес-логикой, и с вашим пониманием её.
Как тут вам могут помочь???

Если... Совпадение... IPO = 1... То... @TP=... Несовпадение...
Мы в этом не разберёмся, да и никто не захочет, потому что это не вопрос для форума по MSSQL

Возьмите листы бумаги, рисуйте и пишите алгоритмы, разбирайтесь, как то так...

...

Рейтинг:

0 / 0

10.01.2018, 22:58

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39582132

peskauskas

Гость

alexeyvgpeskauskasи тут начинается сложность.Это сложность логики вашего кода, которая неразрывно связана с бизнес-логикой, и с вашим пониманием её.
Как тут вам могут помочь???

Если... Совпадение... IPO = 1... То... @TP=... Несовпадение...
Мы в этом не разберёмся, да и никто не захочет, потому что это не вопрос для форума по MSSQL

Возьмите листы бумаги, рисуйте и пишите алгоритмы, разбирайтесь, как то так...
спрошу более узко.
например, у меня есть выборка, - 1 строка, есть поле datetime, как правильно написать присвоение ?

...

Рейтинг:

0 / 0

10.01.2018, 23:28

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39582139

alexeyvg

Участник

Откуда: Moscow

Сообщения: 26 926

Рейтинг: 0 / 0

peskauskasспрошу более узко.
например, у меня есть выборка, - 1 строка, есть поле datetime, как правильно написать присвоение ?"Есть выборка" - это что имеется в виду?
Есть запрос, возвращающий одну строку, в которой есть поле типа datetime?
Тогда:

Код: sql

1.
2.
3.
4.

declare @V datetime
select @V = somefield
from sometable
where ...

...

Рейтинг:

0 / 0

10.01.2018, 23:46

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39582143

peskauskas

Гость

alexeyvgpeskauskasспрошу более узко.
например, у меня есть выборка, - 1 строка, есть поле datetime, как правильно написать присвоение ?"Есть выборка" - это что имеется в виду?
Есть запрос, возвращающий одну строку, в которой есть поле типа datetime?
Тогда:

Код: sql

1.
2.
3.
4.

declare @V datetime
select @V = somefield
from sometable
where ...

Недопустимое имя столбца "timestam".

...

Рейтинг:

0 / 0

10.01.2018, 23:56

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39582145

peskauskas

Гость

peskauskas,

УРА!
СПАСИБО!!!!

...

Рейтинг:

0 / 0

10.01.2018, 23:59

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39582554

peskauskas

Гость

peskauskas,

Господа.
Простите меня.
я не могу решить эту задачу

Прошу Вас, помогите мне понять или курсор или задачу
завтра тестирование =(
бьюсь с 29 числа...

...

Рейтинг:

0 / 0

11.01.2018, 14:58

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39582556

peskauskas

Гость

Алгоритм

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

и

2. GPS (ключевые поля)
U_ID int not NULL (айди Юнита)
TimePoint datatime (Время точки GPS)
Line tinyint (два значения 0 или 1)

Так же есть Таблица сводная TOU (--TimeOfflineUnit)
в ней 2 колонки
name
Offline

у каждого юнита есть трекинг по GPS соотв долгота и широта и
время контрольной точки (поле TimePoint), так же есть статус онлайн или оффлайн (Line)
нужно назначить курсор с перебором ВСЕХ айди начиная с 1 и по N таким образом,
чтобы:
0. Присвоить => @TS = 0
1. найти первую строку (первое вхождение) где юнит оффлайн (Line = 0) и назначить переменной @TP(-- нолевая точка времени) поле TimePoint.
2. ЕСЛИ (ИЛИ):
а. следующая строка оффлайн (Line = 0)
б. следующая строка онлайн (Line = 1) И ((@TS (--Сумарное время простоя) = TimePoint - @TP ) > 15 (--более 15 секунд))
=> Присвоить переменный @TS = Timapoint - @TP
=> Присвоить переменный @TP = Timapoint
=> Сл строка соответствия по @ID
в. следующая строка онлайн (Line = 1) И ((@TS (--Сумарное время простоя) = TimePoint - @TP ) < 15 (--менее 15 секунд))
=> Присвоить переменный @TS = Timapoin
=> Сл строка соответствия по @ID

3. ЕСЛИ строка с @ID не найдена (Пред - идущая была последней или единственной или небыло вообще),
то, нужно добавить строку в таблицу TOU (--TimeOfflineUnit) @ID , @TS .(для каждого юнита свое значение)
при переходе на сл айди (@ID = @ID + 1),
соотв переменная пункты 0 и 1.

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

переменные:
DECLARE @ID int --Перебор ID
SET @ID = 1 --Старт от 1
DECLARE @TP datetime --Пер. времени отсчета простоя (1-я поз Line = 0)
Set @TP = NULL --Обнуление
DECLARE @TS datetime --Общее время простоя
SET @TS = null --Обнуление

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

...

Рейтинг:

0 / 0

11.01.2018, 14:59

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39582562

peskauskas

Гость

Код: xml

GPS-TABLE
ID 	Unit_Id	Time_Point	Line
1	5	01.05.2017 12:10:38	0
2	1	01.05.2017 12:10:42	1
3	5	01.05.2017 12:11:14	1
4	4	01.05.2017 12:11:39	0
5	4	01.05.2017 12:12:07	0
6	3	01.05.2017 12:12:28	1
7	1	01.05.2017 12:12:31	1
8	3	01.05.2017 12:12:52	0
9	3	01.05.2017 12:13:09	0
10	2	01.05.2017 12:13:20	1
11	1	01.05.2017 12:13:23	0
12	5	01.05.2017 12:13:54	0
13	3	01.05.2017 12:14:11	1
14	5	01.05.2017 12:14:44	0
15	5	01.05.2017 12:15:14	0
16	3	01.05.2017 12:15:34	0
17	5	01.05.2017 12:16:03	1
18	4	01.05.2017 12:16:26	0
19	4	01.05.2017 12:16:49	1
20	3	01.05.2017 12:17:02	1
21	3	01.05.2017 12:17:14	1
22	2	01.05.2017 12:17:24	0
23	1	01.05.2017 12:17:27	0
24	2	01.05.2017 12:17:37	0
25	3	01.05.2017 12:17:49	0
26	2	01.05.2017 12:17:59	0
27	5	01.05.2017 12:18:27	0
28	1	01.05.2017 12:18:27	1
29	3	01.05.2017 12:18:45	1
30	1	01.05.2017 12:18:49	0
31	1	01.05.2017 12:18:52	0
32	5	01.05.2017 12:19:19	1
33	3	01.05.2017 12:19:32	1
34	1	01.05.2017 12:19:32	1
35	4	01.05.2017 12:19:56	1
36	1	01.05.2017 12:19:59	0
37	4	01.05.2017 12:20:23	0
38	2	01.05.2017 12:20:29	0
39	3	01.05.2017 12:20:41	0
40	3	01.05.2017 12:20:59	0
41	3	01.05.2017 12:21:11	1
42	5	01.05.2017 12:21:40	0
43	2	01.05.2017 12:21:46	0
44	2	01.05.2017 12:21:52	1
45	1	01.05.2017 12:21:55	0
46	1	01.05.2017 12:21:58	1
47	2	01.05.2017 12:22:06	0
48	1	01.05.2017 12:22:09	1
49	2	01.05.2017 12:22:15	0
50	3	01.05.2017 12:22:29	0
51	4	01.05.2017 12:22:52	0
52	5	01.05.2017 12:23:18	0
53	3	01.05.2017 12:23:32	0
54	3	01.05.2017 12:23:49	0
55	3	01.05.2017 12:24:05	0
56	5	01.05.2017 12:24:33	1
57	3	01.05.2017 12:24:49	0
58	3	01.05.2017 12:25:00	0
59	2	01.05.2017 12:25:06	0
60	3	01.05.2017 12:25:19	1
61	3	01.05.2017 12:25:33	1
62	3	01.05.2017 12:25:43	1
63	4	01.05.2017 12:26:02	0
64	3	01.05.2017 12:26:13	1
65	3	01.05.2017 12:26:23	0
66	5	01.05.2017 12:26:47	1
67	5	01.05.2017 12:27:10	0
68	3	01.05.2017 12:27:23	1
69	1	01.05.2017 12:27:26	1
70	3	01.05.2017 12:27:41	1
71	1	01.05.2017 12:27:44	0
72	4	01.05.2017 12:28:02	1
73	2	01.05.2017 12:28:07	1
74	4	01.05.2017 12:28:24	1
75	1	01.05.2017 12:28:27	0
76	3	01.05.2017 12:28:42	0
77	3	01.05.2017 12:28:54	0
78	1	01.05.2017 12:28:57	0
79	4	01.05.2017 12:29:17	1
80	2	01.05.2017 12:29:24	1
81	1	01.05.2017 12:29:26	0
82	5	01.05.2017 12:29:48	1
83	3	01.05.2017 12:30:03	1
84	3	01.05.2017 12:30:15	0
85	5	01.05.2017 12:30:36	0
86	4	01.05.2017 12:30:55	0
87	4	01.05.2017 12:31:14	0
88	3	01.05.2017 12:31:26	1
89	3	01.05.2017 12:31:40	1
90	4	01.05.2017 12:31:58	0
91	1	01.05.2017 12:31:58	1
92	3	01.05.2017 12:32:10	1
93	3	01.05.2017 12:32:21	1
94	1	01.05.2017 12:32:24	0
95	5	01.05.2017 12:32:44	1
96	4	01.05.2017 12:33:00	0
97	2	01.05.2017 12:33:06	1
98	1	01.05.2017 12:33:09	1
99	3	01.05.2017 12:33:17	1
100	2	01.05.2017 12:33:24	0

...

Рейтинг:

0 / 0

11.01.2018, 15:02

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39582566

peskauskas

Гость

U_ID U_NAME

1 DR01
2 DR02
3 DR03
4 DR04
5 DR05
6 DR06
7 DR07
8 DR08
9 DR09
10 DR10
11 DR11
12 DR12
13 DR13
14 DR14
15 DR15
16 DR16
17 DR17
18 DR18
19 DR19
20 DR20
21 DR21
22 DR22
23 DR23
24 DR24
25 DR25
26 DR26
27 DR27
28 DR28
29 DR29
30 DR30
31 DR31
32 DR32
33 DR33
34 DR34
35 DR35
36 DR36
37 DR37
38 DR38
39 DR39
40 DR40
41 DR41
42 DR42
43 DR43
44 DR44
45 DR45
46 DR46
47 DR47
48 DR48
49 DR49
50 DR50
51 DR51
52 DR52
53 DR53
54 DR54
55 DR55
56 DR56
57 DR57
58 DR58
59 DR59
60 DR60
61 DR61
62 DR62
63 DR63
64 DR64
65 DR65
66 DR66
67 DR67
68 DR68
69 DR69
70 DR70
71 DR71
72 DR72
73 DR73
74 DR74
75 DR75
76 DR76
77 DR77
78 DR78
79 DR79
80 DR80
81 DR81
82 DR82
83 DR83
84 DR84
85 DR85
86 DR86
87 DR87
88 DR88
89 DR89
90 DR90
91 DR91
92 DR92
93 DR93
94 DR94
95 DR95
96 DR96
97 DR97
98 DR98
99 DR99
100 TR01
101 TR02
102 TR03
103 TR04
104 TR05
105 TR06
106 TR07
107 TR08
108 TR09
109 TR10
110 TR11
111 TR12
112 TR13
113 TR14
114 TR15
115 TR16
116 TR17
117 TR18
118 TR19
119 TR20
120 TR21
121 TR22
122 TR23
123 TR24
124 TR25
125 TR26
126 TR27
127 TR28
128 TR29
129 TR30
130 TR31
131 TR32
132 TR33
133 TR34
134 TR35
135 TR36
136 TR37
137 TR38
138 TR39
139 TR40
140 TR41
141 TR42
142 TR43
143 TR44
144 TR45
145 TR46
146 TR47
147 TR48
148 TR49
149 TR50
150 TR51
151 TR52
152 TR53
153 TR54
154 TR55
155 TR56
156 TR57
157 TR58
158 TR59
159 TR60
160 TR61
161 TR62
162 TR63
163 TR64
164 TR65
165 TR66
166 TR67
167 TR68
168 TR69
169 TR70
170 TR71
171 TR72
172 TR73
173 TR74
174 TR75
175 TR76
176 TR77
177 TR78
178 TR79
179 TR80
180 TR81
181 TR82
182 TR83
183 TR84
184 TR85
185 TR86
186 TR87
187 TR88
188 TR89
189 TR90
190 TR91
191 TR92
192 TR93
193 TR94
194 TR95
195 TR96
196 TR97
197 TR98
198 TR99
199 TR100
200 GR01
201 GR02
202 GR03
203 GR04
204 GR05
205 GR06
206 GR07
207 GR08
208 GR09
209 GR10
210 GR11
211 GR12
212 GR13
213 GR14
214 GR15
215 GR16
216 GR17
217 GR18
218 GR19
219 GR20
220 GR21
221 GR22
222 GR23
223 GR24
224 GR25
225 GR26
226 GR27
227 GR28
228 GR29
229 GR30
230 GR31
231 GR32
232 GR33
233 GR34
234 GR35
235 GR36
236 GR37
237 GR38
238 GR39
239 GR40
240 GR41
241 GR42
242 GR43
243 GR44
244 GR45
245 GR46
246 GR47
247 GR48
248 GR49
249 GR50
250 GR51
251 GR52
252 GR53
253 GR54
254 GR55
255 GR56
256 GR57
257 GR58
258 GR59
259 GR60
260 GR61
261 GR62
262 GR63
263 GR64
264 GR65
265 GR66
266 GR67
267 GR68
268 GR69
269 GR70
270 GR71
271 GR72
272 GR73
273 GR74
274 GR75
275 GR76
276 GR77
277 GR78
278 GR79
279 GR80
280 GR81
281 GR82
282 GR83
283 GR84
284 GR85
285 GR86
286 GR87
287 GR88
288 GR89
289 GR90
290 GR91
291 GR92
292 GR93
293 GR94
294 GR95
295 GR96
296 GR97
297 GR98
298 GR99
299 GR100
300 UT01
301 UT02
302 UT03
303 UT04
304 UT05
305 UT06
306 UT07
307 UT08
308 UT09
309 UT10
310 UT11
311 UT12
312 UT13
313 UT14
314 UT15
315 UT16
316 UT17
317 UT18
318 UT19
319 UT20
320 UT21
321 UT22
322 UT23
323 UT24
324 UT25
325 UT26
326 UT27
327 UT28
328 UT29
329 UT30
330 UT31
331 UT32
332 UT33
333 UT34
334 UT35
335 UT36
336 UT37
337 UT38
338 UT39
339 UT40
340 UT41
341 UT42
342 UT43
343 UT44
344 UT45
345 UT46
346 UT47
347 UT48
348 UT49
349 UT50
350 UT51
351 UT52
352 UT53
353 UT54
354 UT55
355 UT56
356 UT57
357 UT58
358 UT59
359 UT60
360 UT61
361 UT62
362 UT63
363 UT64
364 UT65
365 UT66
366 UT67
367 UT68
368 UT69
369 UT70
370 UT71
371 UT72
372 UT73
373 UT74
374 UT75
375 UT76
376 UT77
377 UT78
378 UT79
379 UT80
380 UT81
381 UT82
382 UT83
383 UT84
384 UT85
385 UT86
386 UT87
387 UT88
388 UT89
389 UT90
390 UT91
391 UT92
392 UT93
393 UT94
394 UT95
395 UT96
396 UT97
397 UT98
398 UT99
399 UT100
400 WH01
401 WH02
402 WH03
403 WH04
404 WH05
405 WH06
406 WH07
407 WH08
408 WH09
409 WH10
410 WH11
411 WH12
412 WH13
413 WH14
414 WH15
415 WH16
416 WH17
417 WH18
418 WH19
419 WH20
420 WH21
421 WH22
422 WH23
423 WH24
424 WH25
425 WH26
426 WH27
427 WH28
428 WH29
429 WH30
430 WH31
431 WH32
432 WH33
433 WH34
434 WH35
435 WH36
436 WH37
437 WH38
438 WH39
439 WH40
440 WH41
441 WH42
442 WH43
443 WH44
444 WH45
445 WH46
446 WH47
447 WH48
448 WH49
449 WH50
450 WH51
451 WH52
452 WH53
453 WH54
454 WH55
455 WH56
456 WH57
457 WH58
458 WH59
459 WH60
460 WH61
461 WH62
462 WH63
463 WH64
464 WH65
465 WH66
466 WH67
467 WH68
468 WH69
469 WH70
470 WH71
471 WH72
472 WH73
473 WH74
474 WH75
475 WH76
476 WH77
477 WH78
478 WH79
479 WH80
480 WH81
481 WH82
482 WH83
483 WH84
484 WH85
485 WH86
486 WH87
487 WH88
488 WH89
489 WH90
490 WH91
491 WH92
492 WH93
493 WH94
494 WH95
495 WH96
496 WH97
497 WH98
498 WH99
499 WH100
500 ZD01
501 ZD02
502 ZD03
503 ZD04
504 ZD05
505 ZD06
506 ZD07
507 ZD08
508 ZD09
509 ZD10
510 ZD11
511 ZD12
512 ZD13
513 ZD14
514 ZD15
515 ZD16
516 ZD17
517 ZD18
518 ZD19
519 ZD20
520 ZD21
521 ZD22
522 ZD23
523 ZD24
524 ZD25
525 ZD26
526 ZD27
527 ZD28
528 ZD29
529 ZD30
530 ZD31
531 ZD32
532 ZD33
533 ZD34
534 ZD35
535 ZD36
536 ZD37
537 ZD38
538 ZD39
539 ZD40
540 ZD41
541 ZD42
542 ZD43
543 ZD44
544 ZD45
545 ZD46
546 ZD47
547 ZD48
548 ZD49
549 ZD50
550 ZD51
551 ZD52
552 ZD53
553 ZD54
554 ZD55
555 ZD56
556 ZD57
557 ZD58
558 ZD59
559 ZD60
560 ZD61
561 ZD62
562 ZD63
563 ZD64
564 ZD65
565 ZD66
566 ZD67
567 ZD68
568 ZD69
569 ZD70
570 ZD71
571 ZD72
572 ZD73
573 ZD74
574 ZD75
575 ZD76
576 ZD77
577 ZD78
578 ZD79
579 ZD80
580 ZD81
581 ZD82
582 ZD83
583 ZD84
584 ZD85
585 ZD86
586 ZD87
587 ZD88
588 ZD89
589 ZD90
590 ZD91
591 ZD92
592 ZD93
593 ZD94
594 ZD95
595 ZD96
596 ZD97
597 ZD98
598 ZD99
599 ZD100

Units

...

Рейтинг:

0 / 0

11.01.2018, 15:05

| Ответить | Цитировать | Написать

Transact-SQL @CURSOR

#39583642

peskauskas

Гость

Алгоритм

peskauskasАлгоритм

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

и

2. GPS (ключевые поля)
U_ID int not NULL (айди Юнита)
TimePoint datatime (Время точки GPS)
Line tinyint (два значения 0 или 1)

Так же есть Таблица сводная TOU (--TimeOfflineUnit)
в ней 2 колонки
name
Offline

у каждого юнита есть трекинг по GPS соотв долгота и широта и
время контрольной точки (поле TimePoint), так же есть статус онлайн или оффлайн (Line)
нужно назначить курсор с перебором ВСЕХ айди начиная с 1 и по N таким образом,
чтобы:
0. Присвоить => @TS = 0
1. найти первую строку (первое вхождение) где юнит оффлайн (Line = 0) и назначить переменной @TP(-- нолевая точка времени) поле TimePoint.
2. ЕСЛИ (ИЛИ):
а. следующая строка оффлайн (Line = 0)
б. следующая строка онлайн (Line = 1) И ((@TS (--Сумарное время простоя) = TimePoint - @TP ) > 15 (--более 15 секунд))
=> Присвоить переменный @TS = Timapoint - @TP
=> Присвоить переменный @TP = Timapoint
=> Сл строка соответствия по @ID
в. следующая строка онлайн (Line = 1) И ((@TS (--Сумарное время простоя) = TimePoint - @TP ) < 15 (--менее 15 секунд))
=> Присвоить переменный @TS = Timapoin
=> Сл строка соответствия по @ID

3. ЕСЛИ строка с @ID не найдена (Пред - идущая была последней или единственной или небыло вообще),
то, нужно добавить строку в таблицу TOU (--TimeOfflineUnit) @ID , @TS .(для каждого юнита свое значение)
при переходе на сл айди (@ID = @ID + 1),
соотв переменная пункты 0 и 1.

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

переменные:
DECLARE @ID int --Перебор ID
SET @ID = 1 --Старт от 1
DECLARE @TP datetime --Пер. времени отсчета простоя (1-я поз Line = 0)
Set @TP = NULL --Обнуление
DECLARE @TS datetime --Общее время простоя
SET @TS = null --Обнуление

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

Решение

--обьявление вспомогательных (где С в конце - для fetch-ей)
DECLARE @time1C datetime,
@time2C datetime,
@line1C bit,
@line2C bit,
@id_unitsC int,
@time_sum int,
@time_delta int;

delete from Time_Unit_Off; -- очистка сводной таблицы

DECLARE units_cursor CURSOR FOR -- обьявление курсора из select-а всех юнитов
SELECT Units.id FROM Units

OPEN units_cursor -- открытие курсора

FETCH NEXT FROM units_cursor -- получение перой записи
INTO @id_unitsC; -- в переменную обьявленную ранее
WHILE @@FETCH_STATUS = 0
BEGIN
SET @time_sum=0; -- обнуление переменной дельты

DECLARE gps_cursor CURSOR -- обьявление курсора, который будет проходиться по всем записям из GPS таблицы конкретного юнита (@id_unitsC)
FOR SELECT GPS.time_point, GPS.Line FROM GPS
WHERE GPS.id_units=@id_unitsC
ORDER by GPS.time_point -- на всякий случай сортируем по времени
OPEN gps_cursor
FETCH NEXT FROM gps_cursor
INTO @time1C, @line1C;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @time_delta=0; -- обнуляем дельту
FETCH NEXT FROM gps_cursor
INTO @time2C, @line2C;
IF @@FETCH_STATUS<>0
BREAK;
IF @line1C=0 AND @line2C=0 -- 0 0 - простой
SET @time_delta=DATEDIFF(s, @time1C,@time2C); -- получаем дельту
IF @line1C=1 AND @line2C=0 OR @line1C=0 AND @line2C=1 -- 0 1 или 0 1
BEGIN
SET @time_delta=DATEDIFF(s, @time1C,@time2C);-- получаем дельту
IF(@time_delta<=15) -- если меньше 15, то дельта = 0
set @time_delta=0;
END
SET @time_sum += @time_delta; -- добавление к общей сумме конкретного юнита
SET @time1C=@time2C; -- "смещение текущей записи"
SET @line1C=@line2C;
END
INSERT INTO Time_Unit_Off(id,time_elapsed) -- вставляем в сводную таблицу полученные результаты
VALUES (@id_unitsC,@time_sum);
CLOSE gps_cursor; -- закрываем курсор
DEALLOCATE gps_cursor;
FETCH NEXT FROM units_cursor -- идем на следующего юнита
INTO @id_unitsC;
END
CLOSE units_cursor;
DEALLOCATE units_cursor;

SELECT
Time_Unit_Off.id,
Units.Name,
Time_Unit_Off.time_elapsed ,
CONCAT(time_elapsed/60/60/24 ,':',time_elapsed/60/60%24,':',time_elapsed/60%60,':',time_elapsed%60) AS 'dd:hh:mm:ss' --просто так нельзя сконвертировать секунды и формат даты, поэтому приходится конвертировать в дни, часы, минуты, секунды самостоятельно
FROM
Time_Unit_Off,
Units
WHERE
Time_Unit_Off.id = Units.id AND
Time_Unit_Off.time_elapsed <> 0

Всем ОГРОМНОЕ СПАСИБО за советы и содействие!!!!!

...

Рейтинг:

0 / 0

13.01.2018, 11:31

| Ответить | Цитировать | Написать

36 сообщений из 36, показаны все 2 страниц

все

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Transact-SQL @CURSOR

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?all=1&fid=46&tid=1690517]:	0ms
get settings:	8ms
get forum list:	17ms
check forum access:	3ms
check topic access:	3ms
track hit:	60ms
get topic data:	9ms
get forum data:	2ms
get page messages:	89ms
get tp. blocked users:	2ms
others:	249ms

total:	442ms