Выбор части строки из столбца в улье и получение счета

У меня есть столбец «Платформа» со строками, как показано ниже.

имя3:"строка1/строка2/строка3/с:1.2.1/ABCD/XYZ".

И у меня есть другое имя столбца с именем «name2». Мой стол выглядит так

' id       |    name2     |   name3
-----------+--------------+---------------------
 1         |      x1      | string1/string2/string3/s:1.2.1/ABCD/XYZ
 2         |      x1      | string1/string2/string3/S:2.2.1/ABCD/XYZ
 3         |      x2      | string5/string4/string3/s:1.1/ABCD/XYZ
 4         |      x3      | string1/string6/string7/m:0.2.2/ABCD/XYZ
 5         |      x2      | string1/string2/string3/S:2.2.0/ABCD/XYZ'

Я хочу получить количество событий на основе подстрок платформы. нравится

'name3     | X1    |   X2 |    X3    |

string4        |       |   1  |          |
string6        |       |      |   1      |'

или если я хочу получить подсчет, основанный только на «Android» или «iOS», как я могу это сделать?

'name3     | X1    |   X2 |    X3    |

 string4          |       |   1  |          |
 string1      |   2   |   1  |   1      |'

Запрос, который я использую для подсчета, приведен ниже. Он отлично работает для подсчета событий, но не может понять, как получить подсчет на основе подстрок.

'select name2,
    count(1) AS total
from table1 where name2='x1' OR name2='x2' OR name2='x3'
group by name2;'

Какие-либо предложения?


hive hiveql
person dheee    schedule 06.05.2014    source источник
comment
На мой взгляд, этот дизайн базы данных совершенно неверен. Я бы создал таблицу со столбцом платформы (имя), а затем выполнил отношение 1-N (1 событие-(одна или несколько платформ)). Тогда было бы легко сделать то, что вы хотите достичь.   -  person bestprogrammerintheworld    schedule 06.05.2014
comment
Таблица находится в той форме, которую я упомянул, и я не могу ее изменить. Можем ли мы сделать это, не меняя дизайн? например, можем ли мы использовать любые регулярные выражения?   -  person dheee    schedule 06.05.2014
comment
Какую форму вы упомянули?   -  person bestprogrammerintheworld    schedule 06.05.2014
comment
Первый с идентификатором столбца, событием и платформой.   -  person dheee    schedule 07.05.2014

Ответы (2)


arrow_upward
0
arrow_downward

Прежде всего, я бы разделил эту строку на представление с фактическими столбцами. Что-то вроде:

create view my_view as select
id,
event,
regexp_extract(platform, '^(.*)/(.*)/(.*)/(.*)/(.*)/(.*)/(.*)$', 1) as os,
regexp_extract(platform, '^(.*)/(.*)/(.*)/(.*)/(.*)/(.*)/(.*)$', 2) as brand,
regexp_extract(platform, '^(.*)/(.*)/(.*)/(.*)/(.*)/(.*)/(.*)$', 3) as model,
regexp_extract(platform, '^(.*)/(.*)/(.*)/(.*)/(.*)/(.*)/(.*)$', 4) as lte,
regexp_extract(platform, '^(.*)/(.*)/(.*)/(.*)/(.*)/(.*)/(.*)$', 5) as abcd,
regexp_extract(platform, '^(.*)/(.*)/(.*)/(.*)/(.*)/(.*)/(.*)$', 6) as user,
regexp_extract(platform, '^(.*)/(.*)/(.*)/(.*)/(.*)/(.*)/(.*)$', 7) as xyz
from my_table;

Тогда запросить это представление будет проще. Вы также можете просто использовать это как внутренний запрос. Этот тип запроса «счет-где», когда вы хотите, чтобы разные счетчики в разных столбцах, был довольно распространенным использованием. Лучший способ, который я знаю, это сделать с помощью шаблона:

sum(if( [condition] , 1, 0))

Итак, для ваших примеров это будет:

select os,
sum(if(event = 'x1', 1, 0)) as x1,
sum(if(event = 'x2', 1, 0)) as x2,
sum(if(event = 'x3', 1, 0)) as x3
from my_view
group by os;

Or:

select brand,
sum(if(event = 'x1', 1, 0)) as x1,
sum(if(event = 'x2', 1, 0)) as x2,
sum(if(event = 'x3', 1, 0)) as x3
from my_view
group by brand;

А вот приведенный выше запрос, но с использованием этого представления в качестве внутреннего запроса вместо фактического представления:

select brand,
sum(if(event = 'x1', 1, 0)) as x1,
sum(if(event = 'x2', 1, 0)) as x2,
sum(if(event = 'x3', 1, 0)) as x3
from (
  select
  id,
  event,
  regexp_extract(platform, '^(.*)/(.*)/(.*)/(.*)/(.*)/(.*)/(.*)$', 1) as os,
  regexp_extract(platform, '^(.*)/(.*)/(.*)/(.*)/(.*)/(.*)/(.*)$', 2) as brand,
  regexp_extract(platform, '^(.*)/(.*)/(.*)/(.*)/(.*)/(.*)/(.*)$', 3) as model,
  regexp_extract(platform, '^(.*)/(.*)/(.*)/(.*)/(.*)/(.*)/(.*)$', 4) as lte,
  regexp_extract(platform, '^(.*)/(.*)/(.*)/(.*)/(.*)/(.*)/(.*)$', 5) as abcd,
  regexp_extract(platform, '^(.*)/(.*)/(.*)/(.*)/(.*)/(.*)/(.*)$', 6) as user,
  regexp_extract(platform, '^(.*)/(.*)/(.*)/(.*)/(.*)/(.*)/(.*)$', 7) as xyz
  from my_table
) t
group by brand;
person Joe K    schedule 06.05.2014
comment
Привет, я думаю, ты прав. Но я только что сделал небольшое изменение в строке. я добавил подстроку типа sfour:4.2.2. Можете ли вы взглянуть на полную строку выше, дайте мне знать, как меняется регулярное выражение для этого. Большое спасибо - person dheee; 07.05.2014
comment
Что ж, то же самое регулярное выражение теперь будет захватывать всю строку sfour:4.2.2 как одно поле. Если вы хотите разделить его на два поля, например. sfour и 4.2.2, это будет: '^(.*)/(.*)/(.*)/(.*):(.*)/(.*)/(.*)/(.*)$', то есть восемь полей, которые вам нужно будет выбрать (поскольку есть восемь групп захвата) вместо семи. - person Joe K; 07.05.2014
comment
Привет, Джо, быстрый вопрос. Для вышеуказанной проблемы у меня есть два имени столбца для того же формата строки, что и упомянутый выше. одно имя столбца — платформа, а другое — тип платформы. Данные в обоих этих столбцах похожи и имеют одинаковый формат строки («строка1/строка2/строка3/s:1.2.1/ABCD/XYZ»). Есть ли способ включить оба этих столбца в один запрос? Заранее спасибо - person dheee; 12.05.2014

arrow_upward
0
arrow_downward

Надеюсь это поможет...

Запрос:

 select a.platforms, a.event, count(1) as count from 
 (select regexp_extract(platform, '^(.*)/(.*)/(.*)/(.*)/(.*)/(.*)/(.*)$',1) 
 as platforms, event from table1) a group by a.platforms, a.event;

Выход:

platforms       event   count
android         x1      2
android         x2      1    
android         x3      1
ios             x2      1
person Arun    schedule 06.05.2014