Dando continuidade a série de posts relacionados às funções analíticas do Oracle, falarei neste post sobre mais algumas delas. As funções abordadas serão:

  • LAST_VALUE
  • LEAD
  • LISTAGG
  • MAX
  • MIN
  • NTILE

1) LAST_VALUE

Funciona como função analítica. Ela serve para retornar o último valor de uma série ordenada de valores.

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

2) LEAD

Funciona como uma função analítica. Permite acessar mais de uma linha da mesma tabela sem a necessidade de um SELF-JOIN.

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

3) LISTAGG

Ordena os dados em cada grupo, especificado pelo ORDER BY, e posteriormente concatena esses dados. Serve para transformar uma coluna em linhas. É possivel usado tanto como função analítica, quanto como função agregada.

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

4) MAX

Retorma o maior valor de determinada coluna. É possível ser usado tanto como função analítica, quanto como função agregada.

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

5) MIN

Retorna o menor valor de determinada coluna. É possível ser usado tanto como função analítica, quanto como função agregada.

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

6) NTILE

Função analítica usada para dividir um conjunto ordenado de valores em N partes iguais, e cada parte representa 1/N da população.

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

Até a próxima parte!

Para conferir o primeiro post da série Funções Analíticas, abaixo:

Funções Analíticas – Parte 1