Это довольно сложно, поэтому покажу всю свою работу:
Определения таблиц
create table parts
(part_number Varchar(64),
load_date Date,
total_qty Dec(5,0));
create table locations
(part_number Varchar(64),
condition Char(1),
location Char(3),
qty Dec(5,0));
insert into parts
values ('m-123', '1994-01-02', 32),
('1234Cf', '2001-08-09', 3),
('wf9-2', '2016-04-21', 14);
insert into locations
values ('m-123', 'U', 'A02', 2),
('1234Cf', 'S', 'A02', 3),
('m-123', 'U', 'B01', 1),
('wf9-2', 'S', 'A06', 7),
('m-123', 'S', 'A18', 29),
('wf9-2', 'U', 'F16', 7);
Запрос:
with -- CTE's
-- This collects locations into a comma seperated list
tmp (part_number, condition, location, csv, level) as (
select part_number, condition, min(location),
cast(min(location) as varchar(128)), 1
from locations
group by part_number, condition
union all
select a.part_number, a.condition, b.location,
a.csv || ',' || b.location, a.level + 1
from tmp a
join locations b using (part_number, condition)
where a.csv not like '%' || b.location || '%'
and b.location > a.location),
-- This chooses the correct csv list, and adds quantity for the condition
tmp2 (part_number, condition, csv, qty) as (
select t.part_number, t.condition, t.csv,
(select sum(qty) qty
from locations
where part_number = t.part_number
and condition = t.condition)
from tmp t
where level = (select max(level)
from tmp
where part_number = t.part_number
and condition = t.condition))
-- This is the final select that combines the parts file with
-- the second stage CTE and arranges things horizontally by condition
select p.part_number, p.load_date,
(select sum(qty)
from locations
where part_number = p.part_number) as total_qty,
coalesce(u.csv, '') as u_loc,
coalesce(u.qty, 0) as uqty,
coalesce(s.csv, '') as s_loc,
coalesce(s.qty, 0) as sqty
from parts p
left outer join tmp2 u
on u.part_number = p.part_number and u.condition = 'U'
left outer join tmp2 s
on s.part_number = p.part_number and s.condition = 'S'
order by p.load_date;
EDIT Мне пришлось добавить сюда несколько дополнительных битов, чтобы поддерживать более двух местоположений для части/условия, и я сделал имена столбцов в CTE более согласованными. Итак, позвольте мне немного объяснить, этот запрос состоит из 3 частей, 2 CTE и запроса, вы можете видеть, что эти три части разделены комментариями. Первый CTE является рекурсивным CTE. Его цель состоит в том, чтобы создать список местоположений, разделенных запятыми. Вы должны иметь возможность запускать select сам по себе, чтобы увидеть, что он делает. tmp — это имя таблицы, part_number, condition, csv и level — это имена столбцов. Рекурсивному CTE требуется SELECT для заполнения CTE и UNION ALL с SELECT, который заполняет следующие детали. В этом случае заполнение SELECT извлекает номер детали, условие и первое местоположение (в алфавитном порядке) для этой комбинации. уровень установлен на 1. Если вы запустите только предварительный выбор, вы получите:
part_number condition location csv level
----------- --------- -------- --- -----
1234Cf S A01 A02 1
m-123 S A18 A18 1
m-123 U A02 A02 1
wf9-2 U F16 F16 1
wf9-2 S A06 A06 1
Обратите внимание на одну строку на деталь/состояние. Оставшаяся часть рекурсивного CTE заполнит оставшиеся места в csv, но фактически добавит дополнительные записи, поэтому нам нужно отфильтровать результаты здесь и позже. Таким образом, записи обрабатываются по мере их добавления. Первые строки, перечисленные выше, объединяются с файлом местоположения по номеру детали и условию. Обратите внимание, что в начальном выборе у меня есть приведение второго min(location) к varchar(128). Это оставляет место для расширения столбца CSV. Без этого он все равно будет расширяться, но не настолько, чтобы удерживать более 2-х локаций.
Второй выбор в рекурсивном CTE объединяет запятую и следующее местоположение в конце CSV. Конкретный бит, который делает это, — a.csv || ',' || b.location. Он также увеличивает столбец уровня. Это помогает нам отслеживать, где мы находимся в запросе. В конце концов, мы хотим использовать строку с наибольшим значением level. У нас также есть способ завершить рекурсию и несколько фильтров для уменьшения количества строк, добавляемых во временный результирующий набор. Если у нас есть 2 местоположения, A02 и B02, оставленных непроверенными, мы получим следующие строки: A02, A02,A02, A02,B02, A02,A02,A02, A02,B02,A02, A02,A02,B02, A02,B02,B02, ... до бесконечности. Антидупликационного фильтра where a.csv not like '%' || b.location || '%' достаточно для двух местоположений, чтобы завершить рекурсию и минимизировать строки, как указано выше, для местоположений A02 и B02, с антидупликационным фильтром мы получим строки A02 и A02,B02. Обратите внимание, что ни один из других результатов из первого примера с повторяющимися местоположениями не возвращается. Добавление третьего местоположения C02 даст с фильтром предотвращения дублирования следующие строки: A02, A02,B02, A02,C02, A02,B02,C02, A02,C02,B02. Здесь нет дубликатов, но у нас есть избыточные строки, и по мере добавления местоположений становится все хуже. Здесь нам нужен способ обнаружения этих избыточных строк. Поскольку мы начинаем с наименьшего номера местоположения, мы всегда можем убедиться, что местоположения, добавленные к CSV, больше, чем ранее добавленное местоположение. Для этого все, что нам нужно сделать, это включить в результирующий набор столбец, указывающий, какой столбец был добавлен (мы могли бы опросить CSV, но это сложнее). Вот почему нам нужен столбец location в tmp. Тогда мы можем написать фильтр b.location > a.location. В приведенном выше примере с тремя местоположениями этот фильтр не позволяет строке A02,C02,B02 оставить только одну строку со всеми тремя местоположениями. Добавление более трех местоположений в файл местоположений приведет к еще большему увеличению количества строк в TMP, но для каждой детали и условия будет только одна строка со всеми местоположениями, и она будет содержать все местоположения в порядке возрастания.
Второй CTE делает две вещи. Во-первых, он фильтрует TMP, чтобы удалить все строки, кроме строк, содержащих все местоположения для данной детали/условия. Во-вторых, он накапливает общее количество для каждой детали/состояния.
Бит, который выполняет фильтрацию, находится в предложении where:
where level = (select max(level)
from tmp
where part_number = t.part_number
and condition = t.condition))
Довольно прямолинейно. Бит, который накапливает общее количество для детали/состояния, также является простым для понимания подзапросом:
(select sum(qty) qty
from locations
where part_number = t.part_number
and condition = t.condition)
Последняя часть этого чудовищного запроса — основной выбор. Он объединяет файл частей с результатами второго CTE, чтобы сформировать окончательный набор результатов:
select p.part_number, p.load_date,
(select sum(qty) from locations where part_number = p.part_number) as total_qty,
coalesce(u.csv, '') as u_loc, coalesce(u.qty, 0) as uqty,
coalesce(s.csv, '') as s_loc, coalesce(s.qty, 0) as sqty
from parts p
left outer join tmp2 u
on u.part_number = p.part_number and u.condition = 'U'
left outer join tmp2 s
on s.part_number = p.part_number and s.condition = 'S'
order by p.load_date
Обратите внимание на подзапрос для получения общего количества из таблицы locations. Вы можете использовать поле tqty в parts, но это может не синхронизироваться с фактическими количествами в таблице locations. Кроме того, есть два левых внешних соединения с tmp2, одно для условия U, а другое для условия S. Они создают горизонтальный массив местоположения/количества в строке результата. И последнее — это функции coalesce. Они дают нулевые значения (когда результат внешнего соединения отсутствует) значение по умолчанию.
Конец ИЗМЕНЕНИЯ
Окончательный результат:
part_number load_date tqty u_loc uqty s_loc sqty
----------- ---------- ---- ------- ---- ----- ----
m-123 1994-01-02 32 A02,B01 3 A18 29
1234Cf 2001-08-09 3 0 A02 3
wf9-2 2016-04-21 14 F16 7 A06 7
Примечание XMLAGG и XMLSERIALIZE стали доступны в DB2 для i v7.1, а LISTAGG стали доступны в DB2 для i v7.2. Самая последняя версия по состоянию на 09.08.2017 — v7.3. Поскольку вы используете v5r4, вероятно, вам потребуется не только программное обеспечение, но и аппаратное обновление, чтобы получить актуальную версию.
person
jmarkmurphy
schedule
09.08.2017
