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!) [code language=”sql”] –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
