Fala pessoal!

neste vídeo mostro bem rapidamente como utilizar as Window Function com as funções de classificação do T-SQL. Vamos aprofundar um pouco no módulo seguinte!

Aqui está o código (retirei do Simple Talk e da apresentação do Fabiano no SQLSAT 100, as fontes estão logo em cima!)

--cria a base de dados
/*fonte:  https://www.simple-talk.com/sql/t-sql-programming/sql-server-2012-window-function-basics/ 
outras fontes: http://blogfabiano.com/?s=sql+saturday+100 */
USE TSQL2012;
GO
 
IF OBJECT_ID('RegionalSales', 'U') IS NOT NULL
DROP TABLE RegionalSales;
GO
 
CREATE TABLE RegionalSales
(
  SalesID INT NOT NULL IDENTITY PRIMARY KEY,
  SalesGroup NVARCHAR(30) NOT NULL,
  Country NVARCHAR(30) NOT NULL,
  AnnualSales INT NOT NULL
);
GO
 
INSERT INTO RegionalSales
  (SalesGroup, Country, AnnualSales)
VALUES
  ('North America', 'United States', 22000),
  ('North America', 'Canada', 32000),
  ('North America', 'Mexico', 28000),
  ('Europe', 'France', 19000),
  ('Europe', 'Germany', 22000),
  ('Europe', 'Italy', 18000),
  ('Europe', 'Greece', 16000),
  ('Europe', 'Spain', 16000),
  ('Europe', 'United Kingdom', 32000),
  ('Pacific', 'Australia', 18000),
  ('Pacific', 'China', 28000),
  ('Pacific', 'Singapore', 21000),
  ('Pacific', 'New Zealand', 18000),
  ('Pacific', 'Thailand', 17000),
  ('Pacific', 'Malaysia', 19000),
  ('Pacific', 'Japan', 22000);
GO
-----------------------------------------------------------------------------------------------------------------------------------
--sintaxe b�sica:

<window function> OVER
  (
    [ PARTITION BY <expression> [, ... n] ] --define a janela
    [ ORDER BY <expression> [ASC|DESC] [, ... n] ] --define a ordem dos dados dentro da janela
    [ ROWS|RANGE <window frame> ] --frame, ou moldura, define em quais linhas a fun��o � calculada
  )
go


---------------------------------------------------------------------------------------------------------------
--fun��es de RANKING:
--ranking functions:
--a janela � a tabela toda:
--ORDER BY OBRIGAT�RIO:
SELECT
  SalesGroup,
  Country,
  AnnualSales,
  ROW_NUMBER() OVER(ORDER BY AnnualSales DESC) AS RowNumber,
  RANK() OVER(ORDER BY AnnualSales DESC) AS BasicRank,
  DENSE_RANK() OVER(ORDER BY AnnualSales DESC) AS DenseRank,
  NTILE(3) OVER(ORDER BY AnnualSales DESC) AS NTileRank
FROM
  RegionalSales;
 go
 --------------------------------------------------------------
 --ordena��o diferente
 SELECT
  SalesGroup,
  Country,
  AnnualSales,
  ROW_NUMBER() OVER(ORDER BY AnnualSales DESC) AS RowNumber,
  RANK() OVER(ORDER BY AnnualSales DESC) AS BasicRank,
  DENSE_RANK() OVER(ORDER BY AnnualSales DESC) AS DenseRank,
  NTILE(3) OVER(ORDER BY AnnualSales DESC) AS NTileRank
FROM
  RegionalSales
ORDER BY
  SalesGroup, Country;

 -------------------------------------------------------------
 --defini��o de janela: SALESGROUP
 SELECT
  SalesGroup,
  Country,
  AnnualSales,
  ROW_NUMBER() OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC) AS RowNumber,
  RANK() OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC) AS BasicRank,
  DENSE_RANK() OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC) AS DenseRank,
  NTILE(3) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC) AS NTileRank
FROM
  RegionalSales;
------------------------------------------------------------
--utilizando fun��es de agrupamento em janelas:
--grouping functions:

SELECT
  SalesGroup,
  Country,
  AnnualSales,
  COUNT(AnnualSales) OVER(PARTITION BY SalesGroup) AS CountryCount,--janela � SALESGROUP, contar quantos existem
  SUM(AnnualSales) OVER(PARTITION BY SalesGroup) AS TotalSales, --soma total de vendas  por SALESGROUP
  AVG(AnnualSales) OVER(PARTITION BY SalesGroup) AS AverageSales --m�dia por SALESGROUP
FROM
  RegionalSales
ORDER BY
  SalesGroup, AnnualSales DESC;

-----------------------------------------
--igual, menos colunas
SELECT DISTINCT
  SalesGroup,
  COUNT(AnnualSales) OVER(PARTITION BY SalesGroup) AS CountryCount,
  SUM(AnnualSales) OVER(PARTITION BY SalesGroup) AS TotalSales,
  AVG(AnnualSales) OVER(PARTITION BY SalesGroup) AS AverageSales
FROM
  RegionalSales
ORDER BY
  TotalSales DESC;
 --------------------------------------------
 --AGRUPANDO E ORDENANDO
 --grouping with ordering: each grouping column is different!
 --duplicate are aggregated together
 /*
 The default setting for the ROWS/RANGE subclause is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. 
 This means that, for each row in the partition, the window function is applied to the current row and the preceding rows only. 
 So aggregations don�t operate on the entire set of values within the partition, but only on the value in the current row and the previous rows, 
 as we saw in the example below.
 */

 --contagem a partir de dentro da janela, ordenando do maior valor de venda at� o menor, agregando da primeira linha at� a atual.
 SELECT
  SalesGroup,
  Country,
  AnnualSales,
  COUNT(AnnualSales) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC) AS CountryCount,
  SUM(AnnualSales) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC) AS TotalSales,
  AVG(AnnualSales) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC) AS AverageSales
FROM
  RegionalSales  ;
------------------------------------------------------------------------------------------------
--PALAVRAS-CHAVE: PRECEDING -> anteriores, FOLLOWING -> seguintes
--CURRENT ROW -> linha atual
--UNBOUNDED -> sem limite, a partir da primeira ou at� a �ltima

--IGUAL com a defini��o de frame padr�o:
--RANGE considera valores iguais como o mesmo, GREECE e SPAIN, agregando apenas um deles:

 SELECT
  SalesGroup,
  Country,
  AnnualSales,
  COUNT(AnnualSales) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC 
  range between unbounded preceding and current row) AS CountryCount,
  SUM(AnnualSales) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC 
  range between unbounded preceding and current row) AS TotalSales,
  AVG(AnnualSales) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC 
  range between unbounded preceding and current row) AS AverageSales
FROM
  RegionalSales  ;
---------------------------------------------------------------------------
--ROWS considera cada linha como um valor a ser agregado, normalmente � o mais utilizado
 SELECT
  SalesGroup,
  Country,
  AnnualSales,
  COUNT(AnnualSales) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC 
  rows between unbounded preceding and current row) AS CountryCount,
  SUM(AnnualSales) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC 
  rows between unbounded preceding and current row) AS TotalSales,
  AVG(AnnualSales) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC 
  rows between unbounded preceding and current row) AS AverageSales
FROM
  RegionalSales  ;
-------------------------------------------------------------------------------------
--fa�a a agrega��o apenas com as 2 linhas anteriores:
--only the 2 preceding rows and current
SELECT
  SalesGroup,
  Country,
  AnnualSales,
  COUNT(AnnualSales) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC
      ROWS 2 PRECEDING) AS CountryCount,
  SUM(AnnualSales) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC
      ROWS 2 PRECEDING) AS TotalSales,
  AVG(AnnualSales) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC
      ROWS 2 PRECEDING) AS AverageSales
FROM
  RegionalSales;
  ------------------------------------------
 --fa�a a agrga��o considerando a janela toda: 
SELECT
  SalesGroup,
  Country,
  AnnualSales,
  COUNT(AnnualSales) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC
       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS CountryCount,
  SUM(AnnualSales) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC
       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS TotalSales,
  AVG(AnnualSales) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS AverageSales
FROM
  RegionalSales;




 ------------------------------------------
 --FUN��ES ANAL�ICAS:
  --First/Last values - analytic functions
 --veja horizontalmente
 SELECT
  SalesGroup,
  Country,
  AnnualSales,
  FIRST_VALUE(AnnualSales) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC) AS HighestSales, -- ordene o valor do maior pro menor, ous seja, o primeiro � o maior, o �ltimo � o menor
  LAST_VALUE(AnnualSales) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC) AS LowestSales
FROM
  RegionalSales;

------------------------------
--todas as linhas da parti��o!
SELECT
  SalesGroup,
  Country,
  AnnualSales,
  FIRST_VALUE(AnnualSales) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC) AS HighestSales,
  LAST_VALUE(AnnualSales) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
      AS LowestSales
FROM
  RegionalSales;
 ----------------------------

 --LAG: valor de n linhas antes da atual
 --LEAD: valor de n linhas depois da atual
 SELECT
  SalesGroup,
  Country,
  AnnualSales,
  LAG(AnnualSales, 1) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC) AS PreviousSale,
  LEAD(AnnualSales, 1) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC) AS NextSale
FROM
  RegionalSales;
  ----------------------------
  --tratando nulls:
   SELECT
  SalesGroup,
  Country,
  AnnualSales,
  LAG(AnnualSales, 1,0) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC) AS PreviousSale,
  LEAD(AnnualSales, 1,0) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC) AS NextSale
FROM
  RegionalSales;



Abraços e Happy SQL Serving!

Leave a Reply

Your email address will not be published. Required fields are marked *

Post Navigation