Dicas e Truques sobre consultas complexas no SQL Server

Boa Tarde Pessoal,

 

Ontem fiz a apresentação do Webcast "Dicas e Truques sobre consultas complexas no SQL Server" onde demonstrei como utilizar o TSQL para elaborar consultas avançadas com foco no SQL Server 2005.

 

O objetivo do Webcast era demonstrar algumas necessidades de consultas comuns que aparecem, bem como apontar soluções para essas dúvidas e listar algumas práticas recomendadas.

 

Foram apresentados 5 problemas bem freqüentes. Posto também os scripts de forma resumida.

 

Totalização de resultados
Como elaborar uma coluna de saldo acumulando valores

 

— Tabela de Lançamentos para exemplificar o Subtotal
CREATE TABLE tblLancamentos (
    DataLancamento SMALLDATETIME,
    ValorLancamento SMALLMONEY)

 

— Insere os registros
INSERT INTO tblLancamentos VALUES (‘20080623’,100)
INSERT INTO tblLancamentos VALUES (‘20080624’,-250)
INSERT INTO tblLancamentos VALUES (‘20080625’,380)
INSERT INTO tblLancamentos VALUES (‘20080626’,200)
INSERT INTO tblLancamentos VALUES (‘20080627’,-300)

 

— Listar o saldo automático

SELECT DataLancamento, ValorLancamento,
    (SELECT SUM(ValorLancamento) FROM tblLancamentos
    WHERE DataLancamento <= QE.DataLancamento) AS Saldo
FROM tblLancamentos AS QE

 

Concatenação e delimitação
Como juntar vários resultados em uma única linha com algum caractér delimitador

 

— Cria a tabela de Clientes para exemplificar a concatenação
CREATE TABLE tblClientes (
    CodigoCli INT,
    NomeCli VARCHAR(50))

 

— Insere os registros
INSERT INTO tblClientes VALUES (1,‘Amanda’)
INSERT INTO tblClientes VALUES (2,‘Ângela’)

 

— Cria a tabela de Pedidos para exemplificar a concatenação
CREATE TABLE tblPedidos (
    CodigoCli INT,
    NumPedido INT,
    DataPedido SMALLDATETIME)

 

— Insere os registros
INSERT INTO tblPedidos VALUES (1,3310,‘20080620’)
INSERT INTO tblPedidos VALUES (1,5340,‘20080624’)
INSERT INTO tblPedidos VALUES (1,6723,‘20080628’)
INSERT INTO tblPedidos VALUES (2,4456,‘20080621’)
INSERT INTO tblPedidos VALUES (2,5219,‘20080625’)
INSERT INTO tblPedidos VALUES (2,5780,‘20080630’)

 

— Utilizar a natureza semiestruturada para concatenar valores
SELECT NomeCli, REPLACE(REPLACE(PedidosXML,‘</row>’,‘ ; ‘),‘<row>’,”) AS Pedidos FROM (
    SELECT NomeCli,
        (SELECT NumPedido AS [text()] FROM tblPedidos AS PED
        WHERE CLI.CodigoCli = PED.CodigoCli FOR XML PATH) AS PedidosXML
    FROM tblClientes AS CLI) AS Res

 

O TOP “N”
Como retornar a N ocorrências relacionadas a um conjunto de registros

 

— Cria a tabela de Clientes para exemplificar o TOP 2 para cada cliente
CREATE TABLE tblClientes (
    CodigoCli INT,
    NomeCli VARCHAR(50))

 

— Insere os registros
INSERT INTO tblClientes VALUES (1,‘Amanda’)
INSERT INTO tblClientes VALUES (2,‘Ângela’)

 

— Cria a tabela de Pedidos para exemplificar o TOP 2 para cada cliente
CREATE TABLE tblPedidos (
    CodigoCli INT,
    NumPedido INT,
    DataPedido SMALLDATETIME)

 

— Insere os registros
INSERT INTO tblPedidos VALUES (1,3310,‘20080620’)
INSERT INTO tblPedidos VALUES (1,5340,‘20080624’)
INSERT INTO tblPedidos VALUES (1,6723,‘20080628’)
INSERT INTO tblPedidos VALUES (2,4456,‘20080621’)
INSERT INTO tblPedidos VALUES (2,5219,‘20080625’)
INSERT INTO tblPedidos VALUES (2,5780,‘20080630’)

 

— O operador CROSS APPLY é indicado para situações TOP "N"
SELECT CLI.NomeCli, PED.NumPedido, PED.DataPedido
FROM tblClientes AS CLI
CROSS APPLY (
    SELECT TOP 2 NumPedido, DataPedido FROM tblPedidos
    WHERE CodigoCli = CLI.CodigoCLI
    ORDER BY DataPedido) AS PED

 

PIVOT & PIVOT dinâmico
Como transformar linhas em colunas usando o Operador PIVOT e superar suas limitações

 

— Cria a tabela de Marcas para exemplificar o PIVOT
CREATE TABLE Marcas (
    CodigoCli INT,
    Marca CHAR(1))

 

— Insere os registros
INSERT INTO Marcas VALUES (1, ‘A’)
INSERT INTO Marcas VALUES (1, ‘B’)
INSERT INTO Marcas VALUES (2, ‘A’)
INSERT INTO Marcas VALUES (3, ‘B’)

 

— Criando e formatando o resultado

SELECT Cliente,
    CASE A WHEN 1 THEN ‘S’ ELSE ‘N’ END AS MarcaA,
    CASE B WHEN 1 THEN ‘S’ ELSE ‘N’ END AS MarcaB
FROM (
SELECT CodigoCli AS Cliente, [A], [B] FROM
(SELECT CodigoCli, Marca FROM Marcas) AS Marcas
PIVOT
( COUNT(Marca) FOR Marca IN ([A],[B]) ) AS PivotTable) AS Q

 

— Criando e formatando o resultado sem especificar as marcas

DECLARE
    @Marca CHAR(1),  @Marcas VARCHAR(100), @cmdSQLHeader VARCHAR(100),
    @cmdSQLAux VARCHAR(100), @cmdSQLFooter VARCHAR(1000),
    @cmdSQL VARCHAR(1500)

 

    SET @cmdSQLHeader = ‘SELECT Cliente’ + CHAR(10)
    SET @cmdSQLAux = ‘, CASE ? WHEN 1 THEN ”S” ELSE ”N” END AS Marca?’ + CHAR(10)
    SET @cmdSQLFooter = ‘
        FROM (
        SELECT CodigoCli AS Cliente, ?
        FROM
        (SELECT CodigoCli, Marca 
            FROM Marcas) AS MarcasFormatadas
        PIVOT
        ( COUNT(Marca) FOR Marca IN (?) ) AS P) AS Q’

— Recuperar a lista de marcas existentes
DECLARE cMarcas CURSOR
FOR SELECT DISTINCT Marca FROM Marcas
FAST_FORWARD

 

OPEN cMarcas

 

FETCH NEXT FROM cMarcas INTO @Marca

SET @Marcas = ”

SET @cmdSQL = @cmdSQLHeader

 

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmdSQL = @cmdSQL + REPLACE(@cmdSQLAux,‘?’,@marca)
    SET @Marcas = @Marcas + ‘[‘ + @Marca + ‘],’
    FETCH NEXT FROM cMarcas INTO @Marca
END

 

CLOSE cMarcas

DEALLOCATE cMarcas

 

— Executando o comando

SET @Marcas = LEFT(@Marcas,LEN(@Marcas)-1)
SET @cmdSQL = @cmdSQL + REPLACE(@cmdSQLFooter,‘?’,@Marcas)

EXEC(@cmdSQL)

 

Consultas Hierárquicas
Como elaborar consultas em relacionamentos hierárquicos (autorelacionamento)

 

— Cria a tabela de Funcionários para exemplificar a hierarquia
CREATE TABLE tblFuncionarios (
    IDFuncionario INT,
    NomeFuncionario VARCHAR(50),
    IDSuperior INT NULL,
    ValorVendido SMALLMONEY)

 

— Insere os registros
INSERT INTO tblFuncionarios VALUES (1,‘Thelma’,NULL,350.00)
INSERT INTO tblFuncionarios VALUES (2,‘Denise’,1,250.00)
INSERT INTO tblFuncionarios VALUES (3,‘Tamara’,2,200.00)
INSERT INTO tblFuncionarios VALUES (4,‘Herline’,3,100.00)
INSERT INTO tblFuncionarios VALUES (5,‘Ricardo’,NULL,600.00)
INSERT INTO tblFuncionarios VALUES (6,‘Cléber’,5,500.00)
INSERT INTO tblFuncionarios VALUES (7,‘Eduardo’,5,300.00)

 

— Utilização de uma CTE Recursiva
WITH Superiores
AS (
SELECT IDFuncionario, NomeFuncionario,
    IDSuperior, ValorVendido,
    CASE WHEN IDSuperior IS NULL THEN IDFuncionario
    ELSE IDSuperior END AS ID
FROM tblFuncionarios
WHERE IDSuperior IS NULL

 

UNION ALL

 

SELECT FUNC.IDFuncionario, FUNC.NomeFuncionario,
  FUNC.IDSuperior, FUNC.ValorVendido,
  SUP.ID
FROM Superiores AS SUP
INNER JOIN tblFuncionarios AS FUNC ON SUP.IDFuncionario = FUNC.IDSuperior)

 

SELECT FUNC.IDFuncionario, FUNC.NomeFuncionario, FUNC.ValorVendido,
    SUM(SUP.ValorVendido) – FUNC.ValorVendido AS VendasIndiretas,
    SUM(SUP.ValorVendido) AS TotalVendas
FROM
    tblFuncionarios AS FUNC
    INNER JOIN Superiores AS SUP ON FUNC.IDFuncionario = SUP.ID
GROUP BY
    FUNC.IDFuncionario, FUNC.NomeFuncionario, FUNC.ValorVendido

 

Assim que a data e o link do Webcast foram definidos, fiz uma divulgação nos principais grupos de SQL Server que faço parte. Fiquei muito satisfeito com a quantidade de pessoas que assistiram a apresentação (cerca de 80 pessoas). Foi muito gratificante poder levar conhecimento a um grupo tão grande já que normalmente uma apresentação no Webcast tem um quorum menor de pessoas.

 

A essas pessoas que assistiram a apresentação eu gostaria de agradecer novamente pelo comparecimento, interesse e pela avaliação. Mesmo com os problemas de áudio a avaliação foi positiva e recebi muitos feedbacks. Estou sugerindo para a equipe organizadora que façamos uma segunda apresentação. Se de fato for acontecer, divulgarei a data e o link logo que disponíveis.

 

Conforme prometido no Webcast, segue o link para efetuar o download do projeto em SQL Server 2005 com os scripts utilizados na apresentação.

 

http://cid-f4f5c630410b9865.skydrive.live.com/self.aspx/ProjetosSQLServer/20080807%7C_TSQLTricks01.zip

 

O Webcast foi gravado e acredito que nos próximos dias deva estar disponível. O link original do Webcast é:

 

http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=pt-BR&EventID=1032383203&CountryCode=BR

 

Obrigado a todos,

 

Gustavo

Publicidade