Neste primeiro post, de uma série, falarei sobre as funções analíticas do Oracle, que podem ser úteis no dia-a-dia tanto de DBAs quando de desenvolvedores.

É importante mencionar que os exemplos abaixo são bem simples, apenas com o objetivo de mostrar o que pode ser feito e como fazer, usando funções analíticas

1) CONCEITOS INICIAIS

Funções analíticas computam um valor agregado baseado em um número de linhas. Funções agregadas retornam uma linha para cada grupo, já funções analíticas retornam multiplas linhas para cada grupo, ou seja, funções analíticas não agrupam resultados, ao contrário das funções agregadas.

A grande maioria das funções analíticas, também podem ser usadas como funções agregadas, e vice-versa.

Funções analíticas podem aparecer somente na lista do SELECT ou no ORDER BY

2) ATÉ A VERSÃO 11.2 DO ORACLE, ESTE DISPUNHA DAS SEGUINTES FUNÇÕES ANALÍTICAS:

  • AVG
  • CORR
  • COUNT
  • COVAR_POP
  • COVAR_SAMP
  • CUME_DIST
  • DENSE_RANK
  • FIRST
  • LAST
  • FIRST_VALUE
  • LAG
  • LAST_VALUE
  • LEAD
  • LISTAGG
  • MAX
  • MIN
  • NTH_VALUE
  • NTILE
  • PERCENT_RANK
  • PERCENTILE_CONT
  • PERCENTILE_DISC
  • RANK
  • RATIO_TO_REPORT
  • REGR_ (Linear Regression) Functions
  • ROW_NUMBER
  • STDDEV
  • STDDEV_POP
  • STDDEV_SAMP
  • SUM
  • VAR_POP
  • VAR_SAMP
  • VARIANCE

Vamos seguir a ordem listada acima, falando primeiro das seguintes funções:

  • AVG
  • CORR
  • COUNT
  • COVAR_POP
  • COVAR_SAMP
  • CUME_DIST
  • DENSE_RANK
  • FIRST
  • LAST
  • FIRST_VALUE
  • LAG

2.1) AVG

Retorna a média de valores de determinada coluna. Recebe um valor numérico, ou não-numérico, mas que possa ser implicitamente convertido. A cláusula OVER indica que está se usando a função de forma analítica.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tZXhlbXBsbyBkZSB1c28gZG8gYXZnIGNvbW8gZnVuw6fDo28gYWdyZWdhZGEKIC0tbW9zdHJhIGEgbcOpZGlhIHNhbGFyaWFsLCBwb3IgZGVwYXJ0YW1lbnRvCiBTRUxFQ1QgSURfREVQQVJUQU1FTlRPLAogQVZHKFNBTEFSSU8pIEFTIE1FRElBX1NBTEFSSU8KIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCiBHUk9VUCBCWSBJRF9ERVBBUlRBTUVOVE8KCiAtLWV4ZW1wbG8gZGUgdXNvIGRvIGF2ZyBjb21vIGZ1bsOnw6NvIGFuYWzDrXRpY2EKIC0tbW9zdHJhIGEgbWVkaWEgc2FsYXJpYWwsIHBvciBpZGFkZSBkbyBlbXByZWdhZG8KIFNFTEVDVCBJRF9FTVAsCiBOT01FX0VNUCwKIElEQURFLAogU0FMQVJJTywKIEFWRyhTQUxBUklPKSBPVkVSIChQQVJUSVRJT04gQlkgSURBREUgT1JERVIgQlkgSURfRU1QKSBNRURJQV9TQUxfUE9SX0lEQURFCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUw==” hover_enabled=”0″]IC0tZXhlbXBsbyBkZSB1c28gZG8gYXZnIGNvbW8gZnVuw6fDo28gYWdyZWdhZGEKIC0tbW9zdHJhIGEgbcOpZGlhIHNhbGFyaWFsLCBwb3IgZGVwYXJ0YW1lbnRvCiBTRUxFQ1QgSURfREVQQVJUQU1FTlRPLAogQVZHKFNBTEFSSU8pIEFTIE1FRElBX1NBTEFSSU8KIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCiBHUk9VUCBCWSBJRF9ERVBBUlRBTUVOVE8KCiAtLWV4ZW1wbG8gZGUgdXNvIGRvIGF2ZyBjb21vIGZ1bsOnw6NvIGFuYWzDrXRpY2EKIC0tbW9zdHJhIGEgbWVkaWEgc2FsYXJpYWwsIHBvciBpZGFkZSBkbyBlbXByZWdhZG8KIFNFTEVDVCBJRF9FTVAsCiBOT01FX0VNUCwKIElEQURFLAogU0FMQVJJTywKIEFWRyhTQUxBUklPKSBPVkVSIChQQVJUSVRJT04gQlkgSURBREUgT1JERVIgQlkgSURfRU1QKSBNRURJQV9TQUxfUE9SX0lEQURFCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUw==[/et_pb_dmb_code_snippet]

2.2) CORR

Mostra o coeficiênte de correlação de Pearson, medindo o grau de correlação entre 2 valores numéricos, ou não-numéricos, mas que possam ser implicitamente convertidos. Caso o coeficiente seja 1, há uma correlação perfeita positiva entre esses 2 valores, caso -1, há uma correlação perfeita negativa entre esses 2 valores, e caso 0, indica que as 2 variáveis não dependem linearmente uma da outra. Caso o resultado se aproxime de 1, ou -1, isso indica que o 1º valor possui certa correlação com o 2º valor, o que indica que o 2º valor influencia o 1º. Quanto mais perto de 1, ou -1, mais forte é essa correlação. A cláusula OVER indica que está se usando a função de forma analítica.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tZXhlbXBsbyBkZSB1c28gZG8gY29yciBjb21vIGZ1bmNhbyBhZ3JlZ2FkYQogLS1tb3N0cmEgc2UgcXVhbnRvIG1haXMgdGVtcG8gZGUgZW1wcmVzYSBvcyBmdW5jaW9uYXJpb3MgZGUgY2FkYSAKIC0tZGVwYXJ0YW1lbnRvIHRlbSwgbWFpcyBvIGZ1bmNpb25hcmlvIHJlY2ViZSwgYWdydXBhbmRvIAogLS1wb3IgZGVwYXJ0YW1lbnRvCiBTRUxFQ1QgSURfREVQQVJUQU1FTlRPLAogQ09SUihTWVNEQVRFIC0gREFUQV9BRE1JU1NBTywgU0FMQVJJTykgQVMgQ09SUkVMQUNBTwogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKIEdST1VQIEJZIElEX0RFUEFSVEFNRU5UTwogT1JERVIgQlkgSURfREVQQVJUQU1FTlRPCgogLS1leGVtcGxvIGRlIHVzbyBkbyBjb3JyIGNvbW8gZnVuY2FvIGFuYWxpdGljYQogLS1tb3N0cmEgc2UgcXVhbnRvIG1haXMgdGVtcG8gZGUgZW1wcmVzYSBvcyBmdW5jaW9uYXJpb3MgZGUgY2FkYSAKIC0tZGVwYXJ0YW1lbnRvIHRlbSwgbWFpcyBvIGZ1bmNpb25hcmlvIHJlY2ViZSwgcG9yIGVtcHJlZ2FkbwogU0VMRUNUIElEX0VNUCwKIFRPX0NIQVIoKFNZU0RBVEUgLSBEQVRBX0FETUlTU0FPKSBZRUFSIFRPIE1PTlRIKSBURU1QT19ERV9FTVBSRVNBLAogQ09SUihTWVNEQVRFIC0gREFUQV9BRE1JU1NBTywgU0FMQVJJTykgT1ZFUiAoUEFSVElUSU9OIEJZIElEX0RFUEFSVEFNRU5UTykgQVMgQ09SUkVMQUNBTwogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKIE9SREVSIEJZIElEX0VNUA==” hover_enabled=”0″]IC0tZXhlbXBsbyBkZSB1c28gZG8gY29yciBjb21vIGZ1bmNhbyBhZ3JlZ2FkYQogLS1tb3N0cmEgc2UgcXVhbnRvIG1haXMgdGVtcG8gZGUgZW1wcmVzYSBvcyBmdW5jaW9uYXJpb3MgZGUgY2FkYSAKIC0tZGVwYXJ0YW1lbnRvIHRlbSwgbWFpcyBvIGZ1bmNpb25hcmlvIHJlY2ViZSwgYWdydXBhbmRvIAogLS1wb3IgZGVwYXJ0YW1lbnRvCiBTRUxFQ1QgSURfREVQQVJUQU1FTlRPLAogQ09SUihTWVNEQVRFIC0gREFUQV9BRE1JU1NBTywgU0FMQVJJTykgQVMgQ09SUkVMQUNBTwogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKIEdST1VQIEJZIElEX0RFUEFSVEFNRU5UTwogT1JERVIgQlkgSURfREVQQVJUQU1FTlRPCgogLS1leGVtcGxvIGRlIHVzbyBkbyBjb3JyIGNvbW8gZnVuY2FvIGFuYWxpdGljYQogLS1tb3N0cmEgc2UgcXVhbnRvIG1haXMgdGVtcG8gZGUgZW1wcmVzYSBvcyBmdW5jaW9uYXJpb3MgZGUgY2FkYSAKIC0tZGVwYXJ0YW1lbnRvIHRlbSwgbWFpcyBvIGZ1bmNpb25hcmlvIHJlY2ViZSwgcG9yIGVtcHJlZ2FkbwogU0VMRUNUIElEX0VNUCwKIFRPX0NIQVIoKFNZU0RBVEUgLSBEQVRBX0FETUlTU0FPKSBZRUFSIFRPIE1PTlRIKSBURU1QT19ERV9FTVBSRVNBLAogQ09SUihTWVNEQVRFIC0gREFUQV9BRE1JU1NBTywgU0FMQVJJTykgT1ZFUiAoUEFSVElUSU9OIEJZIElEX0RFUEFSVEFNRU5UTykgQVMgQ09SUkVMQUNBTwogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKIE9SREVSIEJZIElEX0VNUA==[/et_pb_dmb_code_snippet]

2.3) COUNT

Exibe o número de registros retornados por uma query.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tZXhlbXBsbyBkZSB1c28gZG8gY291bnQgY29tbyBmdW7Dp8OjbyBhZ3JlZ2FkYQogLS1tb3N0cmEgbyBudW1lcm8gdG90YWwgZGUgZW1wcmVnYWRvcywgYWdydXBhZG9zIHBvciBkZXBhcnRhbWVudG8KIFNFTEVDVCBJRF9kZXBhcnRhbWVudG8sCiBDT1VOVCgqKSBOVU1fRU1QX1BPUl9ERVBUCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUwogR1JPVVAgQlkgSURfREVQQVJUQU1FTlRPCiBPUkRFUiBCWSBJRF9ERVBBUlRBTUVOVE8KCiAtLWV4ZW1wbG8gZGUgdXNvIGRvIGNvdW50IGNvbW8gZnVuY2FvIGFuYWxpdGljYQogLS1tb3N0cmEgbyBlbXByZWdhZG8sIHNldSBkZXBhcnRhbWVudG8gZSBhIHF1YW50aWRhZGUgZGUgCiAtLWVtcHJlZ2Fkb3MgcG9yIGRlcGFydGFtZW50bywgcGFyYSBjYWRhIGVtcHJlZ2FkbwogU0VMRUNUIElEX0VNUCwKIElEX0RFUEFSVEFNRU5UTywKIENPVU5UKCopIE9WRVIgKFBBUlRJVElPTiBCWSBJRF9kZXBhcnRhbWVudG8pIEFTIFRPVEFMX0RFUEFSVEFNRU5UTwogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKCiAtLWV4ZW1wbG8gZGUgdXNvIGRvIGNvdW50IGNvbW8gZnVuY2FvIGFuYWxpdGljYQogLS1tb3N0cmEgaW5mb3JtYWNvZXMgc29icmUgZW1wcmVnYWRvcywgYmVtIGNvbW8gYSBxdWFudGlkYWRlIGRlIAogLS1lbXByZWdhZG9zIHF1ZSByZWNlYmVtIFIkMjAwMCwwMCBhIG1lbm9zIGUgUiQxMDAwMCwwMCBhIG1haXMsIAogLS1kbyBxdWUgZGV0ZXJtaW5hZG8gZW1wcmVnYWRvCiBTRUxFQ1QgSURfRU1QLAogTk9NRV9FTVAsCiBTQUxBUklPLAogQ09VTlQoKikgT1ZFUiAoT1JERVIgQlkgU0FMQVJJTyBSQU5HRSBCRVRXRUVOIDIwMDAgUFJFQ0VESU5HIEFORCAxMDAwMCBGT0xMT1dJTkcpCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUw==” hover_enabled=”0″]IC0tZXhlbXBsbyBkZSB1c28gZG8gY291bnQgY29tbyBmdW7Dp8OjbyBhZ3JlZ2FkYQogLS1tb3N0cmEgbyBudW1lcm8gdG90YWwgZGUgZW1wcmVnYWRvcywgYWdydXBhZG9zIHBvciBkZXBhcnRhbWVudG8KIFNFTEVDVCBJRF9kZXBhcnRhbWVudG8sCiBDT1VOVCgqKSBOVU1fRU1QX1BPUl9ERVBUCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUwogR1JPVVAgQlkgSURfREVQQVJUQU1FTlRPCiBPUkRFUiBCWSBJRF9ERVBBUlRBTUVOVE8KCiAtLWV4ZW1wbG8gZGUgdXNvIGRvIGNvdW50IGNvbW8gZnVuY2FvIGFuYWxpdGljYQogLS1tb3N0cmEgbyBlbXByZWdhZG8sIHNldSBkZXBhcnRhbWVudG8gZSBhIHF1YW50aWRhZGUgZGUgCiAtLWVtcHJlZ2Fkb3MgcG9yIGRlcGFydGFtZW50bywgcGFyYSBjYWRhIGVtcHJlZ2FkbwogU0VMRUNUIElEX0VNUCwKIElEX0RFUEFSVEFNRU5UTywKIENPVU5UKCopIE9WRVIgKFBBUlRJVElPTiBCWSBJRF9kZXBhcnRhbWVudG8pIEFTIFRPVEFMX0RFUEFSVEFNRU5UTwogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKCiAtLWV4ZW1wbG8gZGUgdXNvIGRvIGNvdW50IGNvbW8gZnVuY2FvIGFuYWxpdGljYQogLS1tb3N0cmEgaW5mb3JtYWNvZXMgc29icmUgZW1wcmVnYWRvcywgYmVtIGNvbW8gYSBxdWFudGlkYWRlIGRlIAogLS1lbXByZWdhZG9zIHF1ZSByZWNlYmVtIFIkMjAwMCwwMCBhIG1lbm9zIGUgUiQxMDAwMCwwMCBhIG1haXMsIAogLS1kbyBxdWUgZGV0ZXJtaW5hZG8gZW1wcmVnYWRvCiBTRUxFQ1QgSURfRU1QLAogTk9NRV9FTVAsCiBTQUxBUklPLAogQ09VTlQoKikgT1ZFUiAoT1JERVIgQlkgU0FMQVJJTyBSQU5HRSBCRVRXRUVOIDIwMDAgUFJFQ0VESU5HIEFORCAxMDAwMCBGT0xMT1dJTkcpCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUw==[/et_pb_dmb_code_snippet]

2.4) COVAR_POP E COVAR_SAMP

Calculam a covariância da população e a covariância amostral, respectivamente, de 2 conjuntos de pares numéricos. Essas funções servem para verificar a relação entre 2 variáveis. Uma covariância positiva indica que as variáveis possuem uma relação direta, e uma covariância negativa indica que as variáveis possuem uma relação inversa.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tZXhlbXBsbyBkZSB1c28gZG8gY292YXJfcG9wIGUgY292YXJfc2FtcCBjb21vIGZ1bmNvZXMgYWdyZWdhZGFzCiBTRUxFQ1QgSURfREVQQVJUQU1FTlRPLAogVFJVTkMoQ09WQVJfUE9QKFNZU0RBVEUgLSBEQVRBX0FETUlTU0FPLCBTQUxBUklPKSwgMikgQVMgQ09WQVJfUE9QLAogVFJVTkMoQ09WQVJfU0FNUChTWVNEQVRFIC0gREFUQV9BRE1JU1NBTywgU0FMQVJJTyksIDIpIEFTIENPVkFSX1NBTVAKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCiBHUk9VUCBCWSBJRF9ERVBBUlRBTUVOVE8KCiAtLWV4ZW1wbG8gZGUgdXNvIGRvIGNvdmFyX3BvcCBlIGNvdmFyX3NhbXAgY29tbyBmdW5jb2VzIGFuYWxpdGljYQogU0VMRUNUIElEX0VNUCwKIE5PTUVfRU1QLAogU0FMQVJJTywKIFRSVU5DKENPVkFSX1BPUChTWVNEQVRFIC0gREFUQV9BRE1JU1NBTywgU0FMQVJJTykgT1ZFUiAoT1JERVIgQlkgU0FMQVJJTyksIDIpIEFTIENPVl9QT1BVTEFDSU9OQUwsCiBUUlVOQyhDT1ZBUl9TQU1QKFNZU0RBVEUgLSBEQVRBX0FETUlTU0FPLCBTQUxBUklPKSBPVkVSIChPUkRFUiBCWSBTQUxBUklPKSwgMikgQVMgQ09WX0FNT1NUUkEKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCiBPUkRFUiBCWSBJRF9FTVA=” hover_enabled=”0″]IC0tZXhlbXBsbyBkZSB1c28gZG8gY292YXJfcG9wIGUgY292YXJfc2FtcCBjb21vIGZ1bmNvZXMgYWdyZWdhZGFzCiBTRUxFQ1QgSURfREVQQVJUQU1FTlRPLAogVFJVTkMoQ09WQVJfUE9QKFNZU0RBVEUgLSBEQVRBX0FETUlTU0FPLCBTQUxBUklPKSwgMikgQVMgQ09WQVJfUE9QLAogVFJVTkMoQ09WQVJfU0FNUChTWVNEQVRFIC0gREFUQV9BRE1JU1NBTywgU0FMQVJJTyksIDIpIEFTIENPVkFSX1NBTVAKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCiBHUk9VUCBCWSBJRF9ERVBBUlRBTUVOVE8KCiAtLWV4ZW1wbG8gZGUgdXNvIGRvIGNvdmFyX3BvcCBlIGNvdmFyX3NhbXAgY29tbyBmdW5jb2VzIGFuYWxpdGljYQogU0VMRUNUIElEX0VNUCwKIE5PTUVfRU1QLAogU0FMQVJJTywKIFRSVU5DKENPVkFSX1BPUChTWVNEQVRFIC0gREFUQV9BRE1JU1NBTywgU0FMQVJJTykgT1ZFUiAoT1JERVIgQlkgU0FMQVJJTyksIDIpIEFTIENPVl9QT1BVTEFDSU9OQUwsCiBUUlVOQyhDT1ZBUl9TQU1QKFNZU0RBVEUgLSBEQVRBX0FETUlTU0FPLCBTQUxBUklPKSBPVkVSIChPUkRFUiBCWSBTQUxBUklPKSwgMikgQVMgQ09WX0FNT1NUUkEKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCiBPUkRFUiBCWSBJRF9FTVA=[/et_pb_dmb_code_snippet]

2.5) CUME_DIST

Calcula a distribuição cumulativa de um valor, dentro de um grupo de valores, ou seja, calcula a posição relativa de um valor específico, dentro de um grupo de valores.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tZXhlbXBsbyBkZSB1c28gZG8gY3VtZV9kaXN0IGNvbW8gZnVuY2FvIGFncmVnYWRhCiAtLWNhbGN1bGEgYSBkaXN0cmlidWljYW8gY3VtdWxhdGl2YSBkZSBkZXRlcm1pbmFkbyAKIC0tZnVuY2lvbmFyaW8gZW50cmUgdG9kb3Mgb3MgZnVuY2lvbmFyaW9zIGQyIGRlcGFydGFtZW50byAyCiBTRUxFQ1QgQ1VNRV9ESVNUKDIwMDAsIDIpIFdJVEhJTiBHUk9VUAogKE9SREVSIEJZIFNBTEFSSU8sIElEX2RFUEFSVEFNRU5UTykgQVMgQ1VNRV9ESVNUCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUwoKIC0tZXhlbXBsbyBkZSB1c28gZG8gY3VtZV9kaXN0IGNvbW8gZnVuY2FvIGFuYWxpdGljYQogLS1tb3N0cmEgcXVlbSB0ZW0gb3MgbWVub3JlcyBlIG1haW9yZXMgc2FsYXJpb3MsIHBvciBkZXBhcnRhbWVudG8KIC0tbm8gY2FzbyBhYmFpeG8sIHF1YW50byBtYWlzIHByb3hpbW8gZGUgMSwgbWVub3IgZSBvIAogLS1zYWxhcmlvIHBvciBkZXBhcnRhbWVudG8KIFNFTEVDVCBJRF9FTVAsCiBJRF9ERVBBUlRBTUVOVE8sCiBOT01FX0VNUCwKIFNBTEFSSU8sCiBUUlVOQyhDVU1FX0RJU1QoKSBPVkVSIChQQVJUSVRJT04gQlkgSURfREVQQVJUQU1FTlRPIE9SREVSIEJZIFNBTEFSSU8gREVTQyksIDIpCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUw==” hover_enabled=”0″]IC0tZXhlbXBsbyBkZSB1c28gZG8gY3VtZV9kaXN0IGNvbW8gZnVuY2FvIGFncmVnYWRhCiAtLWNhbGN1bGEgYSBkaXN0cmlidWljYW8gY3VtdWxhdGl2YSBkZSBkZXRlcm1pbmFkbyAKIC0tZnVuY2lvbmFyaW8gZW50cmUgdG9kb3Mgb3MgZnVuY2lvbmFyaW9zIGQyIGRlcGFydGFtZW50byAyCiBTRUxFQ1QgQ1VNRV9ESVNUKDIwMDAsIDIpIFdJVEhJTiBHUk9VUAogKE9SREVSIEJZIFNBTEFSSU8sIElEX2RFUEFSVEFNRU5UTykgQVMgQ1VNRV9ESVNUCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUwoKIC0tZXhlbXBsbyBkZSB1c28gZG8gY3VtZV9kaXN0IGNvbW8gZnVuY2FvIGFuYWxpdGljYQogLS1tb3N0cmEgcXVlbSB0ZW0gb3MgbWVub3JlcyBlIG1haW9yZXMgc2FsYXJpb3MsIHBvciBkZXBhcnRhbWVudG8KIC0tbm8gY2FzbyBhYmFpeG8sIHF1YW50byBtYWlzIHByb3hpbW8gZGUgMSwgbWVub3IgZSBvIAogLS1zYWxhcmlvIHBvciBkZXBhcnRhbWVudG8KIFNFTEVDVCBJRF9FTVAsCiBJRF9ERVBBUlRBTUVOVE8sCiBOT01FX0VNUCwKIFNBTEFSSU8sCiBUUlVOQyhDVU1FX0RJU1QoKSBPVkVSIChQQVJUSVRJT04gQlkgSURfREVQQVJUQU1FTlRPIE9SREVSIEJZIFNBTEFSSU8gREVTQyksIDIpCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUw==[/et_pb_dmb_code_snippet]

2.6) DENSE_RANK

Calcula o rank de uma linha dentro de um grupo de linhas, ou seja, retorna a posição (rank) dessa linha dentro do grupo de linhas especificado pela query

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tZXhlbXBsbyBkZSB1c28gZG8gZGVuc2VfcmFuayBjb21vIGZ1bmNhbyBhZ3JlZ2FkYQogLS1kYWRvIHVtIHZhbG9yLCBhIHF1ZXJ5IHJldG9ybmEgYSBwb3NpY2FvIChyYW5rKSAKIC0tZGVzc2UgdmFsb3IsIGRlbnRybyBkYSB0YWJlbGEKIFNFTEVDVCBERU5TRV9SQU5LKDEwMDAwKSBXSVRISU4gR1JPVVAgKE9SREVSIEJZIFNBTEFSSU8gREVTQykgUE9TSUNBTwogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKCiAtLWV4ZW1wbG8gZGUgdXNvIGRvIGRlbnNlX3JhbmsgY29tbyBmdW5jYW8gYW5hbGl0aWNhCiAtLW1vc3RyYSBvIHJhbmsgZGUgc2FsYXJpbyBkZSBjYWRhIGZ1bmNpb25hcmlvLCBwb3IgZGVwYXJ0YW1lbnRvCiBTRUxFQ1QgSURfRU1QLAogTk9NRV9FTVAsCiBJRF9ERVBBUlRBTUVOVE8sCiBTQUxBUklPLAogREVOU0VfUkFOSygpIE9WRVIgKFBBUlRJVElPTiBCWSBJRF9ERVBBUlRBTUVOVE8gT1JERVIgQlkgU0FMQVJJTyBERVNDKSBBUyBSQU5LCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUw==” hover_enabled=”0″]IC0tZXhlbXBsbyBkZSB1c28gZG8gZGVuc2VfcmFuayBjb21vIGZ1bmNhbyBhZ3JlZ2FkYQogLS1kYWRvIHVtIHZhbG9yLCBhIHF1ZXJ5IHJldG9ybmEgYSBwb3NpY2FvIChyYW5rKSAKIC0tZGVzc2UgdmFsb3IsIGRlbnRybyBkYSB0YWJlbGEKIFNFTEVDVCBERU5TRV9SQU5LKDEwMDAwKSBXSVRISU4gR1JPVVAgKE9SREVSIEJZIFNBTEFSSU8gREVTQykgUE9TSUNBTwogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKCiAtLWV4ZW1wbG8gZGUgdXNvIGRvIGRlbnNlX3JhbmsgY29tbyBmdW5jYW8gYW5hbGl0aWNhCiAtLW1vc3RyYSBvIHJhbmsgZGUgc2FsYXJpbyBkZSBjYWRhIGZ1bmNpb25hcmlvLCBwb3IgZGVwYXJ0YW1lbnRvCiBTRUxFQ1QgSURfRU1QLAogTk9NRV9FTVAsCiBJRF9ERVBBUlRBTUVOVE8sCiBTQUxBUklPLAogREVOU0VfUkFOSygpIE9WRVIgKFBBUlRJVElPTiBCWSBJRF9ERVBBUlRBTUVOVE8gT1JERVIgQlkgU0FMQVJJTyBERVNDKSBBUyBSQU5LCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUw==[/et_pb_dmb_code_snippet]

2.7) FIRST E LAST

Retornam o primeiro e o último valores de uma sequência ordenada.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tZXhlbXBsbyBkZSB1c28gZG8gZmlyc3QgZSBsYXN0IGNvbW8gZnVuY29lcyBhZ3JlZ2FkYXMKIC0tbGlzdGEsIHBvciBkZXBhcnRhbWVudG8sIG8gZnVuY2lvbmFyaW8gbWFpcyB2ZWxobyBxdWUgdGVtIG8gCiAtLXBpb3Igc2FsYXJpbyBlIG8gZnVuY2lvbmFyaW8gbWFpcyBub3ZvIHF1ZSB0ZW0gbyBtZWxob3Igc2FsYXJpbwogU0VMRUNUIElEX0RFUEFSVEFNRU5UTywKIE1JTihTQUxBUklPKSBLRUVQIChERU5TRV9SQU5LIExBU1QgT1JERVIgQlkgSURBREUpIEFTIFBJT1JfU0FMQVJJT19FTVBfTUFJU19WRUxITywKIE1BWChTQUxBUklPKSBLRUVQIChERU5TRV9SQU5LIEZJUlNUIE9SREVSIEJZIElEQURFKSBBUyBNRUxIT1JfU0FMQVJJT19FTVBfTUFJU19OT1ZPCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUwogR1JPVVAgQlkgSURfREVQQVJUQU1FTlRPCiBPUkRFUiBCWSBJRF9ERVBBUlRBTUVOVE8KCiAtLWV4ZW1wbG8gZGUgdXNvIGRvIGZpcnN0IGUgbGFzdCBjb21vIGZ1bmNvZXMgYW5hbGl0aWNhcwogLS1saXN0YSwgcG9yIGZ1bmNpb25hcmlvLCBvIGZ1bmNpb25hcmlvIG1haXMgdmVsaG8gcXVlIHRlbSAKIC0tbyBwaW9yIHNhbGFyaW8gZSBvIG1haXMgbm92byBxdWUgdGVtIG8gbWVsaG9yIHNhbGFyaW8KIFNFTEVDVCBJRF9FTVAsIE5PTUVfRU1QLCBJREFERSwgU0FMQVJJTywKIE1JTihTQUxBUklPKSBLRUVQIChERU5TRV9SQU5LIExBU1QgT1JERVIgQlkgSURBREUpIAogICBPVkVSIChQQVJUSVRJT04gQlkgSURfREVQQVJUQU1FTlRPKSBBUyBQSU9SX1NBTEFSSU9fRU1QX01BSVNfVkVMSE8sCiBNQVgoU0FMQVJJTykgS0VFUCAoREVOU0VfUkFOSyBGSVJTVCBPUkRFUiBCWSBJREFERSkgCiAgIE9WRVIgKFBBUlRJVElPTiBCWSBJRF9ERVBBUlRBTUVOVE8pIEFTIE1FTEhPUl9TQUxBUklPX0VNUF9NQUlTX05PVk8KIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCiBPUkRFUiBCWSBJRF9FTVAsIE5PTUVfRU1QLCBJREFERSwgU0FMQVJJTw==” hover_enabled=”0″]IC0tZXhlbXBsbyBkZSB1c28gZG8gZmlyc3QgZSBsYXN0IGNvbW8gZnVuY29lcyBhZ3JlZ2FkYXMKIC0tbGlzdGEsIHBvciBkZXBhcnRhbWVudG8sIG8gZnVuY2lvbmFyaW8gbWFpcyB2ZWxobyBxdWUgdGVtIG8gCiAtLXBpb3Igc2FsYXJpbyBlIG8gZnVuY2lvbmFyaW8gbWFpcyBub3ZvIHF1ZSB0ZW0gbyBtZWxob3Igc2FsYXJpbwogU0VMRUNUIElEX0RFUEFSVEFNRU5UTywKIE1JTihTQUxBUklPKSBLRUVQIChERU5TRV9SQU5LIExBU1QgT1JERVIgQlkgSURBREUpIEFTIFBJT1JfU0FMQVJJT19FTVBfTUFJU19WRUxITywKIE1BWChTQUxBUklPKSBLRUVQIChERU5TRV9SQU5LIEZJUlNUIE9SREVSIEJZIElEQURFKSBBUyBNRUxIT1JfU0FMQVJJT19FTVBfTUFJU19OT1ZPCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUwogR1JPVVAgQlkgSURfREVQQVJUQU1FTlRPCiBPUkRFUiBCWSBJRF9ERVBBUlRBTUVOVE8KCiAtLWV4ZW1wbG8gZGUgdXNvIGRvIGZpcnN0IGUgbGFzdCBjb21vIGZ1bmNvZXMgYW5hbGl0aWNhcwogLS1saXN0YSwgcG9yIGZ1bmNpb25hcmlvLCBvIGZ1bmNpb25hcmlvIG1haXMgdmVsaG8gcXVlIHRlbSAKIC0tbyBwaW9yIHNhbGFyaW8gZSBvIG1haXMgbm92byBxdWUgdGVtIG8gbWVsaG9yIHNhbGFyaW8KIFNFTEVDVCBJRF9FTVAsIE5PTUVfRU1QLCBJREFERSwgU0FMQVJJTywKIE1JTihTQUxBUklPKSBLRUVQIChERU5TRV9SQU5LIExBU1QgT1JERVIgQlkgSURBREUpIAogICBPVkVSIChQQVJUSVRJT04gQlkgSURfREVQQVJUQU1FTlRPKSBBUyBQSU9SX1NBTEFSSU9fRU1QX01BSVNfVkVMSE8sCiBNQVgoU0FMQVJJTykgS0VFUCAoREVOU0VfUkFOSyBGSVJTVCBPUkRFUiBCWSBJREFERSkgCiAgIE9WRVIgKFBBUlRJVElPTiBCWSBJRF9ERVBBUlRBTUVOVE8pIEFTIE1FTEhPUl9TQUxBUklPX0VNUF9NQUlTX05PVk8KIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCiBPUkRFUiBCWSBJRF9FTVAsIE5PTUVfRU1QLCBJREFERSwgU0FMQVJJTw==[/et_pb_dmb_code_snippet]

2.8) FIRST_VALUE

Funciona como função analíca apenas. Retorna o primeiro valor em uma sequencia ordenada de valores.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tZXhlbXBsbyBkZSB1c28gZG8gZmlyc3RfdmFsdWUgY29tbyBmdW5jYW8gYW5hbGl0aWNhCiAtLWV4aWJlIG8gbm9tZSBkbyBlbXByZWdhZG8gcXVlIHJlY2ViZSBvIG1lbm9yIHNhbGFyaW8sIHBvciAKIC0tZGVwYXJ0YW1lbnRvCiBTRUxFQ1QgaWRfZW1wLAogbm9tZV9lbXAsCiBzYWxhcmlvLAogRklSU1RfVkFMVUUoTk9NRV9FTVApIE9WRVIgKFBBUlRJVElPTiBCWSBJRF9ERVBBUlRBTUVOVE8gT1JERVIgQlkgU0FMQVJJTykgQVMgTUVOT1JfU0FMQVJJTwogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKCiAtLWV4ZW1wbG8gZGUgdXNvIGRvIGZpcnN0X3ZhbHVlIGNvbW8gZnVuY2FvIGFuYWxpdGljYQogLS1leGliZSBvIG5vbWUgZG8gZW1wcmVnYWRvIHF1ZSByZWNlYmUgbyBtZW5vciBzYWxhcmlvLCAKIC0tcG9yIGRlcGFydGFtZW50by4KIC0tcGVyY2ViYSBxdWUsIHBhcmEgbXVkYXIgYSBleGliaWNhbyBkbyBtZW5vciBzYWxhcmlvLCBwYXJhIG8gCiAtLW1haW9yIHNhbGFyaW8sIGJhc3RhIGNvbG9jYXIgbyBkZXNjIG5vIGZpbmFsIGRvIHBhcnRpdGlvbiBieQogU0VMRUNUIGlkX2VtcCwKIG5vbWVfZW1wLAogc2FsYXJpbywKIEZJUlNUX1ZBTFVFKE5PTUVfRU1QKSBPVkVSIChQQVJUSVRJT04gQlkgSURfREVQQVJUQU1FTlRPIE9SREVSIEJZIFNBTEFSSU8gREVTQykgQVMgTUVOT1JfU0FMQVJJTwogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1M=” hover_enabled=”0″]IC0tZXhlbXBsbyBkZSB1c28gZG8gZmlyc3RfdmFsdWUgY29tbyBmdW5jYW8gYW5hbGl0aWNhCiAtLWV4aWJlIG8gbm9tZSBkbyBlbXByZWdhZG8gcXVlIHJlY2ViZSBvIG1lbm9yIHNhbGFyaW8sIHBvciAKIC0tZGVwYXJ0YW1lbnRvCiBTRUxFQ1QgaWRfZW1wLAogbm9tZV9lbXAsCiBzYWxhcmlvLAogRklSU1RfVkFMVUUoTk9NRV9FTVApIE9WRVIgKFBBUlRJVElPTiBCWSBJRF9ERVBBUlRBTUVOVE8gT1JERVIgQlkgU0FMQVJJTykgQVMgTUVOT1JfU0FMQVJJTwogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKCiAtLWV4ZW1wbG8gZGUgdXNvIGRvIGZpcnN0X3ZhbHVlIGNvbW8gZnVuY2FvIGFuYWxpdGljYQogLS1leGliZSBvIG5vbWUgZG8gZW1wcmVnYWRvIHF1ZSByZWNlYmUgbyBtZW5vciBzYWxhcmlvLCAKIC0tcG9yIGRlcGFydGFtZW50by4KIC0tcGVyY2ViYSBxdWUsIHBhcmEgbXVkYXIgYSBleGliaWNhbyBkbyBtZW5vciBzYWxhcmlvLCBwYXJhIG8gCiAtLW1haW9yIHNhbGFyaW8sIGJhc3RhIGNvbG9jYXIgbyBkZXNjIG5vIGZpbmFsIGRvIHBhcnRpdGlvbiBieQogU0VMRUNUIGlkX2VtcCwKIG5vbWVfZW1wLAogc2FsYXJpbywKIEZJUlNUX1ZBTFVFKE5PTUVfRU1QKSBPVkVSIChQQVJUSVRJT04gQlkgSURfREVQQVJUQU1FTlRPIE9SREVSIEJZIFNBTEFSSU8gREVTQykgQVMgTUVOT1JfU0FMQVJJTwogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1M=[/et_pb_dmb_code_snippet]

2.9) LAG

Funciona como função analítica apenas. Permite acessar informações de uma linha anterior à posição atual.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tZXhlbXBsbyBkZSB1c28gZG8gbGFnIGNvbW8gZnVuY2FvIGFuYWxpdGljYQogLS1leGliZSBhIGRpZmVyZW5jYSBlbnRyZSBvIHNhbGFyaW8gZG9zIGVtcHJlZ2Fkb3MsIG9yZGVuYWRvIAogLS1wZWxhIGlkYWRlLCBvdSBzZWphLCBkYWRvIHVtIGZ1bmNpb25hcmlvLCBjYWxjdWxhIGEgZGlmZXJlbmNhIAogLS1lbnRyZSBlc3NlIGZ1bmNpb25hcmlvLCBlIHByaW1laXJvIG1haXMgbm92byBkbyBxdWUgZWxlCiBTRUxFQ1QgSURfRU1QLAogTk9NRV9FTVAsCiBTQUxBUklPLAogSURBREUsCiBMQUcoU0FMQVJJTywgMSwgMCkgT1ZFUiAoT1JERVIgQlkgSURBREUpIEFTIFNBTEFSSU9fQU5URVJJT1IsCiBTQUxBUklPIC0gTEFHKFNBTEFSSU8sIDEsIDApIE9WRVIgKE9SREVSIEJZIElEQURFKSBBUyBESUZFUkVOQ0EKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCiBPUkRFUiBCWSBJREFERQ==” hover_enabled=”0″]IC0tZXhlbXBsbyBkZSB1c28gZG8gbGFnIGNvbW8gZnVuY2FvIGFuYWxpdGljYQogLS1leGliZSBhIGRpZmVyZW5jYSBlbnRyZSBvIHNhbGFyaW8gZG9zIGVtcHJlZ2Fkb3MsIG9yZGVuYWRvIAogLS1wZWxhIGlkYWRlLCBvdSBzZWphLCBkYWRvIHVtIGZ1bmNpb25hcmlvLCBjYWxjdWxhIGEgZGlmZXJlbmNhIAogLS1lbnRyZSBlc3NlIGZ1bmNpb25hcmlvLCBlIHByaW1laXJvIG1haXMgbm92byBkbyBxdWUgZWxlCiBTRUxFQ1QgSURfRU1QLAogTk9NRV9FTVAsCiBTQUxBUklPLAogSURBREUsCiBMQUcoU0FMQVJJTywgMSwgMCkgT1ZFUiAoT1JERVIgQlkgSURBREUpIEFTIFNBTEFSSU9fQU5URVJJT1IsCiBTQUxBUklPIC0gTEFHKFNBTEFSSU8sIDEsIDApIE9WRVIgKE9SREVSIEJZIElEQURFKSBBUyBESUZFUkVOQ0EKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCiBPUkRFUiBCWSBJREFERQ==[/et_pb_dmb_code_snippet]

OBS1: O segundo parâmetro da cláusula LAG indica N linhas anteriores, ou seja, no exemplo acima, eu especifico 1, que significa a diferença entre o registro da linha atual e o registro n – 1. Caso eu coloque 2, a query retornará a diferença entre o registro atual e o registro n – 2, e assim por diante.

Até o próximo post.