#sql-server #null #gaps-and-islands #wonderware
#sql-сервер #null #пробелы и острова #wonderware
Вопрос:
Я подключаюсь к Microsoft SQL Server в Tableau с помощью пользовательского SQL-запроса. У меня есть таблица с 3 полями DateTime, tagName, Value, и я хочу заменить нулевые значения в поле Value на последнее (с учетом значения DateTime) ненулевое значение в каждой группе tagName.
|---------------------|------------------|-----------------|
| DateTime | TagName | Value
|---------------------|------------------|-----------------
| 15.04.2019 16:51:30| A | 10
|---------------------|------------------|-----------------
| 15.04.2019 16:52:42| A | NULL
|---------------------|------------------|-----------------
| 15.04.2019 16:53:14| A | NULL
|---------------------|------------------|-----------------
| 15.04.2019 17:52:14| A | 15
|---------------------|------------------|-----------------
| 15.04.2019 16:51:30| B | NULL
|---------------------|------------------|-----------------
| 15.04.2019 16:52:42| B | NULL
|---------------------|------------------|-----------------
| 15.04.2019 16:53:14| B | NULL
|---------------------|------------------|-----------------
| 15.04.2019 17:52:14| B | 15
|---------------------|------------------|-----------------|
Новая таблица должна выглядеть следующим образом:
|---------------------|------------------|-----------------|
| DateTime | Computer | Value
|---------------------|------------------|-----------------
| 15.04.2019 16:51:30| A | 10
|---------------------|------------------|-----------------
| 15.04.2019 16:52:42| A | 10
|---------------------|------------------|-----------------
| 15.04.2019 16:53:14| A | 10
|---------------------|------------------|-----------------
| 15.04.2019 17:52:14| A | 15
|---------------------|------------------|-----------------
| 15.04.2019 16:51:30| B | 0
|---------------------|------------------|-----------------
| 15.04.2019 16:52:42| B | 0
|---------------------|------------------|-----------------
| 15.04.2019 16:53:14| B | 0
|---------------------|------------------|-----------------
| 15.04.2019 17:52:14| B | 15
|---------------------|------------------|-----------------|
Это уже то, что я пробовал, но оно заменяет нулевые значения без учета значений TagNames (в этом примере есть только одно tagName).
SELECT Computer, DateTime
, CASE
WHEN Value IS NULL
THEN
(SELECT TOP 1 Value
FROM History
WHERE DateTime<T.DateTime
AND TagName='RM02EL00CPT81.rEp'
AND DateTime >='2018-12-31 23:59:00'
AND wwRetrievalMode='Delta'
AND Value IS NOT NULL ORDER BY DateTime DESC
)
ELSE Value
END
AS ValueNEW
FROM History T
WHERE TagName='RM02EL00CPT81.rEp' AND DateTime >='2018-12-31 23:59:00' AND wwRetrievalMode='Delta'
Я хотел сделать почти то же самое, добавив OVER(PARTITION BY TagName) , но это выдало ошибку. (Это потому, что он не работает SELECT TOP 1 с.)
Ответ №1:
Это «классический» вопрос о пробелах и островах. Вы можете добиться этого без 2-х сканирований или треугольного соединения с помощью оконных функций:
WITH VTE AS(
SELECT CONVERT(datetime, [DateTime],104) AS [DateTime],
TagName,
[Value]
FROM (VALUES ('15.04.2019 16:51:30','A',10 ),
('15.04.2019 16:52:42','A',NULL),
('15.04.2019 16:53:14','A',NULL),
('15.04.2019 17:52:14','A',15 ),
('15.04.2019 16:51:30','B',NULL),
('15.04.2019 16:52:42','B',NULL),
('15.04.2019 16:53:14','B',NULL),
('15.04.2019 17:52:14','B',15 )) V([DateTime],TagName,[Value])),
Grps AS(
SELECT [DateTime],
TagName,
[Value],
COUNT(CASE WHEN [Value] IS NOT NULL THEN 1 END) OVER (PARTITION BY TagName ORDER BY [DateTime]
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Grp
FROM VTE)
SELECT DateTime,
TagName,
ISNULL(MAX([Value]) OVER (PARTITION BY TagName, Grp),0) AS [Value]
FROM Grps
ORDER BY TagName, [DateTime]
Комментарии:
1. Это решило мою проблему, и я никогда не думал об этом. Большое вам спасибо за вашу помощь @Larnu
2. Добро пожаловать @sgokce . Функция Window предоставляет SQL Server некоторые мощные / полезные возможности, поэтому о них стоит узнать. 🙂
Ответ №2:
Попробуйте это
;WITH CTE([DateTime],TagName,Valu)
AS
(
SELECT '15.04.2019 16:51:30','A' , 10 UNION ALL
SELECT '15.04.2019 16:52:42','A' , NULL UNION ALL
SELECT '15.04.2019 16:53:14','A' , NULL UNION ALL
SELECT '15.04.2019 17:52:14','A' , 15 UNION ALL
SELECT '15.04.2019 16:51:30','B' , NULL UNION ALL
SELECT '15.04.2019 16:52:42','B' , NULL UNION ALL
SELECT '15.04.2019 16:53:14','B' , NULL UNION ALL
SELECT '15.04.2019 17:52:14','B' , 15
)
SELECT [DateTime],TagName As Computer,
ISNULL(CASE WHEN Valu IS NOT NULL
THEN Valu
ELSE
(
SELECT TOP 1 Valu FROM
CTE i
WHERE i.TagName = o.TagName
) END,0) As Valu
FROM CTE o
Результат
DateTime Computer Valu
---------------------------------------------
15.04.2019 16:51:30 A 10
15.04.2019 16:52:42 A 10
15.04.2019 16:53:14 A 10
15.04.2019 17:52:14 A 15
15.04.2019 16:51:30 B 0
15.04.2019 16:52:42 B 0
15.04.2019 16:53:14 B 0
15.04.2019 17:52:14 B 15
Ответ №3:
Итак, вы пытаетесь получить данные из Wonderware Historian. Возможно, вам не нужно никаких окон и замен, потому что механизм поиска истории должен быть в состоянии предоставить вам необходимые данные без нулей. Попробуйте это:
select DateTime, TagName as Computer, Value
from History
where TagName in ('A', 'B') --put here the tagnames you want to retrieve
and DateTime > '2018-12-31'
AND wwRetrievalMode='Delta'
order by TagName, DateTime
Комментарии:
1. Спасибо за ваш ответ, но я не понимаю, что вы имеете в виду. Если вы упоминаете режим дельта-извлечения, я уже извлекаю данные в режиме дельта. Насколько я знаю, в этом режиме мы все равно можем получать нулевые значения. По крайней мере, это то, что говорится в «Руководстве по историческим концепциям», а также то, что я наблюдаю в своих данных.
2. @sgokce Правильно, он может возвращать нули, но не должен возвращать несколько подряд; из руководства пользователя historian: «Всегда возвращается начальное нулевое значение после ненулевого значения. Несколько нулевых значений подавляются. Первое ненулевое значение после NULL всегда возвращается, даже если оно совпадает с предыдущим ненулевым значением. » Если у вас есть нули в возвращаемых данных, то, вероятно, в то время этот тег имел плохое качество. Я думаю, вам следует использовать только значения хорошего качества для визуализации таблиц. Таким образом, вы могли бы просто отфильтровать нули, добавив «и значение не равно нулю» к условию и использовать только значения хорошего качества
3. Спасибо за ваш подробный ответ. Фильтрация нулевых значений может привести к пробелам в данных, и я хотел бы избежать такого рода пробелов в моем анализе. Я видел, что мы также можем применить «правило оптимистичного качества» (wwQualityRule ), чтобы избежать нулевых значений. ОПТИМИСТИЧНАЯ настройка для правила качества позволяет извлекать информацию, которая, возможно, является неполной, но, тем не менее, может обеспечить лучшие результаты в режимах поиска счетчика и интеграла, где цикл вычисления содержит пробелы в данных. Этот параметр вычисляется с использованием последнего известного допустимого значения до разрыва (если это возможно)