3e:null 3f:["$","title",null,{"children":"Working with Window Queries | PBI Queryous's Workspace | Observable"}] 45:T688,md` Window queries are a powerful tool for data analysis that calculate results over multiple input rows. Unlike normal aggregation queries, which reduce a set of values to a single summary result, window queries instead produce a result for each row of a table. Window query expressions may include aggregation operations, such as \`count()\`, \`sum()\`, and \`average()\`, and may be applied across an entire group or over a sliding window of ordered values, enabling calculation of ranks, percentiles, running totals, and moving averages. This notebook explores the use of window queries in [Arquero](https://observablehq.com/uwdata/introducing-arquero), a library for query processing of array data, alongside equivalent [SQL (Structured Query Language)](https://en.wikipedia.org/wiki/SQL) database queries. We also use the [Vega-Lite API](https://observablehq.com/vega/vega-lite-api) to visualize and interactively explore window query results. As a running example we will examine daily reported COVID-19 cases from March 3 to May 5, 2020 in Berlin, Germany, as reported by the [Robert Koch Institute](https://www.rki.de/DE/Content/InfAZ/N/Neuartiges_Coronavirus/nCoV.html). The data consists of two columns: \`day\` (an integer day count beginning March 3, 2020) and \`cases\` (the reported count of new cases on a day). As COVID-19 visualizations have proliferated in the news and on social media, it is important to understand the data transformations commonly applied to create these charts so we can appropriately interpret them — keeping in mind that each case is a potentially life-threatening exposure for a person, their family, and their contacts. `46:T613,md`Window queries also support specialized [window functions](https://uwdata.github.io/arquero/api/op#window-functions) that operate over an ordered set of values, including [\`rank()\`](https://uwdata.github.io/arquero/api/op#rank), [\`ntile()\`](https://uwdata.github.io/arquero/api/op#ntile) (e.g., percentile), [\`lead()\`](https://uwdata.github.io/arquero/api/op#lead), and [\`lag()\`](https://uwdata.github.io/arquero/api/op#lag) methods. To apply these operations we must indicate how to sort the data before computing the windowed expression. For example, the \`rank()\` operator returns rank-order values starting from 1. In the case of ties, it skips values: the descending values \`[5, 3, 3, 1]\` are assigned ranks \`[1, 2, 2, 4]\`. The [\`dense_rank()\`](https://uwdata.github.io/arquero/api/op#dense_rank), [\`avg_rank()\`](https://uwdata.github.io/arquero/api/op#dense_rank), [\`percent_rank()\`](https://uwdata.github.io/arquero/api/op#percent_rank), and [\`row_number()\`](https://uwdata.github.io/arquero/api/op#row_number) methods provide alternative ranking schemes. Let's highlight the 15 top-ranked days in terms of new cases, so that we can examine how closely these values cluster together. We first order the data in descending order by case count. We then apply the \`rank()\` operator along with a threshold, returning \`true\` for the top 15 ranks and \`false\` otherwise. We see that these top values are grouped within a three-week period, corresponding to the crest of the first wave of coronavirus infections in Berlin. `47:T50b,md`Window queries also permit comparing values with prior ("lagging") or subsequent ("leading") values, using the [\`lag()\`](https://uwdata.github.io/arquero/api/op#lag) and [\`lead()\`](https://uwdata.github.io/arquero/api/op#lead) operators. Using the \`lag()\` function we can subtract the previous day's case count from the current day's to more directly visualize fluctuations in daily reported cases. The \`lag()\` function takes as input a column expression to access, the offset into the ordered list of values (default \`1\`, to extract from the immediately previous row) and a default value to return when the offset lies outside the sorted rows (here, we use \`0\`). The chart below visualizes the daily differences alongside a chart of daily counts. Hover on the upper chart to see which input records participate in the window expression. The daily fluctuation is significant, reflecting not just disease incidence but testing and reporting processes. For example, case counts might decline over a weekend solely due to test processing or administrative reporting delays. We should also be careful when sharing such charts to avoid misintepretation: here a negative value indicates fewer cases than the previous day, but the _cumulative_ number of cases is still increasing.`48:T4ec,diffChart = { const links = diff .derive({ days: aq.rolling(op.array_agg('day'), [-1, 0]), cases: aq.rolling(op.array_agg('cases'), [-1, 0]) }) .select('day', 'days', 'cases') .unroll(['days', 'cases']); const sel = vl.selectSingle('link') .on('mouseover').clear('mouseout') .fields('day').empty('none'); const x = vl.x().fieldO('day').axis({ labelAngle: 0, labelOverlap: true }).title(null); const y = vl.y().fieldQ('cases').title(null); const base = vl.markBar().encode(x).width(width - 45); const bg = base.markBar({ fill: 'transparent' }).select(sel); const daily = base.encode(y).height(50).title({ text: 'New COVID-19 cases per day', anchor: 'start', frame: 'group' }); const input = base.markBar({ fill: 'firebrick' }) .data(links) .transform(vl.filter(sel)) .encode(x.fieldO('days'), y); const diffs = base .encode( x.axis(null), vl.y().fieldQ('diff').title(null), vl.color().if(sel, vl.value('firebrick')).value('#4e79a7') ); return vl.data(diff) .vconcat(vl.layer(bg, diffs), vl.layer(daily, input)) .title({ text: 'Change in new COVID-19 cases from prior day', anchor: 'start', frame: 'group' }) .render() }49:T515,md`So far we have looked at individual daily cases. We might instead want to view the total (or _cumulative_) number of cases to examine case growth over time. To do so we can compute a running sum, adding each daily case count in order. Arquero provides the [\`rolling()\`](https://uwdata.github.io/arquero/api/#rolling) function to indicate that an expression should be calculated over a rolling (or "sliding") window of values. The default is to include the current row and all previous rows. As a result, the \`sum()\` aggregate is applied relative to this rolling range of values rather than to all values. Mouse over the chart to highlight all input values that contribute to a selected total. Use the drop-down menu to adjust the scale transform. Such cumulative charts are typically plotted using a [logarithmic scale](https://en.wikipedia.org/wiki/Logarithmic_scale) in order to: - handle values distributed over multiple orders of magnitude (ranging from tens to hundreds to thousands of cases), - assess growth rates (straight lines on a log scale correspond to a fixed rate of [exponential growth](https://en.wikipedia.org/wiki/Exponential_growth)), and - better compare trajectories across regions (for example, if we add locales in addition to Berlin with varied population sizes). `4a:T612,cumulativeChart = { const links = cumulative .derive({ days: aq.rolling(op.array_agg('day'), [-Infinity, 0]), cases: aq.rolling(op.array_agg('cases'), [-Infinity, 0]) }) .select('day', 'days', 'cases') .unroll(['days', 'cases']); const sel = vl.selectSingle('link') .on('mouseover').clear('mouseout') .fields('day').empty('none').nearest(true); const x = vl.x().fieldO('day').axis({ labelAngle: 0, labelOverlap: true }).title(null); const y = vl.y().fieldQ('total').scale({ type: scaleType }).title(null); const bg = vl.markBar({ fill: 'transparent' }) .select(sel) .encode(x); const daily = vl.markCircle() .transform(vl.filter('datum.cases > 0')) .encode(x, y.fieldQ('cases')); const tally = vl.markRule({ color: 'firebrick', strokeWidth: 1.5, strokeDash: [3, 3] }) .transform(vl.filter(sel)) .encode(x, y, vl.y2().datum(1)); const input = vl.markCircle({ color: 'firebrick' }) .data(links) .transform(vl.filter(sel), vl.filter('datum.cases > 0')) .encode(x.fieldO('days'), y.fieldQ('cases')); const total = vl.markLine({ color: 'black' }) .encode(x, y); const curr = vl.markCircle({ color: 'black', size: 49 }) .transform(vl.filter(sel)) .encode(x, y); return vl.data(cumulative) .layer(bg, daily, tally, input, total, curr) .title({ text: 'Cumulative reported COVID-19 cases in Berlin, from March 3 to May 5, 2020', anchor: 'start', frame: 'group' }) .width(width - 45) .render(); }4b:T75a,md`Transitioning back to the daily figures, we earlier noted the volatility in case counts. To get a clearer picture of the virus' progression it is common to _smooth_ the data. A [moving average](https://en.wikipedia.org/wiki/Moving_average) involves a series of averages computed over a local neighborhood (or "window") of values, such as all days in the past week. To calculate a moving average, we must specify a sliding _window frame_. Our cumulative plot used a default frame containing the current row and all preceding rows. Here, we further specify the window frame as the second argument to the \`rolling()\` helper method. For example, the frame \`[-6, 0]\` indicates a 7-day sliding window, spanning six days prior (\`-6\`) up to the current day (\`0\`). To smooth COVID-19 case counts, many newsrooms use a 7-day moving average frame that ends at the current day and encompasses the prior 6 days (\`[-6, 0]\`). Why this particular configuration? First, including one full week helps to capture weekly periodic effects (for example, different processing rates over weekends). Second, including the prior 6 days ensures that the latest values include a full week of smoothing. If instead we use the three preceding and three following days (\`[-3, 3]\`), the data for the latest day will have a window of reduced size: covering only four days and so potentially leading to more volatility in the most recent estimates. The black line below shows the moving average of case counts, putting the rise and gradual decline of reported cases into clearer relief. (Though not visualized here, after a summer of relatively low case loads, sadly the case counts starkly rose again in Fall 2020 in a second wave of infections.) Mouse over the plot to see which values participate in the moving average for a given point. Use the drop-down menu to view different frame values. `4c:T739,rollingChart = { const data = rolling .derive({ index: () => op.row_number() - 1 }); const links = data .derive({ day: aq.rolling(op.array_agg('day'), frame), cases: aq.rolling(op.array_agg('cases'), frame) }) .unroll(['day', 'cases']); const sel = vl.selectSingle('link') .on('mouseover').clear('mouseout') .fields('index').empty('none').nearest(true); return vl.data(data) .layer( vl.markBar({ fill: 'transparent' }) .select(sel) .encode( vl.x().fieldO('day').axis({ labelAngle: 0, labelOverlap: true }).title(null) ), vl.markBar() .encode( vl.x().fieldO('day').axis({ labelAngle: 0 }).title(null), vl.y().fieldQ('cases').title(null) ), vl.markBar({ fill: 'firebrick' }) .data(links) .transform(vl.filter(sel)) .encode( vl.x().fieldO('day').axis({ labelAngle: 0 }).title(null), vl.y().fieldQ('cases').title(null) ), vl.markLine({ point: false, color: 'black', interpolate: 'monotone' }) .encode( vl.x().fieldO('day'), vl.y().fieldQ('ravg').title(null) ), vl.markRule({ color: 'black', strokeWidth: 1.5, strokeDash: [3, 3] }) .data(links) .transform(vl.filter(sel)) .encode( vl.x().min('day').type('ordinal'), vl.x2().max('day').type('ordinal'), vl.y().mean('ravg').title(null) ), vl.markCircle({ color: 'black', size: 49, opacity: 1 }) .transform(vl.filter(sel)) .encode( vl.x().fieldO('day'), vl.y().fieldQ('ravg').title(null) ) ) .title({ text: 'COVID-19 daily cases and moving average', anchor: 'start', frame: 'group' }) .width(width - 45) .render(); }4d:T622,md`At this point, we have covered the fundamentals of a variety of common window queries. Let's step away from the case count data and consider one final, abstract example. The query below computes a rolling \`sum\` over groups of ordered \`value\`s. The _peers_ parameter specifies how to handle sorting ties. If two table rows are considered equivalent by the ordering criteria we call them "peer" rows. By default Arquero ignores peers: the window frame is defined solely by the offsets from the current row. However, this is not always desirable. Imagine that we are sorting by day but may have multiple values observed per day: we might want the window frame to automatically adjust its boundaries to incorporate all peer values. Window queries can also apply to grouped (or _partitioned_) data: the data is subdivided into separate, ordered collections and the window query is evaluated independently for each group. In Arquero, the standard [\`groupby()\` verb](https://uwdata.github.io/arquero/api/verbs#groupby) applies, while SQL instead uses the \`PARTITION BY\` clause. Adjust the interactive parameters to explore the space of window queries. Mouse along the \`sum\` row to view the window frames over which each output is calculated. Use the "Peers?" checkbox to toggle consideration of peers and examine the change in output, and note how the SQL query changes: the \`ROWS\` keyword corresponds to ignoring peers, whereas the \`RANGE\` keyword accommodates them. Use the "Groupby?" checkbox to toggle the inclusion of groups and inspect the results. `4e:T4f0,viewof params = { const ctrls = [ { name: 'start', node: select({ description: 'Frame Start', options: [-Infinity, -5, -4, -3, -2, -1, 0], value: -3 }) }, { name: 'end', node: select({ description: 'Frame End', options: [0, 1, 2, 3, 4, 5, Infinity], value: 3 }) }, { name: 'peers', node: checkbox({ description: 'Include Peer Values', options: [{ value: false, label: "Peers?" }] }) }, { name: 'group', node: checkbox({ description: 'Partition Groups', options: [{ value: false, label: "Groupby?" }] }) } ]; function getValue() { return { frame: [+ctrls[0].node.value, +ctrls[1].node.value], peers: !!ctrls[2].node.value, group: !!ctrls[3].node.value }; } const div = html`

`; ctrls.forEach((ctrl, index) => { ctrl.node.style.display = 'inline-block'; if (index) ctrl.node.style.marginLeft = '1.5em'; div.appendChild(ctrl.node); ctrl.node.addEventListener('change', () => { div.value = getValue(); div.dispatchEvent(new CustomEvent('input')); }); }); div.value = getValue(); return div; }4f:T985,function visualize(table, input, output, frame, peers, group) { const { data, links } = windowData(table, input, output, frame, peers, group); const sel = vl.selectSingle('link') .on('mouseover').empty('none') .fields('index').init({ index: 8 }); const winGroup = vl .layer( vl.markText({ fontSize: 16, fill: '#ccc' }) .encode( vl.x().fieldO('index').axis(null), vl.text().fieldN('group') ), vl.markRect({ stroke: null, fill: '#f5f5f5' }) .data(links) .transform(vl.filter(sel)) .encode(vl.x().fieldO('position').axis(null)), vl.markText({ fontSize: 16, fill: 'firebrick' }) .data(links) .transform(vl.filter(sel)) .encode( vl.x().fieldO('position').axis(null), vl.text().fieldN('group') ) ).title({ text: 'group', orient: 'left', offset: 10, angle: 0, align: 'left', baseline: 'middle' }); const winInput = vl .layer( vl.markText({ fontSize: 16, fill: '#ccc' }) .encode( vl.x().fieldO('index').axis(null), vl.text().fieldN('input') ), vl.markRect({ stroke: null, fill: '#f5f5f5' }) .data(links) .transform(vl.filter(sel)) .encode(vl.x().fieldO('position').axis(null)), vl.markText({ fontSize: 16, fill: 'firebrick' }) .data(links) .transform(vl.filter(sel)) .encode( vl.x().fieldO('position').axis(null), vl.text().fieldN('input') ) ).title({ text: 'value', orient: 'left', offset: 10, angle: 0, align: 'left', baseline: 'middle' }); const winOutput = vl .layer( vl.markRect() .select(sel) .encode( vl.x().fieldO('index').axis(null), vl.color().if(sel, vl.value('#f5f5f5')).value('transparent') ), vl.markText({ fontSize: 16 }) .encode( vl.x().fieldO('index').axis(null), vl.text().fieldN('output'), vl.color().if(sel, vl.value('firebrick')).value('black') ) ).title({ text: 'sum', orient: 'left', offset: 10, angle: 0, align: 'left', baseline: 'middle' }); return vl.data(data) .vconcat([ ...(group ? [winGroup] : []), winInput, winOutput ]) .spacing(5) .config({ view: { stroke: null, step: 35 } }) .render({ renderer: 'svg' }) }50:[] 43:{"id":"d0432253dcab473e","slug":null,"title":"Working with Window Queries","thumbnail":"146ca414fc52043ba9f801f9d3accd275385001b2ea1d880ed01c0b3a2d93d78","access_level":"public","create_time":"$D2023-12-28T12:24:30.220Z","update_time":"$D2023-12-28T12:24:30.220Z","view_time":null,"first_public_time":"$D2023-12-28T12:24:30.220Z","has_unpublished_edits":null,"fork":{"id":"6ee143830de8a7a8","slug":"working-with-window-queries","title":"Working with Window Queries","owner":{"id":"33e135341fd39b6b","login":"uwdata","name":"UW Interactive Data Lab"}},"stars":0,"starred":false,"owner":{"type":"team","id":"313be0b4ebc7608f","login":"pbi-queryous-ws","name":"PBI Queryous's Workspace","avatar_url":"https://avatars-next.observableusercontent.com/404ba15c07c49cb4ef6b5f753efa3e5db0c7d2be11fa244cc724a2f8b2b3494f","tier":"starter_2024","bio":"","following":false},"version":1305,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"095ccf69e3466ab9","login":"pbi-queryous","name":"PBI Queryous","avatar_url":"https://avatars-next.observableusercontent.com/0e362e0326315a8b72bf30cfe2bf107a47fb597d0f8798eeb7f6330b2bbb725a","tier":null,"bio":"","following":false}],"members":[{"type":"individual","id":"095ccf69e3466ab9","login":"pbi-queryous","name":"PBI Queryous","avatar_url":"https://avatars-next.observableusercontent.com/0e362e0326315a8b72bf30cfe2bf107a47fb597d0f8798eeb7f6330b2bbb725a","tier":null,"role":"editor"},{"type":"team","id":"313be0b4ebc7608f","login":"pbi-queryous-ws","name":"PBI Queryous's Workspace","avatar_url":"https://avatars-next.observableusercontent.com/404ba15c07c49cb4ef6b5f753efa3e5db0c7d2be11fa244cc724a2f8b2b3494f","tier":"starter_2024","role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"titling":"auto","title":"Working with Window Queries","cells":[{"id":0,"value":"md`# Working with Window Queries`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":885,"value":"$45","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":500,"value":"// extracted from daily reports from the Robert Koch Institut: \n// https://www.rki.de/DE/Content/InfAZ/N/Neuartiges_Coronavirus/Situationsberichte/Gesamt.html\ndata = aq.table({\n day: op.sequence(1, 65),\n cases: [\n 3, 4, 6, 6, 9, 12, 8, 0, 42, 47, 37, 42, 49, 35, 45, 46, 182, 158, 135, 158, 53, 143,\n 208, 228, 299, 206, 199, 104, 111, 179, 216, 232, 269, 142, 57, 175, 183, 174, 147, 109, 109, 34, 67, 54,\n 126, 97, 121, 93, 37, 41, 75, 12, 135, 66, 75, 38, 31, 65, 93, 60, 56, 33, 34, 32\n ]\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":84,"value":"casesChart = vl.markBar()\n .data(data)\n .encode(\n vl.x().fieldO('day').axis({ labelAngle: 0, labelOverlap: true }).title(null),\n vl.y().fieldQ('cases').title(null),\n vl.tooltip().fieldQ('cases')\n )\n .title({\n text: 'New reported COVID-19 cases per day in Berlin from March 3 to May 5, 2020',\n anchor: 'start', frame: 'group'\n })\n .width(width - 45)\n .render()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":911,"value":"md`Let's start with a basic bar chart of cases per day. We can see that daily cases in Berlin increased into mid-March and April, followed by a gradual decline, and with significant volatility throughout.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":5,"value":"md`

\n## Incorporating Aggregates`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":917,"value":"md`Window queries support aggregate calculations within _per-row_ expressions. For example, we could divide each value by the sum of all values to produce proportions (or probabilities), or subtract the average value and divide by the standard deviation to produce [standardized z-scores](https://en.wikipedia.org/wiki/Standard_score).\n\nTo visualize which days have case counts above or below the global average, we can use a window query that subtracts the average from each daily case count. Underneath the hood, the query processor first calculates the group mean, then uses this aggregate result to compute the per-row outputs. Here the \"window\" of values under consideration for the average consists of _all_ values in the group; we will examine \"sliding\" window frames later.\n\nArquero's [\\`derive()\\`](https://uwdata.github.io/arquero/api/verbs#derive) verb, which derives new per-row values, can express this window query as a simple expression:\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":28,"value":"deviation = data.derive({ deviation: d => d.cases - op.average(d.cases) })","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":197,"value":"deviationChart = vl.markBar()\n .data(deviation)\n .encode(\n vl.x().fieldO('day').axis({ labelAngle: 0, labelOverlap: true }).title(null),\n vl.y().fieldQ('deviation').title(null),\n vl.tooltip().fieldQ('deviation')\n )\n .title({\n text: 'Deviation from global average of new reported COVID-19 cases per day',\n anchor: 'start', frame: 'group'\n })\n .width(width - 45)\n .render()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":822,"value":"md`_SQL equivalent:_\n~~~\nSELECT day, cases - AVERAGE(cases) OVER () AS deviation FROM data\n~~~`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":924,"value":"md`In SQL, the \\`OVER ()\\` clause indicates that the \\`AVERAGE()\\` call should be calculated as a window query. The empty parentheses are required and indicate that neither sorting (\\`ORDER BY\\`) nor grouping (\\`PARTITION BY\\`) need to be performed.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":33,"value":"md`

\n## Ranking Values`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":933,"value":"$46","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":37,"value":"rank = data\n .orderby(aq.desc('cases'))\n .derive({ topK: d => op.rank() <= 15 })","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":188,"value":"rankChart = vl.markBar()\n .data(rank)\n .encode(\n vl.x().fieldO('day').axis({ labelAngle: 0, labelOverlap: true }).title(null),\n vl.y().fieldQ('cases').title(null),\n vl.color().fieldN('topK').scale({ range: ['#4e79a7', 'firebrick'] }),\n vl.tooltip().fieldQ('cases')\n )\n .title({\n text: 'New COVID-19 cases per day, emphasizing the 15 highest days',\n anchor: 'start', frame: 'group'\n })\n .width(width - 45)\n .render()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":832,"value":"md`_SQL equivalent:_\n~~~\nSELECT day, cases, rank() OVER (ORDER BY cases DESC) <= 15 AS topK FROM data\n~~~`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":943,"value":"md`For the SQL version, we now include \\`ORDER BY\\` within the \\`OVER (...)\\` clause.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":35,"value":"md`

\n## Comparing to Past or Future Values`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":952,"value":"$47","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":43,"value":"diff = data\n .orderby('day')\n .derive({ diff: d => d.cases - op.lag(d.cases, 1, 0) })","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":174,"value":"$48","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":835,"value":"md`_SQL equivalent:_\n~~~\nSELECT day, cases, cases - LAG(cases, 1, 0) OVER (ORDER BY day) AS diff FROM data\n~~~`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":53,"value":"md`

\n## Running Totals`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":970,"value":"$49","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":56,"value":"cumulative = data\n .orderby('day')\n .derive({ total: aq.rolling(d => op.sum(d.cases)) })","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":135,"value":"$4a","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":458,"value":"viewof scaleType = select({\n description: 'Y-Axis Scale',\n options: [\n { label: 'Linear', value: 'linear' },\n { label: 'Logarithmic', value: 'log' },\n ],\n value: 'log'\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":839,"value":"md`_SQL equivalent:_\n~~~\nSELECT day, cases, SUM(cases) OVER (ORDER BY day) AS total FROM data\n~~~`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":63,"value":"md`

\n## Sliding Window Frames`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":980,"value":"$4b","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":147,"value":"rolling = data\n .orderby('day')\n .derive({ ravg: aq.rolling(d => op.average(d.cases), frame) })","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1292,"value":"$4c","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":411,"value":"viewof frame = {\n const opt = {\n description: 'Sliding Window Frame',\n options: [\n { label: '7-day moving average, with prior 6 days: [-6,0]', value: [-6, 0] },\n { label: '7-day moving average, centered at current day: [-3,3]', value: [-3, 3] },\n { label: 'Moving average, with all prior days [-∞, 0]', value: [-Infinity, 0] },\n { label: 'Global average [-∞, +∞]', value: [-Infinity, +Infinity] }\n ]\n };\n\n const div = document.createElement('div');\n\n const menu = select(opt);\n menu.addEventListener('input', () => {\n const index = menu.querySelector('select').selectedIndex;\n div.value = opt.options[index].value;\n div.dispatchEvent(new CustomEvent('input'));\n });\n\n div.appendChild(menu);\n div.value = opt.options[0].value;\n return div;\n};","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":849,"value":"md`_SQL equivalent:_\n~~~\nSELECT day, cases, AVERAGE(cases) OVER (\n ORDER BY day ${frameSQL(frame, false)}\n) AS ravg FROM data\n~~~`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":997,"value":"md`SQL uses a more verbose, pseudo natural language syntax for specifying a window frame. Either the \\`ROWS\\` or \\`RANGE\\` keyword can be used to initiate a frame specification. We discuss the difference between the two below...`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":200,"value":"md`

\n## Window Frame Explorer\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1005,"value":"$4d","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":703,"value":"$4e","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":295,"value":"frameExplorer = {\n const { frame, peers, group } = params;\n return visualize(\n aq.table({\n group: 'aaaaaaaaabbbbbbbb'.split(''),\n value: [1, 2, 3, 4, 5, 5, 6, 7, 8, 9, 10, 11, 11, 12, 13, 14, 15]\n })\n .orderby('value')\n .groupby(group ? 'group' : [])\n .derive({ sum: aq.rolling(op.sum('value'), frame, peers) }),\n 'value', 'sum', frame, peers, group\n )\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":800,"value":"{\n const { frame, peers, group } = params;\n return md`\n_As Arquero code:_\n~~~ js\ndt.orderby('value')${group ? \"\\n .groupby('group')\" : ''}\n .derive({ sum: aq.rolling(op.sum('value'), [${frame.join(', ')}], ${peers}) })\n~~~`\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":785,"value":"md`_As a SQL query:_\n~~~\nSELECT value, SUM(value) OVER (\n ${params.group ? 'PARTITION BY group ' : ''}ORDER BY value ${frameSQL(params.frame, params.peers)}\n) AS sum FROM data\n~~~`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1127,"value":"md`

\n## Conclusion\n\nAppropriate transformations are critical when trying to convey the dynamics and impact of pressing societal issues, including the COVID-19 pandemic. Hopefully you are now better equipped to interpret charts based on window queries and properly use them to advance your own analyses. For more examples of public health communication (and window queries), see the [Financial Times Coronavirus Tracker](https://www.ft.com/content/a2901ce8-5eb7-4633-b89c-cbdf5b386938).\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":8,"value":"md`

\n## Appendix`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":12,"value":"import {aq, op} from '@uwdata/arquero'","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":80,"value":"import { vl } from '@vega/vega-lite-api'","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":677,"value":"import { checkbox, select } from '@jashkenas/inputs'","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":275,"value":"function windowData(table, source, target, frame, peers, group) {\n table = table.ungroup().derive({ _index: () => op.row_number() - 1 });\n if (group) table = table.groupby('group');\n\n const indexed = table\n .derive({\n _frame: aq.rolling(op.array_agg('_index'), frame, peers),\n _input: aq.rolling(op.array_agg(source), frame, peers),\n ...(group ? { _group: aq.rolling(op.array_agg('group'), frame, peers) } : {} )\n });\n\n return {\n data: indexed\n .select({ _index: 'index', group: 'group', [source]: 'input', [target]: 'output' })\n .objects(),\n links: indexed\n .unroll(['_frame', '_input', ...(group ? ['_group'] : [])])\n .select({\n _index: 'index',\n _frame: 'position',\n _input: 'input',\n ...(group ? { _group: 'group' } : null)\n })\n .objects()\n };\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":293,"value":"$4f","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":852,"value":"function frameSQL(frame, peers) {\n const [f0, f1] = frame;\n return (peers ? 'RANGE' : 'ROWS')\n + ' BETWEEN '\n + (f0 === 0 ? 'CURRENT ROW' : ((isFinite(f0) ? Math.abs(f0) : 'UNBOUNDED') + ' PRECEDING'))\n + ' AND '\n + (f1 === 0 ? 'CURRENT ROW' : ((isFinite(f1) ? Math.abs(f1) : 'UNBOUNDED') + ' FOLLOWING'));\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W50","stdlib":"1"}} 44:"$43:body"