Использование SQL для суммирования значений при проверке и сравнении значений других столбцов
- В = значение
- Я = Индикатор
- Т = Тип
Выше у меня есть таблица с 4 строками. Моя цель — суммировать все значения (V1-V4) каждой строки, где они имеют тип (T1-4) 1 и индикатор (I1-4) 1 или 2. Если строка имеет тип 1 и индикатор 1 и 2 на строке, то следует учитывать только одно значение. Результаты из приведенной выше таблицы должны быть:
- Ряд 1: 27
- Ряд 2: 31
- Ряд 3: 15
- Ряд 4: 18
Мне было очень легко решить эту проблему, пока я не столкнулся с проблемой со предпоследней строкой. Я просто делал:
CASE
WHEN T1 = 1
AND I1 = (1 OR 2)
THEN V1
ELSE 0
END +
CASE
WHEN T2 = 1
AND I2 = (1 OR 2)
THEN V2
ELSE 0
END +
CASE
WHEN T3 = 1
AND I3 = (1 OR 2)
THEN V3
ELSE 0
END +
Я просто использовал эту логику для всей таблицы, чтобы суммировать все вместе, но недавно узнал, что если строка имеет значение I, равное 1 и 2, к сумме следует добавлять только одно из значений.
Есть ли способ в SQL использовать CTE или табличную переменную, чтобы найти решение здесь? Я надеюсь разобраться в этом, просто используя SQL, поскольку проблема заключается не только в этой части, и она уже встроена в SQL.
Above I have a table with 3 rows кажется у вас 4 ряда. У вас также есть значение I 12 в первой строке. indicator 1 and 2 on a line then only one value should be counted какое из значений следует учитывать? Здесь действительно нужно быть точным
@siggemannen Где значение I, равное 12? Если линия имеет I, равный 1 и 2, значения будут одинаковыми, поэтому можно посчитать любое из них.
Кстати, я использую SQL Server, добавление MySQL было ошибкой.
Вам необходимо предоставить как образцы данных, так и желаемые результаты, чтобы мы могли увидеть, чего вы пытаетесь достичь.
If a row has a Type of 1 and an indicator 1 and 2 on a line then only one value should be counted.. Что следует учитывать? Самый высокий, самый низкий, самый левый, самый правый, самый высокий показатель, самый низкий показатель? Вам нужны категоричные правила, которые учитывают все возможности и являются детерминированными.
Можно ли реструктурировать данные? Этот формат плоского файла удобен для чтения людьми, но плох для обработки sql. Это «должно» быть 16 строк по три столбца (плюс четвертый столбец, чтобы связать вместе пакеты из четырех строк). (На самом деле 9 строк, пустым значениям строки не нужны.)
@DaleK Я предоставил примеры данных в таблице и результаты, которые мне нужны.
@MatBailie Данные можно реструктурировать, это транзакционные данные, так что это может быть решением здесь. Однако если бы я разбил его на ряд для каждого сегмента, мне также пришлось бы учитывать индикатор 1 и 2 в одной строке. Для строк с типом 1 и индикатором 1 и 2 на строке значение может быть самой левой строкой.
В строке 2 вы разрешаете суммировать два экземпляра (t=1, i=1). Применимо ли то же самое к двум экземплярам (t=1, i=2)? И каковы должны быть результаты в следующей скрипте? dbfiddle.uk/y2QTqpJ8
Ответы 4
CASE
WHEN (T1 = 1
AND
((T1 = T2 AND I1 <> I2)
OR (T1 = T3 AND I1 <> I3)
OR (T1 = T4 AND I1 <> I4)))
THEN AccumulatorAmount1/2
WHEN (T1 = 1)
THEN AccumulatorAmount1
ELSE 0
END +
Решение, которое я нашел на данный момент, таково, но оно запутанное, поскольку вместо того, чтобы делать одну строку нулем, я просто делю сумму на 2 там, где есть совпадающее T и несовпадающее I. Этот способ суммирования их все еще работает, но если значения нечетные, это немного искажает данные.
Грубый силовой подход...
CREATE TABLE example (
id INT,
v1 INT, i1 INT, t1 INT,
v2 INT, i2 INT, t2 INT,
v3 INT, i3 INT, t3 INT,
v4 INT, i4 INT, t4 INT
)
INSERT INTO
example
VALUES
(1, 15, 1, 1, 12, 1, 1, 22, 1, 3, NULL, NULL, NULL),
(2, 15, 1, 1, 22, 2, 3, NULL, NULL, NULL, 16, 1, 1),
(3, 15, 1, 1, 15, 2, 1, NULL, NULL, NULL, NULL, NULL, NULL),
(4, NULL, NULL, NULL, NULL, NULL, NULL, 18, 2, 1, NULL, NULL, NULL)
4 rows affected
SELECT
id,
CASE WHEN t1=1 AND i1=min_i THEN v1 ELSE 0 END
+
CASE WHEN t2=1 AND i2=min_i THEN v2 ELSE 0 END
+
CASE WHEN t3=1 AND i3=min_i THEN v3 ELSE 0 END
+
CASE WHEN t4=1 AND i4=min_i THEN v4 ELSE 0 END
FROM
example
OUTER APPLY
(
SELECT
MIN(i1) AS min_i
FROM
(
SELECT i1 WHERE t1=1
UNION ALL
SELECT i2 WHERE t2=1
UNION ALL
SELECT i3 WHERE t3=1
UNION ALL
SELECT i4 WHERE t4=1
)
AS pvt
)
AS indicator_check
Мне нравится этот подход, и, кажется, он работает без необходимости манипулировать данными. Я ценю вашу помощь в этом.
@ jrdev12345 не стесняйтесь голосовать за/принимать ответы, которые помогут
Этот подход позволяет избежать подзапроса.
CREATE TABLE tablename
(
RowNum INT NOT NULL IDENTITY(1,1),
V1 int, I1 int, T1 int, V2 int, I2 int, T2 int,
V3 int, I3 int, T3 int, V4 int, I4 int, T4 int
);
INSERT INTO tablename (V1, I1, T1, V2, I2, T2, V3, I3, T3, V4, I4, T4) VALUES
(15, 1, 1, 12, 1, 1, 22, 1, 3, 0, 0, 0),
(15, 1, 1, 22, 2, 3, 0, 0, 0, 16, 1, 1),
(15, 1, 1, 15, 2, 1, 0, 0, 0, 0, 0, 0),
(0, 0, 0, 0, 0, 0, 18, 2, 1, 0, 0, 0);
SELECT * FROM tablename
Select RowNum,
CASE T1 WHEN 1 THEN CASE WHEN I1 in (1,2) THEN V1 ELSE 0 END
ELSE 0 END +
CASE T2 WHEN 1 THEN CASE WHEN I2 in (1,2) THEN CASE I1+I2 WHEN 3 THEN 0 ELSE V2 END ELSE 0 END
ELSE 0 END +
CASE T3 WHEN 1 THEN CASE WHEN I3 in (1,2) THEN CASE I1+I2+I3
WHEN 3 THEN 0
WHEN 5 THEN 0
ELSE V3 END ELSE 0 END
ELSE 0 END +
CASE T4 WHEN 1 THEN CASE WHEN I4 in (1,2) THEN CASE I1+I2+I3+I4
WHEN 3 THEN 0
WHEN 5 THEN 0
WHEN 7 THEN 0
ELSE V4 END ELSE 0 END
ELSE 0 END AS SUM
FROM tablename
WHERE (T1=1 AND I1 in (1,2))
or (T1=1 AND I2 in (1,2))
or (T3=1 AND I3 in (1,2))
or (T4=1 AND I4 in (1,2))
Не уверен, что ваше редактирование соответствует вашим намерениям: dbfiddle.uk/yTYeFTnz
Нормальный подход...
CREATE TABLE example (
id INT,
grp INT,
v INT,
i INT,
t INT
)
INSERT INTO
example
VALUES
(1,1, 15, 1, 1),
(1,2, 12, 1, 1),
(1,3, 22, 1, 3),
(2,1, 15, 1, 1),
(2,2, 22, 2, 3),
(2,4, 16, 1, 1),
(3,1, 15, 1, 1),
(3,2, 15, 2, 1),
(4,3, 18, 2, 1)
9 rows affected
WITH
filtered AS
(
SELECT
*,
MIN(i) OVER (PARTITION BY id) AS min_i
FROM
example
WHERE
t=1
)
SELECT
id,
SUM(v)
FROM
filtered
WHERE
i = min_i
GROUP BY
id
Я удалил конфликтующие теги; отредактируйте свой вопрос, чтобы правильно отметить его.