MaxCompute:Plano de otimização para cálculos de métricas de longo período

SELECT item_id,
       COUNT(DISTINCT visitor_id) AS ipv_uv_1d_001
FROM vistor_item_detail_log
WHERE ds <= ${bdp.system.bizdate}
  AND ds >= TO_CHAR(DATEADD(TO_DATE(${bdp.system.bizdate},'yyyymmdd'),-29,'dd'),'yyyymmdd')
GROUP BY item_id;

Funcionamento

Em vez de consultar 30 partições de logs brutos, pré-agregue os logs de cada dia em uma tabela de resumo. Cada linha nessa tabela representa um par único (item_id, visitor_id) para aquele dia. Consultar 30 partições de dados pré-agregados custa muito menos do que consultar 30 partições de logs brutos, pois a etapa de pré-agregação elimina linhas duplicadas de alta cardinalidade antes da execução da consulta de janela deslizante.

Etapa 1: Crie o resumo diário

Execute este script como um nó agendado diariamente no DataWorks, antes dos nós de métricas:

INSERT OVERWRITE TABLE mds_itm_vsr_xx (ds='${bdp.system.bizdate} ')
SELECT item_id,
       visitor_id,
       COUNT(1) AS pv
FROM (
    SELECT item_id,
           visitor_id
    FROM vistor_item_detail_log
    WHERE ds = ${bdp.system.bizdate}
    GROUP BY item_id, visitor_id
) a;

Esse comando grava uma linha deduplicada por par (item_id, visitor_id) na tabela mds_itm_vsr_xx referente à partição de data atual.

Etapa 2: Consulte a tabela de resumo

Com a tabela de resumo disponível, sua consulta de janela deslizante processa um volume de dados muito menor:

SELECT item_id,
       COUNT(DISTINCT visitor_id) AS uv,
       SUM(pv) AS pv
FROM mds_itm_vsr_xx
WHERE ds <= '${bdp.system.bizdate} '
  AND ds >= TO_CHAR(DATEADD(TO_DATE('${bdp.system.bizdate} ','yyyymmdd'),-29,'dd'),'yyyymmdd')
GROUP BY item_id;

Funcionamento

A abordagem de tabela intermediária ainda varre 30 partições a cada execução. A acumulação incremental vai além: mescla todos os dados históricos em uma única partição de acumulação e anexa apenas os novos dados diariamente. Assim, as consultas de janela deslizante leem apenas uma partição.

Essa estratégia é mais eficaz quando o percentual de dados alterados diariamente é pequeno em relação ao conjunto histórico total, ou seja, quando a maior parte dos dados na partição de acumulação permanece estável entre as execuções.

Implementação: tabela de dimensão de compradores recorrentes

O caso de uso de compradores recorrentes (clientes que compraram nos últimos 30 dias) ilustra bem essa abordagem. Uma solução ingênua varre 30 partições de logs de faturamento em cada execução:

SELECT item_id,
       buyer_id AS old_buyer_id
FROM buyer_item_detail_log
WHERE ds < ${bdp.system.bizdate}
  AND ds >= TO_CHAR(DATEADD(TO_DATE(${bdp.system.bizdate},'yyyymmdd'),-29,'dd'),'yyyymmdd')
GROUP BY item_id,
         buyer_id;

Alternativamente, mantenha uma tabela de dimensões onde cada linha representa o relacionamento entre um comprador e um item, registrando campos como hora da primeira compra, hora da última compra, total de itens comprados e gasto total. Atualize-a diariamente com os logs de faturamento do dia anterior.

Para determinar se um comprador é recorrente, verifique se o campo last_purchase_time está dentro dos últimos 30 dias. Isso substitui a varredura de 30 partições de logs de faturamento por uma consulta em partição única na tabela de dimensões, eliminando a deduplicação por varredura completa a cada execução.