assets/js/13ebb7f9.766f108c.js

"use strict";(self.webpackChunk=self.webpackChunk||[]).push([[8572],{14137:(t,e,n)=>{n.d(e,{Zo:()=>m,kt:()=>g});var a=n(67294);function l(t,e,n){return e in t?Object.defineProperty(t,e,{value:n,enumerable:!0,configurable:!0,writable:!0}):t[e]=n,t}function r(t,e){var n=Object.keys(t);if(Object.getOwnPropertySymbols){var a=Object.getOwnPropertySymbols(t);e&&(a=a.filter((function(e){return Object.getOwnPropertyDescriptor(t,e).enumerable}))),n.push.apply(n,a)}return n}function i(t){for(var e=1;e<arguments.length;e++){var n=null!=arguments[e]?arguments[e]:{};e%2?r(Object(n),!0).forEach((function(e){l(t,e,n[e])})):Object.getOwnPropertyDescriptors?Object.defineProperties(t,Object.getOwnPropertyDescriptors(n)):r(Object(n)).forEach((function(e){Object.defineProperty(t,e,Object.getOwnPropertyDescriptor(n,e))}))}return t}function d(t,e){if(null==t)return{};var n,a,l=function(t,e){if(null==t)return{};var n,a,l={},r=Object.keys(t);for(a=0;a<r.length;a++)n=r[a],e.indexOf(n)>=0||(l[n]=t[n]);return l}(t,e);if(Object.getOwnPropertySymbols){var r=Object.getOwnPropertySymbols(t);for(a=0;a<r.length;a++)n=r[a],e.indexOf(n)>=0||Object.prototype.propertyIsEnumerable.call(t,n)&&(l[n]=t[n])}return l}var p=a.createContext({}),k=function(t){var e=a.useContext(p),n=e;return t&&(n="function"==typeof t?t(e):i(i({},e),t)),n},m=function(t){var e=k(t.components);return a.createElement(p.Provider,{value:e},t.children)},N="mdxType",u={inlineCode:"code",wrapper:function(t){var e=t.children;return a.createElement(a.Fragment,{},e)}},o=a.forwardRef((function(t,e){var n=t.components,l=t.mdxType,r=t.originalType,p=t.parentName,m=d(t,["components","mdxType","originalType","parentName"]),N=k(n),o=l,g=N["".concat(p,".").concat(o)]||N[o]||u[o]||r;return n?a.createElement(g,i(i({ref:e},m),{},{components:n})):a.createElement(g,i({ref:e},m))}));function g(t,e){var n=arguments,l=e&&e.mdxType;if("string"==typeof t||l){var r=n.length,i=new Array(r);i[0]=o;var d={};for(var p in e)hasOwnProperty.call(e,p)&&(d[p]=e[p]);d.originalType=t,d[N]="string"==typeof t?t:l,i[1]=d;for(var k=2;k<r;k++)i[k]=n[k];return a.createElement.apply(null,i)}return a.createElement.apply(null,n)}o.displayName="MDXCreateElement"},26011:(t,e,n)=>{n.r(e),n.d(e,{assets:()=>m,contentTitle:()=>p,default:()=>g,frontMatter:()=>d,metadata:()=>k,toc:()=>N});var a=n(87462),l=n(63366),r=(n(67294),n(14137)),i=["components"],d={id:"sql-window-functions",title:"Window functions",description:"Reference for window functions"},p=void 0,k={unversionedId:"querying/sql-window-functions",id:"querying/sql-window-functions",title:"Window functions",description:"Reference for window functions",source:"@site/docs/31.0.0/querying/sql-window-functions.md",sourceDirName:"querying",slug:"/querying/sql-window-functions",permalink:"/docs/31.0.0/querying/sql-window-functions",draft:!1,tags:[],version:"current",frontMatter:{id:"sql-window-functions",title:"Window functions",description:"Reference for window functions"},sidebar:"docs",previous:{title:"Aggregation functions",permalink:"/docs/31.0.0/querying/sql-aggregations"},next:{title:"Array functions",permalink:"/docs/31.0.0/querying/sql-array-functions"}},m={},N=[{value:"Window function syntax",id:"window-function-syntax",level:2},{value:"ORDER BY windows",id:"order-by-windows",level:3},{value:"PARTITION BY windows",id:"partition-by-windows",level:3},{value:"Window frame guardrails",id:"window-frame-guardrails",level:4},{value:"Window function reference",id:"window-function-reference",level:2},{value:"Examples",id:"examples",level:2},{value:"Example with window frames",id:"example-with-window-frames",level:3},{value:"Known issues",id:"known-issues",level:2}],u={toc:N},o="wrapper";function g(t){var e=t.components,n=(0,l.Z)(t,i);return(0,r.kt)(o,(0,a.Z)({},u,n,{components:e,mdxType:"MDXLayout"}),(0,r.kt)("admonition",{type:"info"},(0,r.kt)("p",{parentName:"admonition"},"Apache Druid supports two query languages: ",(0,r.kt)("a",{parentName:"p",href:"/docs/31.0.0/querying/sql"},"Druid SQL")," and ",(0,r.kt)("a",{parentName:"p",href:"/docs/31.0.0/querying/"},"native queries"),".\nThis document describes the SQL language.")),(0,r.kt)("p",null,"Window functions in Apache Druid produce values based upon the relationship of one row within a window of rows to the other rows within the same window. A window is a group of related rows within a result set. For example, rows with the same value for a specific dimension."),(0,r.kt)("p",null,"Window functions in Druid require a GROUP BY statement. Druid performs the row-level aggregations for the GROUP BY before performing the window function calculations."),(0,r.kt)("p",null,"The following example organizes results with the same ",(0,r.kt)("inlineCode",{parentName:"p"},"channel")," value into windows. For each window, the query returns the rank of each row in ascending order based upon its ",(0,r.kt)("inlineCode",{parentName:"p"},"changed")," value."),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-sql"},"SELECT FLOOR(__time TO DAY) AS event_time,\n    channel,\n    ABS(delta) AS change,\n    RANK() OVER w AS rank_value\nFROM wikipedia\nWHERE channel in ('#kk.wikipedia', '#lt.wikipedia')\nAND '2016-06-28' > FLOOR(__time TO DAY) > '2016-06-26'\nGROUP BY channel, ABS(delta), __time\nWINDOW w AS (PARTITION BY channel ORDER BY ABS(delta) ASC)\n")),(0,r.kt)("details",null,(0,r.kt)("summary",null," View results "),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"event_time")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"channel")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"change")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"rank_value")))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"1")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"1")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"7"),(0,r.kt)("td",{parentName:"tr",align:null},"3")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"56"),(0,r.kt)("td",{parentName:"tr",align:null},"4")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"56"),(0,r.kt)("td",{parentName:"tr",align:null},"4")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"63"),(0,r.kt)("td",{parentName:"tr",align:null},"6")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"91"),(0,r.kt)("td",{parentName:"tr",align:null},"7")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"2440"),(0,r.kt)("td",{parentName:"tr",align:null},"8")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"2703"),(0,r.kt)("td",{parentName:"tr",align:null},"9")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"6900"),(0,r.kt)("td",{parentName:"tr",align:null},"10")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"1")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"2"),(0,r.kt)("td",{parentName:"tr",align:null},"2")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"13"),(0,r.kt)("td",{parentName:"tr",align:null},"3")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"28"),(0,r.kt)("td",{parentName:"tr",align:null},"4")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"53"),(0,r.kt)("td",{parentName:"tr",align:null},"5")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"56"),(0,r.kt)("td",{parentName:"tr",align:null},"6")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"59"),(0,r.kt)("td",{parentName:"tr",align:null},"7")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"391"),(0,r.kt)("td",{parentName:"tr",align:null},"8")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"894"),(0,r.kt)("td",{parentName:"tr",align:null},"9")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"4358"),(0,r.kt)("td",{parentName:"tr",align:null},"10"))))),(0,r.kt)("p",null,"Window functions are similar to ",(0,r.kt)("a",{parentName:"p",href:"/docs/31.0.0/querying/aggregations"},"aggregation functions"),".  "),(0,r.kt)("p",null,"You can use the OVER clause to treat other Druid aggregation functions as window functions. For example, the sum of a value for rows within a window."),(0,r.kt)("p",null,"Window functions support aliasing."),(0,r.kt)("h2",{id:"window-function-syntax"},"Window function syntax"),(0,r.kt)("p",null,"You can write a window function in Druid using either syntax below.\nThe second syntax shows a window alias to reference a window that you can reuse."),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-sql"},"window_function() OVER (\n  [PARTITION BY partitioning expression]\n  [ORDER BY order expression]\n  [[ROWS, RANGE] BETWEEN range start AND range end])\nFROM table\nGROUP BY dimensions\n")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-sql"},"window_function() OVER w\nFROM table\nWINDOW w AS ([PARTITION BY partitioning expression] [ORDER BY order expression]\n  [[ROWS, RANGE] BETWEEN range start AND range end])\nGROUP BY dimensions\n")),(0,r.kt)("p",null,"The OVER clause defines the query windows for window functions as follows:"),(0,r.kt)("ul",null,(0,r.kt)("li",{parentName:"ul"},"PARTITION BY indicates the dimension that defines window boundaries"),(0,r.kt)("li",{parentName:"ul"},"ORDER BY specifies the order of the rows within the windows")),(0,r.kt)("p",null,"An empty OVER clause or the absence of a PARTITION BY clause indicates that all data belongs to a single window."),(0,r.kt)("p",null,"In the following example, the following OVER clause example sets the window dimension to ",(0,r.kt)("inlineCode",{parentName:"p"},"channel")," and orders the results by the absolute value of ",(0,r.kt)("inlineCode",{parentName:"p"},"delta")," ascending:"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-sql"},"...\nRANK() OVER (PARTITION BY channel ORDER BY ABS(delta) ASC)\n...\n")),(0,r.kt)("p",null,"Window frames, set in ROWS and RANGE expressions, limit the set of rows used for the windowed aggregation."),(0,r.kt)("p",null,"ROWS and RANGE accept the following values for ",(0,r.kt)("inlineCode",{parentName:"p"},"range start")," and ",(0,r.kt)("inlineCode",{parentName:"p"},"range end"),":"),(0,r.kt)("ul",null,(0,r.kt)("li",{parentName:"ul"},"UNBOUNDED PRECEDING: from the beginning of the window as ordered by the order expression"),(0,r.kt)("li",{parentName:"ul"},(0,r.kt)("em",{parentName:"li"},"N")," ROWS PRECEDING: ",(0,r.kt)("em",{parentName:"li"},"N")," rows before the current row as ordered by the order expression"),(0,r.kt)("li",{parentName:"ul"},"CURRENT ROW: the current row"),(0,r.kt)("li",{parentName:"ul"},(0,r.kt)("em",{parentName:"li"},"N")," ROWS FOLLOWING: ",(0,r.kt)("em",{parentName:"li"},"N")," rows after the current row as ordered by the order expression"),(0,r.kt)("li",{parentName:"ul"},"UNBOUNDED FOLLOWING: to the end of the window as ordered by the order expression")),(0,r.kt)("p",null,"See ",(0,r.kt)("a",{parentName:"p",href:"#example-with-window-frames"},"Example with window frames")," for more detail."),(0,r.kt)("p",null,"Druid applies the GROUP BY dimensions before calculating all non-window aggregation functions. Then it applies the window function over the aggregated results."),(0,r.kt)("admonition",{type:"note"},(0,r.kt)("p",{parentName:"admonition"},"Sometimes windows are called partitions. However, the partitioning for window functions are a shuffle (partition) of the result set created at query time and is not to be confused with Druid's segment partitioning feature which partitions data at ingest time.")),(0,r.kt)("h3",{id:"order-by-windows"},"ORDER BY windows"),(0,r.kt)("p",null,"When the window definition only specifies ORDER BY and not PARTITION BY, it sorts the aggregate data set and applies the function in that order."),(0,r.kt)("p",null,"The following query uses ",(0,r.kt)("inlineCode",{parentName:"p"},"ORDER BY SUM(delta) DESC")," to rank user hourly activity from the most changed the least changed within an hour:"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-sql"},"SELECT\n    TIME_FLOOR(__time, 'PT1H') as time_hour, \n    channel, \n    user,\n    SUM(delta) net_user_changes,\n    RANK() OVER (ORDER BY SUM(delta) DESC) AS editing_rank\nFROM \"wikipedia\"\nWHERE channel IN ('#kk.wikipedia', '#lt.wikipedia')\n  AND __time BETWEEN '2016-06-27' AND '2016-06-28'\nGROUP BY TIME_FLOOR(__time, 'PT1H'), channel, user\nORDER BY 5 \n")),(0,r.kt)("details",null,(0,r.kt)("summary",null," View results "),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"time_hour")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"channel")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"user")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"net_user_changes")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"editing_rank")))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T15:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"Nurkhan")),(0,r.kt)("td",{parentName:"tr",align:null},"6900"),(0,r.kt)("td",{parentName:"tr",align:null},"1")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T19:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"77.221.66.41")),(0,r.kt)("td",{parentName:"tr",align:null},"4358"),(0,r.kt)("td",{parentName:"tr",align:null},"2")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T09:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"\u0421\u0430\u043b\u0438\u0445\u0430")),(0,r.kt)("td",{parentName:"tr",align:null},"2702"),(0,r.kt)("td",{parentName:"tr",align:null},"3")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T04:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"Nurkhan")),(0,r.kt)("td",{parentName:"tr",align:null},"2440"),(0,r.kt)("td",{parentName:"tr",align:null},"4")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T09:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"80.4.147.222")),(0,r.kt)("td",{parentName:"tr",align:null},"894"),(0,r.kt)("td",{parentName:"tr",align:null},"5")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T09:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"178.11.203.212")),(0,r.kt)("td",{parentName:"tr",align:null},"447"),(0,r.kt)("td",{parentName:"tr",align:null},"6")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T11:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"\u041d\u04b1\u0440\u043b\u0430\u043d \u0420\u0430\u0445\u044b\u043c\u0436\u0430\u043d\u043e\u0432")),(0,r.kt)("td",{parentName:"tr",align:null},"126"),(0,r.kt)("td",{parentName:"tr",align:null},"7")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T06:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"\u0428\u043e\u043a\u0430\u0439")),(0,r.kt)("td",{parentName:"tr",align:null},"91"),(0,r.kt)("td",{parentName:"tr",align:null},"8")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T11:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"MaryroseB54")),(0,r.kt)("td",{parentName:"tr",align:null},"59"),(0,r.kt)("td",{parentName:"tr",align:null},"9")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T04:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"\u041d\u04b1\u0440\u043b\u0430\u043d \u0420\u0430\u0445\u044b\u043c\u0436\u0430\u043d\u043e\u0432")),(0,r.kt)("td",{parentName:"tr",align:null},"56"),(0,r.kt)("td",{parentName:"tr",align:null},"10")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T12:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"Karoliuk")),(0,r.kt)("td",{parentName:"tr",align:null},"53"),(0,r.kt)("td",{parentName:"tr",align:null},"11")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T12:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"Powermelon")),(0,r.kt)("td",{parentName:"tr",align:null},"28"),(0,r.kt)("td",{parentName:"tr",align:null},"12")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T07:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"Powermelon")),(0,r.kt)("td",{parentName:"tr",align:null},"13"),(0,r.kt)("td",{parentName:"tr",align:null},"13")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T10:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"80.4.147.222")),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"14")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T07:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"\u0421\u0430\u043b\u0438\u0445\u0430")),(0,r.kt)("td",{parentName:"tr",align:null},"-1"),(0,r.kt)("td",{parentName:"tr",align:null},"15")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T06:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"Powermelon")),(0,r.kt)("td",{parentName:"tr",align:null},"-2"),(0,r.kt)("td",{parentName:"tr",align:null},"16"))))),(0,r.kt)("h3",{id:"partition-by-windows"},"PARTITION BY windows"),(0,r.kt)("p",null,"When a window only specifies PARTITION BY partition expression, Druid calculates the aggregate window function over all the rows that share a value within the selected dataset."),(0,r.kt)("p",null,"The following example demonstrates a query that uses two different windows\u2014",(0,r.kt)("inlineCode",{parentName:"p"},"PARTITION BY channel")," and ",(0,r.kt)("inlineCode",{parentName:"p"},"PARTITION BY user"),"\u2014to calculate the total activity in the channel and total activity by the user so that they can be compared to individual hourly activity:"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-sql"},"SELECT\n    TIME_FLOOR(__time, 'PT1H') as time_hour,\n    channel,\n    user,\n    SUM(delta) AS hourly_user_changes,\n    SUM(SUM(delta)) OVER (PARTITION BY user) AS total_user_changes,\n    SUM(SUM(delta)) OVER (PARTITION BY channel) AS total_channel_changes\nFROM \"wikipedia\"\nWHERE channel IN ('#kk.wikipedia', '#lt.wikipedia')\n  AND __time BETWEEN '2016-06-27' AND '2016-06-28'\nGROUP BY TIME_FLOOR(__time, 'PT1H'), 2, 3\nORDER BY channel, TIME_FLOOR(__time, 'PT1H'), user\n")),(0,r.kt)("details",null,(0,r.kt)("summary",null," View results "),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"time_hour")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"channel")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"user")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"hourly_user_changes")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"total_user_changes")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"total_channel_changes")))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T04:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"Nurkhan")),(0,r.kt)("td",{parentName:"tr",align:null},"2440"),(0,r.kt)("td",{parentName:"tr",align:null},"9340"),(0,r.kt)("td",{parentName:"tr",align:null},"12314")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T04:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"\u041d\u04b1\u0440\u043b\u0430\u043d \u0420\u0430\u0445\u044b\u043c\u0436\u0430\u043d\u043e\u0432")),(0,r.kt)("td",{parentName:"tr",align:null},"56"),(0,r.kt)("td",{parentName:"tr",align:null},"182"),(0,r.kt)("td",{parentName:"tr",align:null},"12314")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T06:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"\u0428\u043e\u043a\u0430\u0439")),(0,r.kt)("td",{parentName:"tr",align:null},"91"),(0,r.kt)("td",{parentName:"tr",align:null},"91"),(0,r.kt)("td",{parentName:"tr",align:null},"12314")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T07:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"\u0421\u0430\u043b\u0438\u0445\u0430")),(0,r.kt)("td",{parentName:"tr",align:null},"-1"),(0,r.kt)("td",{parentName:"tr",align:null},"2701"),(0,r.kt)("td",{parentName:"tr",align:null},"12314")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T09:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"\u0421\u0430\u043b\u0438\u0445\u0430")),(0,r.kt)("td",{parentName:"tr",align:null},"2702"),(0,r.kt)("td",{parentName:"tr",align:null},"2701"),(0,r.kt)("td",{parentName:"tr",align:null},"12314")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T11:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"\u041d\u04b1\u0440\u043b\u0430\u043d \u0420\u0430\u0445\u044b\u043c\u0436\u0430\u043d\u043e\u0432")),(0,r.kt)("td",{parentName:"tr",align:null},"126"),(0,r.kt)("td",{parentName:"tr",align:null},"182"),(0,r.kt)("td",{parentName:"tr",align:null},"12314")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T15:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"Nurkhan")),(0,r.kt)("td",{parentName:"tr",align:null},"6900"),(0,r.kt)("td",{parentName:"tr",align:null},"9340"),(0,r.kt)("td",{parentName:"tr",align:null},"12314")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T06:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"Powermelon")),(0,r.kt)("td",{parentName:"tr",align:null},"-2"),(0,r.kt)("td",{parentName:"tr",align:null},"39"),(0,r.kt)("td",{parentName:"tr",align:null},"5851")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T07:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"Powermelon")),(0,r.kt)("td",{parentName:"tr",align:null},"13"),(0,r.kt)("td",{parentName:"tr",align:null},"39"),(0,r.kt)("td",{parentName:"tr",align:null},"5851")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T09:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"178.11.203.212")),(0,r.kt)("td",{parentName:"tr",align:null},"447"),(0,r.kt)("td",{parentName:"tr",align:null},"447"),(0,r.kt)("td",{parentName:"tr",align:null},"5851")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T09:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"80.4.147.222")),(0,r.kt)("td",{parentName:"tr",align:null},"894"),(0,r.kt)("td",{parentName:"tr",align:null},"895"),(0,r.kt)("td",{parentName:"tr",align:null},"5851")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T10:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"80.4.147.222")),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"895"),(0,r.kt)("td",{parentName:"tr",align:null},"5851")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T11:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"MaryroseB54")),(0,r.kt)("td",{parentName:"tr",align:null},"59"),(0,r.kt)("td",{parentName:"tr",align:null},"59"),(0,r.kt)("td",{parentName:"tr",align:null},"5851")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T12:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"Karoliuk")),(0,r.kt)("td",{parentName:"tr",align:null},"53"),(0,r.kt)("td",{parentName:"tr",align:null},"53"),(0,r.kt)("td",{parentName:"tr",align:null},"5851")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T12:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"Powermelon")),(0,r.kt)("td",{parentName:"tr",align:null},"28"),(0,r.kt)("td",{parentName:"tr",align:null},"39"),(0,r.kt)("td",{parentName:"tr",align:null},"5851")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T19:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"77.221.66.41")),(0,r.kt)("td",{parentName:"tr",align:null},"4358"),(0,r.kt)("td",{parentName:"tr",align:null},"4358"),(0,r.kt)("td",{parentName:"tr",align:null},"5851"))))),(0,r.kt)("p",null,"In this example, the dataset is filtered for a single day. Therefore the window function results represent the total activity for the day, for the ",(0,r.kt)("inlineCode",{parentName:"p"},"user")," and for the ",(0,r.kt)("inlineCode",{parentName:"p"},"channel")," dimensions respectively."),(0,r.kt)("p",null,"This type of result helps you analyze the impact of an individual user's hourly activity:"),(0,r.kt)("ul",null,(0,r.kt)("li",{parentName:"ul"},"the impact to the channel by comparing ",(0,r.kt)("inlineCode",{parentName:"li"},"hourly_user_changes")," to ",(0,r.kt)("inlineCode",{parentName:"li"},"total_channel_changes")),(0,r.kt)("li",{parentName:"ul"},"the impact of each user over the channel by ",(0,r.kt)("inlineCode",{parentName:"li"},"total_user_changes")," to ",(0,r.kt)("inlineCode",{parentName:"li"},"total_channel_changes")),(0,r.kt)("li",{parentName:"ul"},"the progress of each user's individual activity by comparing ",(0,r.kt)("inlineCode",{parentName:"li"},"hourly_user_changes")," to ",(0,r.kt)("inlineCode",{parentName:"li"},"total_user_changes"))),(0,r.kt)("h4",{id:"window-frame-guardrails"},"Window frame guardrails"),(0,r.kt)("p",null,"Druid has guardrail logic to prevent you from executing window function queries with window frame expressions that might return unexpected results."),(0,r.kt)("p",null,"For example:"),(0,r.kt)("ul",null,(0,r.kt)("li",{parentName:"ul"},"You cannot set expressions as bounds for window frames."),(0,r.kt)("li",{parentName:"ul"},"You can only use a RANGE frames when both endpoints are unbounded or current row.")),(0,r.kt)("h2",{id:"window-function-reference"},"Window function reference"),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Function"),(0,r.kt)("th",{parentName:"tr",align:null},"Notes"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"ROW_NUMBER()")),(0,r.kt)("td",{parentName:"tr",align:null},"Returns the number of the row within the window starting from 1")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"RANK()")),(0,r.kt)("td",{parentName:"tr",align:null},"Returns the rank with gaps for a row within a window. For example, if two rows tie for rank 1, the next rank is 3")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"DENSE_RANK()")),(0,r.kt)("td",{parentName:"tr",align:null},"Returns the rank for a row within a window without gaps. For example, if two rows tie for rank of 1, the subsequent row is ranked 2.")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"PERCENT_RANK()")),(0,r.kt)("td",{parentName:"tr",align:null},"Returns the relative rank of the row calculated as a percentage according to the formula: ",(0,r.kt)("inlineCode",{parentName:"td"},"RANK() OVER (window) / COUNT(1) OVER (window)"))),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"CUME_DIST()")),(0,r.kt)("td",{parentName:"tr",align:null},"Returns the cumulative distribution of the current row within the window calculated as number of window rows at the same rank or higher than current row divided by total window rows. The return value ranges between ",(0,r.kt)("inlineCode",{parentName:"td"},"1/number of rows")," and 1")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"NTILE(tiles)")),(0,r.kt)("td",{parentName:"tr",align:null},"Divides the rows within a window as evenly as possible into the number of tiles, also called buckets, and returns the value of the tile that the row falls into")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"LAG(expr[, offset])")),(0,r.kt)("td",{parentName:"tr",align:null},"If you do not supply an ",(0,r.kt)("inlineCode",{parentName:"td"},"offset"),", returns the value evaluated at the row preceding the current row. Specify an offset number, ",(0,r.kt)("inlineCode",{parentName:"td"},"n"),", to return the value evaluated at ",(0,r.kt)("inlineCode",{parentName:"td"},"n")," rows preceding the current one")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"LEAD(expr[, offset])")),(0,r.kt)("td",{parentName:"tr",align:null},"If you do not supply an ",(0,r.kt)("inlineCode",{parentName:"td"},"offset"),", returns the value evaluated at the row following the current row. Specify an offset number ",(0,r.kt)("inlineCode",{parentName:"td"},"n")," to return the value evaluated at ",(0,r.kt)("inlineCode",{parentName:"td"},"n")," rows following the current one; if there is no such row, returns the given default value")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"FIRST_VALUE(expr)")),(0,r.kt)("td",{parentName:"tr",align:null},"Returns the value evaluated for the expression for the first row within the window")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"LAST_VALUE(expr)")),(0,r.kt)("td",{parentName:"tr",align:null},"Returns the value evaluated for the expression for the last row within the window")))),(0,r.kt)("h2",{id:"examples"},"Examples"),(0,r.kt)("p",null,"The following example illustrates all of the built-in window functions to compare the number of characters changed per event for a channel in the Wikipedia data set."),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-sql"},"SELECT FLOOR(__time TO DAY) AS event_time,\n    channel,\n    ABS(delta) AS change,\n    ROW_NUMBER() OVER w AS row_no,\n    RANK() OVER w AS rank_no,\n    DENSE_RANK() OVER w AS dense_rank_no,\n    PERCENT_RANK() OVER w AS pct_rank,\n    CUME_DIST() OVER w AS cumulative_dist,\n    NTILE(4) OVER w AS ntile_val,\n    LAG(ABS(delta), 1, 0) OVER w AS lag_val,\n    LEAD(ABS(delta), 1, 0) OVER w AS lead_val,\n    FIRST_VALUE(ABS(delta)) OVER w AS first_val,\n    LAST_VALUE(ABS(delta)) OVER w AS last_val\nFROM wikipedia\nWHERE channel IN ('#kk.wikipedia', '#lt.wikipedia')\nGROUP BY channel, ABS(delta), FLOOR(__time TO DAY) \nWINDOW w AS (PARTITION BY channel ORDER BY ABS(delta) ASC)\n")),(0,r.kt)("details",null,(0,r.kt)("summary",null," View results "),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"event_time")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"channel")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"change")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"row_no")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"rank_no")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"dense_rank_no")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"pct_rank")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"cumulative_dist")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"ntile_val")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"lag_val")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"lead_val")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"first_val")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"last_val")))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"0.0"),(0,r.kt)("td",{parentName:"tr",align:null},"0.125"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"null"),(0,r.kt)("td",{parentName:"tr",align:null},"7"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"6900")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"7"),(0,r.kt)("td",{parentName:"tr",align:null},"2"),(0,r.kt)("td",{parentName:"tr",align:null},"2"),(0,r.kt)("td",{parentName:"tr",align:null},"2"),(0,r.kt)("td",{parentName:"tr",align:null},"0.14285714285714285"),(0,r.kt)("td",{parentName:"tr",align:null},"0.25"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"56"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"6900")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"56"),(0,r.kt)("td",{parentName:"tr",align:null},"3"),(0,r.kt)("td",{parentName:"tr",align:null},"3"),(0,r.kt)("td",{parentName:"tr",align:null},"3"),(0,r.kt)("td",{parentName:"tr",align:null},"0.2857142857142857"),(0,r.kt)("td",{parentName:"tr",align:null},"0.375"),(0,r.kt)("td",{parentName:"tr",align:null},"2"),(0,r.kt)("td",{parentName:"tr",align:null},"7"),(0,r.kt)("td",{parentName:"tr",align:null},"63"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"6900")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"63"),(0,r.kt)("td",{parentName:"tr",align:null},"4"),(0,r.kt)("td",{parentName:"tr",align:null},"4"),(0,r.kt)("td",{parentName:"tr",align:null},"4"),(0,r.kt)("td",{parentName:"tr",align:null},"0.42857142857142855"),(0,r.kt)("td",{parentName:"tr",align:null},"0.5"),(0,r.kt)("td",{parentName:"tr",align:null},"2"),(0,r.kt)("td",{parentName:"tr",align:null},"56"),(0,r.kt)("td",{parentName:"tr",align:null},"91"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"6900")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"91"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null},"0.5714285714285714"),(0,r.kt)("td",{parentName:"tr",align:null},"0.625"),(0,r.kt)("td",{parentName:"tr",align:null},"3"),(0,r.kt)("td",{parentName:"tr",align:null},"63"),(0,r.kt)("td",{parentName:"tr",align:null},"2440"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"6900")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"2440"),(0,r.kt)("td",{parentName:"tr",align:null},"6"),(0,r.kt)("td",{parentName:"tr",align:null},"6"),(0,r.kt)("td",{parentName:"tr",align:null},"6"),(0,r.kt)("td",{parentName:"tr",align:null},"0.7142857142857143"),(0,r.kt)("td",{parentName:"tr",align:null},"0.75"),(0,r.kt)("td",{parentName:"tr",align:null},"3"),(0,r.kt)("td",{parentName:"tr",align:null},"91"),(0,r.kt)("td",{parentName:"tr",align:null},"2703"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"6900")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"2703"),(0,r.kt)("td",{parentName:"tr",align:null},"7"),(0,r.kt)("td",{parentName:"tr",align:null},"7"),(0,r.kt)("td",{parentName:"tr",align:null},"7"),(0,r.kt)("td",{parentName:"tr",align:null},"0.8571428571428571"),(0,r.kt)("td",{parentName:"tr",align:null},"0.875"),(0,r.kt)("td",{parentName:"tr",align:null},"4"),(0,r.kt)("td",{parentName:"tr",align:null},"2440"),(0,r.kt)("td",{parentName:"tr",align:null},"6900"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"6900")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"6900"),(0,r.kt)("td",{parentName:"tr",align:null},"8"),(0,r.kt)("td",{parentName:"tr",align:null},"8"),(0,r.kt)("td",{parentName:"tr",align:null},"8"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"4"),(0,r.kt)("td",{parentName:"tr",align:null},"2703"),(0,r.kt)("td",{parentName:"tr",align:null},"null"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"6900")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"0"),(0,r.kt)("td",{parentName:"tr",align:null},"0.1"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"null"),(0,r.kt)("td",{parentName:"tr",align:null},"2"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"4358")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"2"),(0,r.kt)("td",{parentName:"tr",align:null},"2"),(0,r.kt)("td",{parentName:"tr",align:null},"2"),(0,r.kt)("td",{parentName:"tr",align:null},"2"),(0,r.kt)("td",{parentName:"tr",align:null},"0.1111111111111111"),(0,r.kt)("td",{parentName:"tr",align:null},"0.2"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"13"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"4358")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"13"),(0,r.kt)("td",{parentName:"tr",align:null},"3"),(0,r.kt)("td",{parentName:"tr",align:null},"3"),(0,r.kt)("td",{parentName:"tr",align:null},"3"),(0,r.kt)("td",{parentName:"tr",align:null},"0.2222222222222222"),(0,r.kt)("td",{parentName:"tr",align:null},"0.3"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"2"),(0,r.kt)("td",{parentName:"tr",align:null},"28"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"4358")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"28"),(0,r.kt)("td",{parentName:"tr",align:null},"4"),(0,r.kt)("td",{parentName:"tr",align:null},"4"),(0,r.kt)("td",{parentName:"tr",align:null},"4"),(0,r.kt)("td",{parentName:"tr",align:null},"0.3333333333333333"),(0,r.kt)("td",{parentName:"tr",align:null},"0.4"),(0,r.kt)("td",{parentName:"tr",align:null},"2"),(0,r.kt)("td",{parentName:"tr",align:null},"13"),(0,r.kt)("td",{parentName:"tr",align:null},"53"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"4358")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"53"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null},"0.4444444444444444"),(0,r.kt)("td",{parentName:"tr",align:null},"0.5"),(0,r.kt)("td",{parentName:"tr",align:null},"2"),(0,r.kt)("td",{parentName:"tr",align:null},"28"),(0,r.kt)("td",{parentName:"tr",align:null},"56"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"4358")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"56"),(0,r.kt)("td",{parentName:"tr",align:null},"6"),(0,r.kt)("td",{parentName:"tr",align:null},"6"),(0,r.kt)("td",{parentName:"tr",align:null},"6"),(0,r.kt)("td",{parentName:"tr",align:null},"0.5555555555555556"),(0,r.kt)("td",{parentName:"tr",align:null},"0.6"),(0,r.kt)("td",{parentName:"tr",align:null},"2"),(0,r.kt)("td",{parentName:"tr",align:null},"53"),(0,r.kt)("td",{parentName:"tr",align:null},"59"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"4358")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"59"),(0,r.kt)("td",{parentName:"tr",align:null},"7"),(0,r.kt)("td",{parentName:"tr",align:null},"7"),(0,r.kt)("td",{parentName:"tr",align:null},"7"),(0,r.kt)("td",{parentName:"tr",align:null},"0.6666666666666666"),(0,r.kt)("td",{parentName:"tr",align:null},"0.7"),(0,r.kt)("td",{parentName:"tr",align:null},"3"),(0,r.kt)("td",{parentName:"tr",align:null},"56"),(0,r.kt)("td",{parentName:"tr",align:null},"391"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"4358")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"391"),(0,r.kt)("td",{parentName:"tr",align:null},"8"),(0,r.kt)("td",{parentName:"tr",align:null},"8"),(0,r.kt)("td",{parentName:"tr",align:null},"8"),(0,r.kt)("td",{parentName:"tr",align:null},"0.7777777777777778"),(0,r.kt)("td",{parentName:"tr",align:null},"0.8"),(0,r.kt)("td",{parentName:"tr",align:null},"3"),(0,r.kt)("td",{parentName:"tr",align:null},"59"),(0,r.kt)("td",{parentName:"tr",align:null},"894"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"4358")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"894"),(0,r.kt)("td",{parentName:"tr",align:null},"9"),(0,r.kt)("td",{parentName:"tr",align:null},"9"),(0,r.kt)("td",{parentName:"tr",align:null},"9"),(0,r.kt)("td",{parentName:"tr",align:null},"0.8888888888888888"),(0,r.kt)("td",{parentName:"tr",align:null},"0.9"),(0,r.kt)("td",{parentName:"tr",align:null},"4"),(0,r.kt)("td",{parentName:"tr",align:null},"391"),(0,r.kt)("td",{parentName:"tr",align:null},"4358"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"4358")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"4358"),(0,r.kt)("td",{parentName:"tr",align:null},"10"),(0,r.kt)("td",{parentName:"tr",align:null},"10"),(0,r.kt)("td",{parentName:"tr",align:null},"10"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"4"),(0,r.kt)("td",{parentName:"tr",align:null},"894"),(0,r.kt)("td",{parentName:"tr",align:null},"null"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"4358"))))),(0,r.kt)("p",null,"The following example demonstrates applying the SUM() function over the values in a window to calculate the cumulative changes to a channel over time:"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-sql"},"SELECT\n    FLOOR(__time TO MINUTE) as \"time\",\n    channel,\n    ABS(delta) AS changes,\n    sum(ABS(delta)) OVER (PARTITION BY channel ORDER BY FLOOR(__time TO MINUTE) ASC) AS cum_changes\nFROM wikipedia\nWHERE channel IN ('#kk.wikipedia', '#lt.wikipedia')\nGROUP BY channel, __time, delta\n")),(0,r.kt)("details",null,(0,r.kt)("summary",null," View results "),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"time")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"channel")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"changes")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"cum_changes")))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T04:20:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"56"),(0,r.kt)("td",{parentName:"tr",align:null},"56")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T04:35:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"2440"),(0,r.kt)("td",{parentName:"tr",align:null},"2496")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T06:15:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"91"),(0,r.kt)("td",{parentName:"tr",align:null},"2587")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T07:32:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"2588")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T09:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"2703"),(0,r.kt)("td",{parentName:"tr",align:null},"5291")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T09:24:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"5292")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T11:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"63"),(0,r.kt)("td",{parentName:"tr",align:null},"5355")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T11:05:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"7"),(0,r.kt)("td",{parentName:"tr",align:null},"5362")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T11:32:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"56"),(0,r.kt)("td",{parentName:"tr",align:null},"5418")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T15:21:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#kk.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"6900"),(0,r.kt)("td",{parentName:"tr",align:null},"12318")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T06:17:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"2"),(0,r.kt)("td",{parentName:"tr",align:null},"2")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T07:55:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"13"),(0,r.kt)("td",{parentName:"tr",align:null},"15")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T09:05:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"894"),(0,r.kt)("td",{parentName:"tr",align:null},"909")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T09:12:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"391"),(0,r.kt)("td",{parentName:"tr",align:null},"1300")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T09:23:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"56"),(0,r.kt)("td",{parentName:"tr",align:null},"1356")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T10:59:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null},"1357")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T11:49:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"59"),(0,r.kt)("td",{parentName:"tr",align:null},"1416")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T12:41:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"53"),(0,r.kt)("td",{parentName:"tr",align:null},"1469")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T12:58:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"28"),(0,r.kt)("td",{parentName:"tr",align:null},"1497")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T19:03:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#lt.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},"4358"),(0,r.kt)("td",{parentName:"tr",align:null},"5855"))))),(0,r.kt)("h3",{id:"example-with-window-frames"},"Example with window frames"),(0,r.kt)("p",null,"The following query uses a few different window frames to calculate overall activity by channel:"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-sql"},"SELECT\n    channel, \n    TIME_FLOOR(__time, 'PT1H')      AS time_hour, \n    SUM(delta)                      AS hourly_channel_changes,\n    SUM(SUM(delta)) OVER cumulative AS cumulative_activity_in_channel,\n    SUM(SUM(delta)) OVER moving5    AS csum5,\n    COUNT(1) OVER moving5           AS count5\nFROM \"wikipedia\"\nWHERE channel = '#en.wikipedia'\n  AND __time BETWEEN '2016-06-27' AND '2016-06-28'\nGROUP BY 1, TIME_FLOOR(__time, 'PT1H')\nWINDOW cumulative AS (   \n                         PARTITION BY channel \n                         ORDER BY TIME_FLOOR(__time, 'PT1H') \n                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW\n                     )\n                     ,\n        moving5 AS ( \n                    PARTITION BY channel \n                    ORDER BY TIME_FLOOR(__time, 'PT1H') \n                    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW\n                  )\n")),(0,r.kt)("details",null,(0,r.kt)("summary",null," View results "),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"channel")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"time_hour")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"hourly_channel_changes")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"cumulative_activity_in_channel")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"csum5")),(0,r.kt)("th",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"th"},"count5")),(0,r.kt)("th",{parentName:"tr",align:null}),(0,r.kt)("th",{parentName:"tr",align:null}))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#en.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T00:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},"74996"),(0,r.kt)("td",{parentName:"tr",align:null},"74996"),(0,r.kt)("td",{parentName:"tr",align:null},"74996"),(0,r.kt)("td",{parentName:"tr",align:null},"1"),(0,r.kt)("td",{parentName:"tr",align:null}),(0,r.kt)("td",{parentName:"tr",align:null})),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#en.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T01:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},"24150"),(0,r.kt)("td",{parentName:"tr",align:null},"99146"),(0,r.kt)("td",{parentName:"tr",align:null},"99146"),(0,r.kt)("td",{parentName:"tr",align:null},"2"),(0,r.kt)("td",{parentName:"tr",align:null}),(0,r.kt)("td",{parentName:"tr",align:null})),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#en.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T02:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},"102372"),(0,r.kt)("td",{parentName:"tr",align:null},"201518"),(0,r.kt)("td",{parentName:"tr",align:null},"201518"),(0,r.kt)("td",{parentName:"tr",align:null},"3"),(0,r.kt)("td",{parentName:"tr",align:null}),(0,r.kt)("td",{parentName:"tr",align:null})),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#en.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T03:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},"61362"),(0,r.kt)("td",{parentName:"tr",align:null},"262880"),(0,r.kt)("td",{parentName:"tr",align:null},"262880"),(0,r.kt)("td",{parentName:"tr",align:null},"4"),(0,r.kt)("td",{parentName:"tr",align:null}),(0,r.kt)("td",{parentName:"tr",align:null})),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#en.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T04:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},"61666"),(0,r.kt)("td",{parentName:"tr",align:null},"324546"),(0,r.kt)("td",{parentName:"tr",align:null},"324546"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null}),(0,r.kt)("td",{parentName:"tr",align:null})),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#en.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T05:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},"144199"),(0,r.kt)("td",{parentName:"tr",align:null},"468745"),(0,r.kt)("td",{parentName:"tr",align:null},"393749"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null}),(0,r.kt)("td",{parentName:"tr",align:null})),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#en.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T06:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},"33414"),(0,r.kt)("td",{parentName:"tr",align:null},"502159"),(0,r.kt)("td",{parentName:"tr",align:null},"403013"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null}),(0,r.kt)("td",{parentName:"tr",align:null})),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#en.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T07:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},"79397"),(0,r.kt)("td",{parentName:"tr",align:null},"581556"),(0,r.kt)("td",{parentName:"tr",align:null},"380038"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null}),(0,r.kt)("td",{parentName:"tr",align:null})),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#en.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T08:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},"104436"),(0,r.kt)("td",{parentName:"tr",align:null},"685992"),(0,r.kt)("td",{parentName:"tr",align:null},"423112"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null}),(0,r.kt)("td",{parentName:"tr",align:null})),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#en.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T09:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},"58020"),(0,r.kt)("td",{parentName:"tr",align:null},"744012"),(0,r.kt)("td",{parentName:"tr",align:null},"419466"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null}),(0,r.kt)("td",{parentName:"tr",align:null})),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#en.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T10:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},"93904"),(0,r.kt)("td",{parentName:"tr",align:null},"837916"),(0,r.kt)("td",{parentName:"tr",align:null},"369171"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null}),(0,r.kt)("td",{parentName:"tr",align:null})),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#en.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T11:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},"74436"),(0,r.kt)("td",{parentName:"tr",align:null},"912352"),(0,r.kt)("td",{parentName:"tr",align:null},"410193"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null}),(0,r.kt)("td",{parentName:"tr",align:null})),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#en.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T12:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},"83491"),(0,r.kt)("td",{parentName:"tr",align:null},"995843"),(0,r.kt)("td",{parentName:"tr",align:null},"414287"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null}),(0,r.kt)("td",{parentName:"tr",align:null})),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#en.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T13:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},"103051"),(0,r.kt)("td",{parentName:"tr",align:null},"1098894"),(0,r.kt)("td",{parentName:"tr",align:null},"412902"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null}),(0,r.kt)("td",{parentName:"tr",align:null})),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#en.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T14:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},"211411"),(0,r.kt)("td",{parentName:"tr",align:null},"1310305"),(0,r.kt)("td",{parentName:"tr",align:null},"566293"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null}),(0,r.kt)("td",{parentName:"tr",align:null})),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#en.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T15:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},"101247"),(0,r.kt)("td",{parentName:"tr",align:null},"1411552"),(0,r.kt)("td",{parentName:"tr",align:null},"573636"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null}),(0,r.kt)("td",{parentName:"tr",align:null})),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#en.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T16:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},"189765"),(0,r.kt)("td",{parentName:"tr",align:null},"1601317"),(0,r.kt)("td",{parentName:"tr",align:null},"688965"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null}),(0,r.kt)("td",{parentName:"tr",align:null})),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#en.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T17:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},"74404"),(0,r.kt)("td",{parentName:"tr",align:null},"1675721"),(0,r.kt)("td",{parentName:"tr",align:null},"679878"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null}),(0,r.kt)("td",{parentName:"tr",align:null})),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#en.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T18:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},"104824"),(0,r.kt)("td",{parentName:"tr",align:null},"1780545"),(0,r.kt)("td",{parentName:"tr",align:null},"681651"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null}),(0,r.kt)("td",{parentName:"tr",align:null})),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#en.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T19:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},"71268"),(0,r.kt)("td",{parentName:"tr",align:null},"1851813"),(0,r.kt)("td",{parentName:"tr",align:null},"541508"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null}),(0,r.kt)("td",{parentName:"tr",align:null})),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#en.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T20:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},"88185"),(0,r.kt)("td",{parentName:"tr",align:null},"1939998"),(0,r.kt)("td",{parentName:"tr",align:null},"528446"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null}),(0,r.kt)("td",{parentName:"tr",align:null})),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"#en.wikipedia")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"2016-06-27T21:00:00.000Z")),(0,r.kt)("td",{parentName:"tr",align:null},"42584"),(0,r.kt)("td",{parentName:"tr",align:null},"1982582"),(0,r.kt)("td",{parentName:"tr",align:null},"381265"),(0,r.kt)("td",{parentName:"tr",align:null},"5"),(0,r.kt)("td",{parentName:"tr",align:null}),(0,r.kt)("td",{parentName:"tr",align:null}))))),(0,r.kt)("p",null,"The example defines multiple window specifications in the WINDOW clause that you can use for various window function calculations."),(0,r.kt)("p",null,"The query uses two windows:"),(0,r.kt)("ul",null,(0,r.kt)("li",{parentName:"ul"},(0,r.kt)("inlineCode",{parentName:"li"},"cumulative")," is partitioned by channel and includes all rows from the beginning of partition up to the current row as ordered by ",(0,r.kt)("inlineCode",{parentName:"li"},"__time")," to enable cumulative aggregation"),(0,r.kt)("li",{parentName:"ul"},(0,r.kt)("inlineCode",{parentName:"li"},"moving5")," is also partitioned by channel but only includes up to the last four rows and the current row as ordered by time")),(0,r.kt)("p",null,"The number of rows considered for the ",(0,r.kt)("inlineCode",{parentName:"p"},"moving5")," window for the ",(0,r.kt)("inlineCode",{parentName:"p"},"count5")," column:"),(0,r.kt)("ul",null,(0,r.kt)("li",{parentName:"ul"},"starts at a single row because there are no rows before the current one"),(0,r.kt)("li",{parentName:"ul"},"grows up to five rows as defined by ",(0,r.kt)("inlineCode",{parentName:"li"},"ROWS BETWEEN 4 ROWS PRECEDING AND CURRENT ROW"))),(0,r.kt)("h2",{id:"known-issues"},"Known issues"),(0,r.kt)("p",null,"The following are known issues with window functions:"),(0,r.kt)("ul",null,(0,r.kt)("li",{parentName:"ul"},"SELECT * queries without a WHERE clause are not supported. If you want to retrieve all columns in this case, specify the column names.")))}g.isMDXComponent=!0}}]);