Desvendando os Segredos do Oracle SQL: OVER e PARTITION BY

Desvendando os Segredos do Oracle SQL: OVER e PARTITION BY

E aí, amantes do Oracle SQL e PL/SQL ! Hoje, vamos mergulhar de cabeça em duas das funcionalidades mais poderosas e intrigantes deste universo: OVER e PARTITION BY. Se você quer levar suas habilidades de manipulação de dados para o próximo nível, não pode deixar de conferir o que essas cláusulas podem fazer por você. Prepare-se para uma jornada emocionante pelos meandros do SQL!

OVER – O Herói Analítico

A cláusula OVER é como o super-herói que chega para salvar o dia quando você precisa realizar operações analíticas avançadas. Ela funciona em conjunto com funções analíticas e proporciona uma visão mais ampla dos dados. Vamos dar uma olhada na sintaxe básica:

SELECT
  column1,
  column2,
  ...
  analytic_function(column) OVER (ORDER BY order_column)
FROM
  your_table;

A mágica acontece com o OVER, onde você pode especificar a ordem das linhas usando a cláusula ORDER BY. Isso é útil quando queremos calcular médias móveis, rankings e outras coisas fascinantes.

Exemplo 1: Ranqueando com Estilo

Digamos que você queira ranquear os funcionários com base no salário, mas quer levar em consideração o departamento de cada um. Vamos lá:

SELECT
  employee_id,
  salary,
  department_id,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS department_rank
FROM
  employees;

Resultado:

Employee_ID	Salary	Department_ID	Department_Rank
101	5000	1	1
102	4500	1	2
103	6000	2	1
…	…	…	…

Neste exemplo, o PARTITION BY divide o resultado em partições com base no departamento, e então, para cada partição, o RANK() calcula o ranking do salário em ordem decrescente.

Exemplo 2: Médias Móveis Mágicas

Agora, imagine que você está lidando com dados temporais e quer calcular uma média móvel simples do salário ao longo do tempo. Aqui está como você faria isso:

SELECT
  salary,
  hire_date,
  AVG(salary) OVER (ORDER BY hire_date RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND CURRENT ROW) AS avg_salary
FROM
  employees;

Resultado:

Salary	Hire_Date	Avg_Salary
5000	01-Jan-22	5000
4500	15-Jan-22	4750
6000	01-Feb-22	5500
…	…	…

Neste exemplo, a cláusula RANGE BETWEEN especifica o intervalo para a média móvel, e você pode ajustar conforme necessário.

PARTITION BY – Dividindo para Conquistar

Agora, vamos falar sobre o sidekick do OVER: PARTITION BY. Essa cláusula é a chave para fatiar seus dados em pedaços menores e aplicar operações analíticas a cada pedaço individualmente.

A sintaxe é tão simples que chega a ser bela:

SELECT
  column1,
  column2,
  ...
  analytic_function(column) OVER (PARTITION BY partition_column)
FROM
  your_table;

Exemplo 3: Quebrando por Categoria

Suponha que você tenha uma tabela de vendas e queira calcular a soma acumulativa de vendas para cada categoria. PARTITION BY é seu melhor amigo aqui:

SELECT
  category,
  sale_amount,
  SUM(sale_amount) OVER (PARTITION BY category ORDER BY sale_date) AS cumulative_sales
FROM
  sales;

Resultado:

Category	Sale_Amount	Cumulative_Sales
A	1000	1000
B	1500	1500
A	800	1800
…	…	…

Neste caso, a cláusula PARTITION BY category divide o resultado em categorias, e a função SUM() então calcula a soma acumulativa para cada categoria individualmente.

Exemplo 4: Comparando com Maestria

E se quisermos comparar o desempenho de cada funcionário com o salário médio de seu departamento? Aqui está como fazemos isso com PARTITION BY:

SELECT
  employee_id,
  salary,
  department_id,
  AVG(salary) OVER (PARTITION BY department_id) AS avg_department_salary
FROM
  employees;

Resultado:

Employee_ID	Salary	Department_ID	Avg_Department_Salary
101	5000	1	4750
102	4500	1	4750
103	6000	2	6000
…	…	…	…

Dessa vez, PARTITION BY agrupa os dados por departamento, e a função AVG() calcula a média do salário dentro de cada departamento.

Conclusão

E aí está, amigos SQL aficionados! OVER e PARTITION BY são como o par dinâmico de super-heróis que garantem que você possa conquistar o mundo dos dados. Espero que esses exemplos práticos e entusiasmados tenham despertado o seu interesse e motivado você a explorar ainda mais as capacidades incríveis do Oracle SQL.

Lembre-se, a documentação oficial do Oracle é sua melhor amiga nessa jornada emocionante. Então, vá lá, experimente esses códigos, crie os seus próprios e domine o mundo dos dados como um verdadeiro Jedi do SQL!

Referências

• Documentação oficial do Oracle: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/