Отличие от первой строки, но агрегирование от второй строки до конца
У меня есть два столбца, где в первой строке (проверка) обнаружены и отмечены некоторые нарушения... может быть более 15 различных нарушений.
Тогда при каждой последующей проверке некоторые нарушения могут быть устранены (устраненные нарушения НЕ могут снова отображаться как неустраненные). Число может варьироваться, а может быть даже так, что проверка не выявила ни одного из оставшихся нарушений, поэтому для этого случая будет пустая строка, но ее все равно придется учитывать.
В результате мне нужны неустраненные нарушения после каждой проверки...
Мне нужно это в T-SQL. Я знаю, как использовать LAG, LEAD, FIRST_VALUE и т. д. Говорю это, когда пытался решить эту проблему с помощью оконных функций. Я создал функцию, чтобы получить разницу между значениями (например, оставшиеся нарушения - нарушения при проверке), но на самом деле я не могу понять, как пройти вторую проверку.
Это совокупность таблиц ниже, и если у вас есть какой-то совет, я могу пойти и изменить запросы, которые передают данные в этом формате. Единственное, что для проверки у меня зафиксированы ответы не по каждому из нарушений, выявленных первым, а только по одному, которое устранено/устранено.
заранее спасибо
SELECT aggdata.Inspection
, RemainingViolations =
CASE
WHEN aggdata.Inspection = 1 THEN aggdata.AggregatedViolations
ELSE dbo.udf_GetDifferenceInValues(FIRST_VALUE(aggdata.AggregatedViolations) OVER (ORDER BY aggdata.Inspection), aggdata.AggregatedViolations)
END
FROM (
SELECT SimpleData.Inspection
, AggregatedViolations = STRING_AGG(SimpleData.Violation, ',')
FROM (VALUES (1, '6/1/2024', 100),
(1, '6/1/2024', 101),
(1, '6/1/2024', 102),
(1, '6/1/2024', 103),
(2, '6/1/2024', 100),
(2, '6/1/2024', 101),
(3, '6/1/2024', NULL),
(4, '6/1/2024', 103),
(5, '6/1/2024', 102)
) AS
SimpleData (Inspection, DateInspection, Violation)
GROUP BY SimpleData.Inspection
) aggdata
ALTER FUNCTION udf_GetDifferenceInValues
(
-- Add the parameters for the function here
@String1 VARCHAR(4000)
, @String2 VARCHAR(4000)
)
RETURNS VARCHAR(4000)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result VARCHAR(4000)
-- Add the T-SQL statements to compute the return value here
SELECT @Result = STRING_AGG(Value, ',')
FROM dbo.dbSplit(@String1, ',')
WHERE Value NOT IN (
SELECT Value
FROM dbo.dbSplit(@String2, ',')
)
-- Return the result of the function
RETURN @Result
END
Пожалуйста, покажите (не в виде изображений) образцы/исходные данные перед запросом и желаемые результаты после запроса.
У меня есть дочерняя таблица... я опубликовал строковые агрегаты, которые я получаю за каждую проверку, просто чтобы проще показать, чего я хочу достичь.
Верно. Я понимаю. Спасибо! Добавлены простые данные
Я думаю, вы упустили момент, когда не нужно использовать изображения — используйте уценку таблицы, поддерживаемую редактором.
Покажите, что вы пробовали, и свою ОДНУ задачу, чтобы мы могли помочь вам ее решить.
Добавил то, что получил на данный момент. Не имея решения, как устранить нарушения построчно из того, что осталось при предыдущей проверке.
Ответы 1
/* *** Test Data *** */
CREATE TABLE #t
(
Inspection int NOT NULL
,[Date] date NOT NULL
,Violation int NULL
);
INSERT INTO #t
VALUES (1, '20240601', 100)
,(1, '20240601', 101)
,(1, '20240601', 102)
,(1, '20240601', 103)
,(2, '20240602', 100)
,(2, '20240602', 101)
,(3, '20240607', NULL)
,(4, '20240608', 103)
,(5, '20240609', 102);
/* *** End Test Data *** */
WITH vCounts
AS
(
SELECT X.Inspection, T.Violation AS AViolation, T2.Violation
,COUNT(T2.Violation) OVER (PARTITION BY T.Violation ORDER BY X.Inspection ROWS UNBOUNDED PRECEDING) AS VCount
FROM #t T
JOIN
(
SELECT DISTINCT Inspection
FROM #t
) X (Inspection)
ON T.Inspection = 1
LEFT JOIN #t T2
ON X.Inspection = T2.Inspection
AND T.Violation = T2.Violation
)
,VGroups
AS
(
SELECT Inspection
,STRING_AGG(Violation, ',') WITHIN GROUP (ORDER BY AViolation) AS Violations
,STRING_AGG(IIF(Violation IS NULL AND VCount = 1, AViolation, NULL), ',') WITHIN GROUP (ORDER BY AViolation) AS ActiveViolations
FROM VCounts
GROUP BY Inspection
)
SELECT Inspection, Violations
,IIF(Inspection = 1, Violations, ActiveViolations) AS ActiveViolations
FROM VGroups
ORDER BY Inspection;
Спасибо, сэр. Это работает так, как мне нужно. Теперь мне просто нужно это понять и применить к гораздо большей таблице, которая у меня есть. Еще раз спасибо!
Хороший ответ помимо рабочего запроса содержит пояснение.
В соответствии с руководством по вопросам не публикуйте изображения кода, данных, сообщений об ошибках и т. д. — скопируйте или введите текст в вопрос. Пожалуйста, ограничьте использование изображений для диаграмм или демонстрации ошибок рендеринга, вещей, которые невозможно точно описать с помощью текста.