#sql
#sql
Вопрос:
Представьте следующую таблицу
| EEID | CODE1 | VALUE1 | CODE2 | VALUE2 |
|------|-------|--------|-------|--------|
| 001 | ABC | 10 | NULL | 0 |
| 001 | ABC | 5 | NULL | 0 |
| 001 | DEF | 2 | NULL | 0 |
| 001 | NULL | 0 | 123 | 3 |
| 001 | NULL | 0 | 123 | 6 |
| 001 | NULL | 0 | 456 | 4 |
| 001 | NULL | 0 | 789 | 1 |
Пытаясь избежать специфичных для поставщика функций, каков наилучший способ «объединить» это с помощью SQL, чтобы выглядеть следующим образом
| EEID | CODE1 | VALUE1 | CODE2 | VALUE2 |
|------|-------|--------|-------|--------|
| 001 | ABC | 15 | 123 | 9 |
| 001 | DEF | 2 | 456 | 4 |
| 001 | NULL | 0 | 789 | 1 |
В принципе, мне нужно иметь возможность суммировать различные значения в столбцах кодекса.
Комментарии:
1. Почему 123 связан с ABC, а не с DEF?
2. @MartinSmith нет необходимости явно связывать его с каким-либо из столбцов CODE1. .
3. В вашем результате это так. Будет ли одинаково допустимым наличие ABC в той же строке результатов, что и 456, и DEF с 123?
4. @MartinSmith да, в результате любой из них будет работать нормально
5. Почему: 001 — DEF — 2 — 456 — 3 вместо 001 — DEF — 2 — 456 — 4 ?
Ответ №1:
Одним из способов было бы
SELECT COALESCE(T1.EEID, T2.EEID) AS EEID,
CODE1,
VALUE1,
CODE2,
VALUE2
FROM (SELECT EEID,
CODE1,
SUM(VALUE1) AS VALUE1,
ROW_NUMBER() OVER (ORDER BY CODE1) AS RN
FROM YourTable
WHERE CODE1 IS NOT NULL
GROUP BY EEID,
CODE1) T1
FULL JOIN (SELECT EEID,
CODE2,
SUM(VALUE2) AS VALUE2,
ROW_NUMBER() OVER (ORDER BY CODE2) AS RN
FROM YourTable
WHERE CODE2 IS NOT NULL
GROUP BY EEID,
CODE2) T2
ON ON T1.RN = T2.RN AND T1.EEID = T2.EEID;
Он не использует какие-либо функции, специфичные для поставщика, хотя MySQL еще не приступил к реализации оконных функций (или полного объединения, если на то пошло).
Или другим способом.
SELECT EEID,
MAX(CODE1) AS CODE1,
SUM(VALUE1) AS VALUE1,
MAX(CODE2) AS CODE2,
SUM(VALUE2) AS VALUE2
FROM (SELECT EEID,
CODE1,
VALUE1,
CODE2,
VALUE2,
DENSE_RANK() OVER (PARTITION BY EEID ORDER BY CASE WHEN CODE1 IS NULL THEN 1 ELSE 0 END, Code1) AS RN1,
DENSE_RANK() OVER (PARTITION BY EEID ORDER BY CASE WHEN CODE2 IS NULL THEN 1 ELSE 0 END, Code2) AS RN2
FROM YourTable) T
GROUP BY EEID,
CASE
WHEN CODE1 IS NULL
THEN RN2
ELSE RN1
END
HAVING COALESCE(MAX(CODE1), MAX(CODE2)) IS NOT NULL;
Комментарии:
1. Вау, это кажется действительно хорошим, и это работает для случая 2 столбцов.
2. (не хотел нажимать enter) теперь, если вы добавите 3-й и измените операторы case на NOT NULLS, я смог заставить его работать для 3 столбцов. Это здорово! Спасибо! Мои навыки работы с оконными функциями улучшились!
Ответ №2:
Если вы хотите суммировать значения в разных столбцах, я бы предложил другую структуру вывода:
select eeid, code1, 'code1' as which, sum(value1)
from t
where code1 is not null
group by eeid, code1
union all
select eeid, code2, 'code2' as which, sum(value2)
from t
where code2 is not null
group by eeid, code2;
Это помещает значения в разные строки, но это гораздо более простое решение вашей проблемы.
Это может быть расширено до сводной, используя group by и row_number() :
select eeid, max(code1) as code1, max(value1) as value1,
max(code2) as code2, max(value2) as value2
from ((select eeid, code1, sum(value1) as value1, NULL as code2, NULL as value2,
row_number() over (partitionby eeid order by code1) as seqnum
from t
where code1 is not null
group by eeid, code1
) union all
(select eeid, NULL, NULL, code2, sum(value2)
row_number() over (partition by eeid order by code2) as seqnum
from t
where code2 is not null
group by eeid, code2
)
) t12
group by eeid, seqnum;