Neste 3º tópico, falarei sobre as seguintes funções analíticas:

  • PERCENT_RANK
  • RANK
  • RATIO_TO_REPORT
  • ROW_NUMBER
  • STDDEV

1) PERCENT_RANK

Seu retorno varia de 0 a 1. A primeira linha em qualquer conjunto possui o PERCENT_RANK de 0. Funciona tanto como função analítica, quanto função agragada. A meu ver essa função é mais útil quando usada como função analítica, pois é possível determinar rankings para valores, que será exemplificado mais adiante:

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

2) RANK

Calcula o rank (posição) de um valor em um grupo de valores. Pode funcionar tanto como função agregada e função analítica. Função útil para calcular posições dentro de um conjunto de dados, como por exemplo quem foi o funcionário que mais gastou em despesas com viagens:

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

3) RATIO_TO_REPORT

Funciona como função analítica. Calcula a razão, ou proporção de um valor, em relação a soma de um conjunto de valores.

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

4) ROW_NUMBER

Funciona como função analítica. Atribui um número único para cada linha.

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

5) STDDEV

Retorna o Desvio Padrão de um conjunto de valores. Por Desvio Padrão, entende-se:

“O desvio padrão é uma medida de dispersão usada com a média. Mede a variabilidade dos valores à volta da média. O valor mínimo do desvio padrão é 0 indicando que não há variabilidade, ou seja, que todos os valores são iguais à média.
fonte: http://stat2.med.up.pt/cursop/glossario/dpadrao.html”

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

É isso ai caros DBAs e desenvolvedores, nos vemos no próximo e último post desta série. Até breve. “Keep querying”