Блог

Главная - Вопросы по программированию - как избежать дубликатов в запросе улья

как избежать дубликатов в запросе улья

#sql #hive #case #hiveql

#sql #улей #случай #hiveql

Вопрос:

У меня есть две таблицы:

таблица1

 the_date | my_id |

02/03/2021,123
02/03/2021, 1234
02/03/2021, 12345

таблица2

 the_date | my_id |seq | txt
02/03/2021, 1234, 1 , 'OK'
02/03/2021, 12345, 1, 'OK'
02/03/2021, 12345, 2, 'HELLO HI THERE'
02/03/2021, 123456, 1, 'Ok'

Вот мой код:

 WITH AB AS (
SELECT  A1.my_id
FROM DB1.table1 A1 , DB1.MSG_REC A2  WHERE
A1.my_id=A2.my_id
),
BC AS (
SELECT  AB.the_date
COUNT ( DISTINCT (CASE WHEN (TXT like '%OK%') THEN AB.my_id ELSE NULL END )) AS
CASE1 ,
COUNT ( DISTINCT (CASE WHEN (TXT like '%HELLO HI THERE%') THEN AB.my_id ELSE NULL END )) AS
CASE2
FROM AB left JOIN DB1.my_id BC ON AB.my_id =BC.my_id

Проблема, которая вытекает из вышесказанного, заключается в том, что я дважды перебираю значение ‘12345’, потому что оно удовлетворяет обоим операторам case.

Это приводит к дублированию данных при захвате метрик подсчетов. Есть ли способ выполнить первый случай, а затем выполнить второй случай, но исключить цикл любой из записей «my_id» из первого случая.

Так, например, когда пришло время запускать приведенный выше сценарий и выполняется первый случай, он подберет приведенные ниже записи, и количество будет равно 3

  02/03/2021, 1234, 1 , 'OK'
  02/03/2021, 12345, 1, 'OK'
 02/03/2021, 123456, 1, 'Ok

Во втором случае должен выполняться только цикл по приведенным ниже записям, и количество будет только 1

  02/03/2021, 12345, 2, 'HELLO HI THERE'

ЗНАЧЕНИЕ CASE1 будет равно 4, а значение CASE2 — 2, если я не создам условие для обхода этой проблемы. Какие-либо советы или предложения?

Комментарии:

1. Этот запрос нуждается в некоторой работе — 1) столбец «the_date» не указан в common table expression (cte) AB , 2) cte BC нуждается в предложении «group by» в фантомном столбце the_date (см. 1) — 3) cte BC должен быть закрыт — 4) оператор должензавершите с помощью select * из BC 5) В cte AB — означает ли «table2» быть MSG_REC ?

Ответ №1:

Назначьте регистр каждому вашему идентификатору перед ОТДЕЛЬНОЙ агрегацией. После этого выполните отдельную агрегацию, таким образом, вы устраните одинаковые идентификаторы, учитываемые в разных случаях. Смотрите Комментарии в коде:

 select --do final distinct aggregation
      count(distinct (case when assigned_case='CASE1' then my_id else null end ) ) as CASE1,
      count(distinct (case when assigned_case='CASE2' then my_id else null end ) ) as CASE2
from 
(
select my_id
       --assign single CASE to all rows with the same id based on some logic:
        case when case1_flag = 1 then 'CASE1' 
            when case1_flag = 1 then 'CASE2'
           else NULL
        end as assigned_case
from
(--calculate all CASE flags for each ID 
select AB.my_id, 
       max(CASE WHEN (TXT like '%OK%') THEN 1 ELSE NULL END)  over (partition by AB.my_id) as case1_flag
       max(CASE WHEN (TXT like '%HELLO HI THERE%') THEN 1 ELSE NULL END)  over (partition by AB.my_id) as case2_flag
from ...
) s
) s