У меня есть запрос, который извлекает некоторые группы в Moodle:
select
g.id groupid,
g.name groupname,
count(distinct gm.userid)
from prefix_groups g
left join prefix_groups_members gm on gm.groupid = g.id
group by g.id
выход
| groupid | groupname | count(distinct gm.userid) |
|---------|-----------|---------------------------|
| 1 | 20NEW-4A | 6 |
| 2 | 18PAR-5F | 3 |
| 3 | 20BER-6G | 2 |
| 4 | 50NEV-6G | 6 |
| 5 | 34HOG-5Q | 77 |
| 6 | 10BAT-GG | 5 |
| etc. | etc. | etc. |
Я хочу добавить столбец с именем location
, в котором указано местоположение группы (в соответствии со стандартом имени группы на этой платформе, например, BER = Berlin). У меня есть более 100 таких местоположений для фильтрации. Я знаю, что могу добавить их все в статус case
и назвать это очень долгим днем (например, ниже), но я хочу сделать это максимально эффективно. В этом случае я не могу создать для этого временную таблицу. Есть идеи?
select
g.id groupid,
g.name groupname,
case when substring(g.name, 3, 3) = 'BER'
then 'Berlin'
when substring(g.name, 3, 3) = 'NEW'
then 'Newcastle'
-- etc.
end location,
count(distinct gm.userid)
from prefix_groups g
left join prefix_groups_members gm on gm.groupid = g.id
group by g.id
выход:
| groupid | groupname | location | count(distinct gm.userid) |
|---------|-----------|-----------|---------------------------|
| 1 | 20NEW-4A | Newcastle | 6 |
| 2 | 18PAR-5F | Paris | 3 |
| 3 | 20BER-6G | Berlin | 2 |
| 4 | 50NEV-6G | Neverland | 6 |
| 5 | 34HOG-5Q | Hogwarts | 77 |
| 6 | 10BAT-GG | Bath | 5 |
| etc. | etc. | etc. | etc. |
Сохраните отношение (код-местоположение) в отдельную таблицу и присоедините к своему запросу.
@juergend - g.name
- это имя группы в соответствии с первым запросом и его выводом.
@ Акина, не уверен, что ты пропустил эту часть моего поста: In this instance, I cannot create a temporary table to do this.
. Не стоит вдаваться в подробности, почему здесь, но я в основном ограничен использованием только операторов select
для выполнения этого запроса.
Ответ обновлен.
Если вы хотите, чтобы ваш запрос был более читабельным, вы можете поместить логику своего местоположения в функцию (удобно, если вы используете ее более чем в одном месте), поэтому вы должны написать ее в своем запросе, например SELECT... yourFunc(g .name) location — это, конечно, просто сократит ваш запрос, а не улучшит производительность.
Нормализуйте свою схему, чтобы вы не присоединялись к подстроке
CREATE TABLE locations ( code CHAR(3), location VARCHAR(255) );
INSERT INTO locations VALUES ('BER', 'Berlin'), ('NEW', 'Newcastle'), ... ;
а потом
select
g.id groupid,
g.name groupname,
locations.location,
count(distinct gm.userid)
from prefix_groups g
left join prefix_groups_members gm on gm.groupid = g.id
LEFT JOIN locations ON substring(g.name, 3, 3) = locations.code
group by g.id, g.name, locations.location;
Для повышения производительности вы можете добавить сгенерированный столбец в prefix_groups
и присоединиться к нему, избегая функции в состоянии соединения.
В этом случае я не могу создать временную таблицу
Если это так, то, чтобы избежать длинного CASE, вы можете использовать выражение вроде:
ELT(FIND_IN SET(SUBSTRING(g.name, 3, 3), 'BER,NEW,...'), 'Berlin', 'Newcastle', ...) AS location
Не уверен, что это будет эффективнее. Но короче будет с гарантией.
PS. Вы можете использовать другой вариант функции CASE:
case substring(g.name, 3, 3) when 'BER' then 'Berlin'
when 'NEW' then 'Newcastle'
-- etc.
end location,
Разве не должно быть substring(g.name, 1, 3)
?
@tcadidot0 См. пример значений в вопросе - необходимо удалить первые две цифры.
Я вижу, мой плохой. Я тестировал в скрипке соответствующий пример данных и его работу. +1 за этот очень интересный способ!
Извините за задержку с ответом. Отметив это как ответ. Я обнаружил, что ваш последний вариант (с использованием регистра и подстроки) работает наиболее плавно. Спасибо за вашу помощь!!
Каково полное содержание
g.name
? Мы видим только подстроки