| "use strict";(self.webpackChunk=self.webpackChunk||[]).push([[2993],{3905:(e,n,a)=>{a.d(n,{Zo:()=>p,kt:()=>d});var r=a(67294);function t(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 r=Object.getOwnPropertySymbols(e);n&&(r=r.filter((function(n){return Object.getOwnPropertyDescriptor(e,n).enumerable}))),a.push.apply(a,r)}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){t(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,r,t=function(e,n){if(null==e)return{};var a,r,t={},l=Object.keys(e);for(r=0;r<l.length;r++)a=l[r],n.indexOf(a)>=0||(t[a]=e[a]);return t}(e,n);if(Object.getOwnPropertySymbols){var l=Object.getOwnPropertySymbols(e);for(r=0;r<l.length;r++)a=l[r],n.indexOf(a)>=0||Object.prototype.propertyIsEnumerable.call(e,a)&&(t[a]=e[a])}return t}var u=r.createContext({}),s=function(e){var n=r.useContext(u),a=n;return e&&(a="function"==typeof e?e(n):i(i({},n),e)),a},p=function(e){var n=s(e.components);return r.createElement(u.Provider,{value:n},e.children)},g="mdxType",c={inlineCode:"code",wrapper:function(e){var n=e.children;return r.createElement(r.Fragment,{},n)}},m=r.forwardRef((function(e,n){var a=e.components,t=e.mdxType,l=e.originalType,u=e.parentName,p=o(e,["components","mdxType","originalType","parentName"]),g=s(a),m=t,d=g["".concat(u,".").concat(m)]||g[m]||c[m]||l;return a?r.createElement(d,i(i({ref:n},p),{},{components:a})):r.createElement(d,i({ref:n},p))}));function d(e,n){var a=arguments,t=n&&n.mdxType;if("string"==typeof e||t){var l=a.length,i=new Array(l);i[0]=m;var o={};for(var u in n)hasOwnProperty.call(n,u)&&(o[u]=n[u]);o.originalType=e,o[g]="string"==typeof e?e:t,i[1]=o;for(var s=2;s<l;s++)i[s]=a[s];return r.createElement.apply(null,i)}return r.createElement.apply(null,a)}m.displayName="MDXCreateElement"},60547:(e,n,a)=>{a.r(n),a.d(n,{assets:()=>p,contentTitle:()=>u,default:()=>d,frontMatter:()=>o,metadata:()=>s,toc:()=>g});var r=a(87462),t=a(63366),l=(a(67294),a(3905)),i=["components"],o={id:"having",title:"Having filters (groupBy)"},u=void 0,s={unversionedId:"querying/having",id:"querying/having",title:"Having filters (groupBy)",description:"\x3c!--",source:"@site/docs/27.0.0/querying/having.md",sourceDirName:"querying",slug:"/querying/having",permalink:"/docs/27.0.0/querying/having",draft:!1,tags:[],version:"current",frontMatter:{id:"having",title:"Having filters (groupBy)"},sidebar:"docs",previous:{title:"Expressions",permalink:"/docs/27.0.0/querying/math-expr"},next:{title:"Sorting and limiting (groupBy)",permalink:"/docs/27.0.0/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,t.Z)(e,i);return(0,l.kt)(m,(0,r.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/27.0.0/querying/sql"},"Druid SQL")," and ",(0,l.kt)("a",{parentName:"p",href:"/docs/27.0.0/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/27.0.0/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/27.0.0/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}}]); |