| "use strict";(self.webpackChunk=self.webpackChunk||[]).push([[6814],{3905:(e,n,a)=>{a.d(n,{Zo:()=>p,kt:()=>d});var t=a(67294);function r(e,n,a){return n in e?Object.defineProperty(e,n,{value:a,enumerable:!0,configurable:!0,writable:!0}):e[n]=a,e}function l(e,n){var a=Object.keys(e);if(Object.getOwnPropertySymbols){var t=Object.getOwnPropertySymbols(e);n&&(t=t.filter((function(n){return Object.getOwnPropertyDescriptor(e,n).enumerable}))),a.push.apply(a,t)}return a}function i(e){for(var n=1;n<arguments.length;n++){var a=null!=arguments[n]?arguments[n]:{};n%2?l(Object(a),!0).forEach((function(n){r(e,n,a[n])})):Object.getOwnPropertyDescriptors?Object.defineProperties(e,Object.getOwnPropertyDescriptors(a)):l(Object(a)).forEach((function(n){Object.defineProperty(e,n,Object.getOwnPropertyDescriptor(a,n))}))}return e}function o(e,n){if(null==e)return{};var a,t,r=function(e,n){if(null==e)return{};var a,t,r={},l=Object.keys(e);for(t=0;t<l.length;t++)a=l[t],n.indexOf(a)>=0||(r[a]=e[a]);return r}(e,n);if(Object.getOwnPropertySymbols){var l=Object.getOwnPropertySymbols(e);for(t=0;t<l.length;t++)a=l[t],n.indexOf(a)>=0||Object.prototype.propertyIsEnumerable.call(e,a)&&(r[a]=e[a])}return r}var s=t.createContext({}),u=function(e){var n=t.useContext(s),a=n;return e&&(a="function"==typeof e?e(n):i(i({},n),e)),a},p=function(e){var n=u(e.components);return t.createElement(s.Provider,{value:n},e.children)},g="mdxType",c={inlineCode:"code",wrapper:function(e){var n=e.children;return t.createElement(t.Fragment,{},n)}},m=t.forwardRef((function(e,n){var a=e.components,r=e.mdxType,l=e.originalType,s=e.parentName,p=o(e,["components","mdxType","originalType","parentName"]),g=u(a),m=r,d=g["".concat(s,".").concat(m)]||g[m]||c[m]||l;return a?t.createElement(d,i(i({ref:n},p),{},{components:a})):t.createElement(d,i({ref:n},p))}));function d(e,n){var a=arguments,r=n&&n.mdxType;if("string"==typeof e||r){var l=a.length,i=new Array(l);i[0]=m;var o={};for(var s in n)hasOwnProperty.call(n,s)&&(o[s]=n[s]);o.originalType=e,o[g]="string"==typeof e?e:r,i[1]=o;for(var u=2;u<l;u++)i[u]=a[u];return t.createElement.apply(null,i)}return t.createElement.apply(null,a)}m.displayName="MDXCreateElement"},24294:(e,n,a)=>{a.r(n),a.d(n,{assets:()=>p,contentTitle:()=>s,default:()=>d,frontMatter:()=>o,metadata:()=>u,toc:()=>g});var t=a(87462),r=a(63366),l=(a(67294),a(3905)),i=["components"],o={id:"having",title:"Having filters (groupBy)"},s=void 0,u={unversionedId:"querying/having",id:"querying/having",title:"Having filters (groupBy)",description:"\x3c!--",source:"@site/docs/latest/querying/having.md",sourceDirName:"querying",slug:"/querying/having",permalink:"/docs/latest/querying/having",draft:!1,tags:[],version:"current",frontMatter:{id:"having",title:"Having filters (groupBy)"},sidebar:"docs",previous:{title:"Expressions",permalink:"/docs/latest/querying/math-expr"},next:{title:"Sorting and limiting (groupBy)",permalink:"/docs/latest/querying/limitspec"}},p={},g=[{value:"Query filters",id:"query-filters",level:3},{value:"Numeric filters",id:"numeric-filters",level:3},{value:"Equal To",id:"equal-to",level:4},{value:"Greater Than",id:"greater-than",level:4},{value:"Less Than",id:"less-than",level:4},{value:"Dimension Selector Filter",id:"dimension-selector-filter",level:3},{value:"dimSelector",id:"dimselector",level:4},{value:"Logical expression filters",id:"logical-expression-filters",level:3},{value:"AND",id:"and",level:4},{value:"OR",id:"or",level:4},{value:"NOT",id:"not",level:4}],c={toc:g},m="wrapper";function d(e){var n=e.components,a=(0,r.Z)(e,i);return(0,l.kt)(m,(0,t.Z)({},c,a,{components:n,mdxType:"MDXLayout"}),(0,l.kt)("admonition",{type:"info"},(0,l.kt)("p",{parentName:"admonition"}," Apache Druid supports two query languages: ",(0,l.kt)("a",{parentName:"p",href:"/docs/latest/querying/sql"},"Druid SQL")," and ",(0,l.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,l.kt)("a",{parentName:"p",href:"/docs/latest/querying/sql-scalar"},"SQL documentation"),".")),(0,l.kt)("p",null,"A having clause is a JSON object identifying which rows from a groupBy query should be returned, by specifying conditions on aggregated values."),(0,l.kt)("p",null,"It is essentially the equivalent of the HAVING clause in SQL."),(0,l.kt)("p",null,"Apache Druid supports the following types of having clauses."),(0,l.kt)("h3",{id:"query-filters"},"Query filters"),(0,l.kt)("p",null,"Query filter HavingSpecs allow all ",(0,l.kt)("a",{parentName:"p",href:"/docs/latest/querying/filters"},"Druid query filters")," to be used in the Having part of the query."),(0,l.kt)("p",null,"The grammar for a query filter HavingSpec is:"),(0,l.kt)("pre",null,(0,l.kt)("code",{parentName:"pre",className:"language-json"},'{\n "queryType": "groupBy",\n "dataSource": "sample_datasource",\n ...\n "having":\n {\n "type" : "filter",\n "filter" : <any Druid query filter>\n }\n}\n')),(0,l.kt)("p",null,"For example, to use a selector filter:"),(0,l.kt)("pre",null,(0,l.kt)("code",{parentName:"pre",className:"language-json"},'{\n "queryType": "groupBy",\n "dataSource": "sample_datasource",\n ...\n "having":\n {\n "type" : "filter",\n "filter" : {\n "type": "selector",\n "dimension" : "<dimension>",\n "value" : "<dimension_value>"\n }\n }\n}\n')),(0,l.kt)("p",null,'You can use "filter" HavingSpecs to filter on the timestamp of result rows by applying a filter to the "',"_","_",'time"\ncolumn.'),(0,l.kt)("h3",{id:"numeric-filters"},"Numeric filters"),(0,l.kt)("p",null,"The simplest having clause is a numeric filter.\nNumeric filters can be used as the base filters for more complex boolean expressions of filters."),(0,l.kt)("p",null,"Here's an example of a having-clause numeric filter:"),(0,l.kt)("pre",null,(0,l.kt)("code",{parentName:"pre",className:"language-json"},'{\n "queryType": "groupBy",\n "dataSource": "sample_datasource",\n ...\n "having":\n {\n "type": "greaterThan",\n "aggregation": "<aggregate_metric>",\n "value": <numeric_value>\n }\n}\n')),(0,l.kt)("h4",{id:"equal-to"},"Equal To"),(0,l.kt)("p",null,"The equalTo filter will match rows with a specific aggregate value.\nThe grammar for an ",(0,l.kt)("inlineCode",{parentName:"p"},"equalTo")," filter is as follows:"),(0,l.kt)("pre",null,(0,l.kt)("code",{parentName:"pre",className:"language-json"},'{\n "queryType": "groupBy",\n "dataSource": "sample_datasource",\n ...\n "having":\n {\n "type": "equalTo",\n "aggregation": "<aggregate_metric>",\n "value": <numeric_value>\n }\n}\n')),(0,l.kt)("p",null,"This is the equivalent of ",(0,l.kt)("inlineCode",{parentName:"p"},"HAVING <aggregate> = <value>"),"."),(0,l.kt)("h4",{id:"greater-than"},"Greater Than"),(0,l.kt)("p",null,"The greaterThan filter will match rows with aggregate values greater than the given value.\nThe grammar for a ",(0,l.kt)("inlineCode",{parentName:"p"},"greaterThan")," filter is as follows:"),(0,l.kt)("pre",null,(0,l.kt)("code",{parentName:"pre",className:"language-json"},'{\n "queryType": "groupBy",\n "dataSource": "sample_datasource",\n ...\n "having":\n {\n "type": "greaterThan",\n "aggregation": "<aggregate_metric>",\n "value": <numeric_value>\n }\n}\n')),(0,l.kt)("p",null,"This is the equivalent of ",(0,l.kt)("inlineCode",{parentName:"p"},"HAVING <aggregate> > <value>"),"."),(0,l.kt)("h4",{id:"less-than"},"Less Than"),(0,l.kt)("p",null,"The lessThan filter will match rows with aggregate values less than the specified value.\nThe grammar for a ",(0,l.kt)("inlineCode",{parentName:"p"},"greaterThan")," filter is as follows:"),(0,l.kt)("pre",null,(0,l.kt)("code",{parentName:"pre",className:"language-json"},'{\n "queryType": "groupBy",\n "dataSource": "sample_datasource",\n ...\n "having":\n {\n "type": "lessThan",\n "aggregation": "<aggregate_metric>",\n "value": <numeric_value>\n }\n}\n')),(0,l.kt)("p",null,"This is the equivalent of ",(0,l.kt)("inlineCode",{parentName:"p"},"HAVING <aggregate> < <value>"),"."),(0,l.kt)("h3",{id:"dimension-selector-filter"},"Dimension Selector Filter"),(0,l.kt)("h4",{id:"dimselector"},"dimSelector"),(0,l.kt)("p",null,"The dimSelector filter will match rows with dimension values equal to the specified value.\nThe grammar for a ",(0,l.kt)("inlineCode",{parentName:"p"},"dimSelector")," filter is as follows:"),(0,l.kt)("pre",null,(0,l.kt)("code",{parentName:"pre",className:"language-json"},'{\n "queryType": "groupBy",\n "dataSource": "sample_datasource",\n ...\n "having":\n {\n "type": "dimSelector",\n "dimension": "<dimension>",\n "value": <dimension_value>\n }\n}\n')),(0,l.kt)("h3",{id:"logical-expression-filters"},"Logical expression filters"),(0,l.kt)("h4",{id:"and"},"AND"),(0,l.kt)("p",null,"The grammar for an AND filter is as follows:"),(0,l.kt)("pre",null,(0,l.kt)("code",{parentName:"pre",className:"language-json"},'{\n "queryType": "groupBy",\n "dataSource": "sample_datasource",\n ...\n "having":\n {\n "type": "and",\n "havingSpecs": [\n {\n "type": "greaterThan",\n "aggregation": "<aggregate_metric>",\n "value": <numeric_value>\n },\n {\n "type": "lessThan",\n "aggregation": "<aggregate_metric>",\n "value": <numeric_value>\n }\n ]\n }\n}\n')),(0,l.kt)("h4",{id:"or"},"OR"),(0,l.kt)("p",null,"The grammar for an OR filter is as follows:"),(0,l.kt)("pre",null,(0,l.kt)("code",{parentName:"pre",className:"language-json"},'{\n "queryType": "groupBy",\n "dataSource": "sample_datasource",\n ...\n "having":\n {\n "type": "or",\n "havingSpecs": [\n {\n "type": "greaterThan",\n "aggregation": "<aggregate_metric>",\n "value": <numeric_value>\n },\n {\n "type": "equalTo",\n "aggregation": "<aggregate_metric>",\n "value": <numeric_value>\n }\n ]\n }\n}\n')),(0,l.kt)("h4",{id:"not"},"NOT"),(0,l.kt)("p",null,"The grammar for a NOT filter is as follows:"),(0,l.kt)("pre",null,(0,l.kt)("code",{parentName:"pre",className:"language-json"},'{\n "queryType": "groupBy",\n "dataSource": "sample_datasource",\n ...\n "having":\n {\n "type": "not",\n "havingSpec":\n {\n "type": "equalTo",\n "aggregation": "<aggregate_metric>",\n "value": <numeric_value>\n }\n }\n}\n')))}d.isMDXComponent=!0}}]); |