SQL Server Query – групповое умножение

Если у нас есть таблица вроде этого:

Grp Value Grp1 2 Grp1 5 Grp1 3 Grp2 3 Grp2 -5 Grp2 -2 Grp3 4 Grp3 0 Grp3 1 Grp4 -2 Grp4 -4 Grp5 7 Grp5 NULL Grp6 NULL Grp6 NULL Grp7 -1 Grp7 10 

Как мы можем группировать / размножаться, чтобы получить это?

 GrpID Value Grp1 30 Grp2 30 Grp3 0 Grp4 8 Grp5 7 Grp6 NULL Grp7 -10 

Умножение значений строк такое же, как добавление логарифмов значений строк

Трюк имеет дело с нулями и нулями.

Хорошо, сейчас проверено

 DECLARE @foo TABLE (GrpID varchar(10), Value float) INSERt @foo (GrpID, Value) SELECT 'Grp1', 2 UNION ALL SELECT 'Grp1', 5 UNION ALL SELECT 'Grp1', 3 UNION ALL SELECT 'Grp2', 3 UNION ALL SELECT 'Grp2', -5 UNION ALL SELECT 'Grp2', -2 UNION ALL SELECT 'Grp3', 4 UNION ALL SELECT 'Grp3', 0 UNION ALL SELECT 'Grp3', 1 UNION ALL SELECT 'Grp4', -2 UNION ALL SELECT 'Grp4', -4 UNION ALL SELECT 'Grp5', 7 UNION ALL SELECT 'Grp5', NULL UNION ALL SELECT 'Grp6', NULL UNION ALL SELECT 'Grp6', NULL UNION ALL SELECT 'Grp7', -1 UNION ALL SELECT 'Grp7', 10 SELECT GrpID, CASE WHEN MinVal = 0 THEN 0 WHEN Neg % 2 = 1 THEN -1 * EXP(ABSMult) ELSE EXP(ABSMult) END FROM ( SELECT GrpID, --log of +ve row values SUM(LOG(ABS(NULLIF(Value, 0)))) AS ABSMult, --count of -ve values. Even = +ve result. SUM(SIGN(CASE WHEN Value < 0 THEN 1 ELSE 0 END)) AS Neg, --anything * zero = zero MIN(ABS(Value)) AS MinVal FROM @foo GROUP BY GrpID ) foo 

На меня раздражает, что в SQL-сервере нет функции агрегации Multiply / Product. К сожалению, я не нашел ответа @gbn выше, пока я уже не решил проблему по-другому. Я публикую это альтернативное решение на всякий случай, если оно помогает кому-либо, или если оно окажется более эффективным.

Мое решение в основном предполагает использование рекурсивного общего табличного выражения для умножения всех значений вместе.

 DECLARE @t TABLE (PID INT, ID INT, multiplier DECIMAL(14,5)) INSERT @t (PID, ID, multiplier) VALUES (1, 1, 1.5 ) , (2, 1, 1.2) , (3, 2, 1.7) , (4, 3, 1) , (5, 4, 0.8) , (6, 4, 0.5) , (7, 4, 2) , (8, 4, 0.5) SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY PID ) FROM @t; WITH trn AS ( SELECT PID, ID, multiplier, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY PID ) AS rn FROM @t ), tmu AS ( SELECT DISTINCT ID, trn.multiplier AS multiplier, 1 AS Iteration FROM trn WHERE rn = 1 UNION ALL SELECT trn.ID, CAST(tmu.multiplier * trn.multiplier AS DECIMAL(14,5)) AS multiplier , tmu.Iteration + 1 AS Iteration FROM tmu --AS tmu1 JOIN trn ON tmu.ID = trn.ID AND tmu.Iteration + 1 = trn.rn ), mxi AS ( SELECT ID, COUNT(ID) AS Iter FROM trn GROUP BY ID ) SELECT tmu.* FROM tmu JOIN mxi ON mxi.ID = tmu.ID AND mxi.Iter = tmu.Iteration ORDER BY ID 

Начальная таблица (+ номер разделенной строки) выбирает:

 PID ID Multiplier rn 1 1 1.50000 1 2 1 1.20000 2 3 2 1.70000 1 4 3 1.00000 1 5 4 0.80000 1 6 4 0.50000 2 7 4 2.00000 3 8 4 0.50000 4 

Список идентификаторов / умножителей, все умноженные, выбирает:

 ID multiplier Iteration 1 1.80000 2 2 1.70000 1 3 1.00000 1 4 0.40000 4 

Это происходит от CodePlex «Функция агрегированного продукта расширяет SQL». Я изменил инструкцию, чтобы вернуть желаемые результаты, за исключением группы NULL, она возвращает «1».

  SELECT GrpID, Exp(Sum(IIf(Abs(ISNULL(value,1))=0,0,Log(Abs(ISNULL(value,1))))))*IIf(Min(Abs(ISNULL(value,1)))=0,0,1)* (1-2*(Sum(IIf(ISNULL(value,1)>=0,0,1)) % 2)) AS value FROM @foo GROUP BY GrpID; GrpID Value Grp1 30 Grp2 30 Grp3 0 Grp4 8 Grp5 7 Grp6 1 Grp7 -10 

ref: http://productfunctionsql.codeplex.com/

Interesting Posts

TSQL: запрос с дополнительным соединением

Можем ли мы писать обновления и удалять запросы в представлениях?

Ошибка SQL ROW_NUMBER () с поисковым вызовом

INNER JOIN работает как CROSS JOIN

Ссылка на предыдущее значение строки для вычисления арифметики в SQL Server 2008 R2

SQL выбрать все, если параметр равен null else return specfic item

Как создать «материализованное что-то», которое обращается к различным таблицам, в зависимости от конкретной настройки

Утечка соединения может вызвать истечение времени ожидания. Прошел период ожидания до получения соединения из пула?

В INSERT может ли значение первичного ключа перейти в другое поле, отличное от нуля, в той же таблице?

Исправить крест, применяя строки, когда записи дублируются

возврат значения на основе минимального значения из другого столбца в sql-сервере

SQL Server CASE 1 КОГДА 1, ГДЕ 1 = 1 И 1 = 1

Что не так с этим вставным запросом?

SQL Как присоединиться к двум таблицам для получения записей в следующем сценарии?

Можно ли подключиться к SqlServer (MSSQL) с помощью Haskell и Linux?

Давайте будем гением компьютера.