Как я комбинирую два запроса (объединение все) в одну строку?
У меня есть щекотливая ситуация в попытке получить информацию от нескольких запросов в одну строку.
Рассмотрите следующую таблицу:
CpuUage:
Time time
Group char(10)
Subsys char(4)
Jobs int
Cpu int
содержание следующих данных:
Time Group Subsys Jobs Cpu
----- ------ ------ ---- ---
00:00 group1 NORM 1 101 (grp1-norm) A1
01:00 group1 SYS7 3 102 (grp1-sys7) A2
01:00 group1 NORM 5 104 (grp1-norm) A1
02:00 group1 NORM 7 108 (grp1-norm) A1
02:00 group2 NORM 9 116 (grp2-norm) B1
02:00 group3 SYS7 11 132 (grp3-sys7) C2
03:00 group1 SYS7 13 164 (grp1-sys7) A2
03:00 group1 IGNR 99 228 (grp1-ignr) --
Маркеры справа (например, A1) разделы в отчете, ниже которого каждая строка, как предполагается, влияет.
Мне нужен запрос, который может возвратить одну строку для каждой группы пользователей, но с одним условием. Значения для Jobs и Cpu должны войти в различные столбцы в зависимости от идентификатора подсистемы, и я только интересуюсь SYS7 и NORM идентификаторы подсистемы.
Так, например, нам нужно следующее ( A/B/1/2 биты являются перекрестной ссылкой назад на строки выше):
<------ 1 ------> <------ 2 ------>
Group NormJobs NormCpu Sys7Jobs Sys7Cpu
------ -------- ------- -------- -------
A: group1 13 313 16 266
B: group2 9 116 0 0
C: group3 0 0 11 164
Наше старое решение для создания отчетов могло выполнить несколько запросов (с a union all), затем выполните последующую обработку строки для объединения тех, которые имеют то же название группы, так, чтобы:
Group NormJobs NormCpu Sys7Jobs Sys7Cpu
------ -------- ------- -------- -------
group1 13 313 0 0
group1 0 0 16 266
были объединены вместе, вроде:
select groupname, sum(jobs), sum(cpu), 0, 0 from tbl
where subsys = 'NORM'
group by groupname
union all
select groupname, 0, 0, sum(jobs), sum(cpu) from tbl
where subsys = 'SYS7'
group by groupname
К сожалению, наше новое решение не позволяет выполнять последующую обработку, и все это должно быть сделано в SQL-запросе.
Учет того факта, что могут быть группы с SYS7 строки, NORM строки, оба или ни один, что лучший способ состоит в том, чтобы достигнуть этого?
Я думал о подзапросах таблицы из внешнего выбора, но это может иметь разветвления производительности.
Кроме того, это было бы болью, так как я должен буду заставить внешний запрос включать НОРМУ, и подсистемы SYS7 затем выполняют подзапрос для каждого поля (я не могу сделать внешний запрос только для NORM задания начиная с присутствия группы с только SYS7 строки не были бы пойманы тот путь).
Можете Вы советы директоров ткать какое-либо Ваше left-middle-inner-offside-join волшебство предложить эффективное решение?
Я предпочел бы агностическое поставщиком решение, но, если необходимо пойти определенные для поставщика, платформа является DB2. Однако, другие платформы могут, по крайней мере, дать мне идеи того, что попробовать так, я рад видеть их.
3 ответа
Я не понимаю проблему с подзапросом, кажется, что он должен быть таким же быстрым:
select
sub.gn as groupname,
sum(sub.nj) as NormJobs, sum(sun.nc) as NormCpu,
sum(sub.sj) as Sys7Jobs, sum(sub.sc) as Sys7Cpu
from (
select
groupname as gn,
sum(jobs) as nj, sum(cpu) as nc,
0 as sj, 0 as sc
from tbl
where subsys = 'NORM'
group by groupname
union all select
groupname as gn,
0 as nj, 0 as nc,
sum(jobs) as sj, sum(cpu) as sc
from tbl
where subsys = 'SYS7'
group by groupname
) as sub
group by sub.gn
order by 1
Это запрос к поворотной таблице. (Ищите, если вам нужна дополнительная информация.)
Структура запроса, которую вы хотите, строится следующим образом:
SELECT groupname,
SUM(CASE WHEN subsys = 'NORM' THEN jobs ELSE 0 END) AS NormJobs,
SUM(CASE WHEN subsys = 'NORM' THEN cpu ELSE 0 END) AS NormCpu,
SUM(CASE WHEN subsys = 'SYS7' THEN jobs ELSE 0 END) AS Sys7Jobs,
SUM(CASE WHEN subsys = 'SYS7' THEN cpu ELSE 0 END) AS Sys7Cpu,
SUM(CASE WHEN subsys NOT IN ('NORM', 'SYS7') THEN jobs ELSE 0 END) AS OtherJobs,
SUM(CASE WHEN subsys NOT IN ('NORM', 'SYS7') THEN cpu ELSE 0 END) AS OtherCpu
FROM ???
GROUP BY groupname
Это типичный поворотный запрос - вот как это сделать с CASE утверждениями:
SELECT t.group,
SUM(CASE
WHEN t.subsys = 'NORM' THEN t.jobs
ELSE NULL
END CASE) AS NormJobs,
SUM(CASE
WHEN t.subsys = 'NORM' THEN t.cpu
ELSE NULL
END CASE) AS NormCpu,
SUM(CASE
WHEN t.subsys = 'SYS7' THEN t.jobs
ELSE NULL
END CASE) AS Sys7Jobs,
SUM(CASE
WHEN t.subsys = 'SYS7' THEN t.cpu
ELSE NULL
END CASE) AS Sys7Cpu
FROM CPUUSAGE t
GROUP BY t.group
К сожалению, утверждения CASE DB2 должны заканчиваться на END CASE, когда Oracle/SQL Server/MySQL/Postgres этого не делает. Ну, PLSQL поддерживает END CASE...
Также существует синтаксис PIVOT, который также поддерживается на Oracle 11g, и SQL Server 2005+.
.