Блог

У меня есть следующая таблица:

 CREATE TABLE example (  accountNumber,  z, --long string that contains accountID within  status, -- red green or blue  partition_0,    ); INSERT INTO example Values(123, 'abcde: xyz, accountID: 10434', 'green', 20211010), (124, 'abcde: xhf, accountID: 25949', 'green', 20211010),--accountNumber 124 would be excluded because it has gt;= different accountID (25949 and 34322) (124, 'abcde: xhf, accountID: 34322', 'green', 20211010), (124, 'abcde: xhf, accountID: 25949', 'red', 20211011), (154, 'abcde: dasd, accountID: 43341', 'green', 20211010);  

Эта таблица может содержать 100 000 000-gt;10 000 000 000 строк. Иногда система, заполняющая эту таблицу, работает неправильно, и я получаю номера счетов с несколькими учетными записями. Они должны быть 1:1. Те, которые не являются артефактами, являются артефактами, и я хотел бы отфильтровать их. У меня есть следующая настройка:

 with cte as ( select accountNumber,  upper(substr(z,strpos(z,'accountID') 11,5)) as accountID, partition_0 from example),  cte_limiter_raw as ( select accountNumber, count(distinct accountID) as countAccountID from cte group by accountNumber), --I chose to put the limiter here because I could call accountID instead of the substr function  cte_limiter as ( select accountNumber from cte_limiter_raw where countAccountID = 1)  select accountNumber, accountID, partition_0, sum(case when status like 'Green' then 1 end) as countGreen, sum(case when status like 'Red' then 1 end) as countRed from cte where accountNumber in (select accountNumber from cte_limiter) group by accountNumber, accountID, partition_0  

Это кажется немного неуклюжим. Есть ли более эффективный способ сделать это? Я спрашиваю об Амазонке Афине.

Если я вас правильно понимаю, вы хотите это сделать:

 SELECT accountNumber FROM example GROUP BY accountNumber HAVING COUNT(DISTINCT UPPER(SUBSTR(z, STRPOS(z, 'accountID')   11, 5))) gt; 1  

т. е. найдите все accountNumber , для которых существует более одного отдельного значения подстроки in z .

Вышеизложенное-более простой способ выражения запроса, но это полное сканирование таблицы. Если у вас есть какой-то способ выполнить запрос только по новым данным, это может ограничить влияние.

Ваш запрос выполнит два полных сканирования таблицы, так как он выполняется SELECT … FROM cte дважды. Афина не будет повторно использовать промежуточные результаты (это предварительное ограничение, это намного сложнее, чем кажется распределенному движку). Вы могли бы переписать свой запрос, чтобы избежать коррелированного подзапроса, но я думаю, что использовать HAVING его проще.

HAVING похоже WHERE , но вычисляется в другой фазе, чтобы вы могли выражать свойства группы. Другой способ написать то же самое (и который получает тот же план запроса) выглядит так:

 WITH counts AS (  SELECT  accountNumber,  COUNT(DISTINCT UPPER(SUBSTR(z, STRPOS(z, 'accountID')   11, 5))) AS count  FROM example  GROUP BY accountNumber ) SELECT accountNumber FROM counts WHERE "count" gt; 1