Блог

Я пытаюсь назначить сегмент на xml_id основе того, в каком сегменте xml_id он имеет наиболее активные свойства. Я написал запрос к этому:

Я создаю таблицу с выбором следующим образом:

 create table schema.table4 as
select
    yyyy_mm_dd,
    xml_id,
    segment as xml_segment,
    max(property_count)
from(
    select
        t1.yyyy_mm_dd,
        t2.xml_id,
        t3.segment,
        count(t1.hotel_id) as property_count
    from(
        select
            yyyy_mm_dd,
            hotel_id
        from
            schema.table1
        where
            is_active = 1
            and yyyy_mm_dd = "2020-10-01"
    ) t1
    left join(
        select
            yyyy_mm_dd,
            hotel_id,
            xml_id
        from
            schema.table2
        where
            yyyy_mm_dd = "2020-10-01"
            and xml_id is not null
    ) t2 on t2.hotel_id = t1.hotel_id and t2.yyyy_mm_dd = t1.yyyy_mm_dd
    inner join
        schema.table3 t3 on t3.hotel_id = t1.hotel_id
    group by
        1,2,3
) x
group by
    1,2,3
  

Однако, когда я запрашиваю эту таблицу, я вижу несколько сегментов на xml_id . Я бы ожидал MAX() , что будет вставлен только сегмент с property_count . Почему это не так?

 select
    xml_id, count(*)
from
    schema.table4
  

Показывает несколько строк, в которых xml_id есть две и три строки. Мне нужно, чтобы это была только одна строка, а сегмент должен быть самым высоким property_count .

Ниже приведен пример вывода. xml_id наследует сегмент, в котором он имеет наибольшее количество свойств.

t1:

 | yyyy_mm_dd | hotel_id | is_active |
|------------|----------|-----------|
| 2020-10-01 | 1        | 1         |
| 2020-10-01 | 2        | 1         |
| 2020-10-01 | 3        | 1         |
| 2020-10-01 | 4        | 1         |
| 2020-10-01 | 5        | 1         |
| 2020-10-01 | 6        | 1         |
| 2020-10-01 | 7        | 0         |
  

t2:

 | yyyy_mm_dd | hotel_id | xml_id |
|------------|----------|--------|
| 2020-10-01 | 1        | 444    |
| 2020-10-01 | 2        | 444    |
| 2020-10-01 | 3        | 444    |
| 2020-10-01 | 4        | 920    |
| 2020-10-01 | 5        | 920    |
| 2020-10-01 | 6        | 920    |
| 2020-10-01 | 7        | null   |
  

t3:

 | hotel_id | segment |
|----------|---------|
| 1        | Home    |
| 2        | Core    |
| 3        | Core    |
| 4        | Core    |
| 5        | Home    |
| 6        | Home    |
| 7        | Chain   |
  

Ожидаемый результат:

 | yyyy_mm_dd | xml_id | segment |
|------------|--------|---------|
| 2020-10-01 | 444    | Core    |
| 2020-10-01 | 920    | Home    |
  

Если я правильно понимаю, вы хотите подсчитать количество активных файлов по дате xml_id и segment . Это основной запрос для этого подсчета:

 select t2.yyyy_mm_dd, t2.xml_id, t3.segment, count(*) as num_actives
from t2 join
     t1
     on t2.hotel_id = t1.hotel_id and
        t2.yyyy_mm_dd  = t1.yyyy_mm_dd join
     t3
     on t3.hotel_id = t2.hotel_id
where t1.is_active = 1
group by t2.yyyy_mm_dd, t2.xml_id, t3.segment;
  

Чтобы получить наибольшее значение, вы можете использовать оконные функции:

 select t.*
from (select t2.yyyy_mm_dd, t2.xml_id, t3.segment, count(*) as num_actives,
             row_number() over (partition by t2.yyyy_mm_dd, t2.xml_id order by count(*) desc) as seqnum
      from t2 join
           t1
           on t2.hotel_id = t1.hotel_id and
              t2.yyyy_mm_dd  = t1.yyyy_mm_dd join
           t3
           on t3.hotel_id = t2.hotel_id
      where t1.is_active = 1
      group by t2.yyyy_mm_dd, t2.xml_id, t3.segment
     ) t
where seqnum = 1;