Em análise de dados, problemas que envolvem "sequências consecutivas" são desafios clássicos em processos seletivos de empresas de tecnologia. Este artigo aborda como resolver um problema comum: encontrar o motorista com a maior sequência ininterrupta de avaliações de 5 estrelas dentro de cada semana.

O Problema

Considere uma tabela que registra as avaliações que os motoristas recebem dos passageiros. O objetivo é extrair o id_motorista que obteve a maior quantidade de avaliações 5 estrelas consecutivas em cada semana do ano. Se ocorrer qualquer avaliação abaixo de 5 estrelas, a contagem da sequência é reiniciada.

A estrutura simplificada da tabela é:

• id_motorista: Identificador único do condutor.
• ts_avaliacao: Timestamp de quando a avaliação foi realizada.
• nota: Valor da avaliação (de 1 a 5).

Lógica de Resolução

Para resolver problemas de registros consecutivos em SQL, a estratégia mais eficaz é criar um identificador de grupo. No caso de avaliações 5 estrelas, podemos definir que cada sequência de 5 estrelas pertence a um grupo identificado pelo número de avaliações "não-5 estrelas" que ocorreram antes dela.

Imagine o seguinte cenário para um motorista em uma semana:

1. Nota 5 (0 avaliações anteriores < 5) -> Grupo 0
2. Nota 5 (0 avaliações anteriores < 5) -> Grupo 0
3. Nota 4 (1 avaliação anterior < 5) -> Grupo 1
4. Nota 5 (1 avaliação anterrior < 5) -> Grupo 1
5. Nota 5 (1 avaliação anterior < 5) -> Grupo 1
6. Nota 5 (1 avaliação anterior < 5) -> Grupo 1

Agrupando pelo id_motorista, semana e id_grupo, conseguimos contar o tamanho de cada sequência de notas 5.

Simulação de Dados (Python)

Para validar a lógica SQL, podemos gerar um conjunto de dados sintético utilizando Python e NumPy, simulando o comportamento de motoristas e a distribuição de notas.

import pandas as pd
import numpy as np
from datetime import datetime, timedelta

# Configurações iniciais
np.random.seed(42)
n_motoristas = 50
data_inicio = datetime(2024, 1, 1)

registros = []

for m_id in range(n_motoristas):
    current_time = data_inicio
    # Gera entre 100 e 200 avaliações por motorista
    for _ in range(np.random.randint(100, 200)):
        current_time += timedelta(minutes=np.random.randint(30, 240))
        # Probabilidade de 90% para nota 5
        nota = np.random.choice([1, 2, 3, 4, 5], p=[0.02, 0.02, 0.02, 0.04, 0.9])
        registros.append({
            "id_motorista": f"MOT-{str(m_id).zfill(3)}",
            "ts_avaliacao": current_time,
            "nota": nota
        })

df_avaliacoes = pd.DataFrame(registros)
df_avaliacoes.to_csv("avaliacoes_motoristas.csv", index=False)

Implementação em SQL (Hive/SparkSQL)

Abaixo, utilizamos Common Table Expressions (CTEs) para estruturar a consulta em etapas lógicas: identificação de grupos, contagem de sequências e ranking final por semana.

WITH processamento_grupos AS (
    SELECT 
        id_motorista,
        -- Define a semana (ajustar formato conforme o dialeto SQL)
        DATE_FORMAT(ts_avaliacao, 'yyyy-ww') AS ano_semana,
        nota,
        -- Cria um identificador único para cada bloco consecutivo
        SUM(CASE WHEN nota < 5 THEN 1 ELSE 0 END) OVER (
            PARTITION BY id_motorista, DATE_FORMAT(ts_avaliacao, 'yyyy-ww') 
            ORDER BY ts_avaliacao ASC
        ) AS flag_grupo
    FROM tb_avaliacoes_uber
),
contagem_sequencias AS (
    SELECT 
        id_motorista,
        ano_semana,
        COUNT(*) AS tamanho_sequencia,
        -- Rankeia os motoristas com maiores sequências por semana
        RANK() OVER (PARTITION BY ano_semana ORDER BY COUNT(*) DESC) AS rnk
    FROM processamento_grupos
    WHERE nota = 5
    GROUP BY id_motorista, ano_semana, flag_grupo
)
SELECT 
    ano_semana,
    -- Coleta motoristas em caso de empate no primeiro lugar
    COLLECT_SET(id_motorista) AS motoristas_vencedores,
    MAX(tamanho_sequencia) AS max_consecutivas
FROM contagem_sequencias
WHERE rnk = 1
GROUP BY ano_semana
ORDER BY ano_semana;

Considerações Técnicas

• Janelas de Tempo: A função DATE_FORMAT com o parâmetro de semana pode variar entre bancos de dados. No Hive, o padrão geralmente inicia a semana no domingo ou segunda-feira, dependendo da configuração.
• Tratamento de Empates: O uso de RANK() permite capturar múltiplos motoristas caso eles tenham o mesmo número máximo de avaliações consecutivas.
• Eficiência: A lógica de SUM(...) OVER(...) evita a necessidade de Self-Joins complexos, tornando a consulta performática mesmo em grandes volumes de dados.
• Agrupamento: Utilizamos COLLECT_SET para garantir que o identificador do motorista apareça apenas uma vez no resultado final de cada semana, mesmo que ele tenha atingido a mesma sequência recorde múltiplas vezes no mesmo período.