"use strict";(self.webpackChunk=self.webpackChunk||[]).push([[674],{3905:(e,t,a)=>{a.d(t,{Zo:()=>p,kt:()=>m});var n=a(7294);function r(e,t,a){return t in e?Object.defineProperty(e,t,{value:a,enumerable:!0,configurable:!0,writable:!0}):e[t]=a,e}function o(e,t){var a=Object.keys(e);if(Object.getOwnPropertySymbols){var n=Object.getOwnPropertySymbols(e);t&&(n=n.filter((function(t){return Object.getOwnPropertyDescriptor(e,t).enumerable}))),a.push.apply(a,n)}return a}function i(e){for(var t=1;t<arguments.length;t++){var a=null!=arguments[t]?arguments[t]:{};t%2?o(Object(a),!0).forEach((function(t){r(e,t,a[t])})):Object.getOwnPropertyDescriptors?Object.defineProperties(e,Object.getOwnPropertyDescriptors(a)):o(Object(a)).forEach((function(t){Object.defineProperty(e,t,Object.getOwnPropertyDescriptor(a,t))}))}return e}function s(e,t){if(null==e)return{};var a,n,r=function(e,t){if(null==e)return{};var a,n,r={},o=Object.keys(e);for(n=0;n<o.length;n++)a=o[n],t.indexOf(a)>=0||(r[a]=e[a]);return r}(e,t);if(Object.getOwnPropertySymbols){var o=Object.getOwnPropertySymbols(e);for(n=0;n<o.length;n++)a=o[n],t.indexOf(a)>=0||Object.prototype.propertyIsEnumerable.call(e,a)&&(r[a]=e[a])}return r}var l=n.createContext({}),g=function(e){var t=n.useContext(l),a=t;return e&&(a="function"==typeof e?e(t):i(i({},t),e)),a},p=function(e){var t=g(e.components);return n.createElement(l.Provider,{value:t},e.children)},u="mdxType",c={inlineCode:"code",wrapper:function(e){var t=e.children;return n.createElement(n.Fragment,{},t)}},d=n.forwardRef((function(e,t){var a=e.components,r=e.mdxType,o=e.originalType,l=e.parentName,p=s(e,["components","mdxType","originalType","parentName"]),u=g(a),d=r,m=u["".concat(l,".").concat(d)]||u[d]||c[d]||o;return a?n.createElement(m,i(i({ref:t},p),{},{components:a})):n.createElement(m,i({ref:t},p))}));function m(e,t){var a=arguments,r=t&&t.mdxType;if("string"==typeof e||r){var o=a.length,i=new Array(o);i[0]=d;var s={};for(var l in t)hasOwnProperty.call(t,l)&&(s[l]=t[l]);s.originalType=e,s[u]="string"==typeof e?e:r,i[1]=s;for(var g=2;g<o;g++)i[g]=a[g];return n.createElement.apply(null,i)}return n.createElement.apply(null,a)}d.displayName="MDXCreateElement"},7405:(e,t,a)=>{a.r(t),a.d(t,{assets:()=>p,contentTitle:()=>l,default:()=>m,frontMatter:()=>s,metadata:()=>g,toc:()=>u});var n=a(7462),r=a(3366),o=(a(7294),a(3905)),i=["components"],s={id:"post-aggregations",title:"Post-aggregations"},l=void 0,g={unversionedId:"querying/post-aggregations",id:"querying/post-aggregations",title:"Post-aggregations",description:"\x3c!--",source:"@site/docs/latest/querying/post-aggregations.md",sourceDirName:"querying",slug:"/querying/post-aggregations",permalink:"/docs/latest/querying/post-aggregations",draft:!1,tags:[],version:"current",frontMatter:{id:"post-aggregations",title:"Post-aggregations"},sidebar:"docs",previous:{title:"Aggregations",permalink:"/docs/latest/querying/aggregations"},next:{title:"Expressions",permalink:"/docs/latest/misc/math-expr"}},p={},u=[{value:"Arithmetic post-aggregator",id:"arithmetic-post-aggregator",level:3},{value:"Field accessor post-aggregators",id:"field-accessor-post-aggregators",level:3},{value:"Constant post-aggregator",id:"constant-post-aggregator",level:3},{value:"Expression post-aggregator",id:"expression-post-aggregator",level:3},{value:"Greatest / Least post-aggregators",id:"greatest--least-post-aggregators",level:3},{value:"JavaScript post-aggregator",id:"javascript-post-aggregator",level:3},{value:"HyperUnique Cardinality post-aggregator",id:"hyperunique-cardinality-post-aggregator",level:3},{value:"Example Usage",id:"example-usage",level:2}],c={toc:u},d="wrapper";function m(e){var t=e.components,a=(0,r.Z)(e,i);return(0,o.kt)(d,(0,n.Z)({},c,a,{components:t,mdxType:"MDXLayout"}),(0,o.kt)("blockquote",null,(0,o.kt)("p",{parentName:"blockquote"},"Apache Druid supports two query languages: ",(0,o.kt)("a",{parentName:"p",href:"/docs/latest/querying/sql"},"Druid SQL")," and ",(0,o.kt)("a",{parentName:"p",href:"/docs/latest/querying/"},"native queries"),".\nThis document describes the native\nlanguage. For information about functions available in SQL, refer to the\n",(0,o.kt)("a",{parentName:"p",href:"/docs/latest/querying/sql-aggregations"},"SQL documentation"),".")),(0,o.kt)("p",null,"Post-aggregations are specifications of processing that should happen on aggregated values as they come out of Apache Druid. If you include a post aggregation as part of a query, make sure to include all aggregators the post-aggregator requires."),(0,o.kt)("p",null,"There are several post-aggregators available."),(0,o.kt)("h3",{id:"arithmetic-post-aggregator"},"Arithmetic post-aggregator"),(0,o.kt)("p",null,"The arithmetic post-aggregator applies the provided function to the given\nfields from left to right. The fields can be aggregators or other post aggregators."),(0,o.kt)("p",null,"Supported functions are ",(0,o.kt)("inlineCode",{parentName:"p"},"+"),", ",(0,o.kt)("inlineCode",{parentName:"p"},"-"),", ",(0,o.kt)("inlineCode",{parentName:"p"},"*"),", ",(0,o.kt)("inlineCode",{parentName:"p"},"/"),", ",(0,o.kt)("inlineCode",{parentName:"p"},"pow")," and ",(0,o.kt)("inlineCode",{parentName:"p"},"quotient"),"."),(0,o.kt)("p",null,(0,o.kt)("strong",{parentName:"p"},"Note"),":"),(0,o.kt)("ul",null,(0,o.kt)("li",{parentName:"ul"},(0,o.kt)("inlineCode",{parentName:"li"},"/")," division always returns ",(0,o.kt)("inlineCode",{parentName:"li"},"0")," if dividing by",(0,o.kt)("inlineCode",{parentName:"li"},"0"),", regardless of the numerator."),(0,o.kt)("li",{parentName:"ul"},(0,o.kt)("inlineCode",{parentName:"li"},"quotient")," division behaves like regular floating point division"),(0,o.kt)("li",{parentName:"ul"},"Arithmetic post-aggregators always use floating point arithmetic.")),(0,o.kt)("p",null,"Arithmetic post-aggregators may also specify an ",(0,o.kt)("inlineCode",{parentName:"p"},"ordering"),", which defines the order\nof resulting values when sorting results (this can be useful for topN queries for instance):"),(0,o.kt)("ul",null,(0,o.kt)("li",{parentName:"ul"},"If no ordering (or ",(0,o.kt)("inlineCode",{parentName:"li"},"null"),") is specified, the default floating point ordering is used."),(0,o.kt)("li",{parentName:"ul"},(0,o.kt)("inlineCode",{parentName:"li"},"numericFirst")," ordering always returns finite values first, followed by ",(0,o.kt)("inlineCode",{parentName:"li"},"NaN"),", and infinite values last.")),(0,o.kt)("p",null,"The grammar for an arithmetic post aggregation is:"),(0,o.kt)("pre",null,(0,o.kt)("code",{parentName:"pre",className:"language-json"},'postAggregation : {\n  "type"  : "arithmetic",\n  "name"  : <output_name>,\n  "fn"    : <arithmetic_function>,\n  "fields": [<post_aggregator>, <post_aggregator>, ...],\n  "ordering" : <null (default), or "numericFirst">\n}\n')),(0,o.kt)("h3",{id:"field-accessor-post-aggregators"},"Field accessor post-aggregators"),(0,o.kt)("p",null,"These post-aggregators return the value produced by the specified ",(0,o.kt)("a",{parentName:"p",href:"/docs/latest/querying/aggregations"},"aggregator"),"."),(0,o.kt)("p",null,(0,o.kt)("inlineCode",{parentName:"p"},"fieldName")," refers to the output name of the aggregator given in the ",(0,o.kt)("a",{parentName:"p",href:"/docs/latest/querying/aggregations"},"aggregations"),' portion of the query.\nFor complex aggregators, like "cardinality" and "hyperUnique", the ',(0,o.kt)("inlineCode",{parentName:"p"},"type"),' of the post-aggregator determines what\nthe post-aggregator will return. Use type "fieldAccess" to return the raw aggregation object, or use type\n"finalizingFieldAccess" to return a finalized value, such as an estimated cardinality.'),(0,o.kt)("pre",null,(0,o.kt)("code",{parentName:"pre",className:"language-json"},'{ "type" : "fieldAccess", "name": <output_name>, "fieldName" : <aggregator_name> }\n')),(0,o.kt)("p",null,"or"),(0,o.kt)("pre",null,(0,o.kt)("code",{parentName:"pre",className:"language-json"},'{ "type" : "finalizingFieldAccess", "name": <output_name>, "fieldName" : <aggregator_name> }\n')),(0,o.kt)("h3",{id:"constant-post-aggregator"},"Constant post-aggregator"),(0,o.kt)("p",null,"The constant post-aggregator always returns the specified value."),(0,o.kt)("pre",null,(0,o.kt)("code",{parentName:"pre",className:"language-json"},'{ "type"  : "constant", "name"  : <output_name>, "value" : <numerical_value> }\n')),(0,o.kt)("h3",{id:"expression-post-aggregator"},"Expression post-aggregator"),(0,o.kt)("p",null,"The expression post-aggregator is defined using a Druid ",(0,o.kt)("a",{parentName:"p",href:"/docs/latest/misc/math-expr"},"expression"),"."),(0,o.kt)("pre",null,(0,o.kt)("code",{parentName:"pre",className:"language-json"},'{\n  "type": "expression",\n  "name": <output_name>,\n  "expression": <post-aggregation expression>,\n  "ordering" : <null (default), or "numericFirst">\n}\n')),(0,o.kt)("h3",{id:"greatest--least-post-aggregators"},"Greatest / Least post-aggregators"),(0,o.kt)("p",null,(0,o.kt)("inlineCode",{parentName:"p"},"doubleGreatest")," and ",(0,o.kt)("inlineCode",{parentName:"p"},"longGreatest")," computes the maximum of all fields and Double.NEGATIVE_INFINITY.\n",(0,o.kt)("inlineCode",{parentName:"p"},"doubleLeast")," and ",(0,o.kt)("inlineCode",{parentName:"p"},"longLeast")," computes the minimum of all fields and Double.POSITIVE_INFINITY."),(0,o.kt)("p",null,"The difference between the ",(0,o.kt)("inlineCode",{parentName:"p"},"doubleMax")," aggregator and the ",(0,o.kt)("inlineCode",{parentName:"p"},"doubleGreatest")," post-aggregator is that ",(0,o.kt)("inlineCode",{parentName:"p"},"doubleMax")," returns the highest value of\nall rows for one specific column while ",(0,o.kt)("inlineCode",{parentName:"p"},"doubleGreatest")," returns the highest value of multiple columns in one row. These are similar to the\nSQL ",(0,o.kt)("inlineCode",{parentName:"p"},"MAX")," and ",(0,o.kt)("inlineCode",{parentName:"p"},"GREATEST")," functions."),(0,o.kt)("p",null,"Example:"),(0,o.kt)("pre",null,(0,o.kt)("code",{parentName:"pre",className:"language-json"},'{\n  "type"  : "doubleGreatest",\n  "name"  : <output_name>,\n  "fields": [<post_aggregator>, <post_aggregator>, ...]\n}\n')),(0,o.kt)("h3",{id:"javascript-post-aggregator"},"JavaScript post-aggregator"),(0,o.kt)("p",null,"Applies the provided JavaScript function to the given fields. Fields are passed as arguments to the JavaScript function in the given order."),(0,o.kt)("pre",null,(0,o.kt)("code",{parentName:"pre",className:"language-json"},'postAggregation : {\n  "type": "javascript",\n  "name": <output_name>,\n  "fieldNames" : [<aggregator_name>, <aggregator_name>, ...],\n  "function": <javascript function>\n}\n')),(0,o.kt)("p",null,"Example JavaScript aggregator:"),(0,o.kt)("pre",null,(0,o.kt)("code",{parentName:"pre",className:"language-json"},'{\n  "type": "javascript",\n  "name": "absPercent",\n  "fieldNames": ["delta", "total"],\n  "function": "function(delta, total) { return 100 * Math.abs(delta) / total; }"\n}\n')),(0,o.kt)("blockquote",null,(0,o.kt)("p",{parentName:"blockquote"},"JavaScript-based functionality is disabled by default. Please refer to the Druid ",(0,o.kt)("a",{parentName:"p",href:"/docs/latest/development/javascript"},"JavaScript programming guide")," for guidelines about using Druid's JavaScript functionality, including instructions on how to enable it.")),(0,o.kt)("h3",{id:"hyperunique-cardinality-post-aggregator"},"HyperUnique Cardinality post-aggregator"),(0,o.kt)("p",null,"The hyperUniqueCardinality post aggregator is used to wrap a hyperUnique object such that it can be used in post aggregations."),(0,o.kt)("pre",null,(0,o.kt)("code",{parentName:"pre",className:"language-json"},'{\n  "type"  : "hyperUniqueCardinality",\n  "name": <output name>,\n  "fieldName"  : <the name field value of the hyperUnique aggregator>\n}\n')),(0,o.kt)("p",null,"It can be used in a sample calculation as so:"),(0,o.kt)("pre",null,(0,o.kt)("code",{parentName:"pre",className:"language-json"},'  "aggregations" : [{\n    {"type" : "count", "name" : "rows"},\n    {"type" : "hyperUnique", "name" : "unique_users", "fieldName" : "uniques"}\n  }],\n  "postAggregations" : [{\n    "type"   : "arithmetic",\n    "name"   : "average_users_per_row",\n    "fn"     : "/",\n    "fields" : [\n      { "type" : "hyperUniqueCardinality", "fieldName" : "unique_users" },\n      { "type" : "fieldAccess", "name" : "rows", "fieldName" : "rows" }\n    ]\n  }]\n')),(0,o.kt)("p",null,'This post-aggregator will inherit the rounding behavior of the aggregator it references. Note that this inheritance\nis only effective if you directly reference an aggregator. Going through another post-aggregator, for example, will\ncause the user-specified rounding behavior to get lost and default to "no rounding".'),(0,o.kt)("h2",{id:"example-usage"},"Example Usage"),(0,o.kt)("p",null,'In this example, let\u2019s calculate a simple percentage using post aggregators. Let\u2019s imagine our data set has a metric called "total".'),(0,o.kt)("p",null,"The format of the query JSON is as follows:"),(0,o.kt)("pre",null,(0,o.kt)("code",{parentName:"pre",className:"language-json"},'{\n  ...\n  "aggregations" : [\n    { "type" : "count", "name" : "rows" },\n    { "type" : "doubleSum", "name" : "tot", "fieldName" : "total" }\n  ],\n  "postAggregations" : [{\n    "type"   : "arithmetic",\n    "name"   : "average",\n    "fn"     : "/",\n    "fields" : [\n           { "type" : "fieldAccess", "name" : "tot", "fieldName" : "tot" },\n           { "type" : "fieldAccess", "name" : "rows", "fieldName" : "rows" }\n         ]\n  }]\n  ...\n}\n')),(0,o.kt)("pre",null,(0,o.kt)("code",{parentName:"pre",className:"language-json"},'{\n  ...\n  "aggregations" : [\n    { "type" : "doubleSum", "name" : "tot", "fieldName" : "total" },\n    { "type" : "doubleSum", "name" : "part", "fieldName" : "part" }\n  ],\n  "postAggregations" : [{\n    "type"   : "arithmetic",\n    "name"   : "part_percentage",\n    "fn"     : "*",\n    "fields" : [\n       { "type"   : "arithmetic",\n         "name"   : "ratio",\n         "fn"     : "/",\n         "fields" : [\n           { "type" : "fieldAccess", "name" : "part", "fieldName" : "part" },\n           { "type" : "fieldAccess", "name" : "tot", "fieldName" : "tot" }\n         ]\n       },\n       { "type" : "constant", "name": "const", "value" : 100 }\n    ]\n  }]\n  ...\n}\n')),(0,o.kt)("p",null,"The same could be computed using an expression post-aggregator: "),(0,o.kt)("pre",null,(0,o.kt)("code",{parentName:"pre",className:"language-json"},'{\n  ...\n  "aggregations" : [\n    { "type" : "doubleSum", "name" : "tot", "fieldName" : "total" },\n    { "type" : "doubleSum", "name" : "part", "fieldName" : "part" }\n  ],\n  "postAggregations" : [{\n    "type"       : "expression",\n    "name"       : "part_percentage",\n    "expression" : "100 * (part / tot)"\n  }]\n  ...\n}\n')))}m.isMDXComponent=!0}}]);