| "use strict";(self.webpackChunk=self.webpackChunk||[]).push([[2016],{15680:(e,n,a)=>{a.d(n,{xA:()=>u,yg:()=>m});var r=a(96540);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 g=r.createContext({}),s=function(e){var n=r.useContext(g),a=n;return e&&(a="function"==typeof e?e(n):i(i({},n),e)),a},u=function(e){var n=s(e.components);return r.createElement(g.Provider,{value:n},e.children)},p="mdxType",c={inlineCode:"code",wrapper:function(e){var n=e.children;return r.createElement(r.Fragment,{},n)}},y=r.forwardRef((function(e,n){var a=e.components,t=e.mdxType,l=e.originalType,g=e.parentName,u=o(e,["components","mdxType","originalType","parentName"]),p=s(a),y=t,m=p["".concat(g,".").concat(y)]||p[y]||c[y]||l;return a?r.createElement(m,i(i({ref:n},u),{},{components:a})):r.createElement(m,i({ref:n},u))}));function m(e,n){var a=arguments,t=n&&n.mdxType;if("string"==typeof e||t){var l=a.length,i=new Array(l);i[0]=y;var o={};for(var g in n)hasOwnProperty.call(n,g)&&(o[g]=n[g]);o.originalType=e,o[p]="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)}y.displayName="MDXCreateElement"},74676:(e,n,a)=>{a.r(n),a.d(n,{assets:()=>u,contentTitle:()=>g,default:()=>m,frontMatter:()=>o,metadata:()=>s,toc:()=>p});var r=a(58168),t=a(98587),l=(a(96540),a(15680)),i=["components"],o={id:"having",title:"Having filters (groupBy)"},g=void 0,s={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"}},u={},p=[{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:p},y="wrapper";function m(e){var n=e.components,a=(0,t.A)(e,i);return(0,l.yg)(y,(0,r.A)({},c,a,{components:n,mdxType:"MDXLayout"}),(0,l.yg)("admonition",{type:"info"},(0,l.yg)("p",{parentName:"admonition"}," Apache Druid supports two query languages: ",(0,l.yg)("a",{parentName:"p",href:"/docs/latest/querying/sql"},"Druid SQL")," and ",(0,l.yg)("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.yg)("a",{parentName:"p",href:"/docs/latest/querying/sql-scalar"},"SQL documentation"),".")),(0,l.yg)("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.yg)("p",null,"It is essentially the equivalent of the HAVING clause in SQL."),(0,l.yg)("p",null,"Apache Druid supports the following types of having clauses."),(0,l.yg)("h3",{id:"query-filters"},"Query filters"),(0,l.yg)("p",null,"Query filter HavingSpecs allow all ",(0,l.yg)("a",{parentName:"p",href:"/docs/latest/querying/filters"},"Druid query filters")," to be used in the Having part of the query."),(0,l.yg)("p",null,"The grammar for a query filter HavingSpec is:"),(0,l.yg)("pre",null,(0,l.yg)("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.yg)("p",null,"For example, to use a selector filter:"),(0,l.yg)("pre",null,(0,l.yg)("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.yg)("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.yg)("h3",{id:"numeric-filters"},"Numeric filters"),(0,l.yg)("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.yg)("p",null,"Here's an example of a having-clause numeric filter:"),(0,l.yg)("pre",null,(0,l.yg)("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.yg)("h4",{id:"equal-to"},"Equal To"),(0,l.yg)("p",null,"The equalTo filter will match rows with a specific aggregate value.\nThe grammar for an ",(0,l.yg)("inlineCode",{parentName:"p"},"equalTo")," filter is as follows:"),(0,l.yg)("pre",null,(0,l.yg)("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.yg)("p",null,"This is the equivalent of ",(0,l.yg)("inlineCode",{parentName:"p"},"HAVING <aggregate> = <value>"),"."),(0,l.yg)("h4",{id:"greater-than"},"Greater Than"),(0,l.yg)("p",null,"The greaterThan filter will match rows with aggregate values greater than the given value.\nThe grammar for a ",(0,l.yg)("inlineCode",{parentName:"p"},"greaterThan")," filter is as follows:"),(0,l.yg)("pre",null,(0,l.yg)("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.yg)("p",null,"This is the equivalent of ",(0,l.yg)("inlineCode",{parentName:"p"},"HAVING <aggregate> > <value>"),"."),(0,l.yg)("h4",{id:"less-than"},"Less Than"),(0,l.yg)("p",null,"The lessThan filter will match rows with aggregate values less than the specified value.\nThe grammar for a ",(0,l.yg)("inlineCode",{parentName:"p"},"greaterThan")," filter is as follows:"),(0,l.yg)("pre",null,(0,l.yg)("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.yg)("p",null,"This is the equivalent of ",(0,l.yg)("inlineCode",{parentName:"p"},"HAVING <aggregate> < <value>"),"."),(0,l.yg)("h3",{id:"dimension-selector-filter"},"Dimension Selector Filter"),(0,l.yg)("h4",{id:"dimselector"},"dimSelector"),(0,l.yg)("p",null,"The dimSelector filter will match rows with dimension values equal to the specified value.\nThe grammar for a ",(0,l.yg)("inlineCode",{parentName:"p"},"dimSelector")," filter is as follows:"),(0,l.yg)("pre",null,(0,l.yg)("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.yg)("h3",{id:"logical-expression-filters"},"Logical expression filters"),(0,l.yg)("h4",{id:"and"},"AND"),(0,l.yg)("p",null,"The grammar for an AND filter is as follows:"),(0,l.yg)("pre",null,(0,l.yg)("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.yg)("h4",{id:"or"},"OR"),(0,l.yg)("p",null,"The grammar for an OR filter is as follows:"),(0,l.yg)("pre",null,(0,l.yg)("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.yg)("h4",{id:"not"},"NOT"),(0,l.yg)("p",null,"The grammar for a NOT filter is as follows:"),(0,l.yg)("pre",null,(0,l.yg)("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')))}m.isMDXComponent=!0}}]); |