Замена функции слияния в ClickHouse

Question

Я новичок в ClickHouse и пытаюсь использовать для обработки статистики нашего проекта. Некоторые настольные программы отправляют информацию на наши серверы, и нам необходимо объединить операционные системы в короткий список. Этот пример запроса:

SELECT OS
FROM Req
GROUP BY OS

┌─OS──────────────────────────────────────────────────────────────────────────────┐
│ Майкрософт Windows 10 Корпоративная 2016 с долгосрочным обслуживанием           │
│ Майкрософт Ознакомительная версия Windows Server 2012 Standard                  │
│ Майкрософт Windows 10 Домашняя для одного языка                                 │
│ Microsoft Windows 8.1 Enterprise                                                │
│ Майкрософт Windows 8 Корпоративная Прогрессивная                                │
│ Microsoft Windows Server 2008 R2 Standard                                       │
│ Microsoft Windows 8.1 mit Bing                                                  │
│ Microsoft Windows 10 Home                                                       │
│ Microsoft Windows 8 Enterprise N                                                │
│ Майкрософт Windows 8.1 Профессиональная                                         │
│ Майкрософт Windows 8 Профессиональная                                           │
│ Microsoft Windows 7 Rеактивная                                                  │
│ Microsoft Windows 10 Pro Insider Preview                                        │

должны быть агрегированы, чтобы очистить список:

8       xxx
8.1     yyy
2008    zzz
2008 R2 aaa

и так далее. Я не нашел функцию COALESCE и пытаюсь использовать извлечение для идентификации ОС по номерам версий:

 select extract(OS, ' 7 ') || extract(OS, ' 8.1 ') || extract(OS, ' 10 ') || extract(OS, ' 2008 R2 ') || extract (OS, ' 2008 ') || extract(OS, ' 2012 R2 ') || extract(OS, ' 2012 ')  as Value, count(distinct SID) from Req group by Value limit 100000;

Но! Поскольку Windows 2008 и Windows 2008 R2 имеют "2008" в строке версии, я получил этот результат:

┌─Value───────────┬─uniqExact(SID)─┐
│                 │            224 │
│  2012           │             17 │
│  10             │           1315 │
│  7              │           4282 │
│  2008           │             20 │
│  2012 R2  2012  │             57 │
│  2008 R2  2008  │            136 │
│  8.1            │            754 │
└─────────────────┴────────────────┘

Какую функцию мне нужно использовать в моем случае? Благодарю.

Answer 1

Найденный!

select OS, arrayFirst(x -> cast(position(OS, x) as UInt8), [' 8 ',' 8.1 ', '2008 R2', '2008'])
from Req
limit 1000;

(без CAST я получаю исключение: DB:: Exception: неожиданный тип столбца фильтра., странно...)

Answer 2

То, что вам нужно здесь, это мультифайл.

Если найдена строка "2012 R2", верните мне это, если "2012" верните мне это... и т. Д.

так что в вашем случае вы можете сделать что-то вроде этого:

multiIf(like(OS, '% 2008 R2 %'), extract(OS, ' 2008 R2 ') , like(OS, '% 2008 %'), extract (OS, ' 2008 '),  'OS_not_found') as Value

В основном это if if if, и вы можете добавить столько значений, сколько хотите, я просто использовал их, потому что я не хотел писать слишком много, но в вашем случае просто добавьте все значения ОС, которые вам нужны. Это немного многословно, но оно выполняет свою работу.

Функция:

like(OS, '% 2008 R2 %') 

возвращает true, если строка найдена, и false, в противном случае "%" является подстановочным знаком regex в clickhouse. Так как мультифик останавливается при первом совпадении, вы не получите две извлеченные строки с одинаковым значением.