Вычислять различия между строками при группировке с помощью SQL

У меня есть таблица postgresql, содержащая перемещения разных предметов (моделей) между складами.

Например, следующая запись означает, что со склада 1 на склад 2 отправлено 5 единиц модели 1:

source target model units
------ ------ ----- -----
     1      2     1     5

Я пытаюсь построить SQL-запрос, чтобы получить разницу между отправленными и полученными единицами, сгруппированными по моделям. Опять же на примере:

source target model units
------ ------ ----- -----
     1      2     1     5  -- 5 sent from 1 to 2
     1      2     2     1
     2      1     1     2  -- 2 sent from 2 to 1
     2      1     1     1  -- 1 more sent from 2 to 1

Результат должен быть:

source target model diff
------ ------ ----- ----
     1      2     1    2   -- 5 sent minus 3 received
     1      2     2    1

Интересно, возможно ли это с помощью одного SQL-запроса.

Вот скрипт создания таблицы и некоторые данные, на всякий случай, если кто-то захочет попробовать:

CREATE TEMP TABLE movements
(
    source  INTEGER,
    target  INTEGER,
    model   INTEGER,
    units   INTEGER
);

insert into movements values (1,2,1,5);
insert into movements values (1,2,2,1);
insert into movements values (2,1,1,2);
insert into movements values (2,1,1,1);

postgresql sql grouping
person Guido    schedule 08.05.2010    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Это делает то, что вам нужно? У меня нет базы данных Oracle для тестирования, поэтому я надеюсь, что правила группировки выражений такие же, как для MS SQL Server.

SELECT     
 CASE WHEN source < target THEN source ELSE target END AS source,
 CASE WHEN source < target THEN target ELSE source END AS target,
 SUM(CASE WHEN source < target THEN units ELSE -units END) AS Diff, 
    model
FROM  movements
GROUP BY
 CASE WHEN source < target THEN source ELSE target END,
 CASE WHEN source < target THEN target ELSE source END,
    model
person Martin Smith    schedule 08.05.2010

arrow_upward
4
arrow_downward

Вы можете сделать это с помощью двух подзапросов, которые суммируют движения в каждом направлении, а затем объединяют и суммируют результаты этих двух подзапросов:

SELECT source, target, model, SUM(units)
FROM (
    SELECT source, target, model, SUM(units) AS units
    FROM movements
    WHERE source < target
    GROUP BY source, target, model
    UNION ALL
    SELECT target, source, model, SUM(-units) AS units
    FROM movements
    WHERE source > target
    GROUP BY source, target, model
) T1
GROUP BY source, target, model
person Mark Byers    schedule 08.05.2010
comment
Спасибо, позвольте мне попробовать. Как ваш запрос сравнивается с запросом, предоставленным @Martin Smith, который избегает использования UNION ALL (вместо этого используются некоторые предложения CASE)? - person Guido; 08.05.2010
comment
@Guido: его ответ сканирует таблицу только один раз, мой - дважды. Сколько у вас рядов? Является ли производительность проблемой? Я бы сказал, что мое предложение более ясно/читабельно, но это мое личное мнение и оно субъективно. - person Mark Byers; 08.05.2010
comment
Я немного подправил форматирование своего кода, чтобы сделать его немного более эстетичным, но я должен согласиться с тем, что 5 повторяющихся операторов CASE ухудшают читабельность. Я бы сказал, что мой, возможно, больше соответствует духу части вопроса с одним SQL-запросом, хотя и в силу того, что содержит только 1 SELECT :-) - person Martin Smith; 09.05.2010
comment
Я отмечаю ответ Мартина как действительный, так как в его запрос проще добавить дополнительные фильтры. Стол тоже довольно большой. Спасибо вам обоим. - person Guido; 10.05.2010