Улей агрегатные столбцы, основанные на различных условиях

голоса
1

Предположим, моя таблица выглядит следующим образом:

cust_id, domain, year, mon, day
1, google.au, 2018, 10, 1
2, virgin.com.au, 2018, 10, 1
3, hotmail.au, 2018, 10, 1
4, yahoo.au, 2018, 10, 1
1, foobar.au, 2018, 10, 1
3, foobar.com.au, 2018, 10, 1
15, haha.com, 2018, 10, 1
11, hehe.net, 2018, 10, 1

Мне нужно сгруппировать по году / MON / день и агрегатных столбцов, основанных на различных условиях:

1) count of distinct domains ending with .au but not .com.au
2) count of distinct domains ending with .com.au
3) count of distinct hostnames where cust_id in a specific list, let's assume (1, 2, 3, 4)
4) count of all distinct hostnames

Так что мой результат будет выглядеть следующим образом:

2018, 10, 1, 4, 2, 6, 8

Я склоняюсь к использованию подзапросов для каждого состояния, а затем присоединиться к ним:

select condition_1.year, condition_1.mon, condition_1.day, condition_1.c1, condition_3.c3, condition_4.c4
    from
(select year, mon, day, count(distinct domain) c1 from mytable where year = 2018 and mon = 10 and day = 1
and domain rlike '[.]au' and domain not rlike '[.]com[.]au'
group by year, mon, day) condition_1

full outer join

(select count(distinct domain) c2 from mytable where year = 2018 and mon = 10 and day = 1
and domain rlike '[.]com[.]au') condition_2

full outer join

(select count(distinct domain) c3 from mytable where year = 2018 and mon = 10 and day = 1
and cust_id in (1, 2, 3, 4)) condition_3

full outer join
(select count(distinct hostname) c4 from mytable where year = 2018 and mon = 10 and day = 1) condition_4

Это кажется ужасно неэффективным, хотя я не могу думать о лучшем способе. Заявление СЛУЧАЯ не будет работать здесь, как мне нужно разные счетчики. Как я могу добиться этого более эффективно?

Задан 27/11/2018 в 15:03
источник пользователем
На других языках...                            


2 ответов

голоса
1

Это может быть достигнуто с помощью регулярных выражений и с условной агрегацией.

select year,mon,day
,count(distinct case when domain regexp '(?<!\.com)\.au$' then domain end) as ends_with_au
,count(distinct case when domain regexp '\.com\.au$' then domain end) as ends_with_com_au
,count(distinct case when cust_id in (1,2,3,4) then domain end) as specific_cust
,count(distinct domain) as all_domains
from mytable
group by year,mon,day

Регулярное выражение (?<!\.com)\.au$использует отрицательное утверждение с просмотром назад для проверки предыдущих символов .auне .com. $метасимволом означает матч .auв последних 3 -х символов в строке. .должен быть отделался \.

Ответил 27/11/2018 в 15:18
источник пользователем

голоса
0

Использование collect_set()- он собирает определенный набор, игнорируя NULLs, используйте sizeфункцию , чтобы получить количество элементов (уже различны):

select
      year, mon, day,
      size(condition_1) as condition_1_cnt,
      size(condition_2) as condition_2_cnt,
      size(condition_3) as condition_3_cnt,
      size(condition_4) as condition_4_cnt    
 from
   (
    select year, mon, day,
       collect_set(case when domain rlike '(?<![.]com)[.]au' then domain end) condition_1,
       collect_set(case when domain rlike '[.]com[.]au'      then domain end) condition_2,
       collect_set(case when cust_id in (1, 2, 3, 4)         then domain end) condition_3,
       collect_set(hostname)                                                  condition_4
      from mytable 
     where year = 2018 and mon = 10 and day = 1
     group by year, mon, day
    )s;
Ответил 27/11/2018 в 16:23
источник пользователем

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more