Nesta 4º e última parte da série, falarei sobre as seguintes funções analíticas:

  • SUM
  • VAR_POP
  • VARIANCE

1) SUM

Retorna a soma de valores de determinada coluna. Esta, na minha opinião, é uma das funções mais úteis e usadas por DBAs e desenvolvedores. Pode ser usada como função agragada e analítica.

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

2) VAR_POP

Retorna a variância da população para um conjunto de números, após descartar os valores NULL. Pode ser usada tanto como função agregada, quanto como função analítica.

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

3) VARIANCE

Retorna a variância de determinado conjunto de valores. Pode ser usada tanto como função analítica, quanto como função agregada Por variância entende-se:

“[…]é uma medida da sua dispersão estatística, indicando quão longe em geral os seus valores se encontram do valor esperado.[…]
fonte: Wikipedia”

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

Bom, então é isso. Essa série te posts termina aqui. Muito em breve teremos mais sequências de posts sobre os mais variados assuntos relacionados a bancos de dados. Até mais. Keep Querying.