🕷️ Crawler Inspector

URL Lookup

Direct Parameter Lookup

Raw Queries and Responses

1. Shard Calculation

Query:

Response:

Calculated Shard: 89 (from laksa014)

2. Crawled Status Check

Query:

curl -X POST \
  'http://laksa089.int.ahrefs:8124/' \
  -H 'Content-Type: text/plain' \
  -H 'X-ClickHouse-Database: crawler3' \
  -H 'Authorization: Basic YXBpOg==' \
  -d 'SELECT getAhrefsURLFromUnparsed(src_unparsed) AS found_url, ifNull(toUnixTimestamp(download_stamp), 0) AS crawl_time, ifNull(toUnixTimestamp(props_url_first_seen), 0) AS first_indexed_time, download_http_code AS http_code, src_unparsed AS src_unparsed, src_root_hash AS src_root_hash, history_drop_reason AS history_drop_reason, meta_title AS meta_title, meta_descriptions AS meta_descriptions, attrs_boilerpipe_text AS attrs_boilerpipe_text, attrs_markdown AS attrs_markdown, attrs_readable_markdown AS attrs_readable_markdown, meta_canonical AS meta_canonical FROM crawler3.page_info_local FINAL PREWHERE (src_root_hash, src_unparsed) IN ((getAhrefsRootHashFromUnparsed(getAhrefsUnparsedNoserviceFromURL(\'https://clickhouse.com/docs/sql-reference/window-functions\')), getAhrefsUnparsedNoserviceFromURL(\'https://clickhouse.com/docs/sql-reference/window-functions\'))) FORMAT JSONEachRow'

Response:

{"found_url":"https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions","crawl_time":1775876956,"first_indexed_time":1740096567,"http_code":200,"src_unparsed":"com,clickhouse!\/docs\/sql-reference\/window-functions s443","src_root_hash":"12633450985039531489","history_drop_reason":null,"meta_title":"Window Functions | ClickHouse Docs","meta_descriptions":["Overview page for window functions"],"attrs_boilerpipe_text":"Window functions let you perform calculations across a set of rows that are related to the current row.\nSome of the calculations that you can do are similar to those that can be done with an aggregate function, but a window function doesn't cause rows to be grouped into a single output - the individual rows are still returned.\nStandard window functions\n\nClickHouse supports the standard grammar for defining windows and window functions. The table below indicates whether a feature is currently supported.\nFeature\nSupported?\nad hoc window specification (\ncount(*) over (partition by id order by time desc)\n)\n✅\nexpressions involving window functions, e.g.\n(count(*) over ()) \/ 2)\n✅\nWINDOW\nclause (\nselect ... from table window w as (partition by id)\n)\n✅\nROWS\nframe\n✅\nRANGE\nframe\n✅ (the default)\nINTERVAL\nsyntax for\nDateTime\nRANGE OFFSET\nframe\n❌ (specify the number of seconds instead (\nRANGE\nworks with any numeric type).)\nGROUPS\nframe\n❌\nCalculating aggregate functions over a frame (\nsum(value) over (order by time)\n)\n✅ (All aggregate functions are supported)\nrank()\n,\ndense_rank()\n,\nrow_number()\n✅\nAlias:\ndenseRank()\npercent_rank()\n✅  Efficiently computes the relative standing of a value within a partition in a dataset. This function effectively replaces the more verbose and computationally intensive manual SQL calculation expressed as\nifNull((rank() OVER(PARTITION BY x ORDER BY y) - 1) \/ nullif(count(1) OVER(PARTITION BY x) - 1, 0), 0)\nAlias:\npercentRank()\ncume_dist()\n✅  Computes the cumulative distribution of a value within a group of values. Returns the percentage of rows with values less than or equal to the current row's value.\nlag\/lead(value, offset)\n✅\nYou can also use one of the following workarounds:\n1)\nany(value) over (.... rows between <offset> preceding and <offset> preceding)\n, or\nfollowing\nfor\nlead\n2)\nlagInFrame\/leadInFrame\n, which are analogous, but respect the window frame. To get behavior identical to\nlag\/lead\n, use\nrows between unbounded preceding and unbounded following\nntile(buckets)\n✅\nSpecify window like, (partition by x order by y rows between unbounded preceding and unbounded following).\nClickHouse-specific window functions\n\nThere is also the following ClickHouse specific window function:\nnonNegativeDerivative(metric_column, timestamp_column[, INTERVAL X UNITS])\n\nFinds non-negative derivative for given\nmetric_column\nby\ntimestamp_column\n.\nINTERVAL\ncan be omitted, default is\nINTERVAL 1 SECOND\n.\nThe computed value is the following for each row:\n0\nfor 1st row,\nmetric\ni\n−\nmetric\ni\n−\n1\ntimestamp\ni\n−\ntimestamp\ni\n−\n1\n∗\ninterval\n{\\text{metric}_i - \\text{metric}_{i-1} \\over \\text{timestamp}_i - \\text{timestamp}_{i-1}}  * \\text{interval}\nfor\ni\nt\nh\ni_{th}\nrow.\nSyntax\n\naggregate_function (column_name)\nOVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column]\n[ROWS or RANGE expression_to_bound_rows_within_the_group]] | [window_name])\nFROM table_name\nWINDOW window_name as ([\n[PARTITION BY grouping_column]\n[ORDER BY sorting_column]\n[ROWS or RANGE expression_to_bound_rows_within_the_group]\n])\nPARTITION BY\n- defines how to break a resultset into groups.\nORDER BY\n- defines how to order rows inside the group during calculation aggregate_function.\nROWS or RANGE\n- defines bounds of a frame, aggregate_function is calculated within a frame.\nWINDOW\n- allows multiple expressions to use the same window definition.\nPARTITION\n┌─────────────────┐  <-- UNBOUNDED PRECEDING (BEGINNING of the PARTITION)\n│                 │\n│                 │\n│=================│  <-- N PRECEDING  <─┐\n│      N ROWS     │                     │  F\n│  Before CURRENT │                     │  R\n│~~~~~~~~~~~~~~~~~│  <-- CURRENT ROW    │  A\n│     M ROWS      │                     │  M\n│   After CURRENT │                     │  E\n│=================│  <-- M FOLLOWING  <─┘\n│                 │\n│                 │\n└─────────────────┘  <--- UNBOUNDED FOLLOWING (END of the PARTITION)\nFunctions\n\nThese functions can be used only as a window function.\nrow_number()\n- Number the current row within its partition starting from 1.\nfirst_value(x)\n- Return the first value evaluated within its ordered frame.\nlast_value(x)\n-    Return the last value evaluated within its ordered frame.\nnth_value(x, offset)\n- Return the first non-NULL value evaluated against the nth row (offset) in its ordered frame.\nrank()\n- Rank the current row within its partition with gaps.\ndense_rank()\n- Rank the current row within its partition without gaps.\nlagInFrame(x)\n- Return a value evaluated at the row that is at a specified physical offset row before the current row within the ordered frame.\nleadInFrame(x)\n- Return a value evaluated at the row that is offset rows after the current row within the ordered frame.\nExamples\n\nLet's have a look at some examples of how window functions can be used.\nNumbering rows\n\nCREATE\nTABLE\nsalaries\n(\n`\nteam\n`\nString\n,\n`\nplayer\n`\nString\n,\n`\nsalary\n`\nUInt32\n,\n`\nposition\n`\nString\n)\nEngine\n=\nMemory\n;\nINSERT\nINTO\nsalaries FORMAT\nValues\n(\n'Port Elizabeth Barbarians'\n,\n'Gary Chen'\n,\n195000\n,\n'F'\n)\n,\n(\n'New Coreystad Archdukes'\n,\n'Charles Juarez'\n,\n190000\n,\n'F'\n)\n,\n(\n'Port Elizabeth Barbarians'\n,\n'Michael Stanley'\n,\n150000\n,\n'D'\n)\n,\n(\n'New Coreystad Archdukes'\n,\n'Scott Harrison'\n,\n150000\n,\n'D'\n)\n,\n(\n'Port Elizabeth Barbarians'\n,\n'Robert George'\n,\n195000\n,\n'M'\n)\n;\nSELECT\nplayer\n,\nsalary\n,\nrow_number\n(\n)\nOVER\n(\nORDER\nBY\nsalary\nASC\n)\nAS\nrow\nFROM\nsalaries\n;\n┌─player──────────┬─salary─┬─row─┐\n│ Michael Stanley │ 150000 │   1 │\n│ Scott Harrison  │ 150000 │   2 │\n│ Charles Juarez  │ 190000 │   3 │\n│ Gary Chen       │ 195000 │   4 │\n│ Robert George   │ 195000 │   5 │\n└─────────────────┴────────┴─────┘\nSELECT\nplayer\n,\nsalary\n,\nrow_number\n(\n)\nOVER\n(\nORDER\nBY\nsalary\nASC\n)\nAS\nrow\n,\nrank\n(\n)\nOVER\n(\nORDER\nBY\nsalary\nASC\n)\nAS\nrank\n,\ndense_rank\n(\n)\nOVER\n(\nORDER\nBY\nsalary\nASC\n)\nAS\ndenseRank\nFROM\nsalaries\n;\n┌─player──────────┬─salary─┬─row─┬─rank─┬─denseRank─┐\n│ Michael Stanley │ 150000 │   1 │    1 │         1 │\n│ Scott Harrison  │ 150000 │   2 │    1 │         1 │\n│ Charles Juarez  │ 190000 │   3 │    3 │         2 │\n│ Gary Chen       │ 195000 │   4 │    4 │         3 │\n│ Robert George   │ 195000 │   5 │    4 │         3 │\n└─────────────────┴────────┴─────┴──────┴───────────┘\nAggregation functions\n\nCompare each player's salary to the average for their team.\nSELECT\nplayer\n,\nsalary\n,\nteam\n,\navg\n(\nsalary\n)\nOVER\n(\nPARTITION\nBY\nteam\n)\nAS\nteamAvg\n,\nsalary\n-\nteamAvg\nAS\ndiff\nFROM\nsalaries\n;\n┌─player──────────┬─salary─┬─team──────────────────────┬─teamAvg─┬───diff─┐\n│ Charles Juarez  │ 190000 │ New Coreystad Archdukes   │  170000 │  20000 │\n│ Scott Harrison  │ 150000 │ New Coreystad Archdukes   │  170000 │ -20000 │\n│ Gary Chen       │ 195000 │ Port Elizabeth Barbarians │  180000 │  15000 │\n│ Michael Stanley │ 150000 │ Port Elizabeth Barbarians │  180000 │ -30000 │\n│ Robert George   │ 195000 │ Port Elizabeth Barbarians │  180000 │  15000 │\n└─────────────────┴────────┴───────────────────────────┴─────────┴────────┘\nCompare each player's salary to the maximum for their team.\nSELECT\nplayer\n,\nsalary\n,\nteam\n,\nmax\n(\nsalary\n)\nOVER\n(\nPARTITION\nBY\nteam\n)\nAS\nteamMax\n,\nsalary\n-\nteamMax\nAS\ndiff\nFROM\nsalaries\n;\n┌─player──────────┬─salary─┬─team──────────────────────┬─teamMax─┬───diff─┐\n│ Charles Juarez  │ 190000 │ New Coreystad Archdukes   │  190000 │      0 │\n│ Scott Harrison  │ 150000 │ New Coreystad Archdukes   │  190000 │ -40000 │\n│ Gary Chen       │ 195000 │ Port Elizabeth Barbarians │  195000 │      0 │\n│ Michael Stanley │ 150000 │ Port Elizabeth Barbarians │  195000 │ -45000 │\n│ Robert George   │ 195000 │ Port Elizabeth Barbarians │  195000 │      0 │\n└─────────────────┴────────┴───────────────────────────┴─────────┴────────┘\nPartitioning by column\n\nCREATE\nTABLE\nwf_partition\n(\n`\npart_key\n`\nUInt64\n,\n`\nvalue\n`\nUInt64\n,\n`\norder\n`\nUInt64\n)\nENGINE\n=\nMemory\n;\nINSERT\nINTO\nwf_partition FORMAT\nValues\n(\n1\n,\n1\n,\n1\n)\n,\n(\n1\n,\n2\n,\n2\n)\n,\n(\n1\n,\n3\n,\n3\n)\n,\n(\n2\n,\n0\n,\n0\n)\n,\n(\n3\n,\n0\n,\n0\n)\n;\nSELECT\npart_key\n,\nvalue\n,\norder\n,\ngroupArray\n(\nvalue\n)\nOVER\n(\nPARTITION\nBY\npart_key\n)\nAS\nframe_values\nFROM\nwf_partition\nORDER\nBY\npart_key\nASC\n,\nvalue\nASC\n;\n┌─part_key─┬─\nvalue\n─┬─\norder\n─┬─frame_values─┐\n│\n1\n│\n1\n│\n1\n│\n[\n1\n,\n2\n,\n3\n]\n│\n<\n┐\n│\n1\n│\n2\n│\n2\n│\n[\n1\n,\n2\n,\n3\n]\n│    │\n1\n-\nst\ngroup\n│\n1\n│\n3\n│\n3\n│\n[\n1\n,\n2\n,\n3\n]\n│\n<\n┘\n│\n2\n│\n0\n│\n0\n│\n[\n0\n]\n│\n<\n-\n2\n-\nnd\ngroup\n│\n3\n│\n0\n│\n0\n│\n[\n0\n]\n│\n<\n-\n3\n-\nd\ngroup\n└──────────┴───────┴───────┴──────────────┘\nFrame bounding\n\nCREATE\nTABLE\nwf_frame\n(\n`\npart_key\n`\nUInt64\n,\n`\nvalue\n`\nUInt64\n,\n`\norder\n`\nUInt64\n)\nENGINE\n=\nMemory\n;\nINSERT\nINTO\nwf_frame FORMAT\nValues\n(\n1\n,\n1\n,\n1\n)\n,\n(\n1\n,\n2\n,\n2\n)\n,\n(\n1\n,\n3\n,\n3\n)\n,\n(\n1\n,\n4\n,\n4\n)\n,\n(\n1\n,\n5\n,\n5\n)\n;\n-- Frame is bounded by bounds of a partition (BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)\nSELECT\npart_key\n,\nvalue\n,\norder\n,\ngroupArray\n(\nvalue\n)\nOVER\n(\nPARTITION\nBY\npart_key\nORDER\nBY\norder\nASC\nROWS\nBETWEEN\nUNBOUNDED\nPRECEDING\nAND\nUNBOUNDED\nFOLLOWING\n)\nAS\nframe_values\nFROM\nwf_frame\nORDER\nBY\npart_key\nASC\n,\nvalue\nASC\n;\n┌─part_key─┬─\nvalue\n─┬─\norder\n─┬─frame_values─┐\n│\n1\n│\n1\n│\n1\n│\n[\n1\n,\n2\n,\n3\n,\n4\n,\n5\n]\n│\n│\n1\n│\n2\n│\n2\n│\n[\n1\n,\n2\n,\n3\n,\n4\n,\n5\n]\n│\n│\n1\n│\n3\n│\n3\n│\n[\n1\n,\n2\n,\n3\n,\n4\n,\n5\n]\n│\n│\n1\n│\n4\n│\n4\n│\n[\n1\n,\n2\n,\n3\n,\n4\n,\n5\n]\n│\n│\n1\n│\n5\n│\n5\n│\n[\n1\n,\n2\n,\n3\n,\n4\n,\n5\n]\n│\n└──────────┴───────┴───────┴──────────────┘\n-- short form - no bound expression, no order by,\n-- an equalent of `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`\nSELECT\npart_key\n,\nvalue\n,\norder\n,\ngroupArray\n(\nvalue\n)\nOVER\n(\nPARTITION\nBY\npart_key\n)\nAS\nframe_values_short\n,\ngroupArray\n(\nvalue\n)\nOVER\n(\nPARTITION\nBY\npart_key\nROWS\nBETWEEN\nUNBOUNDED\nPRECEDING\nAND\nUNBOUNDED\nFOLLOWING\n)\nAS\nframe_values\nFROM\nwf_frame\nORDER\nBY\npart_key\nASC\n,\nvalue\nASC\n;\n┌─part_key─┬─\nvalue\n─┬─\norder\n─┬─frame_values_short─┬─frame_values─┐\n│\n1\n│\n1\n│\n1\n│\n[\n1\n,\n2\n,\n3\n,\n4\n,\n5\n]\n│\n[\n1\n,\n2\n,\n3\n,\n4\n,\n5\n]\n│\n│\n1\n│\n2\n│\n2\n│\n[\n1\n,\n2\n,\n3\n,\n4\n,\n5\n]\n│\n[\n1\n,\n2\n,\n3\n,\n4\n,\n5\n]\n│\n│\n1\n│\n3\n│\n3\n│\n[\n1\n,\n2\n,\n3\n,\n4\n,\n5\n]\n│\n[\n1\n,\n2\n,\n3\n,\n4\n,\n5\n]\n│\n│\n1\n│\n4\n│\n4\n│\n[\n1\n,\n2\n,\n3\n,\n4\n,\n5\n]\n│\n[\n1\n,\n2\n,\n3\n,\n4\n,\n5\n]\n│\n│\n1\n│\n5\n│\n5\n│\n[\n1\n,\n2\n,\n3\n,\n4\n,\n5\n]\n│\n[\n1\n,\n2\n,\n3\n,\n4\n,\n5\n]\n│\n└──────────┴───────┴───────┴────────────────────┴──────────────┘\n-- frame is bounded by the beginning of a partition and the current row\nSELECT\npart_key\n,\nvalue\n,\norder\n,\ngroupArray\n(\nvalue\n)\nOVER\n(\nPARTITION\nBY\npart_key\nORDER\nBY\norder\nASC\nROWS\nBETWEEN\nUNBOUNDED\nPRECEDING\nAND\nCURRENT\nROW\n)\nAS\nframe_values\nFROM\nwf_frame\nORDER\nBY\npart_key\nASC\n,\nvalue\nASC\n;\n┌─part_key─┬─\nvalue\n─┬─\norder\n─┬─frame_values─┐\n│\n1\n│\n1\n│\n1\n│\n[\n1\n]\n│\n│\n1\n│\n2\n│\n2\n│\n[\n1\n,\n2\n]\n│\n│\n1\n│\n3\n│\n3\n│\n[\n1\n,\n2\n,\n3\n]\n│\n│\n1\n│\n4\n│\n4\n│\n[\n1\n,\n2\n,\n3\n,\n4\n]\n│\n│\n1\n│\n5\n│\n5\n│\n[\n1\n,\n2\n,\n3\n,\n4\n,\n5\n]\n│\n└──────────┴───────┴───────┴──────────────┘\n-- short form (frame is bounded by the beginning of a partition and the current row)\n-- an equalent of `ORDER BY order ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`\nSELECT\npart_key\n,\nvalue\n,\norder\n,\ngroupArray\n(\nvalue\n)\nOVER\n(\nPARTITION\nBY\npart_key\nORDER\nBY\norder\nASC\n)\nAS\nframe_values_short\n,\ngroupArray\n(\nvalue\n)\nOVER\n(\nPARTITION\nBY\npart_key\nORDER\nBY\norder\nASC\nROWS\nBETWEEN\nUNBOUNDED\nPRECEDING\nAND\nCURRENT\nROW\n)\nAS\nframe_values\nFROM\nwf_frame\nORDER\nBY\npart_key\nASC\n,\nvalue\nASC\n;\n┌─part_key─┬─\nvalue\n─┬─\norder\n─┬─frame_values_short─┬─frame_values─┐\n│\n1\n│\n1\n│\n1\n│\n[\n1\n]\n│\n[\n1\n]\n│\n│\n1\n│\n2\n│\n2\n│\n[\n1\n,\n2\n]\n│\n[\n1\n,\n2\n]\n│\n│\n1\n│\n3\n│\n3\n│\n[\n1\n,\n2\n,\n3\n]\n│\n[\n1\n,\n2\n,\n3\n]\n│\n│\n1\n│\n4\n│\n4\n│\n[\n1\n,\n2\n,\n3\n,\n4\n]\n│\n[\n1\n,\n2\n,\n3\n,\n4\n]\n│\n│\n1\n│\n5\n│\n5\n│\n[\n1\n,\n2\n,\n3\n,\n4\n,\n5\n]\n│\n[\n1\n,\n2\n,\n3\n,\n4\n,\n5\n]\n│\n└──────────┴───────┴───────┴────────────────────┴──────────────┘\n-- frame is bounded by the beginning of a partition and the current row, but order is backward\nSELECT\npart_key\n,\nvalue\n,\norder\n,\ngroupArray\n(\nvalue\n)\nOVER\n(\nPARTITION\nBY\npart_key\nORDER\nBY\norder\nDESC\n)\nAS\nframe_values\nFROM\nwf_frame\nORDER\nBY\npart_key\nASC\n,\nvalue\nASC\n;\n┌─part_key─┬─\nvalue\n─┬─\norder\n─┬─frame_values─┐\n│\n1\n│\n1\n│\n1\n│\n[\n5\n,\n4\n,\n3\n,\n2\n,\n1\n]\n│\n│\n1\n│\n2\n│\n2\n│\n[\n5\n,\n4\n,\n3\n,\n2\n]\n│\n│\n1\n│\n3\n│\n3\n│\n[\n5\n,\n4\n,\n3\n]\n│\n│\n1\n│\n4\n│\n4\n│\n[\n5\n,\n4\n]\n│\n│\n1\n│\n5\n│\n5\n│\n[\n5\n]\n│\n└──────────┴───────┴───────┴──────────────┘\n-- sliding frame - 1 PRECEDING ROW AND CURRENT ROW\nSELECT\npart_key\n,\nvalue\n,\norder\n,\ngroupArray\n(\nvalue\n)\nOVER\n(\nPARTITION\nBY\npart_key\nORDER\nBY\norder\nASC\nROWS\nBETWEEN\n1\nPRECEDING\nAND\nCURRENT\nROW\n)\nAS\nframe_values\nFROM\nwf_frame\nORDER\nBY\npart_key\nASC\n,\nvalue\nASC\n;\n┌─part_key─┬─\nvalue\n─┬─\norder\n─┬─frame_values─┐\n│\n1\n│\n1\n│\n1\n│\n[\n1\n]\n│\n│\n1\n│\n2\n│\n2\n│\n[\n1\n,\n2\n]\n│\n│\n1\n│\n3\n│\n3\n│\n[\n2\n,\n3\n]\n│\n│\n1\n│\n4\n│\n4\n│\n[\n3\n,\n4\n]\n│\n│\n1\n│\n5\n│\n5\n│\n[\n4\n,\n5\n]\n│\n└──────────┴───────┴───────┴──────────────┘\n-- sliding frame - ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING\nSELECT\npart_key\n,\nvalue\n,\norder\n,\ngroupArray\n(\nvalue\n)\nOVER\n(\nPARTITION\nBY\npart_key\nORDER\nBY\norder\nASC\nROWS\nBETWEEN\n1\nPRECEDING\nAND\nUNBOUNDED\nFOLLOWING\n)\nAS\nframe_values\nFROM\nwf_frame\nORDER\nBY\npart_key\nASC\n,\nvalue\nASC\n;\n┌─part_key─┬─\nvalue\n─┬─\norder\n─┬─frame_values─┐\n│\n1\n│\n1\n│\n1\n│\n[\n1\n,\n2\n,\n3\n,\n4\n,\n5\n]\n│\n│\n1\n│\n2\n│\n2\n│\n[\n1\n,\n2\n,\n3\n,\n4\n,\n5\n]\n│\n│\n1\n│\n3\n│\n3\n│\n[\n2\n,\n3\n,\n4\n,\n5\n]\n│\n│\n1\n│\n4\n│\n4\n│\n[\n3\n,\n4\n,\n5\n]\n│\n│\n1\n│\n5\n│\n5\n│\n[\n4\n,\n5\n]\n│\n└──────────┴───────┴───────┴──────────────┘\n-- row_number does not respect the frame, so rn_1 = rn_2 = rn_3 != rn_4\nSELECT\npart_key\n,\nvalue\n,\norder\n,\ngroupArray\n(\nvalue\n)\nOVER\nw1\nAS\nframe_values\n,\nrow_number\n(\n)\nOVER\nw1\nAS\nrn_1\n,\nsum\n(\n1\n)\nOVER\nw1\nAS\nrn_2\n,\nrow_number\n(\n)\nOVER\nw2\nAS\nrn_3\n,\nsum\n(\n1\n)\nOVER\nw2\nAS\nrn_4\nFROM\nwf_frame\nWINDOW\nw1\nAS\n(\nPARTITION\nBY\npart_key\nORDER\nBY\norder\nDESC\n)\n,\nw2\nAS\n(\nPARTITION\nBY\npart_key\nORDER\nBY\norder\nDESC\nROWS\nBETWEEN\n1\nPRECEDING\nAND\nCURRENT\nROW\n)\nORDER\nBY\npart_key\nASC\n,\nvalue\nASC\n;\n┌─part_key─┬─\nvalue\n─┬─\norder\n─┬─frame_values─┬─rn_1─┬─rn_2─┬─rn_3─┬─rn_4─┐\n│\n1\n│\n1\n│\n1\n│\n[\n5\n,\n4\n,\n3\n,\n2\n,\n1\n]\n│\n5\n│\n5\n│\n5\n│\n2\n│\n│\n1\n│\n2\n│\n2\n│\n[\n5\n,\n4\n,\n3\n,\n2\n]\n│\n4\n│\n4\n│\n4\n│\n2\n│\n│\n1\n│\n3\n│\n3\n│\n[\n5\n,\n4\n,\n3\n]\n│\n3\n│\n3\n│\n3\n│\n2\n│\n│\n1\n│\n4\n│\n4\n│\n[\n5\n,\n4\n]\n│\n2\n│\n2\n│\n2\n│\n2\n│\n│\n1\n│\n5\n│\n5\n│\n[\n5\n]\n│\n1\n│\n1\n│\n1\n│\n1\n│\n└──────────┴───────┴───────┴──────────────┴──────┴──────┴──────┴──────┘\n-- first_value and last_value respect the frame\nSELECT\ngroupArray\n(\nvalue\n)\nOVER\nw1\nAS\nframe_values_1\n,\nfirst_value\n(\nvalue\n)\nOVER\nw1\nAS\nfirst_value_1\n,\nlast_value\n(\nvalue\n)\nOVER\nw1\nAS\nlast_value_1\n,\ngroupArray\n(\nvalue\n)\nOVER\nw2\nAS\nframe_values_2\n,\nfirst_value\n(\nvalue\n)\nOVER\nw2\nAS\nfirst_value_2\n,\nlast_value\n(\nvalue\n)\nOVER\nw2\nAS\nlast_value_2\nFROM\nwf_frame\nWINDOW\nw1\nAS\n(\nPARTITION\nBY\npart_key\nORDER\nBY\norder\nASC\n)\n,\nw2\nAS\n(\nPARTITION\nBY\npart_key\nORDER\nBY\norder\nASC\nROWS\nBETWEEN\n1\nPRECEDING\nAND\nCURRENT\nROW\n)\nORDER\nBY\npart_key\nASC\n,\nvalue\nASC\n;\n┌─frame_values_1─┬─first_value_1─┬─last_value_1─┬─frame_values_2─┬─first_value_2─┬─last_value_2─┐\n│\n[\n1\n]\n│\n1\n│\n1\n│\n[\n1\n]\n│\n1\n│\n1\n│\n│\n[\n1\n,\n2\n]\n│\n1\n│\n2\n│\n[\n1\n,\n2\n]\n│\n1\n│\n2\n│\n│\n[\n1\n,\n2\n,\n3\n]\n│\n1\n│\n3\n│\n[\n2\n,\n3\n]\n│\n2\n│\n3\n│\n│\n[\n1\n,\n2\n,\n3\n,\n4\n]\n│\n1\n│\n4\n│\n[\n3\n,\n4\n]\n│\n3\n│\n4\n│\n│\n[\n1\n,\n2\n,\n3\n,\n4\n,\n5\n]\n│\n1\n│\n5\n│\n[\n4\n,\n5\n]\n│\n4\n│\n5\n│\n└────────────────┴───────────────┴──────────────┴────────────────┴───────────────┴──────────────┘\n-- second value within the frame\nSELECT\ngroupArray\n(\nvalue\n)\nOVER\nw1\nAS\nframe_values_1\n,\nnth_value\n(\nvalue\n,\n2\n)\nOVER\nw1\nAS\nsecond_value\nFROM\nwf_frame\nWINDOW w1\nAS\n(\nPARTITION\nBY\npart_key\nORDER\nBY\norder\nASC\nROWS\nBETWEEN\n3\nPRECEDING\nAND\nCURRENT\nROW\n)\nORDER\nBY\npart_key\nASC\n,\nvalue\nASC\n;\n┌─frame_values_1─┬─second_value─┐\n│\n[\n1\n]\n│\n0\n│\n│\n[\n1\n,\n2\n]\n│\n2\n│\n│\n[\n1\n,\n2\n,\n3\n]\n│\n2\n│\n│\n[\n1\n,\n2\n,\n3\n,\n4\n]\n│\n2\n│\n│\n[\n2\n,\n3\n,\n4\n,\n5\n]\n│\n3\n│\n└────────────────┴──────────────┘\n-- second value within the frame + Null for missing values\nSELECT\ngroupArray\n(\nvalue\n)\nOVER\nw1\nAS\nframe_values_1\n,\nnth_value\n(\ntoNullable\n(\nvalue\n)\n,\n2\n)\nOVER\nw1\nAS\nsecond_value\nFROM\nwf_frame\nWINDOW w1\nAS\n(\nPARTITION\nBY\npart_key\nORDER\nBY\norder\nASC\nROWS\nBETWEEN\n3\nPRECEDING\nAND\nCURRENT\nROW\n)\nORDER\nBY\npart_key\nASC\n,\nvalue\nASC\n;\n┌─frame_values_1─┬─second_value─┐\n│\n[\n1\n]\n│         ᴺᵁᴸᴸ │\n│\n[\n1\n,\n2\n]\n│\n2\n│\n│\n[\n1\n,\n2\n,\n3\n]\n│\n2\n│\n│\n[\n1\n,\n2\n,\n3\n,\n4\n]\n│\n2\n│\n│\n[\n2\n,\n3\n,\n4\n,\n5\n]\n│\n3\n│\n└────────────────┴──────────────┘\nReal world examples\n\nThe following examples solve common real-world problems.\nMaximum\/total salary per department\n\nCREATE\nTABLE\nemployees\n(\n`\ndepartment\n`\nString\n,\n`\nemployee_name\n`\nString\n,\n`\nsalary\n`\nFloat\n)\nENGINE\n=\nMemory\n;\nINSERT\nINTO\nemployees FORMAT\nValues\n(\n'Finance'\n,\n'Jonh'\n,\n200\n)\n,\n(\n'Finance'\n,\n'Joan'\n,\n210\n)\n,\n(\n'Finance'\n,\n'Jean'\n,\n505\n)\n,\n(\n'IT'\n,\n'Tim'\n,\n200\n)\n,\n(\n'IT'\n,\n'Anna'\n,\n300\n)\n,\n(\n'IT'\n,\n'Elen'\n,\n500\n)\n;\nSELECT\ndepartment\n,\nemployee_name\nAS\nemp\n,\nsalary\n,\nmax_salary_per_dep\n,\ntotal_salary_per_dep\n,\nround\n(\n(\nsalary\n\/\ntotal_salary_per_dep\n)\n*\n100\n,\n2\n)\nAS\n`\nshare_per_dep(%)\n`\nFROM\n(\nSELECT\ndepartment\n,\nemployee_name\n,\nsalary\n,\nmax\n(\nsalary\n)\nOVER\nwndw\nAS\nmax_salary_per_dep\n,\nsum\n(\nsalary\n)\nOVER\nwndw\nAS\ntotal_salary_per_dep\nFROM\nemployees\nWINDOW wndw\nAS\n(\nPARTITION\nBY\ndepartment\nROWS\nBETWEEN\nUNBOUNDED\nPRECEDING\nAND\nUNBOUNDED\nFOLLOWING\n)\nORDER\nBY\ndepartment\nASC\n,\nemployee_name\nASC\n)\n;\n┌─department─┬─emp──┬─salary─┬─max_salary_per_dep─┬─total_salary_per_dep─┬─share_per_dep\n(\n%\n)\n─┐\n│ Finance    │ Jean │\n505\n│\n505\n│\n915\n│\n55.19\n│\n│ Finance    │ Joan │\n210\n│\n505\n│\n915\n│\n22.95\n│\n│ Finance    │ Jonh │\n200\n│\n505\n│\n915\n│\n21.86\n│\n│ IT         │ Anna │\n300\n│\n500\n│\n1000\n│\n30\n│\n│ IT         │ Elen │\n500\n│\n500\n│\n1000\n│\n50\n│\n│ IT         │ Tim  │\n200\n│\n500\n│\n1000\n│\n20\n│\n└────────────┴──────┴────────┴────────────────────┴──────────────────────┴──────────────────┘\nCumulative sum\n\nCREATE\nTABLE\nwarehouse\n(\n`\nitem\n`\nString\n,\n`\nts\n`\nDateTime\n,\n`\nvalue\n`\nFloat\n)\nENGINE\n=\nMemory\nINSERT\nINTO\nwarehouse\nVALUES\n(\n'sku38'\n,\n'2020-01-01'\n,\n9\n)\n,\n(\n'sku38'\n,\n'2020-02-01'\n,\n1\n)\n,\n(\n'sku38'\n,\n'2020-03-01'\n,\n-\n4\n)\n,\n(\n'sku1'\n,\n'2020-01-01'\n,\n1\n)\n,\n(\n'sku1'\n,\n'2020-02-01'\n,\n1\n)\n,\n(\n'sku1'\n,\n'2020-03-01'\n,\n1\n)\n;\nSELECT\n    item,\n    ts,\n    value,\n    sum(value) OVER (PARTITION BY item ORDER BY ts ASC) AS stock_balance\nFROM warehouse\nORDER BY\n    item ASC,\n    ts ASC;\n\n┌─item──┬──────────────────ts─┬─value─┬─stock_balance─┐\n│ sku1  │ 2020-01-01 00:00:00 │     1 │             1 │\n│ sku1  │ 2020-02-01 00:00:00 │     1 │             2 │\n│ sku1  │ 2020-03-01 00:00:00 │     1 │             3 │\n│ sku38 │ 2020-01-01 00:00:00 │     9 │             9 │\n│ sku38 │ 2020-02-01 00:00:00 │     1 │            10 │\n│ sku38 │ 2020-03-01 00:00:00 │    -4 │             6 │\n└───────┴─────────────────────┴───────┴───────────────┘\nMoving \/ sliding average (per 3 rows)\n\nCREATE TABLE sensors\n(\n    `metric` String,\n    `ts` DateTime,\n    `value` Float\n)\nENGINE = Memory;\n\ninsert into sensors values('cpu_temp', '2020-01-01 00:00:00', 87),\n                          ('cpu_temp', '2020-01-01 00:00:01', 77),\n                          ('cpu_temp', '2020-01-01 00:00:02', 93),\n                          ('cpu_temp', '2020-01-01 00:00:03', 87),\n                          ('cpu_temp', '2020-01-01 00:00:04', 87),\n                          ('cpu_temp', '2020-01-01 00:00:05', 87),\n                          ('cpu_temp', '2020-01-01 00:00:06', 87),\n                          ('cpu_temp', '2020-01-01 00:00:07', 87);\nSELECT\n    metric,\n    ts,\n    value,\n    avg(value) OVER (\n        PARTITION BY metric \n        ORDER BY ts ASC \n        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW\n    ) AS moving_avg_temp\nFROM sensors\nORDER BY\n    metric ASC,\n    ts ASC;\n\n┌─metric───┬──────────────────ts─┬─value─┬───moving_avg_temp─┐\n│ cpu_temp │ 2020-01-01 00:00:00 │    87 │                87 │\n│ cpu_temp │ 2020-01-01 00:00:01 │    77 │                82 │\n│ cpu_temp │ 2020-01-01 00:00:02 │    93 │ 85.66666666666667 │\n│ cpu_temp │ 2020-01-01 00:00:03 │    87 │ 85.66666666666667 │\n│ cpu_temp │ 2020-01-01 00:00:04 │    87 │                89 │\n│ cpu_temp │ 2020-01-01 00:00:05 │    87 │                87 │\n│ cpu_temp │ 2020-01-01 00:00:06 │    87 │                87 │\n│ cpu_temp │ 2020-01-01 00:00:07 │    87 │                87 │\n└──────────┴─────────────────────┴───────┴───────────────────┘\nMoving \/ sliding average (per 10 seconds)\n\nSELECT\n    metric,\n    ts,\n    value,\n    avg(value) OVER (PARTITION BY metric ORDER BY ts\n      RANGE BETWEEN 10 PRECEDING AND CURRENT ROW) AS moving_avg_10_seconds_temp\nFROM sensors\nORDER BY\n    metric ASC,\n    ts ASC;\n    \n┌─metric───┬──────────────────ts─┬─value─┬─moving_avg_10_seconds_temp─┐\n│ cpu_temp │ 2020-01-01 00:00:00 │    87 │                         87 │\n│ cpu_temp │ 2020-01-01 00:01:10 │    77 │                         77 │\n│ cpu_temp │ 2020-01-01 00:02:20 │    93 │                         93 │\n│ cpu_temp │ 2020-01-01 00:03:30 │    87 │                         87 │\n│ cpu_temp │ 2020-01-01 00:04:40 │    87 │                         87 │\n│ cpu_temp │ 2020-01-01 00:05:50 │    87 │                         87 │\n│ cpu_temp │ 2020-01-01 00:06:00 │    87 │                         87 │\n│ cpu_temp │ 2020-01-01 00:07:10 │    87 │                         87 │\n└──────────┴─────────────────────┴───────┴────────────────────────────┘\nMoving \/ sliding average (per 10 days)\n\nTemperature is stored with second precision, but using\nRange\nand\nORDER BY toDate(ts)\nwe form a frame with the size of 10 units, and because of\ntoDate(ts)\nthe unit is a day.\nCREATE TABLE sensors\n(\n    `metric` String,\n    `ts` DateTime,\n    `value` Float\n)\nENGINE = Memory;\n\ninsert into sensors values('ambient_temp', '2020-01-01 00:00:00', 16),\n                          ('ambient_temp', '2020-01-01 12:00:00', 16),\n                          ('ambient_temp', '2020-01-02 11:00:00', 9),\n                          ('ambient_temp', '2020-01-02 12:00:00', 9),                          \n                          ('ambient_temp', '2020-02-01 10:00:00', 10),\n                          ('ambient_temp', '2020-02-01 12:00:00', 10),\n                          ('ambient_temp', '2020-02-10 12:00:00', 12),                          \n                          ('ambient_temp', '2020-02-10 13:00:00', 12),\n                          ('ambient_temp', '2020-02-20 12:00:01', 16),\n                          ('ambient_temp', '2020-03-01 12:00:00', 16),\n                          ('ambient_temp', '2020-03-01 12:00:00', 16),\n                          ('ambient_temp', '2020-03-01 12:00:00', 16);\nSELECT\n    metric,\n    ts,\n    value,\n    round(avg(value) OVER (PARTITION BY metric ORDER BY toDate(ts) \n       RANGE BETWEEN 10 PRECEDING AND CURRENT ROW),2) AS moving_avg_10_days_temp\nFROM sensors\nORDER BY\n    metric ASC,\n    ts ASC;\n\n┌─metric───────┬──────────────────ts─┬─value─┬─moving_avg_10_days_temp─┐\n│ ambient_temp │ 2020-01-01 00:00:00 │    16 │                      16 │\n│ ambient_temp │ 2020-01-01 12:00:00 │    16 │                      16 │\n│ ambient_temp │ 2020-01-02 11:00:00 │     9 │                    12.5 │\n│ ambient_temp │ 2020-01-02 12:00:00 │     9 │                    12.5 │\n│ ambient_temp │ 2020-02-01 10:00:00 │    10 │                      10 │\n│ ambient_temp │ 2020-02-01 12:00:00 │    10 │                      10 │\n│ ambient_temp │ 2020-02-10 12:00:00 │    12 │                      11 │\n│ ambient_temp │ 2020-02-10 13:00:00 │    12 │                      11 │\n│ ambient_temp │ 2020-02-20 12:00:01 │    16 │                   13.33 │\n│ ambient_temp │ 2020-03-01 12:00:00 │    16 │                      16 │\n│ ambient_temp │ 2020-03-01 12:00:00 │    16 │                      16 │\n│ ambient_temp │ 2020-03-01 12:00:00 │    16 │                      16 │\n└──────────────┴─────────────────────┴───────┴─────────────────────────┘\nReferences\n\nGitHub Issues\n\nThe roadmap for the initial support of window functions is\nin this issue\n.\nAll GitHub issues related to window functions have the\ncomp-window-functions\ntag.\nTests\n\nThese tests contain the examples of the currently supported grammar:\nhttps:\/\/github.com\/ClickHouse\/ClickHouse\/blob\/master\/tests\/performance\/window_functions.xml\nhttps:\/\/github.com\/ClickHouse\/ClickHouse\/blob\/master\/tests\/queries\/0_stateless\/01591_window_functions.sql\nPostgres Docs\n\nhttps:\/\/www.postgresql.org\/docs\/current\/sql-select.html#SQL-WINDOW\nhttps:\/\/www.postgresql.org\/docs\/devel\/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS\nhttps:\/\/www.postgresql.org\/docs\/devel\/functions-window.html\nhttps:\/\/www.postgresql.org\/docs\/devel\/tutorial-window.html\nMySQL Docs\n\nhttps:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/window-function-descriptions.html\nhttps:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/window-functions-usage.html\nhttps:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/window-functions-frames.html\nRelated Content\n\nBlog:\nWorking with time series data in ClickHouse\nBlog:\nWindow and array functions for Git commit sequences\nBlog:\nGetting Data Into ClickHouse - Part 3 - Using S3","attrs_markdown":"[Skip to main content](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#__docusaurus_skipToContent_fallback)\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\n- [Products](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions)\n  \n  - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https:\/\/clickhouse.com\/cloud)\n  - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https:\/\/clickhouse.com\/cloud\/bring-your-own-cloud)\n  - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https:\/\/clickhouse.com\/clickhouse)\n  - [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n  \n  [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n- [Use cases](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions)\n  \n  - [Real-time analytics](https:\/\/clickhouse.com\/use-cases\/real-time-analytics)\n  - [Machine Learning & Generative AI](https:\/\/clickhouse.com\/use-cases\/machine-learning-and-data-science)\n  - [Business Intelligence](https:\/\/clickhouse.com\/use-cases\/data-warehousing)\n  - [Logs, Events, Traces](https:\/\/clickhouse.com\/use-cases\/observability)\n  - [All use cases](https:\/\/clickhouse.com\/use-cases)\n  \n  [All use cases](https:\/\/clickhouse.com\/use-cases)\n- [Documentation](https:\/\/clickhouse.com\/docs)\n- [Resources](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions)\n  \n  - [User stories](https:\/\/clickhouse.com\/user-stories)\n  - [Blog](https:\/\/clickhouse.com\/blog)\n  - [Events](https:\/\/clickhouse.com\/company\/events)\n  - [Learning and certification](https:\/\/clickhouse.com\/learn)\n  - [Comparison](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions)\n    - [BigQuery](https:\/\/clickhouse.com\/comparison\/bigquery)\n    - [PostgreSQL](https:\/\/clickhouse.com\/comparison\/postgresql)\n    - [Redshift](https:\/\/clickhouse.com\/comparison\/redshift)\n    - [Rockset](https:\/\/clickhouse.com\/comparison\/rockset)\n    - [Snowflake](https:\/\/clickhouse.com\/comparison\/snowflake)\n  - [Video](https:\/\/clickhouse.com\/videos)\n  - [Demo](https:\/\/clickhouse.com\/demos)\n- [Pricing](https:\/\/clickhouse.com\/pricing)\n- [Contact](https:\/\/clickhouse.com\/company\/contact?loc=nav)\n\n[46\\.8k](https:\/\/github.com\/ClickHouse\/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n[Sign in](https:\/\/console.clickhouse.cloud\/signIn?loc=docs-nav-signIn-cta&glxid=ec987698-9313-4186-9781-be5fd1b64e13&pagePath=%2Fdocs%2Fsql-reference%2Fwindow-functions&origPath=%2Fdocs%2Fsql-reference%2Fwindow-functions&utm_ga=GA1.1.1971283203.1775876958)\n\n[Get started](https:\/\/console.clickhouse.cloud\/signUp?loc=docs-nav-signUp-cta&glxid=ec987698-9313-4186-9781-be5fd1b64e13&pagePath=%2Fdocs%2Fsql-reference%2Fwindow-functions&origPath=%2Fdocs%2Fsql-reference%2Fwindow-functions&utm_ga=GA1.1.1971283203.1775876958)\n\n[Get started](https:\/\/clickhouse.com\/docs\/introduction-clickhouse)\n\n[Cloud](https:\/\/clickhouse.com\/docs\/cloud\/overview)\n\n[Manage data](https:\/\/clickhouse.com\/docs\/updating-data)\n\n[Server admin](https:\/\/clickhouse.com\/docs\/guides\/manage-and-deploy-index)\n\n[Reference](https:\/\/clickhouse.com\/docs\/sql-reference)\n\n[Integrations](https:\/\/clickhouse.com\/docs\/integrations)\n\n[ClickStack](https:\/\/clickhouse.com\/docs\/use-cases\/observability\/clickstack\/overview)\n\n[chDB](https:\/\/clickhouse.com\/docs\/chdb)\n\n[About](https:\/\/clickhouse.com\/docs\/about)\n\n[Knowledge Base](https:\/\/clickhouse.com\/docs\/knowledgebase)\n\n[English](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions)\n\n- [English](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions)\n- [日本語](https:\/\/clickhouse.com\/docs\/jp\/sql-reference\/window-functions)\n- [中文](https:\/\/clickhouse.com\/docs\/zh\/sql-reference\/window-functions)\n- [Русский](https:\/\/clickhouse.com\/docs\/ru\/sql-reference\/window-functions)\n- [한국어](https:\/\/clickhouse.com\/docs\/ko\/sql-reference\/window-functions)\n\n[Skip to main content](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#__docusaurus_skipToContent_fallback)\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\n- [Products](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions)\n  \n  - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https:\/\/clickhouse.com\/cloud)\n  - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https:\/\/clickhouse.com\/cloud\/bring-your-own-cloud)\n  - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https:\/\/clickhouse.com\/clickhouse)\n  - [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n  \n  [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n- [Use cases](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions)\n  \n  - [Real-time analytics](https:\/\/clickhouse.com\/use-cases\/real-time-analytics)\n  - [Machine Learning & Generative AI](https:\/\/clickhouse.com\/use-cases\/machine-learning-and-data-science)\n  - [Business Intelligence](https:\/\/clickhouse.com\/use-cases\/data-warehousing)\n  - [Logs, Events, Traces](https:\/\/clickhouse.com\/use-cases\/observability)\n  - [All use cases](https:\/\/clickhouse.com\/use-cases)\n  \n  [All use cases](https:\/\/clickhouse.com\/use-cases)\n- [Documentation](https:\/\/clickhouse.com\/docs)\n- [Resources](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions)\n  \n  - [User stories](https:\/\/clickhouse.com\/user-stories)\n  - [Blog](https:\/\/clickhouse.com\/blog)\n  - [Events](https:\/\/clickhouse.com\/company\/events)\n  - [Learning and certification](https:\/\/clickhouse.com\/learn)\n  - [Comparison](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions)\n    - [BigQuery](https:\/\/clickhouse.com\/comparison\/bigquery)\n    - [PostgreSQL](https:\/\/clickhouse.com\/comparison\/postgresql)\n    - [Redshift](https:\/\/clickhouse.com\/comparison\/redshift)\n    - [Rockset](https:\/\/clickhouse.com\/comparison\/rockset)\n    - [Snowflake](https:\/\/clickhouse.com\/comparison\/snowflake)\n  - [Video](https:\/\/clickhouse.com\/videos)\n  - [Demo](https:\/\/clickhouse.com\/demos)\n- [Pricing](https:\/\/clickhouse.com\/pricing)\n- [Contact](https:\/\/clickhouse.com\/company\/contact?loc=nav)\n\n[46\\.8k](https:\/\/github.com\/ClickHouse\/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n[Sign in](https:\/\/console.clickhouse.cloud\/signIn?loc=docs-nav-signIn-cta&glxid=ec987698-9313-4186-9781-be5fd1b64e13&pagePath=%2Fdocs%2Fsql-reference%2Fwindow-functions&origPath=%2Fdocs%2Fsql-reference%2Fwindow-functions&utm_ga=GA1.1.1971283203.1775876958)\n\n[Get started](https:\/\/console.clickhouse.cloud\/signUp?loc=docs-nav-signUp-cta&glxid=ec987698-9313-4186-9781-be5fd1b64e13&pagePath=%2Fdocs%2Fsql-reference%2Fwindow-functions&origPath=%2Fdocs%2Fsql-reference%2Fwindow-functions&utm_ga=GA1.1.1971283203.1775876958)\n\n[Get started](https:\/\/clickhouse.com\/docs\/introduction-clickhouse)\n\n[Cloud](https:\/\/clickhouse.com\/docs\/cloud\/overview)\n\n[Manage data](https:\/\/clickhouse.com\/docs\/updating-data)\n\n[Server admin](https:\/\/clickhouse.com\/docs\/guides\/manage-and-deploy-index)\n\n[Reference](https:\/\/clickhouse.com\/docs\/sql-reference)\n\n[Integrations](https:\/\/clickhouse.com\/docs\/integrations)\n\n[ClickStack](https:\/\/clickhouse.com\/docs\/use-cases\/observability\/clickstack\/overview)\n\n[chDB](https:\/\/clickhouse.com\/docs\/chdb)\n\n[About](https:\/\/clickhouse.com\/docs\/about)\n\n[Knowledge Base](https:\/\/clickhouse.com\/docs\/knowledgebase)\n\n[English](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions)\n\n- [English](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions)\n- [日本語](https:\/\/clickhouse.com\/docs\/jp\/sql-reference\/window-functions)\n- [中文](https:\/\/clickhouse.com\/docs\/zh\/sql-reference\/window-functions)\n- [Русский](https:\/\/clickhouse.com\/docs\/ru\/sql-reference\/window-functions)\n- [한국어](https:\/\/clickhouse.com\/docs\/ko\/sql-reference\/window-functions)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n- [Introduction](https:\/\/clickhouse.com\/docs\/sql-reference)\n  - [Syntax](https:\/\/clickhouse.com\/docs\/sql-reference\/syntax)\n  - [Input and Output Formats](https:\/\/clickhouse.com\/docs\/sql-reference\/formats)\n  - [Data types](https:\/\/clickhouse.com\/docs\/sql-reference\/data-types)\n  - [Statements](https:\/\/clickhouse.com\/docs\/sql-reference\/statements)\n  - [Operators](https:\/\/clickhouse.com\/docs\/sql-reference\/operators)\n- [Engines](https:\/\/clickhouse.com\/docs\/engines)\n  - [Database Engines](https:\/\/clickhouse.com\/docs\/engines\/database-engines)\n  - [Table Engines](https:\/\/clickhouse.com\/docs\/engines\/table-engines)\n- [Functions](https:\/\/clickhouse.com\/docs\/sql-reference\/functions)\n  - [Regular functions](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/regular-functions)\n  - [Aggregate functions](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions)\n  - [Table functions](https:\/\/clickhouse.com\/docs\/sql-reference\/table-functions)\n  - [Window functions](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions)\n    - [row\\_number](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/row_number)\n    - [first\\_value](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/first_value)\n    - [last\\_value](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/last_value)\n    - [nth\\_value](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/nth_value)\n    - [rank](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/rank)\n    - [dense\\_rank](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/dense_rank)\n    - [percent\\_rank](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/percent_rank)\n    - [lag](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/lag)\n    - [lagInFrame](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/lagInFrame)\n    - [lead](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/lead)\n    - [leadInFrame](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/leadInFrame)\n    - [cume\\_dist](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/cume_dist)\n- [Formats](https:\/\/clickhouse.com\/docs\/interfaces\/formats)\n- [Data Lakes](https:\/\/clickhouse.com\/docs\/sql-reference\/datalakes)\n\n- [Functions](https:\/\/clickhouse.com\/docs\/sql-reference\/functions)\n- Window functions\n\n[Edit this page](https:\/\/github.com\/ClickHouse\/ClickHouse\/tree\/master\/docs\/en\/sql-reference\/window-functions\/index.md)\n\n# Window functions\nWindow functions let you perform calculations across a set of rows that are related to the current row. Some of the calculations that you can do are similar to those that can be done with an aggregate function, but a window function doesn't cause rows to be grouped into a single output - the individual rows are still returned.\n\n## Standard window functions[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#standard-window-functions \"Direct link to Standard window functions\")\nClickHouse supports the standard grammar for defining windows and window functions. The table below indicates whether a feature is currently supported.\n\n| Feature | Supported? |\n|---|---|\n| ad hoc window specification (`count(*) over (partition by id order by time desc)`) | ✅ |\n| expressions involving window functions, e.g. `(count(*) over ()) \/ 2)` | ✅ |\n| `WINDOW` clause (`select ... from table window w as (partition by id)`) | ✅ |\n| `ROWS` frame | ✅ |\n| `RANGE` frame | ✅ (the default) |\n| `INTERVAL` syntax for `DateTime` `RANGE OFFSET` frame | ❌ (specify the number of seconds instead (`RANGE` works with any numeric type).) |\n| `GROUPS` frame | ❌ |\n| Calculating aggregate functions over a frame (`sum(value) over (order by time)`) | ✅ (All aggregate functions are supported) |\n| `rank()`, `dense_rank()`, `row_number()` | ✅  Alias: `denseRank()` |\n| `percent_rank()` | ✅ Efficiently computes the relative standing of a value within a partition in a dataset. This function effectively replaces the more verbose and computationally intensive manual SQL calculation expressed as `ifNull((rank() OVER(PARTITION BY x ORDER BY y) - 1) \/ nullif(count(1) OVER(PARTITION BY x) - 1, 0), 0)`  Alias: `percentRank()` |\n| `cume_dist()` | ✅ Computes the cumulative distribution of a value within a group of values. Returns the percentage of rows with values less than or equal to the current row's value. |\n| `lag\/lead(value, offset)` | ✅   You can also use one of the following workarounds:  1) `any(value) over (.... rows between <offset> preceding and <offset> preceding)`, or `following` for `lead`   2) `lagInFrame\/leadInFrame`, which are analogous, but respect the window frame. To get behavior identical to `lag\/lead`, use `rows between unbounded preceding and unbounded following` |\n| ntile(buckets) | ✅   Specify window like, (partition by x order by y rows between unbounded preceding and unbounded following). |\n## ClickHouse-specific window functions[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#clickhouse-specific-window-functions \"Direct link to ClickHouse-specific window functions\")\nThere is also the following ClickHouse specific window function:\n\n### nonNegativeDerivative(metric\\_column, timestamp\\_column\\[, INTERVAL X UNITS\\])[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#nonnegativederivativemetric_column-timestamp_column-interval-x-units \"Direct link to nonNegativeDerivative(metric_column, timestamp_column[, INTERVAL X UNITS])\")\nFinds non-negative derivative for given `metric_column` by `timestamp_column`. `INTERVAL` can be omitted, default is `INTERVAL 1 SECOND`. The computed value is the following for each row:\n\n- `0` for 1st row,\n- metric\n  \n  i\n  \n  −\n  \n  metric\n  \n  i\n  \n  −\n  \n  1\n  \n  timestamp\n  \n  i\n  \n  −\n  \n  timestamp\n  \n  i\n  \n  −\n  \n  1\n  \n  ∗\n  \n  interval\n  \n  {\\\\text{metric}\\_i - \\\\text{metric}\\_{i-1} \\\\over \\\\text{timestamp}\\_i - \\\\text{timestamp}\\_{i-1}} \\* \\\\text{interval}\n  \n  timestampi−timestampi−1metrici−metrici−1∗interval\n  for\n  i\n  \n  t\n  \n  h\n  \n  i\\_{th}\n  \n  ith\n  row.\n## Syntax[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#syntax \"Direct link to Syntax\")\n```\naggregate_function (column_name)\n  OVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column] \n        [ROWS or RANGE expression_to_bound_rows_within_the_group]] | [window_name])\nFROM table_name\nWINDOW window_name as ([\n  [PARTITION BY grouping_column]\n  [ORDER BY sorting_column]\n  [ROWS or RANGE expression_to_bound_rows_within_the_group]\n])\n```\n- `PARTITION BY` - defines how to break a resultset into groups.\n- `ORDER BY` - defines how to order rows inside the group during calculation aggregate\\_function.\n- `ROWS or RANGE` - defines bounds of a frame, aggregate\\_function is calculated within a frame.\n- `WINDOW` - allows multiple expressions to use the same window definition.\n```\n      PARTITION\n┌─────────────────┐  <-- UNBOUNDED PRECEDING (BEGINNING of the PARTITION)\n│                 │\n│                 │\n│=================│  <-- N PRECEDING  <─┐\n│      N ROWS     │                     │  F\n│  Before CURRENT │                     │  R\n│~~~~~~~~~~~~~~~~~│  <-- CURRENT ROW    │  A\n│     M ROWS      │                     │  M\n│   After CURRENT │                     │  E\n│=================│  <-- M FOLLOWING  <─┘\n│                 │\n│                 │\n└─────────────────┘  <--- UNBOUNDED FOLLOWING (END of the PARTITION)\n```\n### Functions[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#functions \"Direct link to Functions\")\nThese functions can be used only as a window function.\n\n- [`row_number()`](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/row_number) - Number the current row within its partition starting from 1.\n- [`first_value(x)`](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/first_value) - Return the first value evaluated within its ordered frame.\n- [`last_value(x)`](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/last_value) - Return the last value evaluated within its ordered frame.\n- [`nth_value(x, offset)`](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/nth_value) - Return the first non-NULL value evaluated against the nth row (offset) in its ordered frame.\n- [`rank()`](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/rank) - Rank the current row within its partition with gaps.\n- [`dense_rank()`](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/dense_rank) - Rank the current row within its partition without gaps.\n- [`lagInFrame(x)`](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/lagInFrame) - Return a value evaluated at the row that is at a specified physical offset row before the current row within the ordered frame.\n- [`leadInFrame(x)`](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/leadInFrame) - Return a value evaluated at the row that is offset rows after the current row within the ordered frame.\n## Examples[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#examples \"Direct link to Examples\")\nLet's have a look at some examples of how window functions can be used.\n\n### Numbering rows[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#numbering-rows \"Direct link to Numbering rows\")\n```\nCREATE TABLE salaries\n(\n    `team` String,\n    `player` String,\n    `salary` UInt32,\n    `position` String\n)\nEngine = Memory;\n\nINSERT INTO salaries FORMAT Values\n    ('Port Elizabeth Barbarians', 'Gary Chen', 195000, 'F'),\n    ('New Coreystad Archdukes', 'Charles Juarez', 190000, 'F'),\n    ('Port Elizabeth Barbarians', 'Michael Stanley', 150000, 'D'),\n    ('New Coreystad Archdukes', 'Scott Harrison', 150000, 'D'),\n    ('Port Elizabeth Barbarians', 'Robert George', 195000, 'M');\n```\n```\nSELECT\n    player,\n    salary,\n    row_number() OVER (ORDER BY salary ASC) AS row\nFROM salaries;\n```\n```\n┌─player──────────┬─salary─┬─row─┐\n│ Michael Stanley │ 150000 │   1 │\n│ Scott Harrison  │ 150000 │   2 │\n│ Charles Juarez  │ 190000 │   3 │\n│ Gary Chen       │ 195000 │   4 │\n│ Robert George   │ 195000 │   5 │\n└─────────────────┴────────┴─────┘\n```\n```\nSELECT\n    player,\n    salary,\n    row_number() OVER (ORDER BY salary ASC) AS row,\n    rank() OVER (ORDER BY salary ASC) AS rank,\n    dense_rank() OVER (ORDER BY salary ASC) AS denseRank\nFROM salaries;\n```\n```\n┌─player──────────┬─salary─┬─row─┬─rank─┬─denseRank─┐\n│ Michael Stanley │ 150000 │   1 │    1 │         1 │\n│ Scott Harrison  │ 150000 │   2 │    1 │         1 │\n│ Charles Juarez  │ 190000 │   3 │    3 │         2 │\n│ Gary Chen       │ 195000 │   4 │    4 │         3 │\n│ Robert George   │ 195000 │   5 │    4 │         3 │\n└─────────────────┴────────┴─────┴──────┴───────────┘\n```\n### Aggregation functions[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#aggregation-functions \"Direct link to Aggregation functions\")\nCompare each player's salary to the average for their team.\n```\nSELECT\n    player,\n    salary,\n    team,\n    avg(salary) OVER (PARTITION BY team) AS teamAvg,\n    salary - teamAvg AS diff\nFROM salaries;\n```\n```\n┌─player──────────┬─salary─┬─team──────────────────────┬─teamAvg─┬───diff─┐\n│ Charles Juarez  │ 190000 │ New Coreystad Archdukes   │  170000 │  20000 │\n│ Scott Harrison  │ 150000 │ New Coreystad Archdukes   │  170000 │ -20000 │\n│ Gary Chen       │ 195000 │ Port Elizabeth Barbarians │  180000 │  15000 │\n│ Michael Stanley │ 150000 │ Port Elizabeth Barbarians │  180000 │ -30000 │\n│ Robert George   │ 195000 │ Port Elizabeth Barbarians │  180000 │  15000 │\n└─────────────────┴────────┴───────────────────────────┴─────────┴────────┘\n```\nCompare each player's salary to the maximum for their team.\n```\nSELECT\n    player,\n    salary,\n    team,\n    max(salary) OVER (PARTITION BY team) AS teamMax,\n    salary - teamMax AS diff\nFROM salaries;\n```\n```\n┌─player──────────┬─salary─┬─team──────────────────────┬─teamMax─┬───diff─┐\n│ Charles Juarez  │ 190000 │ New Coreystad Archdukes   │  190000 │      0 │\n│ Scott Harrison  │ 150000 │ New Coreystad Archdukes   │  190000 │ -40000 │\n│ Gary Chen       │ 195000 │ Port Elizabeth Barbarians │  195000 │      0 │\n│ Michael Stanley │ 150000 │ Port Elizabeth Barbarians │  195000 │ -45000 │\n│ Robert George   │ 195000 │ Port Elizabeth Barbarians │  195000 │      0 │\n└─────────────────┴────────┴───────────────────────────┴─────────┴────────┘\n```\n### Partitioning by column[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#partitioning-by-column \"Direct link to Partitioning by column\")\n```\nCREATE TABLE wf_partition\n(\n    `part_key` UInt64,\n    `value` UInt64,\n    `order` UInt64    \n)\nENGINE = Memory;\n\nINSERT INTO wf_partition FORMAT Values\n   (1,1,1), (1,2,2), (1,3,3), (2,0,0), (3,0,0);\n\nSELECT\n    part_key,\n    value,\n    order,\n    groupArray(value) OVER (PARTITION BY part_key) AS frame_values\nFROM wf_partition\nORDER BY\n    part_key ASC,\n    value ASC;\n\n┌─part_key─┬─value─┬─order─┬─frame_values─┐\n│        1 │     1 │     1 │ [1,2,3]      │   <┐   \n│        1 │     2 │     2 │ [1,2,3]      │    │  1-st group\n│        1 │     3 │     3 │ [1,2,3]      │   <┘ \n│        2 │     0 │     0 │ [0]          │   <- 2-nd group\n│        3 │     0 │     0 │ [0]          │   <- 3-d group\n└──────────┴───────┴───────┴──────────────┘\n```\n### Frame bounding[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#frame-bounding \"Direct link to Frame bounding\")\n```\nCREATE TABLE wf_frame\n(\n    `part_key` UInt64,\n    `value` UInt64,\n    `order` UInt64\n)\nENGINE = Memory;\n\nINSERT INTO wf_frame FORMAT Values\n   (1,1,1), (1,2,2), (1,3,3), (1,4,4), (1,5,5);\n```\n```\n-- Frame is bounded by bounds of a partition (BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)\nSELECT\n    part_key,\n    value,\n    order,\n    groupArray(value) OVER (\n        PARTITION BY part_key \n        ORDER BY order ASC\n        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING\n    ) AS frame_values\nFROM wf_frame\nORDER BY\n    part_key ASC,\n    value ASC;\n    \n┌─part_key─┬─value─┬─order─┬─frame_values─┐\n│        1 │     1 │     1 │ [1,2,3,4,5]  │\n│        1 │     2 │     2 │ [1,2,3,4,5]  │\n│        1 │     3 │     3 │ [1,2,3,4,5]  │\n│        1 │     4 │     4 │ [1,2,3,4,5]  │\n│        1 │     5 │     5 │ [1,2,3,4,5]  │\n└──────────┴───────┴───────┴──────────────┘\n```\n```\n-- short form - no bound expression, no order by,\n-- an equalent of `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`\nSELECT\n    part_key,\n    value,\n    order,\n    groupArray(value) OVER (PARTITION BY part_key) AS frame_values_short,\n    groupArray(value) OVER (PARTITION BY part_key\n         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING\n    ) AS frame_values\nFROM wf_frame\nORDER BY\n    part_key ASC,\n    value ASC;\n┌─part_key─┬─value─┬─order─┬─frame_values_short─┬─frame_values─┐\n│        1 │     1 │     1 │ [1,2,3,4,5]        │ [1,2,3,4,5]  │\n│        1 │     2 │     2 │ [1,2,3,4,5]        │ [1,2,3,4,5]  │\n│        1 │     3 │     3 │ [1,2,3,4,5]        │ [1,2,3,4,5]  │\n│        1 │     4 │     4 │ [1,2,3,4,5]        │ [1,2,3,4,5]  │\n│        1 │     5 │     5 │ [1,2,3,4,5]        │ [1,2,3,4,5]  │\n└──────────┴───────┴───────┴────────────────────┴──────────────┘\n```\n```\n-- frame is bounded by the beginning of a partition and the current row\nSELECT\n    part_key,\n    value,\n    order,\n    groupArray(value) OVER (\n        PARTITION BY part_key \n        ORDER BY order ASC\n        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW\n    ) AS frame_values\nFROM wf_frame\nORDER BY\n    part_key ASC,\n    value ASC;\n\n┌─part_key─┬─value─┬─order─┬─frame_values─┐\n│        1 │     1 │     1 │ [1]          │\n│        1 │     2 │     2 │ [1,2]        │\n│        1 │     3 │     3 │ [1,2,3]      │\n│        1 │     4 │     4 │ [1,2,3,4]    │\n│        1 │     5 │     5 │ [1,2,3,4,5]  │\n└──────────┴───────┴───────┴──────────────┘\n```\n```\n-- short form (frame is bounded by the beginning of a partition and the current row)\n-- an equalent of `ORDER BY order ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`\nSELECT\n    part_key,\n    value,\n    order,\n    groupArray(value) OVER (PARTITION BY part_key ORDER BY order ASC) AS frame_values_short,\n    groupArray(value) OVER (PARTITION BY part_key ORDER BY order ASC\n       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW\n    ) AS frame_values\nFROM wf_frame\nORDER BY\n    part_key ASC,\n    value ASC;\n\n┌─part_key─┬─value─┬─order─┬─frame_values_short─┬─frame_values─┐\n│        1 │     1 │     1 │ [1]                │ [1]          │\n│        1 │     2 │     2 │ [1,2]              │ [1,2]        │\n│        1 │     3 │     3 │ [1,2,3]            │ [1,2,3]      │\n│        1 │     4 │     4 │ [1,2,3,4]          │ [1,2,3,4]    │\n│        1 │     5 │     5 │ [1,2,3,4,5]        │ [1,2,3,4,5]  │\n└──────────┴───────┴───────┴────────────────────┴──────────────┘\n```\n```\n-- frame is bounded by the beginning of a partition and the current row, but order is backward\nSELECT\n    part_key,\n    value,\n    order,\n    groupArray(value) OVER (PARTITION BY part_key ORDER BY order DESC) AS frame_values\nFROM wf_frame\nORDER BY\n    part_key ASC,\n    value ASC;\n\n┌─part_key─┬─value─┬─order─┬─frame_values─┐\n│        1 │     1 │     1 │ [5,4,3,2,1]  │\n│        1 │     2 │     2 │ [5,4,3,2]    │\n│        1 │     3 │     3 │ [5,4,3]      │\n│        1 │     4 │     4 │ [5,4]        │\n│        1 │     5 │     5 │ [5]          │\n└──────────┴───────┴───────┴──────────────┘\n```\n```\n-- sliding frame - 1 PRECEDING ROW AND CURRENT ROW\nSELECT\n    part_key,\n    value,\n    order,\n    groupArray(value) OVER (\n        PARTITION BY part_key \n        ORDER BY order ASC\n        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW\n    ) AS frame_values\nFROM wf_frame\nORDER BY\n    part_key ASC,\n    value ASC;\n\n┌─part_key─┬─value─┬─order─┬─frame_values─┐\n│        1 │     1 │     1 │ [1]          │\n│        1 │     2 │     2 │ [1,2]        │\n│        1 │     3 │     3 │ [2,3]        │\n│        1 │     4 │     4 │ [3,4]        │\n│        1 │     5 │     5 │ [4,5]        │\n└──────────┴───────┴───────┴──────────────┘\n```\n```\n-- sliding frame - ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING \nSELECT\n    part_key,\n    value,\n    order,\n    groupArray(value) OVER (\n        PARTITION BY part_key \n        ORDER BY order ASC\n        ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING\n    ) AS frame_values\nFROM wf_frame\nORDER BY\n    part_key ASC,\n    value ASC;\n\n┌─part_key─┬─value─┬─order─┬─frame_values─┐\n│        1 │     1 │     1 │ [1,2,3,4,5]  │\n│        1 │     2 │     2 │ [1,2,3,4,5]  │\n│        1 │     3 │     3 │ [2,3,4,5]    │\n│        1 │     4 │     4 │ [3,4,5]      │\n│        1 │     5 │     5 │ [4,5]        │\n└──────────┴───────┴───────┴──────────────┘\n```\n```\n-- row_number does not respect the frame, so rn_1 = rn_2 = rn_3 != rn_4\nSELECT\n    part_key,\n    value,\n    order,\n    groupArray(value) OVER w1 AS frame_values,\n    row_number() OVER w1 AS rn_1,\n    sum(1) OVER w1 AS rn_2,\n    row_number() OVER w2 AS rn_3,\n    sum(1) OVER w2 AS rn_4\nFROM wf_frame\nWINDOW\n    w1 AS (PARTITION BY part_key ORDER BY order DESC),\n    w2 AS (\n        PARTITION BY part_key \n        ORDER BY order DESC \n        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW\n    )\nORDER BY\n    part_key ASC,\n    value ASC;\n\n┌─part_key─┬─value─┬─order─┬─frame_values─┬─rn_1─┬─rn_2─┬─rn_3─┬─rn_4─┐\n│        1 │     1 │     1 │ [5,4,3,2,1]  │    5 │    5 │    5 │    2 │\n│        1 │     2 │     2 │ [5,4,3,2]    │    4 │    4 │    4 │    2 │\n│        1 │     3 │     3 │ [5,4,3]      │    3 │    3 │    3 │    2 │\n│        1 │     4 │     4 │ [5,4]        │    2 │    2 │    2 │    2 │\n│        1 │     5 │     5 │ [5]          │    1 │    1 │    1 │    1 │\n└──────────┴───────┴───────┴──────────────┴──────┴──────┴──────┴──────┘\n```\n```\n-- first_value and last_value respect the frame\nSELECT\n    groupArray(value) OVER w1 AS frame_values_1,\n    first_value(value) OVER w1 AS first_value_1,\n    last_value(value) OVER w1 AS last_value_1,\n    groupArray(value) OVER w2 AS frame_values_2,\n    first_value(value) OVER w2 AS first_value_2,\n    last_value(value) OVER w2 AS last_value_2\nFROM wf_frame\nWINDOW\n    w1 AS (PARTITION BY part_key ORDER BY order ASC),\n    w2 AS (PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)\nORDER BY\n    part_key ASC,\n    value ASC;\n\n┌─frame_values_1─┬─first_value_1─┬─last_value_1─┬─frame_values_2─┬─first_value_2─┬─last_value_2─┐\n│ [1]            │             1 │            1 │ [1]            │             1 │            1 │\n│ [1,2]          │             1 │            2 │ [1,2]          │             1 │            2 │\n│ [1,2,3]        │             1 │            3 │ [2,3]          │             2 │            3 │\n│ [1,2,3,4]      │             1 │            4 │ [3,4]          │             3 │            4 │\n│ [1,2,3,4,5]    │             1 │            5 │ [4,5]          │             4 │            5 │\n└────────────────┴───────────────┴──────────────┴────────────────┴───────────────┴──────────────┘\n```\n```\n-- second value within the frame\nSELECT\n    groupArray(value) OVER w1 AS frame_values_1,\n    nth_value(value, 2) OVER w1 AS second_value\nFROM wf_frame\nWINDOW w1 AS (PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)\nORDER BY\n    part_key ASC,\n    value ASC;\n\n┌─frame_values_1─┬─second_value─┐\n│ [1]            │            0 │\n│ [1,2]          │            2 │\n│ [1,2,3]        │            2 │\n│ [1,2,3,4]      │            2 │\n│ [2,3,4,5]      │            3 │\n└────────────────┴──────────────┘\n```\n```\n-- second value within the frame + Null for missing values\nSELECT\n    groupArray(value) OVER w1 AS frame_values_1,\n    nth_value(toNullable(value), 2) OVER w1 AS second_value\nFROM wf_frame\nWINDOW w1 AS (PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)\nORDER BY\n    part_key ASC,\n    value ASC;\n\n┌─frame_values_1─┬─second_value─┐\n│ [1]            │         ᴺᵁᴸᴸ │\n│ [1,2]          │            2 │\n│ [1,2,3]        │            2 │\n│ [1,2,3,4]      │            2 │\n│ [2,3,4,5]      │            3 │\n└────────────────┴──────────────┘\n```\n## Real world examples[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#real-world-examples \"Direct link to Real world examples\")\nThe following examples solve common real-world problems.\n\n### Maximum\/total salary per department[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#maximumtotal-salary-per-department \"Direct link to Maximum\/total salary per department\")\n```\nCREATE TABLE employees\n(\n    `department` String,\n    `employee_name` String,\n    `salary` Float\n)\nENGINE = Memory;\n\nINSERT INTO employees FORMAT Values\n   ('Finance', 'Jonh', 200),\n   ('Finance', 'Joan', 210),\n   ('Finance', 'Jean', 505),\n   ('IT', 'Tim', 200),\n   ('IT', 'Anna', 300),\n   ('IT', 'Elen', 500);\n```\n```\nSELECT\n    department,\n    employee_name AS emp,\n    salary,\n    max_salary_per_dep,\n    total_salary_per_dep,\n    round((salary \/ total_salary_per_dep) * 100, 2) AS `share_per_dep(%)`\nFROM\n(\n    SELECT\n        department,\n        employee_name,\n        salary,\n        max(salary) OVER wndw AS max_salary_per_dep,\n        sum(salary) OVER wndw AS total_salary_per_dep\n    FROM employees\n    WINDOW wndw AS (\n        PARTITION BY department\n        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING\n    )\n    ORDER BY\n        department ASC,\n        employee_name ASC\n);\n\n┌─department─┬─emp──┬─salary─┬─max_salary_per_dep─┬─total_salary_per_dep─┬─share_per_dep(%)─┐\n│ Finance    │ Jean │    505 │                505 │                  915 │            55.19 │\n│ Finance    │ Joan │    210 │                505 │                  915 │            22.95 │\n│ Finance    │ Jonh │    200 │                505 │                  915 │            21.86 │\n│ IT         │ Anna │    300 │                500 │                 1000 │               30 │\n│ IT         │ Elen │    500 │                500 │                 1000 │               50 │\n│ IT         │ Tim  │    200 │                500 │                 1000 │               20 │\n└────────────┴──────┴────────┴────────────────────┴──────────────────────┴──────────────────┘\n```\n### Cumulative sum[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#cumulative-sum \"Direct link to Cumulative sum\")\n```\nCREATE TABLE warehouse\n(\n    `item` String,\n    `ts` DateTime,\n    `value` Float\n)\nENGINE = Memory\n\nINSERT INTO warehouse VALUES\n    ('sku38', '2020-01-01', 9),\n    ('sku38', '2020-02-01', 1),\n    ('sku38', '2020-03-01', -4),\n    ('sku1', '2020-01-01', 1),\n    ('sku1', '2020-02-01', 1),\n    ('sku1', '2020-03-01', 1);\n```\n```\nSELECT\n    item,\n    ts,\n    value,\n    sum(value) OVER (PARTITION BY item ORDER BY ts ASC) AS stock_balance\nFROM warehouse\nORDER BY\n    item ASC,\n    ts ASC;\n\n┌─item──┬──────────────────ts─┬─value─┬─stock_balance─┐\n│ sku1  │ 2020-01-01 00:00:00 │     1 │             1 │\n│ sku1  │ 2020-02-01 00:00:00 │     1 │             2 │\n│ sku1  │ 2020-03-01 00:00:00 │     1 │             3 │\n│ sku38 │ 2020-01-01 00:00:00 │     9 │             9 │\n│ sku38 │ 2020-02-01 00:00:00 │     1 │            10 │\n│ sku38 │ 2020-03-01 00:00:00 │    -4 │             6 │\n└───────┴─────────────────────┴───────┴───────────────┘\n```\n### Moving \/ sliding average (per 3 rows)[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#moving--sliding-average-per-3-rows \"Direct link to Moving \/ sliding average (per 3 rows)\")\n```\nCREATE TABLE sensors\n(\n    `metric` String,\n    `ts` DateTime,\n    `value` Float\n)\nENGINE = Memory;\n\ninsert into sensors values('cpu_temp', '2020-01-01 00:00:00', 87),\n                          ('cpu_temp', '2020-01-01 00:00:01', 77),\n                          ('cpu_temp', '2020-01-01 00:00:02', 93),\n                          ('cpu_temp', '2020-01-01 00:00:03', 87),\n                          ('cpu_temp', '2020-01-01 00:00:04', 87),\n                          ('cpu_temp', '2020-01-01 00:00:05', 87),\n                          ('cpu_temp', '2020-01-01 00:00:06', 87),\n                          ('cpu_temp', '2020-01-01 00:00:07', 87);\n```\n```\nSELECT\n    metric,\n    ts,\n    value,\n    avg(value) OVER (\n        PARTITION BY metric \n        ORDER BY ts ASC \n        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW\n    ) AS moving_avg_temp\nFROM sensors\nORDER BY\n    metric ASC,\n    ts ASC;\n\n┌─metric───┬──────────────────ts─┬─value─┬───moving_avg_temp─┐\n│ cpu_temp │ 2020-01-01 00:00:00 │    87 │                87 │\n│ cpu_temp │ 2020-01-01 00:00:01 │    77 │                82 │\n│ cpu_temp │ 2020-01-01 00:00:02 │    93 │ 85.66666666666667 │\n│ cpu_temp │ 2020-01-01 00:00:03 │    87 │ 85.66666666666667 │\n│ cpu_temp │ 2020-01-01 00:00:04 │    87 │                89 │\n│ cpu_temp │ 2020-01-01 00:00:05 │    87 │                87 │\n│ cpu_temp │ 2020-01-01 00:00:06 │    87 │                87 │\n│ cpu_temp │ 2020-01-01 00:00:07 │    87 │                87 │\n└──────────┴─────────────────────┴───────┴───────────────────┘\n```\n### Moving \/ sliding average (per 10 seconds)[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#moving--sliding-average-per-10-seconds \"Direct link to Moving \/ sliding average (per 10 seconds)\")\n```\nSELECT\n    metric,\n    ts,\n    value,\n    avg(value) OVER (PARTITION BY metric ORDER BY ts\n      RANGE BETWEEN 10 PRECEDING AND CURRENT ROW) AS moving_avg_10_seconds_temp\nFROM sensors\nORDER BY\n    metric ASC,\n    ts ASC;\n    \n┌─metric───┬──────────────────ts─┬─value─┬─moving_avg_10_seconds_temp─┐\n│ cpu_temp │ 2020-01-01 00:00:00 │    87 │                         87 │\n│ cpu_temp │ 2020-01-01 00:01:10 │    77 │                         77 │\n│ cpu_temp │ 2020-01-01 00:02:20 │    93 │                         93 │\n│ cpu_temp │ 2020-01-01 00:03:30 │    87 │                         87 │\n│ cpu_temp │ 2020-01-01 00:04:40 │    87 │                         87 │\n│ cpu_temp │ 2020-01-01 00:05:50 │    87 │                         87 │\n│ cpu_temp │ 2020-01-01 00:06:00 │    87 │                         87 │\n│ cpu_temp │ 2020-01-01 00:07:10 │    87 │                         87 │\n└──────────┴─────────────────────┴───────┴────────────────────────────┘\n```\n### Moving \/ sliding average (per 10 days)[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#moving--sliding-average-per-10-days \"Direct link to Moving \/ sliding average (per 10 days)\")\nTemperature is stored with second precision, but using `Range` and `ORDER BY toDate(ts)` we form a frame with the size of 10 units, and because of `toDate(ts)` the unit is a day.\n```\nCREATE TABLE sensors\n(\n    `metric` String,\n    `ts` DateTime,\n    `value` Float\n)\nENGINE = Memory;\n\ninsert into sensors values('ambient_temp', '2020-01-01 00:00:00', 16),\n                          ('ambient_temp', '2020-01-01 12:00:00', 16),\n                          ('ambient_temp', '2020-01-02 11:00:00', 9),\n                          ('ambient_temp', '2020-01-02 12:00:00', 9),                          \n                          ('ambient_temp', '2020-02-01 10:00:00', 10),\n                          ('ambient_temp', '2020-02-01 12:00:00', 10),\n                          ('ambient_temp', '2020-02-10 12:00:00', 12),                          \n                          ('ambient_temp', '2020-02-10 13:00:00', 12),\n                          ('ambient_temp', '2020-02-20 12:00:01', 16),\n                          ('ambient_temp', '2020-03-01 12:00:00', 16),\n                          ('ambient_temp', '2020-03-01 12:00:00', 16),\n                          ('ambient_temp', '2020-03-01 12:00:00', 16);\n```\n```\nSELECT\n    metric,\n    ts,\n    value,\n    round(avg(value) OVER (PARTITION BY metric ORDER BY toDate(ts) \n       RANGE BETWEEN 10 PRECEDING AND CURRENT ROW),2) AS moving_avg_10_days_temp\nFROM sensors\nORDER BY\n    metric ASC,\n    ts ASC;\n\n┌─metric───────┬──────────────────ts─┬─value─┬─moving_avg_10_days_temp─┐\n│ ambient_temp │ 2020-01-01 00:00:00 │    16 │                      16 │\n│ ambient_temp │ 2020-01-01 12:00:00 │    16 │                      16 │\n│ ambient_temp │ 2020-01-02 11:00:00 │     9 │                    12.5 │\n│ ambient_temp │ 2020-01-02 12:00:00 │     9 │                    12.5 │\n│ ambient_temp │ 2020-02-01 10:00:00 │    10 │                      10 │\n│ ambient_temp │ 2020-02-01 12:00:00 │    10 │                      10 │\n│ ambient_temp │ 2020-02-10 12:00:00 │    12 │                      11 │\n│ ambient_temp │ 2020-02-10 13:00:00 │    12 │                      11 │\n│ ambient_temp │ 2020-02-20 12:00:01 │    16 │                   13.33 │\n│ ambient_temp │ 2020-03-01 12:00:00 │    16 │                      16 │\n│ ambient_temp │ 2020-03-01 12:00:00 │    16 │                      16 │\n│ ambient_temp │ 2020-03-01 12:00:00 │    16 │                      16 │\n└──────────────┴─────────────────────┴───────┴─────────────────────────┘\n```\n## References[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#references \"Direct link to References\")\n### GitHub Issues[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#github-issues \"Direct link to GitHub Issues\")\nThe roadmap for the initial support of window functions is [in this issue](https:\/\/github.com\/ClickHouse\/ClickHouse\/issues\/18097).\n\nAll GitHub issues related to window functions have the [comp-window-functions](https:\/\/github.com\/ClickHouse\/ClickHouse\/labels\/comp-window-functions) tag.\n\n### Tests[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#tests \"Direct link to Tests\")\nThese tests contain the examples of the currently supported grammar:\n\n<https:\/\/github.com\/ClickHouse\/ClickHouse\/blob\/master\/tests\/performance\/window_functions.xml>\n\n<https:\/\/github.com\/ClickHouse\/ClickHouse\/blob\/master\/tests\/queries\/0_stateless\/01591_window_functions.sql>\n\n### Postgres Docs[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#postgres-docs \"Direct link to Postgres Docs\")\n<https:\/\/www.postgresql.org\/docs\/current\/sql-select.html#SQL-WINDOW>\n\n<https:\/\/www.postgresql.org\/docs\/devel\/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS>\n\n<https:\/\/www.postgresql.org\/docs\/devel\/functions-window.html>\n\n<https:\/\/www.postgresql.org\/docs\/devel\/tutorial-window.html>\n\n### MySQL Docs[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#mysql-docs \"Direct link to MySQL Docs\")\n<https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/window-function-descriptions.html>\n\n<https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/window-functions-usage.html>\n\n<https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/window-functions-frames.html>\n\n## Related Content[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#related-content \"Direct link to Related Content\")\n- Blog: [Working with time series data in ClickHouse](https:\/\/clickhouse.com\/blog\/working-with-time-series-data-and-functions-ClickHouse)\n- Blog: [Window and array functions for Git commit sequences](https:\/\/clickhouse.com\/blog\/clickhouse-window-array-functions-git-commits)\n- Blog: [Getting Data Into ClickHouse - Part 3 - Using S3](https:\/\/clickhouse.com\/blog\/getting-data-into-clickhouse-part-3-s3)\n\n[Previous loop](https:\/\/clickhouse.com\/docs\/sql-reference\/table-functions\/loop)\n\n[Next row\\_number](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/row_number)\n\n- [Standard window functions](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#standard-window-functions)\n- [ClickHouse-specific window functions](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#clickhouse-specific-window-functions)\n  - [nonNegativeDerivative(metric\\_column, timestamp\\_column\\[, INTERVAL X UNITS\\])](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#nonnegativederivativemetric_column-timestamp_column-interval-x-units)\n- [Syntax](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#syntax)\n  - [Functions](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#functions)\n- [Examples](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#examples)\n  - [Numbering rows](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#numbering-rows)\n  - [Aggregation functions](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#aggregation-functions)\n  - [Partitioning by column](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#partitioning-by-column)\n  - [Frame bounding](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#frame-bounding)\n- [Real world examples](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#real-world-examples)\n  - [Maximum\/total salary per department](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#maximumtotal-salary-per-department)\n  - [Cumulative sum](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#cumulative-sum)\n  - [Moving \/ sliding average (per 3 rows)](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#moving--sliding-average-per-3-rows)\n  - [Moving \/ sliding average (per 10 seconds)](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#moving--sliding-average-per-10-seconds)\n  - [Moving \/ sliding average (per 10 days)](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#moving--sliding-average-per-10-days)\n- [References](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#references)\n  - [GitHub Issues](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#github-issues)\n  - [Tests](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#tests)\n  - [Postgres Docs](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#postgres-docs)\n  - [MySQL Docs](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#mysql-docs)\n- [Related Content](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#related-content)\n\nWas this page helpful?\n\n###### Try ClickHouse Cloud for FREE\nSeparation of storage and compute, automatic scaling, built-in SQL console, and lots more. \\$300 in free credits when signing up.\n\n[Try it for Free](https:\/\/console.clickhouse.cloud\/signUp?loc=doc-card-banner&glxid=ec987698-9313-4186-9781-be5fd1b64e13&pagePath=%2Fdocs%2Fsql-reference%2Fwindow-functions&origPath=%2Fdocs%2Fsql-reference%2Fwindow-functions&utm_ga=GA1.1.1971283203.1775876958)\n\n© 2016–2026 ClickHouse, Inc.\n\n[Trademark](https:\/\/clickhouse.com\/legal\/trademark-policy)·[Privacy](https:\/\/clickhouse.com\/legal\/privacy-policy)·[Security](https:\/\/trust.clickhouse.com\/)·[Terms of Service](https:\/\/clickhouse.com\/legal\/agreements\/terms-of-service)\n\n![](https:\/\/static.scarf.sh\/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91)\n\n© 2016–2026 ClickHouse, Inc.\n\n[Trademark](https:\/\/clickhouse.com\/legal\/trademark-policy)·[Privacy](https:\/\/clickhouse.com\/legal\/privacy-policy)·[Security](https:\/\/trust.clickhouse.com\/)·[Terms of Service](https:\/\/clickhouse.com\/legal\/agreements\/terms-of-service)\n\n![](https:\/\/static.scarf.sh\/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91)\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\nEN\n\n- Get started▼\n- Cloud▼\n- Manage data▼\n- Server admin▼\n- Reference▼\n- Integrations▼\n- ClickStack▼\n- chDB▼\n- About▼\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\nEN\n\nmain-menu\n\n- Introduction▼\n  - [Syntax](https:\/\/clickhouse.com\/docs\/sql-reference\/syntax)\n  - [Input and Output Formats](https:\/\/clickhouse.com\/docs\/sql-reference\/formats)\n  - Data types▼\n  - Statements▼\n  - Operators▼\n- Engines▼\n- Functions▼\n- Formats▼\n- [Data Lakes](https:\/\/clickhouse.com\/docs\/sql-reference\/datalakes)","attrs_readable_markdown":"Window functions let you perform calculations across a set of rows that are related to the current row. Some of the calculations that you can do are similar to those that can be done with an aggregate function, but a window function doesn't cause rows to be grouped into a single output - the individual rows are still returned.\n\n## Standard window functions[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#standard-window-functions \"Direct link to Standard window functions\")\nClickHouse supports the standard grammar for defining windows and window functions. The table below indicates whether a feature is currently supported.\n\n| Feature | Supported? |\n|---|---|\n| ad hoc window specification (`count(*) over (partition by id order by time desc)`) | ✅ |\n| expressions involving window functions, e.g. `(count(*) over ()) \/ 2)` | ✅ |\n| `WINDOW` clause (`select ... from table window w as (partition by id)`) | ✅ |\n| `ROWS` frame | ✅ |\n| `RANGE` frame | ✅ (the default) |\n| `INTERVAL` syntax for `DateTime` `RANGE OFFSET` frame | ❌ (specify the number of seconds instead (`RANGE` works with any numeric type).) |\n| `GROUPS` frame | ❌ |\n| Calculating aggregate functions over a frame (`sum(value) over (order by time)`) | ✅ (All aggregate functions are supported) |\n| `rank()`, `dense_rank()`, `row_number()` | ✅  Alias: `denseRank()` |\n| `percent_rank()` | ✅ Efficiently computes the relative standing of a value within a partition in a dataset. This function effectively replaces the more verbose and computationally intensive manual SQL calculation expressed as `ifNull((rank() OVER(PARTITION BY x ORDER BY y) - 1) \/ nullif(count(1) OVER(PARTITION BY x) - 1, 0), 0)`  Alias: `percentRank()` |\n| `cume_dist()` | ✅ Computes the cumulative distribution of a value within a group of values. Returns the percentage of rows with values less than or equal to the current row's value. |\n| `lag\/lead(value, offset)` | ✅   You can also use one of the following workarounds:  1) `any(value) over (.... rows between <offset> preceding and <offset> preceding)`, or `following` for `lead`   2) `lagInFrame\/leadInFrame`, which are analogous, but respect the window frame. To get behavior identical to `lag\/lead`, use `rows between unbounded preceding and unbounded following` |\n| ntile(buckets) | ✅   Specify window like, (partition by x order by y rows between unbounded preceding and unbounded following). |\n## ClickHouse-specific window functions[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#clickhouse-specific-window-functions \"Direct link to ClickHouse-specific window functions\")\nThere is also the following ClickHouse specific window function:\n\n### nonNegativeDerivative(metric\\_column, timestamp\\_column\\[, INTERVAL X UNITS\\])[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#nonnegativederivativemetric_column-timestamp_column-interval-x-units \"Direct link to nonNegativeDerivative(metric_column, timestamp_column[, INTERVAL X UNITS])\")\nFinds non-negative derivative for given `metric_column` by `timestamp_column`. `INTERVAL` can be omitted, default is `INTERVAL 1 SECOND`. The computed value is the following for each row:\n\n- `0` for 1st row,\n- metric\n  \n  i\n  \n  −\n  \n  metric\n  \n  i\n  \n  −\n  \n  1\n  \n  timestamp\n  \n  i\n  \n  −\n  \n  timestamp\n  \n  i\n  \n  −\n  \n  1\n  \n  ∗\n  \n  interval\n  \n  {\\\\text{metric}\\_i - \\\\text{metric}\\_{i-1} \\\\over \\\\text{timestamp}\\_i - \\\\text{timestamp}\\_{i-1}} \\* \\\\text{interval}\n  for\n  i\n  \n  t\n  \n  h\n  \n  i\\_{th}\n  row.\n## Syntax[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#syntax \"Direct link to Syntax\")\n```\naggregate_function (column_name)\n  OVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column] \n        [ROWS or RANGE expression_to_bound_rows_within_the_group]] | [window_name])\nFROM table_name\nWINDOW window_name as ([\n  [PARTITION BY grouping_column]\n  [ORDER BY sorting_column]\n  [ROWS or RANGE expression_to_bound_rows_within_the_group]\n])\n```\n- `PARTITION BY` - defines how to break a resultset into groups.\n- `ORDER BY` - defines how to order rows inside the group during calculation aggregate\\_function.\n- `ROWS or RANGE` - defines bounds of a frame, aggregate\\_function is calculated within a frame.\n- `WINDOW` - allows multiple expressions to use the same window definition.\n```\n      PARTITION\n┌─────────────────┐  <-- UNBOUNDED PRECEDING (BEGINNING of the PARTITION)\n│                 │\n│                 │\n│=================│  <-- N PRECEDING  <─┐\n│      N ROWS     │                     │  F\n│  Before CURRENT │                     │  R\n│~~~~~~~~~~~~~~~~~│  <-- CURRENT ROW    │  A\n│     M ROWS      │                     │  M\n│   After CURRENT │                     │  E\n│=================│  <-- M FOLLOWING  <─┘\n│                 │\n│                 │\n└─────────────────┘  <--- UNBOUNDED FOLLOWING (END of the PARTITION)\n```\n### Functions[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#functions \"Direct link to Functions\")\nThese functions can be used only as a window function.\n\n- [`row_number()`](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/row_number) - Number the current row within its partition starting from 1.\n- [`first_value(x)`](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/first_value) - Return the first value evaluated within its ordered frame.\n- [`last_value(x)`](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/last_value) - Return the last value evaluated within its ordered frame.\n- [`nth_value(x, offset)`](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/nth_value) - Return the first non-NULL value evaluated against the nth row (offset) in its ordered frame.\n- [`rank()`](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/rank) - Rank the current row within its partition with gaps.\n- [`dense_rank()`](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/dense_rank) - Rank the current row within its partition without gaps.\n- [`lagInFrame(x)`](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/lagInFrame) - Return a value evaluated at the row that is at a specified physical offset row before the current row within the ordered frame.\n- [`leadInFrame(x)`](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions\/leadInFrame) - Return a value evaluated at the row that is offset rows after the current row within the ordered frame.\n## Examples[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#examples \"Direct link to Examples\")\nLet's have a look at some examples of how window functions can be used.\n\n### Numbering rows[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#numbering-rows \"Direct link to Numbering rows\")\n```\nCREATE TABLE salaries\n(\n    `team` String,\n    `player` String,\n    `salary` UInt32,\n    `position` String\n)\nEngine = Memory;\n\nINSERT INTO salaries FORMAT Values\n    ('Port Elizabeth Barbarians', 'Gary Chen', 195000, 'F'),\n    ('New Coreystad Archdukes', 'Charles Juarez', 190000, 'F'),\n    ('Port Elizabeth Barbarians', 'Michael Stanley', 150000, 'D'),\n    ('New Coreystad Archdukes', 'Scott Harrison', 150000, 'D'),\n    ('Port Elizabeth Barbarians', 'Robert George', 195000, 'M');\n```\n```\nSELECT\n    player,\n    salary,\n    row_number() OVER (ORDER BY salary ASC) AS row\nFROM salaries;\n```\n```\n┌─player──────────┬─salary─┬─row─┐\n│ Michael Stanley │ 150000 │   1 │\n│ Scott Harrison  │ 150000 │   2 │\n│ Charles Juarez  │ 190000 │   3 │\n│ Gary Chen       │ 195000 │   4 │\n│ Robert George   │ 195000 │   5 │\n└─────────────────┴────────┴─────┘\n```\n```\nSELECT\n    player,\n    salary,\n    row_number() OVER (ORDER BY salary ASC) AS row,\n    rank() OVER (ORDER BY salary ASC) AS rank,\n    dense_rank() OVER (ORDER BY salary ASC) AS denseRank\nFROM salaries;\n```\n```\n┌─player──────────┬─salary─┬─row─┬─rank─┬─denseRank─┐\n│ Michael Stanley │ 150000 │   1 │    1 │         1 │\n│ Scott Harrison  │ 150000 │   2 │    1 │         1 │\n│ Charles Juarez  │ 190000 │   3 │    3 │         2 │\n│ Gary Chen       │ 195000 │   4 │    4 │         3 │\n│ Robert George   │ 195000 │   5 │    4 │         3 │\n└─────────────────┴────────┴─────┴──────┴───────────┘\n```\n### Aggregation functions[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#aggregation-functions \"Direct link to Aggregation functions\")\nCompare each player's salary to the average for their team.\n```\nSELECT\n    player,\n    salary,\n    team,\n    avg(salary) OVER (PARTITION BY team) AS teamAvg,\n    salary - teamAvg AS diff\nFROM salaries;\n```\n```\n┌─player──────────┬─salary─┬─team──────────────────────┬─teamAvg─┬───diff─┐\n│ Charles Juarez  │ 190000 │ New Coreystad Archdukes   │  170000 │  20000 │\n│ Scott Harrison  │ 150000 │ New Coreystad Archdukes   │  170000 │ -20000 │\n│ Gary Chen       │ 195000 │ Port Elizabeth Barbarians │  180000 │  15000 │\n│ Michael Stanley │ 150000 │ Port Elizabeth Barbarians │  180000 │ -30000 │\n│ Robert George   │ 195000 │ Port Elizabeth Barbarians │  180000 │  15000 │\n└─────────────────┴────────┴───────────────────────────┴─────────┴────────┘\n```\nCompare each player's salary to the maximum for their team.\n```\nSELECT\n    player,\n    salary,\n    team,\n    max(salary) OVER (PARTITION BY team) AS teamMax,\n    salary - teamMax AS diff\nFROM salaries;\n```\n```\n┌─player──────────┬─salary─┬─team──────────────────────┬─teamMax─┬───diff─┐\n│ Charles Juarez  │ 190000 │ New Coreystad Archdukes   │  190000 │      0 │\n│ Scott Harrison  │ 150000 │ New Coreystad Archdukes   │  190000 │ -40000 │\n│ Gary Chen       │ 195000 │ Port Elizabeth Barbarians │  195000 │      0 │\n│ Michael Stanley │ 150000 │ Port Elizabeth Barbarians │  195000 │ -45000 │\n│ Robert George   │ 195000 │ Port Elizabeth Barbarians │  195000 │      0 │\n└─────────────────┴────────┴───────────────────────────┴─────────┴────────┘\n```\n### Partitioning by column[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#partitioning-by-column \"Direct link to Partitioning by column\")\n```\nCREATE TABLE wf_partition\n(\n    `part_key` UInt64,\n    `value` UInt64,\n    `order` UInt64    \n)\nENGINE = Memory;\n\nINSERT INTO wf_partition FORMAT Values\n   (1,1,1), (1,2,2), (1,3,3), (2,0,0), (3,0,0);\n\nSELECT\n    part_key,\n    value,\n    order,\n    groupArray(value) OVER (PARTITION BY part_key) AS frame_values\nFROM wf_partition\nORDER BY\n    part_key ASC,\n    value ASC;\n\n┌─part_key─┬─value─┬─order─┬─frame_values─┐\n│        1 │     1 │     1 │ [1,2,3]      │   <┐   \n│        1 │     2 │     2 │ [1,2,3]      │    │  1-st group\n│        1 │     3 │     3 │ [1,2,3]      │   <┘ \n│        2 │     0 │     0 │ [0]          │   <- 2-nd group\n│        3 │     0 │     0 │ [0]          │   <- 3-d group\n└──────────┴───────┴───────┴──────────────┘\n```\n### Frame bounding[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#frame-bounding \"Direct link to Frame bounding\")\n```\nCREATE TABLE wf_frame\n(\n    `part_key` UInt64,\n    `value` UInt64,\n    `order` UInt64\n)\nENGINE = Memory;\n\nINSERT INTO wf_frame FORMAT Values\n   (1,1,1), (1,2,2), (1,3,3), (1,4,4), (1,5,5);\n```\n```\n-- Frame is bounded by bounds of a partition (BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)\nSELECT\n    part_key,\n    value,\n    order,\n    groupArray(value) OVER (\n        PARTITION BY part_key \n        ORDER BY order ASC\n        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING\n    ) AS frame_values\nFROM wf_frame\nORDER BY\n    part_key ASC,\n    value ASC;\n    \n┌─part_key─┬─value─┬─order─┬─frame_values─┐\n│        1 │     1 │     1 │ [1,2,3,4,5]  │\n│        1 │     2 │     2 │ [1,2,3,4,5]  │\n│        1 │     3 │     3 │ [1,2,3,4,5]  │\n│        1 │     4 │     4 │ [1,2,3,4,5]  │\n│        1 │     5 │     5 │ [1,2,3,4,5]  │\n└──────────┴───────┴───────┴──────────────┘\n```\n```\n-- short form - no bound expression, no order by,\n-- an equalent of `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`\nSELECT\n    part_key,\n    value,\n    order,\n    groupArray(value) OVER (PARTITION BY part_key) AS frame_values_short,\n    groupArray(value) OVER (PARTITION BY part_key\n         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING\n    ) AS frame_values\nFROM wf_frame\nORDER BY\n    part_key ASC,\n    value ASC;\n┌─part_key─┬─value─┬─order─┬─frame_values_short─┬─frame_values─┐\n│        1 │     1 │     1 │ [1,2,3,4,5]        │ [1,2,3,4,5]  │\n│        1 │     2 │     2 │ [1,2,3,4,5]        │ [1,2,3,4,5]  │\n│        1 │     3 │     3 │ [1,2,3,4,5]        │ [1,2,3,4,5]  │\n│        1 │     4 │     4 │ [1,2,3,4,5]        │ [1,2,3,4,5]  │\n│        1 │     5 │     5 │ [1,2,3,4,5]        │ [1,2,3,4,5]  │\n└──────────┴───────┴───────┴────────────────────┴──────────────┘\n```\n```\n-- frame is bounded by the beginning of a partition and the current row\nSELECT\n    part_key,\n    value,\n    order,\n    groupArray(value) OVER (\n        PARTITION BY part_key \n        ORDER BY order ASC\n        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW\n    ) AS frame_values\nFROM wf_frame\nORDER BY\n    part_key ASC,\n    value ASC;\n\n┌─part_key─┬─value─┬─order─┬─frame_values─┐\n│        1 │     1 │     1 │ [1]          │\n│        1 │     2 │     2 │ [1,2]        │\n│        1 │     3 │     3 │ [1,2,3]      │\n│        1 │     4 │     4 │ [1,2,3,4]    │\n│        1 │     5 │     5 │ [1,2,3,4,5]  │\n└──────────┴───────┴───────┴──────────────┘\n```\n```\n-- short form (frame is bounded by the beginning of a partition and the current row)\n-- an equalent of `ORDER BY order ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`\nSELECT\n    part_key,\n    value,\n    order,\n    groupArray(value) OVER (PARTITION BY part_key ORDER BY order ASC) AS frame_values_short,\n    groupArray(value) OVER (PARTITION BY part_key ORDER BY order ASC\n       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW\n    ) AS frame_values\nFROM wf_frame\nORDER BY\n    part_key ASC,\n    value ASC;\n\n┌─part_key─┬─value─┬─order─┬─frame_values_short─┬─frame_values─┐\n│        1 │     1 │     1 │ [1]                │ [1]          │\n│        1 │     2 │     2 │ [1,2]              │ [1,2]        │\n│        1 │     3 │     3 │ [1,2,3]            │ [1,2,3]      │\n│        1 │     4 │     4 │ [1,2,3,4]          │ [1,2,3,4]    │\n│        1 │     5 │     5 │ [1,2,3,4,5]        │ [1,2,3,4,5]  │\n└──────────┴───────┴───────┴────────────────────┴──────────────┘\n```\n```\n-- frame is bounded by the beginning of a partition and the current row, but order is backward\nSELECT\n    part_key,\n    value,\n    order,\n    groupArray(value) OVER (PARTITION BY part_key ORDER BY order DESC) AS frame_values\nFROM wf_frame\nORDER BY\n    part_key ASC,\n    value ASC;\n\n┌─part_key─┬─value─┬─order─┬─frame_values─┐\n│        1 │     1 │     1 │ [5,4,3,2,1]  │\n│        1 │     2 │     2 │ [5,4,3,2]    │\n│        1 │     3 │     3 │ [5,4,3]      │\n│        1 │     4 │     4 │ [5,4]        │\n│        1 │     5 │     5 │ [5]          │\n└──────────┴───────┴───────┴──────────────┘\n```\n```\n-- sliding frame - 1 PRECEDING ROW AND CURRENT ROW\nSELECT\n    part_key,\n    value,\n    order,\n    groupArray(value) OVER (\n        PARTITION BY part_key \n        ORDER BY order ASC\n        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW\n    ) AS frame_values\nFROM wf_frame\nORDER BY\n    part_key ASC,\n    value ASC;\n\n┌─part_key─┬─value─┬─order─┬─frame_values─┐\n│        1 │     1 │     1 │ [1]          │\n│        1 │     2 │     2 │ [1,2]        │\n│        1 │     3 │     3 │ [2,3]        │\n│        1 │     4 │     4 │ [3,4]        │\n│        1 │     5 │     5 │ [4,5]        │\n└──────────┴───────┴───────┴──────────────┘\n```\n```\n-- sliding frame - ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING \nSELECT\n    part_key,\n    value,\n    order,\n    groupArray(value) OVER (\n        PARTITION BY part_key \n        ORDER BY order ASC\n        ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING\n    ) AS frame_values\nFROM wf_frame\nORDER BY\n    part_key ASC,\n    value ASC;\n\n┌─part_key─┬─value─┬─order─┬─frame_values─┐\n│        1 │     1 │     1 │ [1,2,3,4,5]  │\n│        1 │     2 │     2 │ [1,2,3,4,5]  │\n│        1 │     3 │     3 │ [2,3,4,5]    │\n│        1 │     4 │     4 │ [3,4,5]      │\n│        1 │     5 │     5 │ [4,5]        │\n└──────────┴───────┴───────┴──────────────┘\n```\n```\n-- row_number does not respect the frame, so rn_1 = rn_2 = rn_3 != rn_4\nSELECT\n    part_key,\n    value,\n    order,\n    groupArray(value) OVER w1 AS frame_values,\n    row_number() OVER w1 AS rn_1,\n    sum(1) OVER w1 AS rn_2,\n    row_number() OVER w2 AS rn_3,\n    sum(1) OVER w2 AS rn_4\nFROM wf_frame\nWINDOW\n    w1 AS (PARTITION BY part_key ORDER BY order DESC),\n    w2 AS (\n        PARTITION BY part_key \n        ORDER BY order DESC \n        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW\n    )\nORDER BY\n    part_key ASC,\n    value ASC;\n\n┌─part_key─┬─value─┬─order─┬─frame_values─┬─rn_1─┬─rn_2─┬─rn_3─┬─rn_4─┐\n│        1 │     1 │     1 │ [5,4,3,2,1]  │    5 │    5 │    5 │    2 │\n│        1 │     2 │     2 │ [5,4,3,2]    │    4 │    4 │    4 │    2 │\n│        1 │     3 │     3 │ [5,4,3]      │    3 │    3 │    3 │    2 │\n│        1 │     4 │     4 │ [5,4]        │    2 │    2 │    2 │    2 │\n│        1 │     5 │     5 │ [5]          │    1 │    1 │    1 │    1 │\n└──────────┴───────┴───────┴──────────────┴──────┴──────┴──────┴──────┘\n```\n```\n-- first_value and last_value respect the frame\nSELECT\n    groupArray(value) OVER w1 AS frame_values_1,\n    first_value(value) OVER w1 AS first_value_1,\n    last_value(value) OVER w1 AS last_value_1,\n    groupArray(value) OVER w2 AS frame_values_2,\n    first_value(value) OVER w2 AS first_value_2,\n    last_value(value) OVER w2 AS last_value_2\nFROM wf_frame\nWINDOW\n    w1 AS (PARTITION BY part_key ORDER BY order ASC),\n    w2 AS (PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)\nORDER BY\n    part_key ASC,\n    value ASC;\n\n┌─frame_values_1─┬─first_value_1─┬─last_value_1─┬─frame_values_2─┬─first_value_2─┬─last_value_2─┐\n│ [1]            │             1 │            1 │ [1]            │             1 │            1 │\n│ [1,2]          │             1 │            2 │ [1,2]          │             1 │            2 │\n│ [1,2,3]        │             1 │            3 │ [2,3]          │             2 │            3 │\n│ [1,2,3,4]      │             1 │            4 │ [3,4]          │             3 │            4 │\n│ [1,2,3,4,5]    │             1 │            5 │ [4,5]          │             4 │            5 │\n└────────────────┴───────────────┴──────────────┴────────────────┴───────────────┴──────────────┘\n```\n```\n-- second value within the frame\nSELECT\n    groupArray(value) OVER w1 AS frame_values_1,\n    nth_value(value, 2) OVER w1 AS second_value\nFROM wf_frame\nWINDOW w1 AS (PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)\nORDER BY\n    part_key ASC,\n    value ASC;\n\n┌─frame_values_1─┬─second_value─┐\n│ [1]            │            0 │\n│ [1,2]          │            2 │\n│ [1,2,3]        │            2 │\n│ [1,2,3,4]      │            2 │\n│ [2,3,4,5]      │            3 │\n└────────────────┴──────────────┘\n```\n```\n-- second value within the frame + Null for missing values\nSELECT\n    groupArray(value) OVER w1 AS frame_values_1,\n    nth_value(toNullable(value), 2) OVER w1 AS second_value\nFROM wf_frame\nWINDOW w1 AS (PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)\nORDER BY\n    part_key ASC,\n    value ASC;\n\n┌─frame_values_1─┬─second_value─┐\n│ [1]            │         ᴺᵁᴸᴸ │\n│ [1,2]          │            2 │\n│ [1,2,3]        │            2 │\n│ [1,2,3,4]      │            2 │\n│ [2,3,4,5]      │            3 │\n└────────────────┴──────────────┘\n```\n## Real world examples[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#real-world-examples \"Direct link to Real world examples\")\nThe following examples solve common real-world problems.\n\n### Maximum\/total salary per department[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#maximumtotal-salary-per-department \"Direct link to Maximum\/total salary per department\")\n```\nCREATE TABLE employees\n(\n    `department` String,\n    `employee_name` String,\n    `salary` Float\n)\nENGINE = Memory;\n\nINSERT INTO employees FORMAT Values\n   ('Finance', 'Jonh', 200),\n   ('Finance', 'Joan', 210),\n   ('Finance', 'Jean', 505),\n   ('IT', 'Tim', 200),\n   ('IT', 'Anna', 300),\n   ('IT', 'Elen', 500);\n```\n```\nSELECT\n    department,\n    employee_name AS emp,\n    salary,\n    max_salary_per_dep,\n    total_salary_per_dep,\n    round((salary \/ total_salary_per_dep) * 100, 2) AS `share_per_dep(%)`\nFROM\n(\n    SELECT\n        department,\n        employee_name,\n        salary,\n        max(salary) OVER wndw AS max_salary_per_dep,\n        sum(salary) OVER wndw AS total_salary_per_dep\n    FROM employees\n    WINDOW wndw AS (\n        PARTITION BY department\n        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING\n    )\n    ORDER BY\n        department ASC,\n        employee_name ASC\n);\n\n┌─department─┬─emp──┬─salary─┬─max_salary_per_dep─┬─total_salary_per_dep─┬─share_per_dep(%)─┐\n│ Finance    │ Jean │    505 │                505 │                  915 │            55.19 │\n│ Finance    │ Joan │    210 │                505 │                  915 │            22.95 │\n│ Finance    │ Jonh │    200 │                505 │                  915 │            21.86 │\n│ IT         │ Anna │    300 │                500 │                 1000 │               30 │\n│ IT         │ Elen │    500 │                500 │                 1000 │               50 │\n│ IT         │ Tim  │    200 │                500 │                 1000 │               20 │\n└────────────┴──────┴────────┴────────────────────┴──────────────────────┴──────────────────┘\n```\n### Cumulative sum[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#cumulative-sum \"Direct link to Cumulative sum\")\n```\nCREATE TABLE warehouse\n(\n    `item` String,\n    `ts` DateTime,\n    `value` Float\n)\nENGINE = Memory\n\nINSERT INTO warehouse VALUES\n    ('sku38', '2020-01-01', 9),\n    ('sku38', '2020-02-01', 1),\n    ('sku38', '2020-03-01', -4),\n    ('sku1', '2020-01-01', 1),\n    ('sku1', '2020-02-01', 1),\n    ('sku1', '2020-03-01', 1);\n```\n```\nSELECT\n    item,\n    ts,\n    value,\n    sum(value) OVER (PARTITION BY item ORDER BY ts ASC) AS stock_balance\nFROM warehouse\nORDER BY\n    item ASC,\n    ts ASC;\n\n┌─item──┬──────────────────ts─┬─value─┬─stock_balance─┐\n│ sku1  │ 2020-01-01 00:00:00 │     1 │             1 │\n│ sku1  │ 2020-02-01 00:00:00 │     1 │             2 │\n│ sku1  │ 2020-03-01 00:00:00 │     1 │             3 │\n│ sku38 │ 2020-01-01 00:00:00 │     9 │             9 │\n│ sku38 │ 2020-02-01 00:00:00 │     1 │            10 │\n│ sku38 │ 2020-03-01 00:00:00 │    -4 │             6 │\n└───────┴─────────────────────┴───────┴───────────────┘\n```\n### Moving \/ sliding average (per 3 rows)[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#moving--sliding-average-per-3-rows \"Direct link to Moving \/ sliding average (per 3 rows)\")\n```\nCREATE TABLE sensors\n(\n    `metric` String,\n    `ts` DateTime,\n    `value` Float\n)\nENGINE = Memory;\n\ninsert into sensors values('cpu_temp', '2020-01-01 00:00:00', 87),\n                          ('cpu_temp', '2020-01-01 00:00:01', 77),\n                          ('cpu_temp', '2020-01-01 00:00:02', 93),\n                          ('cpu_temp', '2020-01-01 00:00:03', 87),\n                          ('cpu_temp', '2020-01-01 00:00:04', 87),\n                          ('cpu_temp', '2020-01-01 00:00:05', 87),\n                          ('cpu_temp', '2020-01-01 00:00:06', 87),\n                          ('cpu_temp', '2020-01-01 00:00:07', 87);\n```\n```\nSELECT\n    metric,\n    ts,\n    value,\n    avg(value) OVER (\n        PARTITION BY metric \n        ORDER BY ts ASC \n        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW\n    ) AS moving_avg_temp\nFROM sensors\nORDER BY\n    metric ASC,\n    ts ASC;\n\n┌─metric───┬──────────────────ts─┬─value─┬───moving_avg_temp─┐\n│ cpu_temp │ 2020-01-01 00:00:00 │    87 │                87 │\n│ cpu_temp │ 2020-01-01 00:00:01 │    77 │                82 │\n│ cpu_temp │ 2020-01-01 00:00:02 │    93 │ 85.66666666666667 │\n│ cpu_temp │ 2020-01-01 00:00:03 │    87 │ 85.66666666666667 │\n│ cpu_temp │ 2020-01-01 00:00:04 │    87 │                89 │\n│ cpu_temp │ 2020-01-01 00:00:05 │    87 │                87 │\n│ cpu_temp │ 2020-01-01 00:00:06 │    87 │                87 │\n│ cpu_temp │ 2020-01-01 00:00:07 │    87 │                87 │\n└──────────┴─────────────────────┴───────┴───────────────────┘\n```\n### Moving \/ sliding average (per 10 seconds)[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#moving--sliding-average-per-10-seconds \"Direct link to Moving \/ sliding average (per 10 seconds)\")\n```\nSELECT\n    metric,\n    ts,\n    value,\n    avg(value) OVER (PARTITION BY metric ORDER BY ts\n      RANGE BETWEEN 10 PRECEDING AND CURRENT ROW) AS moving_avg_10_seconds_temp\nFROM sensors\nORDER BY\n    metric ASC,\n    ts ASC;\n    \n┌─metric───┬──────────────────ts─┬─value─┬─moving_avg_10_seconds_temp─┐\n│ cpu_temp │ 2020-01-01 00:00:00 │    87 │                         87 │\n│ cpu_temp │ 2020-01-01 00:01:10 │    77 │                         77 │\n│ cpu_temp │ 2020-01-01 00:02:20 │    93 │                         93 │\n│ cpu_temp │ 2020-01-01 00:03:30 │    87 │                         87 │\n│ cpu_temp │ 2020-01-01 00:04:40 │    87 │                         87 │\n│ cpu_temp │ 2020-01-01 00:05:50 │    87 │                         87 │\n│ cpu_temp │ 2020-01-01 00:06:00 │    87 │                         87 │\n│ cpu_temp │ 2020-01-01 00:07:10 │    87 │                         87 │\n└──────────┴─────────────────────┴───────┴────────────────────────────┘\n```\n### Moving \/ sliding average (per 10 days)[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#moving--sliding-average-per-10-days \"Direct link to Moving \/ sliding average (per 10 days)\")\nTemperature is stored with second precision, but using `Range` and `ORDER BY toDate(ts)` we form a frame with the size of 10 units, and because of `toDate(ts)` the unit is a day.\n```\nCREATE TABLE sensors\n(\n    `metric` String,\n    `ts` DateTime,\n    `value` Float\n)\nENGINE = Memory;\n\ninsert into sensors values('ambient_temp', '2020-01-01 00:00:00', 16),\n                          ('ambient_temp', '2020-01-01 12:00:00', 16),\n                          ('ambient_temp', '2020-01-02 11:00:00', 9),\n                          ('ambient_temp', '2020-01-02 12:00:00', 9),                          \n                          ('ambient_temp', '2020-02-01 10:00:00', 10),\n                          ('ambient_temp', '2020-02-01 12:00:00', 10),\n                          ('ambient_temp', '2020-02-10 12:00:00', 12),                          \n                          ('ambient_temp', '2020-02-10 13:00:00', 12),\n                          ('ambient_temp', '2020-02-20 12:00:01', 16),\n                          ('ambient_temp', '2020-03-01 12:00:00', 16),\n                          ('ambient_temp', '2020-03-01 12:00:00', 16),\n                          ('ambient_temp', '2020-03-01 12:00:00', 16);\n```\n```\nSELECT\n    metric,\n    ts,\n    value,\n    round(avg(value) OVER (PARTITION BY metric ORDER BY toDate(ts) \n       RANGE BETWEEN 10 PRECEDING AND CURRENT ROW),2) AS moving_avg_10_days_temp\nFROM sensors\nORDER BY\n    metric ASC,\n    ts ASC;\n\n┌─metric───────┬──────────────────ts─┬─value─┬─moving_avg_10_days_temp─┐\n│ ambient_temp │ 2020-01-01 00:00:00 │    16 │                      16 │\n│ ambient_temp │ 2020-01-01 12:00:00 │    16 │                      16 │\n│ ambient_temp │ 2020-01-02 11:00:00 │     9 │                    12.5 │\n│ ambient_temp │ 2020-01-02 12:00:00 │     9 │                    12.5 │\n│ ambient_temp │ 2020-02-01 10:00:00 │    10 │                      10 │\n│ ambient_temp │ 2020-02-01 12:00:00 │    10 │                      10 │\n│ ambient_temp │ 2020-02-10 12:00:00 │    12 │                      11 │\n│ ambient_temp │ 2020-02-10 13:00:00 │    12 │                      11 │\n│ ambient_temp │ 2020-02-20 12:00:01 │    16 │                   13.33 │\n│ ambient_temp │ 2020-03-01 12:00:00 │    16 │                      16 │\n│ ambient_temp │ 2020-03-01 12:00:00 │    16 │                      16 │\n│ ambient_temp │ 2020-03-01 12:00:00 │    16 │                      16 │\n└──────────────┴─────────────────────┴───────┴─────────────────────────┘\n```\n## References[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#references \"Direct link to References\")\n### GitHub Issues[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#github-issues \"Direct link to GitHub Issues\")\nThe roadmap for the initial support of window functions is [in this issue](https:\/\/github.com\/ClickHouse\/ClickHouse\/issues\/18097).\n\nAll GitHub issues related to window functions have the [comp-window-functions](https:\/\/github.com\/ClickHouse\/ClickHouse\/labels\/comp-window-functions) tag.\n\n### Tests[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#tests \"Direct link to Tests\")\nThese tests contain the examples of the currently supported grammar:\n\n<https:\/\/github.com\/ClickHouse\/ClickHouse\/blob\/master\/tests\/performance\/window_functions.xml>\n\n<https:\/\/github.com\/ClickHouse\/ClickHouse\/blob\/master\/tests\/queries\/0_stateless\/01591_window_functions.sql>\n\n### Postgres Docs[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#postgres-docs \"Direct link to Postgres Docs\")\n<https:\/\/www.postgresql.org\/docs\/current\/sql-select.html#SQL-WINDOW>\n\n<https:\/\/www.postgresql.org\/docs\/devel\/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS>\n\n<https:\/\/www.postgresql.org\/docs\/devel\/functions-window.html>\n\n<https:\/\/www.postgresql.org\/docs\/devel\/tutorial-window.html>\n\n### MySQL Docs[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#mysql-docs \"Direct link to MySQL Docs\")\n<https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/window-function-descriptions.html>\n\n<https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/window-functions-usage.html>\n\n<https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/window-functions-frames.html>\n\n## Related Content[](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions#related-content \"Direct link to Related Content\")\n- Blog: [Working with time series data in ClickHouse](https:\/\/clickhouse.com\/blog\/working-with-time-series-data-and-functions-ClickHouse)\n- Blog: [Window and array functions for Git commit sequences](https:\/\/clickhouse.com\/blog\/clickhouse-window-array-functions-git-commits)\n- Blog: [Getting Data Into ClickHouse - Part 3 - Using S3](https:\/\/clickhouse.com\/blog\/getting-data-into-clickhouse-part-3-s3)","meta_canonical":null}

3. Robots.txt Check

Query:

Response:

4. Spam/Ban Check

Query:

Response:

5. Seen Status Check

ℹ️ Skipped - page is already crawled

📄

INDEXABLE

✅

CRAWLED

6 days ago

🤖

ROBOTS ALLOWED

Page Info Filters

Filter	Status	Condition	Details
HTTP status	PASS	`download_http_code = 200`	HTTP 200
Age cutoff	PASS	`download_stamp > now() - 6 MONTH`	0.2 months ago
History drop	PASS	`isNull(history_drop_reason)`	No drop reason
Spam/ban	PASS	`fh_dont_index != 1 AND ml_spam_score = 0`	ml_spam_score=0
Canonical	PASS	`meta_canonical IS NULL OR = '' OR = src_unparsed`	Not set

Page Details

Property	Value
URL	https://clickhouse.com/docs/sql-reference/window-functions
Last Crawled	2026-04-11 03:09:16 (6 days ago)
First Indexed	2025-02-21 00:09:27 (1 year ago)
HTTP Status Code	200
Meta Title	Window Functions \| ClickHouse Docs
Meta Description	Overview page for window functions
Meta Canonical	null
Boilerpipe Text	Window functions let you perform calculations across a set of rows that are related to the current row. Some of the calculations that you can do are similar to those that can be done with an aggregate function, but a window function doesn't cause rows to be grouped into a single output - the individual rows are still returned. Standard window functions ClickHouse supports the standard grammar for defining windows and window functions. The table below indicates whether a feature is currently supported. Feature Supported? ad hoc window specification ( count() over (partition by id order by time desc) ) ✅ expressions involving window functions, e.g. (count() over ()) / 2) ✅ WINDOW clause ( select ... from table window w as (partition by id) ) ✅ ROWS frame ✅ RANGE frame ✅ (the default) INTERVAL syntax for DateTime RANGE OFFSET frame ❌ (specify the number of seconds instead ( RANGE works with any numeric type).) GROUPS frame ❌ Calculating aggregate functions over a frame ( sum(value) over (order by time) ) ✅ (All aggregate functions are supported) rank() , dense_rank() , row_number() ✅ Alias: denseRank() percent_rank() ✅ Efficiently computes the relative standing of a value within a partition in a dataset. This function effectively replaces the more verbose and computationally intensive manual SQL calculation expressed as ifNull((rank() OVER(PARTITION BY x ORDER BY y) - 1) / nullif(count(1) OVER(PARTITION BY x) - 1, 0), 0) Alias: percentRank() cume_dist() ✅ Computes the cumulative distribution of a value within a group of values. Returns the percentage of rows with values less than or equal to the current row's value. lag/lead(value, offset) ✅ You can also use one of the following workarounds: 1) any(value) over (.... rows between <offset> preceding and <offset> preceding) , or following for lead 2) lagInFrame/leadInFrame , which are analogous, but respect the window frame. To get behavior identical to lag/lead , use rows between unbounded preceding and unbounded following ntile(buckets) ✅ Specify window like, (partition by x order by y rows between unbounded preceding and unbounded following). ClickHouse-specific window functions There is also the following ClickHouse specific window function: nonNegativeDerivative(metric_column, timestamp_column[, INTERVAL X UNITS]) Finds non-negative derivative for given metric_column by timestamp_column . INTERVAL can be omitted, default is INTERVAL 1 SECOND . The computed value is the following for each row: 0 for 1st row, metric i − metric i − 1 timestamp i − timestamp i − 1 ∗ interval {\text{metric}_i - \text{metric}_{i-1} \over \text{timestamp}_i - \text{timestamp}_{i-1}} * \text{interval} for i t h i_{th} row. Syntax aggregate_function (column_name) OVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column] [ROWS or RANGE expression_to_bound_rows_within_the_group]] \| [window_name]) FROM table_name WINDOW window_name as ([ [PARTITION BY grouping_column] [ORDER BY sorting_column] [ROWS or RANGE expression_to_bound_rows_within_the_group] ]) PARTITION BY - defines how to break a resultset into groups. ORDER BY - defines how to order rows inside the group during calculation aggregate_function. ROWS or RANGE - defines bounds of a frame, aggregate_function is calculated within a frame. WINDOW - allows multiple expressions to use the same window definition. PARTITION ┌─────────────────┐ <-- UNBOUNDED PRECEDING (BEGINNING of the PARTITION) │ │ │ │ │=================│ <-- N PRECEDING <─┐ │ N ROWS │ │ F │ Before CURRENT │ │ R │~~~~~~~~~~~~~~~~~│ <-- CURRENT ROW │ A │ M ROWS │ │ M │ After CURRENT │ │ E │=================│ <-- M FOLLOWING <─┘ │ │ │ │ └─────────────────┘ <--- UNBOUNDED FOLLOWING (END of the PARTITION) Functions These functions can be used only as a window function. row_number() - Number the current row within its partition starting from 1. first_value(x) - Return the first value evaluated within its ordered frame. last_value(x) - Return the last value evaluated within its ordered frame. nth_value(x, offset) - Return the first non-NULL value evaluated against the nth row (offset) in its ordered frame. rank() - Rank the current row within its partition with gaps. dense_rank() - Rank the current row within its partition without gaps. lagInFrame(x) - Return a value evaluated at the row that is at a specified physical offset row before the current row within the ordered frame. leadInFrame(x) - Return a value evaluated at the row that is offset rows after the current row within the ordered frame. Examples Let's have a look at some examples of how window functions can be used. Numbering rows CREATE TABLE salaries ( ` team ` String , ` player ` String , ` salary ` UInt32 , ` position ` String ) Engine = Memory ; INSERT INTO salaries FORMAT Values ( 'Port Elizabeth Barbarians' , 'Gary Chen' , 195000 , 'F' ) , ( 'New Coreystad Archdukes' , 'Charles Juarez' , 190000 , 'F' ) , ( 'Port Elizabeth Barbarians' , 'Michael Stanley' , 150000 , 'D' ) , ( 'New Coreystad Archdukes' , 'Scott Harrison' , 150000 , 'D' ) , ( 'Port Elizabeth Barbarians' , 'Robert George' , 195000 , 'M' ) ; SELECT player , salary , row_number ( ) OVER ( ORDER BY salary ASC ) AS row FROM salaries ; ┌─player──────────┬─salary─┬─row─┐ │ Michael Stanley │ 150000 │ 1 │ │ Scott Harrison │ 150000 │ 2 │ │ Charles Juarez │ 190000 │ 3 │ │ Gary Chen │ 195000 │ 4 │ │ Robert George │ 195000 │ 5 │ └─────────────────┴────────┴─────┘ SELECT player , salary , row_number ( ) OVER ( ORDER BY salary ASC ) AS row , rank ( ) OVER ( ORDER BY salary ASC ) AS rank , dense_rank ( ) OVER ( ORDER BY salary ASC ) AS denseRank FROM salaries ; ┌─player──────────┬─salary─┬─row─┬─rank─┬─denseRank─┐ │ Michael Stanley │ 150000 │ 1 │ 1 │ 1 │ │ Scott Harrison │ 150000 │ 2 │ 1 │ 1 │ │ Charles Juarez │ 190000 │ 3 │ 3 │ 2 │ │ Gary Chen │ 195000 │ 4 │ 4 │ 3 │ │ Robert George │ 195000 │ 5 │ 4 │ 3 │ └─────────────────┴────────┴─────┴──────┴───────────┘ Aggregation functions Compare each player's salary to the average for their team. SELECT player , salary , team , avg ( salary ) OVER ( PARTITION BY team ) AS teamAvg , salary - teamAvg AS diff FROM salaries ; ┌─player──────────┬─salary─┬─team──────────────────────┬─teamAvg─┬───diff─┐ │ Charles Juarez │ 190000 │ New Coreystad Archdukes │ 170000 │ 20000 │ │ Scott Harrison │ 150000 │ New Coreystad Archdukes │ 170000 │ -20000 │ │ Gary Chen │ 195000 │ Port Elizabeth Barbarians │ 180000 │ 15000 │ │ Michael Stanley │ 150000 │ Port Elizabeth Barbarians │ 180000 │ -30000 │ │ Robert George │ 195000 │ Port Elizabeth Barbarians │ 180000 │ 15000 │ └─────────────────┴────────┴───────────────────────────┴─────────┴────────┘ Compare each player's salary to the maximum for their team. SELECT player , salary , team , max ( salary ) OVER ( PARTITION BY team ) AS teamMax , salary - teamMax AS diff FROM salaries ; ┌─player──────────┬─salary─┬─team──────────────────────┬─teamMax─┬───diff─┐ │ Charles Juarez │ 190000 │ New Coreystad Archdukes │ 190000 │ 0 │ │ Scott Harrison │ 150000 │ New Coreystad Archdukes │ 190000 │ -40000 │ │ Gary Chen │ 195000 │ Port Elizabeth Barbarians │ 195000 │ 0 │ │ Michael Stanley │ 150000 │ Port Elizabeth Barbarians │ 195000 │ -45000 │ │ Robert George │ 195000 │ Port Elizabeth Barbarians │ 195000 │ 0 │ └─────────────────┴────────┴───────────────────────────┴─────────┴────────┘ Partitioning by column CREATE TABLE wf_partition ( ` part_key ` UInt64 , ` value ` UInt64 , ` order ` UInt64 ) ENGINE = Memory ; INSERT INTO wf_partition FORMAT Values ( 1 , 1 , 1 ) , ( 1 , 2 , 2 ) , ( 1 , 3 , 3 ) , ( 2 , 0 , 0 ) , ( 3 , 0 , 0 ) ; SELECT part_key , value , order , groupArray ( value ) OVER ( PARTITION BY part_key ) AS frame_values FROM wf_partition ORDER BY part_key ASC , value ASC ; ┌─part_key─┬─ value ─┬─ order ─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [ 1 , 2 , 3 ] │ < ┐ │ 1 │ 2 │ 2 │ [ 1 , 2 , 3 ] │ │ 1 - st group │ 1 │ 3 │ 3 │ [ 1 , 2 , 3 ] │ < ┘ │ 2 │ 0 │ 0 │ [ 0 ] │ < - 2 - nd group │ 3 │ 0 │ 0 │ [ 0 ] │ < - 3 - d group └──────────┴───────┴───────┴──────────────┘ Frame bounding CREATE TABLE wf_frame ( ` part_key ` UInt64 , ` value ` UInt64 , ` order ` UInt64 ) ENGINE = Memory ; INSERT INTO wf_frame FORMAT Values ( 1 , 1 , 1 ) , ( 1 , 2 , 2 ) , ( 1 , 3 , 3 ) , ( 1 , 4 , 4 ) , ( 1 , 5 , 5 ) ; -- Frame is bounded by bounds of a partition (BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) SELECT part_key , value , order , groupArray ( value ) OVER ( PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS frame_values FROM wf_frame ORDER BY part_key ASC , value ASC ; ┌─part_key─┬─ value ─┬─ order ─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [ 1 , 2 , 3 , 4 , 5 ] │ │ 1 │ 2 │ 2 │ [ 1 , 2 , 3 , 4 , 5 ] │ │ 1 │ 3 │ 3 │ [ 1 , 2 , 3 , 4 , 5 ] │ │ 1 │ 4 │ 4 │ [ 1 , 2 , 3 , 4 , 5 ] │ │ 1 │ 5 │ 5 │ [ 1 , 2 , 3 , 4 , 5 ] │ └──────────┴───────┴───────┴──────────────┘ -- short form - no bound expression, no order by, -- an equalent of `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` SELECT part_key , value , order , groupArray ( value ) OVER ( PARTITION BY part_key ) AS frame_values_short , groupArray ( value ) OVER ( PARTITION BY part_key ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS frame_values FROM wf_frame ORDER BY part_key ASC , value ASC ; ┌─part_key─┬─ value ─┬─ order ─┬─frame_values_short─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [ 1 , 2 , 3 , 4 , 5 ] │ [ 1 , 2 , 3 , 4 , 5 ] │ │ 1 │ 2 │ 2 │ [ 1 , 2 , 3 , 4 , 5 ] │ [ 1 , 2 , 3 , 4 , 5 ] │ │ 1 │ 3 │ 3 │ [ 1 , 2 , 3 , 4 , 5 ] │ [ 1 , 2 , 3 , 4 , 5 ] │ │ 1 │ 4 │ 4 │ [ 1 , 2 , 3 , 4 , 5 ] │ [ 1 , 2 , 3 , 4 , 5 ] │ │ 1 │ 5 │ 5 │ [ 1 , 2 , 3 , 4 , 5 ] │ [ 1 , 2 , 3 , 4 , 5 ] │ └──────────┴───────┴───────┴────────────────────┴──────────────┘ -- frame is bounded by the beginning of a partition and the current row SELECT part_key , value , order , groupArray ( value ) OVER ( PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS frame_values FROM wf_frame ORDER BY part_key ASC , value ASC ; ┌─part_key─┬─ value ─┬─ order ─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [ 1 ] │ │ 1 │ 2 │ 2 │ [ 1 , 2 ] │ │ 1 │ 3 │ 3 │ [ 1 , 2 , 3 ] │ │ 1 │ 4 │ 4 │ [ 1 , 2 , 3 , 4 ] │ │ 1 │ 5 │ 5 │ [ 1 , 2 , 3 , 4 , 5 ] │ └──────────┴───────┴───────┴──────────────┘ -- short form (frame is bounded by the beginning of a partition and the current row) -- an equalent of `ORDER BY order ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` SELECT part_key , value , order , groupArray ( value ) OVER ( PARTITION BY part_key ORDER BY order ASC ) AS frame_values_short , groupArray ( value ) OVER ( PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS frame_values FROM wf_frame ORDER BY part_key ASC , value ASC ; ┌─part_key─┬─ value ─┬─ order ─┬─frame_values_short─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [ 1 ] │ [ 1 ] │ │ 1 │ 2 │ 2 │ [ 1 , 2 ] │ [ 1 , 2 ] │ │ 1 │ 3 │ 3 │ [ 1 , 2 , 3 ] │ [ 1 , 2 , 3 ] │ │ 1 │ 4 │ 4 │ [ 1 , 2 , 3 , 4 ] │ [ 1 , 2 , 3 , 4 ] │ │ 1 │ 5 │ 5 │ [ 1 , 2 , 3 , 4 , 5 ] │ [ 1 , 2 , 3 , 4 , 5 ] │ └──────────┴───────┴───────┴────────────────────┴──────────────┘ -- frame is bounded by the beginning of a partition and the current row, but order is backward SELECT part_key , value , order , groupArray ( value ) OVER ( PARTITION BY part_key ORDER BY order DESC ) AS frame_values FROM wf_frame ORDER BY part_key ASC , value ASC ; ┌─part_key─┬─ value ─┬─ order ─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [ 5 , 4 , 3 , 2 , 1 ] │ │ 1 │ 2 │ 2 │ [ 5 , 4 , 3 , 2 ] │ │ 1 │ 3 │ 3 │ [ 5 , 4 , 3 ] │ │ 1 │ 4 │ 4 │ [ 5 , 4 ] │ │ 1 │ 5 │ 5 │ [ 5 ] │ └──────────┴───────┴───────┴──────────────┘ -- sliding frame - 1 PRECEDING ROW AND CURRENT ROW SELECT part_key , value , order , groupArray ( value ) OVER ( PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) AS frame_values FROM wf_frame ORDER BY part_key ASC , value ASC ; ┌─part_key─┬─ value ─┬─ order ─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [ 1 ] │ │ 1 │ 2 │ 2 │ [ 1 , 2 ] │ │ 1 │ 3 │ 3 │ [ 2 , 3 ] │ │ 1 │ 4 │ 4 │ [ 3 , 4 ] │ │ 1 │ 5 │ 5 │ [ 4 , 5 ] │ └──────────┴───────┴───────┴──────────────┘ -- sliding frame - ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING SELECT part_key , value , order , groupArray ( value ) OVER ( PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING ) AS frame_values FROM wf_frame ORDER BY part_key ASC , value ASC ; ┌─part_key─┬─ value ─┬─ order ─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [ 1 , 2 , 3 , 4 , 5 ] │ │ 1 │ 2 │ 2 │ [ 1 , 2 , 3 , 4 , 5 ] │ │ 1 │ 3 │ 3 │ [ 2 , 3 , 4 , 5 ] │ │ 1 │ 4 │ 4 │ [ 3 , 4 , 5 ] │ │ 1 │ 5 │ 5 │ [ 4 , 5 ] │ └──────────┴───────┴───────┴──────────────┘ -- row_number does not respect the frame, so rn_1 = rn_2 = rn_3 != rn_4 SELECT part_key , value , order , groupArray ( value ) OVER w1 AS frame_values , row_number ( ) OVER w1 AS rn_1 , sum ( 1 ) OVER w1 AS rn_2 , row_number ( ) OVER w2 AS rn_3 , sum ( 1 ) OVER w2 AS rn_4 FROM wf_frame WINDOW w1 AS ( PARTITION BY part_key ORDER BY order DESC ) , w2 AS ( PARTITION BY part_key ORDER BY order DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) ORDER BY part_key ASC , value ASC ; ┌─part_key─┬─ value ─┬─ order ─┬─frame_values─┬─rn_1─┬─rn_2─┬─rn_3─┬─rn_4─┐ │ 1 │ 1 │ 1 │ [ 5 , 4 , 3 , 2 , 1 ] │ 5 │ 5 │ 5 │ 2 │ │ 1 │ 2 │ 2 │ [ 5 , 4 , 3 , 2 ] │ 4 │ 4 │ 4 │ 2 │ │ 1 │ 3 │ 3 │ [ 5 , 4 , 3 ] │ 3 │ 3 │ 3 │ 2 │ │ 1 │ 4 │ 4 │ [ 5 , 4 ] │ 2 │ 2 │ 2 │ 2 │ │ 1 │ 5 │ 5 │ [ 5 ] │ 1 │ 1 │ 1 │ 1 │ └──────────┴───────┴───────┴──────────────┴──────┴──────┴──────┴──────┘ -- first_value and last_value respect the frame SELECT groupArray ( value ) OVER w1 AS frame_values_1 , first_value ( value ) OVER w1 AS first_value_1 , last_value ( value ) OVER w1 AS last_value_1 , groupArray ( value ) OVER w2 AS frame_values_2 , first_value ( value ) OVER w2 AS first_value_2 , last_value ( value ) OVER w2 AS last_value_2 FROM wf_frame WINDOW w1 AS ( PARTITION BY part_key ORDER BY order ASC ) , w2 AS ( PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) ORDER BY part_key ASC , value ASC ; ┌─frame_values_1─┬─first_value_1─┬─last_value_1─┬─frame_values_2─┬─first_value_2─┬─last_value_2─┐ │ [ 1 ] │ 1 │ 1 │ [ 1 ] │ 1 │ 1 │ │ [ 1 , 2 ] │ 1 │ 2 │ [ 1 , 2 ] │ 1 │ 2 │ │ [ 1 , 2 , 3 ] │ 1 │ 3 │ [ 2 , 3 ] │ 2 │ 3 │ │ [ 1 , 2 , 3 , 4 ] │ 1 │ 4 │ [ 3 , 4 ] │ 3 │ 4 │ │ [ 1 , 2 , 3 , 4 , 5 ] │ 1 │ 5 │ [ 4 , 5 ] │ 4 │ 5 │ └────────────────┴───────────────┴──────────────┴────────────────┴───────────────┴──────────────┘ -- second value within the frame SELECT groupArray ( value ) OVER w1 AS frame_values_1 , nth_value ( value , 2 ) OVER w1 AS second_value FROM wf_frame WINDOW w1 AS ( PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW ) ORDER BY part_key ASC , value ASC ; ┌─frame_values_1─┬─second_value─┐ │ [ 1 ] │ 0 │ │ [ 1 , 2 ] │ 2 │ │ [ 1 , 2 , 3 ] │ 2 │ │ [ 1 , 2 , 3 , 4 ] │ 2 │ │ [ 2 , 3 , 4 , 5 ] │ 3 │ └────────────────┴──────────────┘ -- second value within the frame + Null for missing values SELECT groupArray ( value ) OVER w1 AS frame_values_1 , nth_value ( toNullable ( value ) , 2 ) OVER w1 AS second_value FROM wf_frame WINDOW w1 AS ( PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW ) ORDER BY part_key ASC , value ASC ; ┌─frame_values_1─┬─second_value─┐ │ [ 1 ] │ ᴺᵁᴸᴸ │ │ [ 1 , 2 ] │ 2 │ │ [ 1 , 2 , 3 ] │ 2 │ │ [ 1 , 2 , 3 , 4 ] │ 2 │ │ [ 2 , 3 , 4 , 5 ] │ 3 │ └────────────────┴──────────────┘ Real world examples The following examples solve common real-world problems. Maximum/total salary per department CREATE TABLE employees ( ` department ` String , ` employee_name ` String , ` salary ` Float ) ENGINE = Memory ; INSERT INTO employees FORMAT Values ( 'Finance' , 'Jonh' , 200 ) , ( 'Finance' , 'Joan' , 210 ) , ( 'Finance' , 'Jean' , 505 ) , ( 'IT' , 'Tim' , 200 ) , ( 'IT' , 'Anna' , 300 ) , ( 'IT' , 'Elen' , 500 ) ; SELECT department , employee_name AS emp , salary , max_salary_per_dep , total_salary_per_dep , round ( ( salary / total_salary_per_dep ) * 100 , 2 ) AS ` share_per_dep(%) ` FROM ( SELECT department , employee_name , salary , max ( salary ) OVER wndw AS max_salary_per_dep , sum ( salary ) OVER wndw AS total_salary_per_dep FROM employees WINDOW wndw AS ( PARTITION BY department ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) ORDER BY department ASC , employee_name ASC ) ; ┌─department─┬─emp──┬─salary─┬─max_salary_per_dep─┬─total_salary_per_dep─┬─share_per_dep ( % ) ─┐ │ Finance │ Jean │ 505 │ 505 │ 915 │ 55.19 │ │ Finance │ Joan │ 210 │ 505 │ 915 │ 22.95 │ │ Finance │ Jonh │ 200 │ 505 │ 915 │ 21.86 │ │ IT │ Anna │ 300 │ 500 │ 1000 │ 30 │ │ IT │ Elen │ 500 │ 500 │ 1000 │ 50 │ │ IT │ Tim │ 200 │ 500 │ 1000 │ 20 │ └────────────┴──────┴────────┴────────────────────┴──────────────────────┴──────────────────┘ Cumulative sum CREATE TABLE warehouse ( ` item ` String , ` ts ` DateTime , ` value ` Float ) ENGINE = Memory INSERT INTO warehouse VALUES ( 'sku38' , '2020-01-01' , 9 ) , ( 'sku38' , '2020-02-01' , 1 ) , ( 'sku38' , '2020-03-01' , - 4 ) , ( 'sku1' , '2020-01-01' , 1 ) , ( 'sku1' , '2020-02-01' , 1 ) , ( 'sku1' , '2020-03-01' , 1 ) ; SELECT item, ts, value, sum(value) OVER (PARTITION BY item ORDER BY ts ASC) AS stock_balance FROM warehouse ORDER BY item ASC, ts ASC; ┌─item──┬──────────────────ts─┬─value─┬─stock_balance─┐ │ sku1 │ 2020-01-01 00:00:00 │ 1 │ 1 │ │ sku1 │ 2020-02-01 00:00:00 │ 1 │ 2 │ │ sku1 │ 2020-03-01 00:00:00 │ 1 │ 3 │ │ sku38 │ 2020-01-01 00:00:00 │ 9 │ 9 │ │ sku38 │ 2020-02-01 00:00:00 │ 1 │ 10 │ │ sku38 │ 2020-03-01 00:00:00 │ -4 │ 6 │ └───────┴─────────────────────┴───────┴───────────────┘ Moving / sliding average (per 3 rows) CREATE TABLE sensors ( `metric` String, `ts` DateTime, `value` Float ) ENGINE = Memory; insert into sensors values('cpu_temp', '2020-01-01 00:00:00', 87), ('cpu_temp', '2020-01-01 00:00:01', 77), ('cpu_temp', '2020-01-01 00:00:02', 93), ('cpu_temp', '2020-01-01 00:00:03', 87), ('cpu_temp', '2020-01-01 00:00:04', 87), ('cpu_temp', '2020-01-01 00:00:05', 87), ('cpu_temp', '2020-01-01 00:00:06', 87), ('cpu_temp', '2020-01-01 00:00:07', 87); SELECT metric, ts, value, avg(value) OVER ( PARTITION BY metric ORDER BY ts ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_temp FROM sensors ORDER BY metric ASC, ts ASC; ┌─metric───┬──────────────────ts─┬─value─┬───moving_avg_temp─┐ │ cpu_temp │ 2020-01-01 00:00:00 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:00:01 │ 77 │ 82 │ │ cpu_temp │ 2020-01-01 00:00:02 │ 93 │ 85.66666666666667 │ │ cpu_temp │ 2020-01-01 00:00:03 │ 87 │ 85.66666666666667 │ │ cpu_temp │ 2020-01-01 00:00:04 │ 87 │ 89 │ │ cpu_temp │ 2020-01-01 00:00:05 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:00:06 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:00:07 │ 87 │ 87 │ └──────────┴─────────────────────┴───────┴───────────────────┘ Moving / sliding average (per 10 seconds) SELECT metric, ts, value, avg(value) OVER (PARTITION BY metric ORDER BY ts RANGE BETWEEN 10 PRECEDING AND CURRENT ROW) AS moving_avg_10_seconds_temp FROM sensors ORDER BY metric ASC, ts ASC; ┌─metric───┬──────────────────ts─┬─value─┬─moving_avg_10_seconds_temp─┐ │ cpu_temp │ 2020-01-01 00:00:00 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:01:10 │ 77 │ 77 │ │ cpu_temp │ 2020-01-01 00:02:20 │ 93 │ 93 │ │ cpu_temp │ 2020-01-01 00:03:30 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:04:40 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:05:50 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:06:00 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:07:10 │ 87 │ 87 │ └──────────┴─────────────────────┴───────┴────────────────────────────┘ Moving / sliding average (per 10 days) Temperature is stored with second precision, but using Range and ORDER BY toDate(ts) we form a frame with the size of 10 units, and because of toDate(ts) the unit is a day. CREATE TABLE sensors ( `metric` String, `ts` DateTime, `value` Float ) ENGINE = Memory; insert into sensors values('ambient_temp', '2020-01-01 00:00:00', 16), ('ambient_temp', '2020-01-01 12:00:00', 16), ('ambient_temp', '2020-01-02 11:00:00', 9), ('ambient_temp', '2020-01-02 12:00:00', 9), ('ambient_temp', '2020-02-01 10:00:00', 10), ('ambient_temp', '2020-02-01 12:00:00', 10), ('ambient_temp', '2020-02-10 12:00:00', 12), ('ambient_temp', '2020-02-10 13:00:00', 12), ('ambient_temp', '2020-02-20 12:00:01', 16), ('ambient_temp', '2020-03-01 12:00:00', 16), ('ambient_temp', '2020-03-01 12:00:00', 16), ('ambient_temp', '2020-03-01 12:00:00', 16); SELECT metric, ts, value, round(avg(value) OVER (PARTITION BY metric ORDER BY toDate(ts) RANGE BETWEEN 10 PRECEDING AND CURRENT ROW),2) AS moving_avg_10_days_temp FROM sensors ORDER BY metric ASC, ts ASC; ┌─metric───────┬──────────────────ts─┬─value─┬─moving_avg_10_days_temp─┐ │ ambient_temp │ 2020-01-01 00:00:00 │ 16 │ 16 │ │ ambient_temp │ 2020-01-01 12:00:00 │ 16 │ 16 │ │ ambient_temp │ 2020-01-02 11:00:00 │ 9 │ 12.5 │ │ ambient_temp │ 2020-01-02 12:00:00 │ 9 │ 12.5 │ │ ambient_temp │ 2020-02-01 10:00:00 │ 10 │ 10 │ │ ambient_temp │ 2020-02-01 12:00:00 │ 10 │ 10 │ │ ambient_temp │ 2020-02-10 12:00:00 │ 12 │ 11 │ │ ambient_temp │ 2020-02-10 13:00:00 │ 12 │ 11 │ │ ambient_temp │ 2020-02-20 12:00:01 │ 16 │ 13.33 │ │ ambient_temp │ 2020-03-01 12:00:00 │ 16 │ 16 │ │ ambient_temp │ 2020-03-01 12:00:00 │ 16 │ 16 │ │ ambient_temp │ 2020-03-01 12:00:00 │ 16 │ 16 │ └──────────────┴─────────────────────┴───────┴─────────────────────────┘ References GitHub Issues The roadmap for the initial support of window functions is in this issue . All GitHub issues related to window functions have the comp-window-functions tag. Tests These tests contain the examples of the currently supported grammar: https://github.com/ClickHouse/ClickHouse/blob/master/tests/performance/window_functions.xml https://github.com/ClickHouse/ClickHouse/blob/master/tests/queries/0_stateless/01591_window_functions.sql Postgres Docs https://www.postgresql.org/docs/current/sql-select.html#SQL-WINDOW https://www.postgresql.org/docs/devel/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS https://www.postgresql.org/docs/devel/functions-window.html https://www.postgresql.org/docs/devel/tutorial-window.html MySQL Docs https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html Related Content Blog: Working with time series data in ClickHouse Blog: Window and array functions for Git commit sequences Blog: Getting Data Into ClickHouse - Part 3 - Using S3
Markdown	[Skip to main content](https://clickhouse.com/docs/sql-reference/window-functions#__docusaurus_skipToContent_fallback) [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) - [Products](https://clickhouse.com/docs/sql-reference/window-functions) - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https://clickhouse.com/cloud) - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https://clickhouse.com/cloud/bring-your-own-cloud) - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https://clickhouse.com/clickhouse) - [Discover more than 100 integrations.](https://clickhouse.com/integrations) [Discover more than 100 integrations.](https://clickhouse.com/integrations) - [Use cases](https://clickhouse.com/docs/sql-reference/window-functions) - [Real-time analytics](https://clickhouse.com/use-cases/real-time-analytics) - [Machine Learning & Generative AI](https://clickhouse.com/use-cases/machine-learning-and-data-science) - [Business Intelligence](https://clickhouse.com/use-cases/data-warehousing) - [Logs, Events, Traces](https://clickhouse.com/use-cases/observability) - [All use cases](https://clickhouse.com/use-cases) [All use cases](https://clickhouse.com/use-cases) - [Documentation](https://clickhouse.com/docs) - [Resources](https://clickhouse.com/docs/sql-reference/window-functions) - [User stories](https://clickhouse.com/user-stories) - [Blog](https://clickhouse.com/blog) - [Events](https://clickhouse.com/company/events) - [Learning and certification](https://clickhouse.com/learn) - [Comparison](https://clickhouse.com/docs/sql-reference/window-functions) - [BigQuery](https://clickhouse.com/comparison/bigquery) - [PostgreSQL](https://clickhouse.com/comparison/postgresql) - [Redshift](https://clickhouse.com/comparison/redshift) - [Rockset](https://clickhouse.com/comparison/rockset) - [Snowflake](https://clickhouse.com/comparison/snowflake) - [Video](https://clickhouse.com/videos) - [Demo](https://clickhouse.com/demos) - [Pricing](https://clickhouse.com/pricing) - [Contact](https://clickhouse.com/company/contact?loc=nav) [46\.8k](https://github.com/ClickHouse/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav) [Search`Ctrl``K`](https://clickhouse.com/docs/search) [Sign in](https://console.clickhouse.cloud/signIn?loc=docs-nav-signIn-cta&glxid=ec987698-9313-4186-9781-be5fd1b64e13&pagePath=%2Fdocs%2Fsql-reference%2Fwindow-functions&origPath=%2Fdocs%2Fsql-reference%2Fwindow-functions&utm_ga=GA1.1.1971283203.1775876958) [Get started](https://console.clickhouse.cloud/signUp?loc=docs-nav-signUp-cta&glxid=ec987698-9313-4186-9781-be5fd1b64e13&pagePath=%2Fdocs%2Fsql-reference%2Fwindow-functions&origPath=%2Fdocs%2Fsql-reference%2Fwindow-functions&utm_ga=GA1.1.1971283203.1775876958) [Get started](https://clickhouse.com/docs/introduction-clickhouse) [Cloud](https://clickhouse.com/docs/cloud/overview) [Manage data](https://clickhouse.com/docs/updating-data) [Server admin](https://clickhouse.com/docs/guides/manage-and-deploy-index) [Reference](https://clickhouse.com/docs/sql-reference) [Integrations](https://clickhouse.com/docs/integrations) [ClickStack](https://clickhouse.com/docs/use-cases/observability/clickstack/overview) [chDB](https://clickhouse.com/docs/chdb) [About](https://clickhouse.com/docs/about) [Knowledge Base](https://clickhouse.com/docs/knowledgebase) [English](https://clickhouse.com/docs/sql-reference/window-functions) - [English](https://clickhouse.com/docs/sql-reference/window-functions) - [日本語](https://clickhouse.com/docs/jp/sql-reference/window-functions) - [中文](https://clickhouse.com/docs/zh/sql-reference/window-functions) - [Русский](https://clickhouse.com/docs/ru/sql-reference/window-functions) - [한국어](https://clickhouse.com/docs/ko/sql-reference/window-functions) [Skip to main content](https://clickhouse.com/docs/sql-reference/window-functions#__docusaurus_skipToContent_fallback) [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) - [Products](https://clickhouse.com/docs/sql-reference/window-functions) - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https://clickhouse.com/cloud) - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https://clickhouse.com/cloud/bring-your-own-cloud) - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https://clickhouse.com/clickhouse) - [Discover more than 100 integrations.](https://clickhouse.com/integrations) [Discover more than 100 integrations.](https://clickhouse.com/integrations) - [Use cases](https://clickhouse.com/docs/sql-reference/window-functions) - [Real-time analytics](https://clickhouse.com/use-cases/real-time-analytics) - [Machine Learning & Generative AI](https://clickhouse.com/use-cases/machine-learning-and-data-science) - [Business Intelligence](https://clickhouse.com/use-cases/data-warehousing) - [Logs, Events, Traces](https://clickhouse.com/use-cases/observability) - [All use cases](https://clickhouse.com/use-cases) [All use cases](https://clickhouse.com/use-cases) - [Documentation](https://clickhouse.com/docs) - [Resources](https://clickhouse.com/docs/sql-reference/window-functions) - [User stories](https://clickhouse.com/user-stories) - [Blog](https://clickhouse.com/blog) - [Events](https://clickhouse.com/company/events) - [Learning and certification](https://clickhouse.com/learn) - [Comparison](https://clickhouse.com/docs/sql-reference/window-functions) - [BigQuery](https://clickhouse.com/comparison/bigquery) - [PostgreSQL](https://clickhouse.com/comparison/postgresql) - [Redshift](https://clickhouse.com/comparison/redshift) - [Rockset](https://clickhouse.com/comparison/rockset) - [Snowflake](https://clickhouse.com/comparison/snowflake) - [Video](https://clickhouse.com/videos) - [Demo](https://clickhouse.com/demos) - [Pricing](https://clickhouse.com/pricing) - [Contact](https://clickhouse.com/company/contact?loc=nav) [46\.8k](https://github.com/ClickHouse/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav) [Search`Ctrl``K`](https://clickhouse.com/docs/search) [Sign in](https://console.clickhouse.cloud/signIn?loc=docs-nav-signIn-cta&glxid=ec987698-9313-4186-9781-be5fd1b64e13&pagePath=%2Fdocs%2Fsql-reference%2Fwindow-functions&origPath=%2Fdocs%2Fsql-reference%2Fwindow-functions&utm_ga=GA1.1.1971283203.1775876958) [Get started](https://console.clickhouse.cloud/signUp?loc=docs-nav-signUp-cta&glxid=ec987698-9313-4186-9781-be5fd1b64e13&pagePath=%2Fdocs%2Fsql-reference%2Fwindow-functions&origPath=%2Fdocs%2Fsql-reference%2Fwindow-functions&utm_ga=GA1.1.1971283203.1775876958) [Get started](https://clickhouse.com/docs/introduction-clickhouse) [Cloud](https://clickhouse.com/docs/cloud/overview) [Manage data](https://clickhouse.com/docs/updating-data) [Server admin](https://clickhouse.com/docs/guides/manage-and-deploy-index) [Reference](https://clickhouse.com/docs/sql-reference) [Integrations](https://clickhouse.com/docs/integrations) [ClickStack](https://clickhouse.com/docs/use-cases/observability/clickstack/overview) [chDB](https://clickhouse.com/docs/chdb) [About](https://clickhouse.com/docs/about) [Knowledge Base](https://clickhouse.com/docs/knowledgebase) [English](https://clickhouse.com/docs/sql-reference/window-functions) - [English](https://clickhouse.com/docs/sql-reference/window-functions) - [日本語](https://clickhouse.com/docs/jp/sql-reference/window-functions) - [中文](https://clickhouse.com/docs/zh/sql-reference/window-functions) - [Русский](https://clickhouse.com/docs/ru/sql-reference/window-functions) - [한국어](https://clickhouse.com/docs/ko/sql-reference/window-functions) [Search`Ctrl``K`](https://clickhouse.com/docs/search) - [Introduction](https://clickhouse.com/docs/sql-reference) - [Syntax](https://clickhouse.com/docs/sql-reference/syntax) - [Input and Output Formats](https://clickhouse.com/docs/sql-reference/formats) - [Data types](https://clickhouse.com/docs/sql-reference/data-types) - [Statements](https://clickhouse.com/docs/sql-reference/statements) - [Operators](https://clickhouse.com/docs/sql-reference/operators) - [Engines](https://clickhouse.com/docs/engines) - [Database Engines](https://clickhouse.com/docs/engines/database-engines) - [Table Engines](https://clickhouse.com/docs/engines/table-engines) - [Functions](https://clickhouse.com/docs/sql-reference/functions) - [Regular functions](https://clickhouse.com/docs/sql-reference/functions/regular-functions) - [Aggregate functions](https://clickhouse.com/docs/sql-reference/aggregate-functions) - [Table functions](https://clickhouse.com/docs/sql-reference/table-functions) - [Window functions](https://clickhouse.com/docs/sql-reference/window-functions) - [row\_number](https://clickhouse.com/docs/sql-reference/window-functions/row_number) - [first\_value](https://clickhouse.com/docs/sql-reference/window-functions/first_value) - [last\_value](https://clickhouse.com/docs/sql-reference/window-functions/last_value) - [nth\_value](https://clickhouse.com/docs/sql-reference/window-functions/nth_value) - [rank](https://clickhouse.com/docs/sql-reference/window-functions/rank) - [dense\_rank](https://clickhouse.com/docs/sql-reference/window-functions/dense_rank) - [percent\_rank](https://clickhouse.com/docs/sql-reference/window-functions/percent_rank) - [lag](https://clickhouse.com/docs/sql-reference/window-functions/lag) - [lagInFrame](https://clickhouse.com/docs/sql-reference/window-functions/lagInFrame) - [lead](https://clickhouse.com/docs/sql-reference/window-functions/lead) - [leadInFrame](https://clickhouse.com/docs/sql-reference/window-functions/leadInFrame) - [cume\_dist](https://clickhouse.com/docs/sql-reference/window-functions/cume_dist) - [Formats](https://clickhouse.com/docs/interfaces/formats) - [Data Lakes](https://clickhouse.com/docs/sql-reference/datalakes) - [Functions](https://clickhouse.com/docs/sql-reference/functions) - Window functions [Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/window-functions/index.md) # Window functions Window functions let you perform calculations across a set of rows that are related to the current row. Some of the calculations that you can do are similar to those that can be done with an aggregate function, but a window function doesn't cause rows to be grouped into a single output - the individual rows are still returned. ## Standard window functions[](https://clickhouse.com/docs/sql-reference/window-functions#standard-window-functions "Direct link to Standard window functions") ClickHouse supports the standard grammar for defining windows and window functions. The table below indicates whether a feature is currently supported. \| Feature \| Supported? \| \|---\|---\| \| ad hoc window specification (`count() over (partition by id order by time desc)`) \| ✅ \| \| expressions involving window functions, e.g. `(count() over ()) / 2)` \| ✅ \| \| `WINDOW` clause (`select ... from table window w as (partition by id)`) \| ✅ \| \| `ROWS` frame \| ✅ \| \| `RANGE` frame \| ✅ (the default) \| \| `INTERVAL` syntax for `DateTime` `RANGE OFFSET` frame \| ❌ (specify the number of seconds instead (`RANGE` works with any numeric type).) \| \| `GROUPS` frame \| ❌ \| \| Calculating aggregate functions over a frame (`sum(value) over (order by time)`) \| ✅ (All aggregate functions are supported) \| \| `rank()`, `dense_rank()`, `row_number()` \| ✅ Alias: `denseRank()` \| \| `percent_rank()` \| ✅ Efficiently computes the relative standing of a value within a partition in a dataset. This function effectively replaces the more verbose and computationally intensive manual SQL calculation expressed as `ifNull((rank() OVER(PARTITION BY x ORDER BY y) - 1) / nullif(count(1) OVER(PARTITION BY x) - 1, 0), 0)` Alias: `percentRank()` \| \| `cume_dist()` \| ✅ Computes the cumulative distribution of a value within a group of values. Returns the percentage of rows with values less than or equal to the current row's value. \| \| `lag/lead(value, offset)` \| ✅ You can also use one of the following workarounds: 1) `any(value) over (.... rows between <offset> preceding and <offset> preceding)`, or `following` for `lead` 2) `lagInFrame/leadInFrame`, which are analogous, but respect the window frame. To get behavior identical to `lag/lead`, use `rows between unbounded preceding and unbounded following` \| \| ntile(buckets) \| ✅ Specify window like, (partition by x order by y rows between unbounded preceding and unbounded following). \| ## ClickHouse-specific window functions[](https://clickhouse.com/docs/sql-reference/window-functions#clickhouse-specific-window-functions "Direct link to ClickHouse-specific window functions") There is also the following ClickHouse specific window function: ### nonNegativeDerivative(metric\_column, timestamp\_column\[, INTERVAL X UNITS\])[](https://clickhouse.com/docs/sql-reference/window-functions#nonnegativederivativemetric_column-timestamp_column-interval-x-units "Direct link to nonNegativeDerivative(metric_column, timestamp_column[, INTERVAL X UNITS])") Finds non-negative derivative for given `metric_column` by `timestamp_column`. `INTERVAL` can be omitted, default is `INTERVAL 1 SECOND`. The computed value is the following for each row: - `0` for 1st row, - metric i − metric i − 1 timestamp i − timestamp i − 1 ∗ interval {\\text{metric}\_i - \\text{metric}\_{i-1} \\over \\text{timestamp}\_i - \\text{timestamp}\_{i-1}} \* \\text{interval} timestampi−timestampi−1metrici−metrici−1∗interval for i t h i\_{th} ith row. ## Syntax[](https://clickhouse.com/docs/sql-reference/window-functions#syntax "Direct link to Syntax") ``` aggregate_function (column_name) OVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column] [ROWS or RANGE expression_to_bound_rows_within_the_group]] \| [window_name]) FROM table_name WINDOW window_name as ([ [PARTITION BY grouping_column] [ORDER BY sorting_column] [ROWS or RANGE expression_to_bound_rows_within_the_group] ]) ``` - `PARTITION BY` - defines how to break a resultset into groups. - `ORDER BY` - defines how to order rows inside the group during calculation aggregate\_function. - `ROWS or RANGE` - defines bounds of a frame, aggregate\_function is calculated within a frame. - `WINDOW` - allows multiple expressions to use the same window definition. ``` PARTITION ┌─────────────────┐ <-- UNBOUNDED PRECEDING (BEGINNING of the PARTITION) │ │ │ │ │=================│ <-- N PRECEDING <─┐ │ N ROWS │ │ F │ Before CURRENT │ │ R │~~~~~~~~~~~~~~~~~│ <-- CURRENT ROW │ A │ M ROWS │ │ M │ After CURRENT │ │ E │=================│ <-- M FOLLOWING <─┘ │ │ │ │ └─────────────────┘ <--- UNBOUNDED FOLLOWING (END of the PARTITION) ``` ### Functions[](https://clickhouse.com/docs/sql-reference/window-functions#functions "Direct link to Functions") These functions can be used only as a window function. - [`row_number()`](https://clickhouse.com/docs/sql-reference/window-functions/row_number) - Number the current row within its partition starting from 1. - [`first_value(x)`](https://clickhouse.com/docs/sql-reference/window-functions/first_value) - Return the first value evaluated within its ordered frame. - [`last_value(x)`](https://clickhouse.com/docs/sql-reference/window-functions/last_value) - Return the last value evaluated within its ordered frame. - [`nth_value(x, offset)`](https://clickhouse.com/docs/sql-reference/window-functions/nth_value) - Return the first non-NULL value evaluated against the nth row (offset) in its ordered frame. - [`rank()`](https://clickhouse.com/docs/sql-reference/window-functions/rank) - Rank the current row within its partition with gaps. - [`dense_rank()`](https://clickhouse.com/docs/sql-reference/window-functions/dense_rank) - Rank the current row within its partition without gaps. - [`lagInFrame(x)`](https://clickhouse.com/docs/sql-reference/window-functions/lagInFrame) - Return a value evaluated at the row that is at a specified physical offset row before the current row within the ordered frame. - [`leadInFrame(x)`](https://clickhouse.com/docs/sql-reference/window-functions/leadInFrame) - Return a value evaluated at the row that is offset rows after the current row within the ordered frame. ## Examples[](https://clickhouse.com/docs/sql-reference/window-functions#examples "Direct link to Examples") Let's have a look at some examples of how window functions can be used. ### Numbering rows[](https://clickhouse.com/docs/sql-reference/window-functions#numbering-rows "Direct link to Numbering rows") ``` CREATE TABLE salaries ( `team` String, `player` String, `salary` UInt32, `position` String ) Engine = Memory; INSERT INTO salaries FORMAT Values ('Port Elizabeth Barbarians', 'Gary Chen', 195000, 'F'), ('New Coreystad Archdukes', 'Charles Juarez', 190000, 'F'), ('Port Elizabeth Barbarians', 'Michael Stanley', 150000, 'D'), ('New Coreystad Archdukes', 'Scott Harrison', 150000, 'D'), ('Port Elizabeth Barbarians', 'Robert George', 195000, 'M'); ``` ``` SELECT player, salary, row_number() OVER (ORDER BY salary ASC) AS row FROM salaries; ``` ``` ┌─player──────────┬─salary─┬─row─┐ │ Michael Stanley │ 150000 │ 1 │ │ Scott Harrison │ 150000 │ 2 │ │ Charles Juarez │ 190000 │ 3 │ │ Gary Chen │ 195000 │ 4 │ │ Robert George │ 195000 │ 5 │ └─────────────────┴────────┴─────┘ ``` ``` SELECT player, salary, row_number() OVER (ORDER BY salary ASC) AS row, rank() OVER (ORDER BY salary ASC) AS rank, dense_rank() OVER (ORDER BY salary ASC) AS denseRank FROM salaries; ``` ``` ┌─player──────────┬─salary─┬─row─┬─rank─┬─denseRank─┐ │ Michael Stanley │ 150000 │ 1 │ 1 │ 1 │ │ Scott Harrison │ 150000 │ 2 │ 1 │ 1 │ │ Charles Juarez │ 190000 │ 3 │ 3 │ 2 │ │ Gary Chen │ 195000 │ 4 │ 4 │ 3 │ │ Robert George │ 195000 │ 5 │ 4 │ 3 │ └─────────────────┴────────┴─────┴──────┴───────────┘ ``` ### Aggregation functions[](https://clickhouse.com/docs/sql-reference/window-functions#aggregation-functions "Direct link to Aggregation functions") Compare each player's salary to the average for their team. ``` SELECT player, salary, team, avg(salary) OVER (PARTITION BY team) AS teamAvg, salary - teamAvg AS diff FROM salaries; ``` ``` ┌─player──────────┬─salary─┬─team──────────────────────┬─teamAvg─┬───diff─┐ │ Charles Juarez │ 190000 │ New Coreystad Archdukes │ 170000 │ 20000 │ │ Scott Harrison │ 150000 │ New Coreystad Archdukes │ 170000 │ -20000 │ │ Gary Chen │ 195000 │ Port Elizabeth Barbarians │ 180000 │ 15000 │ │ Michael Stanley │ 150000 │ Port Elizabeth Barbarians │ 180000 │ -30000 │ │ Robert George │ 195000 │ Port Elizabeth Barbarians │ 180000 │ 15000 │ └─────────────────┴────────┴───────────────────────────┴─────────┴────────┘ ``` Compare each player's salary to the maximum for their team. ``` SELECT player, salary, team, max(salary) OVER (PARTITION BY team) AS teamMax, salary - teamMax AS diff FROM salaries; ``` ``` ┌─player──────────┬─salary─┬─team──────────────────────┬─teamMax─┬───diff─┐ │ Charles Juarez │ 190000 │ New Coreystad Archdukes │ 190000 │ 0 │ │ Scott Harrison │ 150000 │ New Coreystad Archdukes │ 190000 │ -40000 │ │ Gary Chen │ 195000 │ Port Elizabeth Barbarians │ 195000 │ 0 │ │ Michael Stanley │ 150000 │ Port Elizabeth Barbarians │ 195000 │ -45000 │ │ Robert George │ 195000 │ Port Elizabeth Barbarians │ 195000 │ 0 │ └─────────────────┴────────┴───────────────────────────┴─────────┴────────┘ ``` ### Partitioning by column[](https://clickhouse.com/docs/sql-reference/window-functions#partitioning-by-column "Direct link to Partitioning by column") ``` CREATE TABLE wf_partition ( `part_key` UInt64, `value` UInt64, `order` UInt64 ) ENGINE = Memory; INSERT INTO wf_partition FORMAT Values (1,1,1), (1,2,2), (1,3,3), (2,0,0), (3,0,0); SELECT part_key, value, order, groupArray(value) OVER (PARTITION BY part_key) AS frame_values FROM wf_partition ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [1,2,3] │ <┐ │ 1 │ 2 │ 2 │ [1,2,3] │ │ 1-st group │ 1 │ 3 │ 3 │ [1,2,3] │ <┘ │ 2 │ 0 │ 0 │ [0] │ <- 2-nd group │ 3 │ 0 │ 0 │ [0] │ <- 3-d group └──────────┴───────┴───────┴──────────────┘ ``` ### Frame bounding[](https://clickhouse.com/docs/sql-reference/window-functions#frame-bounding "Direct link to Frame bounding") ``` CREATE TABLE wf_frame ( `part_key` UInt64, `value` UInt64, `order` UInt64 ) ENGINE = Memory; INSERT INTO wf_frame FORMAT Values (1,1,1), (1,2,2), (1,3,3), (1,4,4), (1,5,5); ``` ``` -- Frame is bounded by bounds of a partition (BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) SELECT part_key, value, order, groupArray(value) OVER ( PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS frame_values FROM wf_frame ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [1,2,3,4,5] │ │ 1 │ 2 │ 2 │ [1,2,3,4,5] │ │ 1 │ 3 │ 3 │ [1,2,3,4,5] │ │ 1 │ 4 │ 4 │ [1,2,3,4,5] │ │ 1 │ 5 │ 5 │ [1,2,3,4,5] │ └──────────┴───────┴───────┴──────────────┘ ``` ``` -- short form - no bound expression, no order by, -- an equalent of `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` SELECT part_key, value, order, groupArray(value) OVER (PARTITION BY part_key) AS frame_values_short, groupArray(value) OVER (PARTITION BY part_key ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS frame_values FROM wf_frame ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values_short─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [1,2,3,4,5] │ [1,2,3,4,5] │ │ 1 │ 2 │ 2 │ [1,2,3,4,5] │ [1,2,3,4,5] │ │ 1 │ 3 │ 3 │ [1,2,3,4,5] │ [1,2,3,4,5] │ │ 1 │ 4 │ 4 │ [1,2,3,4,5] │ [1,2,3,4,5] │ │ 1 │ 5 │ 5 │ [1,2,3,4,5] │ [1,2,3,4,5] │ └──────────┴───────┴───────┴────────────────────┴──────────────┘ ``` ``` -- frame is bounded by the beginning of a partition and the current row SELECT part_key, value, order, groupArray(value) OVER ( PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS frame_values FROM wf_frame ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [1] │ │ 1 │ 2 │ 2 │ [1,2] │ │ 1 │ 3 │ 3 │ [1,2,3] │ │ 1 │ 4 │ 4 │ [1,2,3,4] │ │ 1 │ 5 │ 5 │ [1,2,3,4,5] │ └──────────┴───────┴───────┴──────────────┘ ``` ``` -- short form (frame is bounded by the beginning of a partition and the current row) -- an equalent of `ORDER BY order ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` SELECT part_key, value, order, groupArray(value) OVER (PARTITION BY part_key ORDER BY order ASC) AS frame_values_short, groupArray(value) OVER (PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS frame_values FROM wf_frame ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values_short─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [1] │ [1] │ │ 1 │ 2 │ 2 │ [1,2] │ [1,2] │ │ 1 │ 3 │ 3 │ [1,2,3] │ [1,2,3] │ │ 1 │ 4 │ 4 │ [1,2,3,4] │ [1,2,3,4] │ │ 1 │ 5 │ 5 │ [1,2,3,4,5] │ [1,2,3,4,5] │ └──────────┴───────┴───────┴────────────────────┴──────────────┘ ``` ``` -- frame is bounded by the beginning of a partition and the current row, but order is backward SELECT part_key, value, order, groupArray(value) OVER (PARTITION BY part_key ORDER BY order DESC) AS frame_values FROM wf_frame ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [5,4,3,2,1] │ │ 1 │ 2 │ 2 │ [5,4,3,2] │ │ 1 │ 3 │ 3 │ [5,4,3] │ │ 1 │ 4 │ 4 │ [5,4] │ │ 1 │ 5 │ 5 │ [5] │ └──────────┴───────┴───────┴──────────────┘ ``` ``` -- sliding frame - 1 PRECEDING ROW AND CURRENT ROW SELECT part_key, value, order, groupArray(value) OVER ( PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) AS frame_values FROM wf_frame ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [1] │ │ 1 │ 2 │ 2 │ [1,2] │ │ 1 │ 3 │ 3 │ [2,3] │ │ 1 │ 4 │ 4 │ [3,4] │ │ 1 │ 5 │ 5 │ [4,5] │ └──────────┴───────┴───────┴──────────────┘ ``` ``` -- sliding frame - ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING SELECT part_key, value, order, groupArray(value) OVER ( PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING ) AS frame_values FROM wf_frame ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [1,2,3,4,5] │ │ 1 │ 2 │ 2 │ [1,2,3,4,5] │ │ 1 │ 3 │ 3 │ [2,3,4,5] │ │ 1 │ 4 │ 4 │ [3,4,5] │ │ 1 │ 5 │ 5 │ [4,5] │ └──────────┴───────┴───────┴──────────────┘ ``` ``` -- row_number does not respect the frame, so rn_1 = rn_2 = rn_3 != rn_4 SELECT part_key, value, order, groupArray(value) OVER w1 AS frame_values, row_number() OVER w1 AS rn_1, sum(1) OVER w1 AS rn_2, row_number() OVER w2 AS rn_3, sum(1) OVER w2 AS rn_4 FROM wf_frame WINDOW w1 AS (PARTITION BY part_key ORDER BY order DESC), w2 AS ( PARTITION BY part_key ORDER BY order DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values─┬─rn_1─┬─rn_2─┬─rn_3─┬─rn_4─┐ │ 1 │ 1 │ 1 │ [5,4,3,2,1] │ 5 │ 5 │ 5 │ 2 │ │ 1 │ 2 │ 2 │ [5,4,3,2] │ 4 │ 4 │ 4 │ 2 │ │ 1 │ 3 │ 3 │ [5,4,3] │ 3 │ 3 │ 3 │ 2 │ │ 1 │ 4 │ 4 │ [5,4] │ 2 │ 2 │ 2 │ 2 │ │ 1 │ 5 │ 5 │ [5] │ 1 │ 1 │ 1 │ 1 │ └──────────┴───────┴───────┴──────────────┴──────┴──────┴──────┴──────┘ ``` ``` -- first_value and last_value respect the frame SELECT groupArray(value) OVER w1 AS frame_values_1, first_value(value) OVER w1 AS first_value_1, last_value(value) OVER w1 AS last_value_1, groupArray(value) OVER w2 AS frame_values_2, first_value(value) OVER w2 AS first_value_2, last_value(value) OVER w2 AS last_value_2 FROM wf_frame WINDOW w1 AS (PARTITION BY part_key ORDER BY order ASC), w2 AS (PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) ORDER BY part_key ASC, value ASC; ┌─frame_values_1─┬─first_value_1─┬─last_value_1─┬─frame_values_2─┬─first_value_2─┬─last_value_2─┐ │ [1] │ 1 │ 1 │ [1] │ 1 │ 1 │ │ [1,2] │ 1 │ 2 │ [1,2] │ 1 │ 2 │ │ [1,2,3] │ 1 │ 3 │ [2,3] │ 2 │ 3 │ │ [1,2,3,4] │ 1 │ 4 │ [3,4] │ 3 │ 4 │ │ [1,2,3,4,5] │ 1 │ 5 │ [4,5] │ 4 │ 5 │ └────────────────┴───────────────┴──────────────┴────────────────┴───────────────┴──────────────┘ ``` ``` -- second value within the frame SELECT groupArray(value) OVER w1 AS frame_values_1, nth_value(value, 2) OVER w1 AS second_value FROM wf_frame WINDOW w1 AS (PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) ORDER BY part_key ASC, value ASC; ┌─frame_values_1─┬─second_value─┐ │ [1] │ 0 │ │ [1,2] │ 2 │ │ [1,2,3] │ 2 │ │ [1,2,3,4] │ 2 │ │ [2,3,4,5] │ 3 │ └────────────────┴──────────────┘ ``` ``` -- second value within the frame + Null for missing values SELECT groupArray(value) OVER w1 AS frame_values_1, nth_value(toNullable(value), 2) OVER w1 AS second_value FROM wf_frame WINDOW w1 AS (PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) ORDER BY part_key ASC, value ASC; ┌─frame_values_1─┬─second_value─┐ │ [1] │ ᴺᵁᴸᴸ │ │ [1,2] │ 2 │ │ [1,2,3] │ 2 │ │ [1,2,3,4] │ 2 │ │ [2,3,4,5] │ 3 │ └────────────────┴──────────────┘ ``` ## Real world examples[](https://clickhouse.com/docs/sql-reference/window-functions#real-world-examples "Direct link to Real world examples") The following examples solve common real-world problems. ### Maximum/total salary per department[](https://clickhouse.com/docs/sql-reference/window-functions#maximumtotal-salary-per-department "Direct link to Maximum/total salary per department") ``` CREATE TABLE employees ( `department` String, `employee_name` String, `salary` Float ) ENGINE = Memory; INSERT INTO employees FORMAT Values ('Finance', 'Jonh', 200), ('Finance', 'Joan', 210), ('Finance', 'Jean', 505), ('IT', 'Tim', 200), ('IT', 'Anna', 300), ('IT', 'Elen', 500); ``` ``` SELECT department, employee_name AS emp, salary, max_salary_per_dep, total_salary_per_dep, round((salary / total_salary_per_dep) * 100, 2) AS `share_per_dep(%)` FROM ( SELECT department, employee_name, salary, max(salary) OVER wndw AS max_salary_per_dep, sum(salary) OVER wndw AS total_salary_per_dep FROM employees WINDOW wndw AS ( PARTITION BY department ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) ORDER BY department ASC, employee_name ASC ); ┌─department─┬─emp──┬─salary─┬─max_salary_per_dep─┬─total_salary_per_dep─┬─share_per_dep(%)─┐ │ Finance │ Jean │ 505 │ 505 │ 915 │ 55.19 │ │ Finance │ Joan │ 210 │ 505 │ 915 │ 22.95 │ │ Finance │ Jonh │ 200 │ 505 │ 915 │ 21.86 │ │ IT │ Anna │ 300 │ 500 │ 1000 │ 30 │ │ IT │ Elen │ 500 │ 500 │ 1000 │ 50 │ │ IT │ Tim │ 200 │ 500 │ 1000 │ 20 │ └────────────┴──────┴────────┴────────────────────┴──────────────────────┴──────────────────┘ ``` ### Cumulative sum[](https://clickhouse.com/docs/sql-reference/window-functions#cumulative-sum "Direct link to Cumulative sum") ``` CREATE TABLE warehouse ( `item` String, `ts` DateTime, `value` Float ) ENGINE = Memory INSERT INTO warehouse VALUES ('sku38', '2020-01-01', 9), ('sku38', '2020-02-01', 1), ('sku38', '2020-03-01', -4), ('sku1', '2020-01-01', 1), ('sku1', '2020-02-01', 1), ('sku1', '2020-03-01', 1); ``` ``` SELECT item, ts, value, sum(value) OVER (PARTITION BY item ORDER BY ts ASC) AS stock_balance FROM warehouse ORDER BY item ASC, ts ASC; ┌─item──┬──────────────────ts─┬─value─┬─stock_balance─┐ │ sku1 │ 2020-01-01 00:00:00 │ 1 │ 1 │ │ sku1 │ 2020-02-01 00:00:00 │ 1 │ 2 │ │ sku1 │ 2020-03-01 00:00:00 │ 1 │ 3 │ │ sku38 │ 2020-01-01 00:00:00 │ 9 │ 9 │ │ sku38 │ 2020-02-01 00:00:00 │ 1 │ 10 │ │ sku38 │ 2020-03-01 00:00:00 │ -4 │ 6 │ └───────┴─────────────────────┴───────┴───────────────┘ ``` ### Moving / sliding average (per 3 rows)[](https://clickhouse.com/docs/sql-reference/window-functions#moving--sliding-average-per-3-rows "Direct link to Moving / sliding average (per 3 rows)") ``` CREATE TABLE sensors ( `metric` String, `ts` DateTime, `value` Float ) ENGINE = Memory; insert into sensors values('cpu_temp', '2020-01-01 00:00:00', 87), ('cpu_temp', '2020-01-01 00:00:01', 77), ('cpu_temp', '2020-01-01 00:00:02', 93), ('cpu_temp', '2020-01-01 00:00:03', 87), ('cpu_temp', '2020-01-01 00:00:04', 87), ('cpu_temp', '2020-01-01 00:00:05', 87), ('cpu_temp', '2020-01-01 00:00:06', 87), ('cpu_temp', '2020-01-01 00:00:07', 87); ``` ``` SELECT metric, ts, value, avg(value) OVER ( PARTITION BY metric ORDER BY ts ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_temp FROM sensors ORDER BY metric ASC, ts ASC; ┌─metric───┬──────────────────ts─┬─value─┬───moving_avg_temp─┐ │ cpu_temp │ 2020-01-01 00:00:00 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:00:01 │ 77 │ 82 │ │ cpu_temp │ 2020-01-01 00:00:02 │ 93 │ 85.66666666666667 │ │ cpu_temp │ 2020-01-01 00:00:03 │ 87 │ 85.66666666666667 │ │ cpu_temp │ 2020-01-01 00:00:04 │ 87 │ 89 │ │ cpu_temp │ 2020-01-01 00:00:05 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:00:06 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:00:07 │ 87 │ 87 │ └──────────┴─────────────────────┴───────┴───────────────────┘ ``` ### Moving / sliding average (per 10 seconds)[](https://clickhouse.com/docs/sql-reference/window-functions#moving--sliding-average-per-10-seconds "Direct link to Moving / sliding average (per 10 seconds)") ``` SELECT metric, ts, value, avg(value) OVER (PARTITION BY metric ORDER BY ts RANGE BETWEEN 10 PRECEDING AND CURRENT ROW) AS moving_avg_10_seconds_temp FROM sensors ORDER BY metric ASC, ts ASC; ┌─metric───┬──────────────────ts─┬─value─┬─moving_avg_10_seconds_temp─┐ │ cpu_temp │ 2020-01-01 00:00:00 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:01:10 │ 77 │ 77 │ │ cpu_temp │ 2020-01-01 00:02:20 │ 93 │ 93 │ │ cpu_temp │ 2020-01-01 00:03:30 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:04:40 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:05:50 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:06:00 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:07:10 │ 87 │ 87 │ └──────────┴─────────────────────┴───────┴────────────────────────────┘ ``` ### Moving / sliding average (per 10 days)[](https://clickhouse.com/docs/sql-reference/window-functions#moving--sliding-average-per-10-days "Direct link to Moving / sliding average (per 10 days)") Temperature is stored with second precision, but using `Range` and `ORDER BY toDate(ts)` we form a frame with the size of 10 units, and because of `toDate(ts)` the unit is a day. ``` CREATE TABLE sensors ( `metric` String, `ts` DateTime, `value` Float ) ENGINE = Memory; insert into sensors values('ambient_temp', '2020-01-01 00:00:00', 16), ('ambient_temp', '2020-01-01 12:00:00', 16), ('ambient_temp', '2020-01-02 11:00:00', 9), ('ambient_temp', '2020-01-02 12:00:00', 9), ('ambient_temp', '2020-02-01 10:00:00', 10), ('ambient_temp', '2020-02-01 12:00:00', 10), ('ambient_temp', '2020-02-10 12:00:00', 12), ('ambient_temp', '2020-02-10 13:00:00', 12), ('ambient_temp', '2020-02-20 12:00:01', 16), ('ambient_temp', '2020-03-01 12:00:00', 16), ('ambient_temp', '2020-03-01 12:00:00', 16), ('ambient_temp', '2020-03-01 12:00:00', 16); ``` ``` SELECT metric, ts, value, round(avg(value) OVER (PARTITION BY metric ORDER BY toDate(ts) RANGE BETWEEN 10 PRECEDING AND CURRENT ROW),2) AS moving_avg_10_days_temp FROM sensors ORDER BY metric ASC, ts ASC; ┌─metric───────┬──────────────────ts─┬─value─┬─moving_avg_10_days_temp─┐ │ ambient_temp │ 2020-01-01 00:00:00 │ 16 │ 16 │ │ ambient_temp │ 2020-01-01 12:00:00 │ 16 │ 16 │ │ ambient_temp │ 2020-01-02 11:00:00 │ 9 │ 12.5 │ │ ambient_temp │ 2020-01-02 12:00:00 │ 9 │ 12.5 │ │ ambient_temp │ 2020-02-01 10:00:00 │ 10 │ 10 │ │ ambient_temp │ 2020-02-01 12:00:00 │ 10 │ 10 │ │ ambient_temp │ 2020-02-10 12:00:00 │ 12 │ 11 │ │ ambient_temp │ 2020-02-10 13:00:00 │ 12 │ 11 │ │ ambient_temp │ 2020-02-20 12:00:01 │ 16 │ 13.33 │ │ ambient_temp │ 2020-03-01 12:00:00 │ 16 │ 16 │ │ ambient_temp │ 2020-03-01 12:00:00 │ 16 │ 16 │ │ ambient_temp │ 2020-03-01 12:00:00 │ 16 │ 16 │ └──────────────┴─────────────────────┴───────┴─────────────────────────┘ ``` ## References[](https://clickhouse.com/docs/sql-reference/window-functions#references "Direct link to References") ### GitHub Issues[](https://clickhouse.com/docs/sql-reference/window-functions#github-issues "Direct link to GitHub Issues") The roadmap for the initial support of window functions is [in this issue](https://github.com/ClickHouse/ClickHouse/issues/18097). All GitHub issues related to window functions have the [comp-window-functions](https://github.com/ClickHouse/ClickHouse/labels/comp-window-functions) tag. ### Tests[](https://clickhouse.com/docs/sql-reference/window-functions#tests "Direct link to Tests") These tests contain the examples of the currently supported grammar: <https://github.com/ClickHouse/ClickHouse/blob/master/tests/performance/window_functions.xml> <https://github.com/ClickHouse/ClickHouse/blob/master/tests/queries/0_stateless/01591_window_functions.sql> ### Postgres Docs[](https://clickhouse.com/docs/sql-reference/window-functions#postgres-docs "Direct link to Postgres Docs") <https://www.postgresql.org/docs/current/sql-select.html#SQL-WINDOW> <https://www.postgresql.org/docs/devel/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS> <https://www.postgresql.org/docs/devel/functions-window.html> <https://www.postgresql.org/docs/devel/tutorial-window.html> ### MySQL Docs[](https://clickhouse.com/docs/sql-reference/window-functions#mysql-docs "Direct link to MySQL Docs") <https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html> <https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html> <https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html> ## Related Content[](https://clickhouse.com/docs/sql-reference/window-functions#related-content "Direct link to Related Content") - Blog: [Working with time series data in ClickHouse](https://clickhouse.com/blog/working-with-time-series-data-and-functions-ClickHouse) - Blog: [Window and array functions for Git commit sequences](https://clickhouse.com/blog/clickhouse-window-array-functions-git-commits) - Blog: [Getting Data Into ClickHouse - Part 3 - Using S3](https://clickhouse.com/blog/getting-data-into-clickhouse-part-3-s3) [Previous loop](https://clickhouse.com/docs/sql-reference/table-functions/loop) [Next row\_number](https://clickhouse.com/docs/sql-reference/window-functions/row_number) - [Standard window functions](https://clickhouse.com/docs/sql-reference/window-functions#standard-window-functions) - [ClickHouse-specific window functions](https://clickhouse.com/docs/sql-reference/window-functions#clickhouse-specific-window-functions) - [nonNegativeDerivative(metric\_column, timestamp\_column\[, INTERVAL X UNITS\])](https://clickhouse.com/docs/sql-reference/window-functions#nonnegativederivativemetric_column-timestamp_column-interval-x-units) - [Syntax](https://clickhouse.com/docs/sql-reference/window-functions#syntax) - [Functions](https://clickhouse.com/docs/sql-reference/window-functions#functions) - [Examples](https://clickhouse.com/docs/sql-reference/window-functions#examples) - [Numbering rows](https://clickhouse.com/docs/sql-reference/window-functions#numbering-rows) - [Aggregation functions](https://clickhouse.com/docs/sql-reference/window-functions#aggregation-functions) - [Partitioning by column](https://clickhouse.com/docs/sql-reference/window-functions#partitioning-by-column) - [Frame bounding](https://clickhouse.com/docs/sql-reference/window-functions#frame-bounding) - [Real world examples](https://clickhouse.com/docs/sql-reference/window-functions#real-world-examples) - [Maximum/total salary per department](https://clickhouse.com/docs/sql-reference/window-functions#maximumtotal-salary-per-department) - [Cumulative sum](https://clickhouse.com/docs/sql-reference/window-functions#cumulative-sum) - [Moving / sliding average (per 3 rows)](https://clickhouse.com/docs/sql-reference/window-functions#moving--sliding-average-per-3-rows) - [Moving / sliding average (per 10 seconds)](https://clickhouse.com/docs/sql-reference/window-functions#moving--sliding-average-per-10-seconds) - [Moving / sliding average (per 10 days)](https://clickhouse.com/docs/sql-reference/window-functions#moving--sliding-average-per-10-days) - [References](https://clickhouse.com/docs/sql-reference/window-functions#references) - [GitHub Issues](https://clickhouse.com/docs/sql-reference/window-functions#github-issues) - [Tests](https://clickhouse.com/docs/sql-reference/window-functions#tests) - [Postgres Docs](https://clickhouse.com/docs/sql-reference/window-functions#postgres-docs) - [MySQL Docs](https://clickhouse.com/docs/sql-reference/window-functions#mysql-docs) - [Related Content](https://clickhouse.com/docs/sql-reference/window-functions#related-content) Was this page helpful? ###### Try ClickHouse Cloud for FREE Separation of storage and compute, automatic scaling, built-in SQL console, and lots more. \$300 in free credits when signing up. [Try it for Free](https://console.clickhouse.cloud/signUp?loc=doc-card-banner&glxid=ec987698-9313-4186-9781-be5fd1b64e13&pagePath=%2Fdocs%2Fsql-reference%2Fwindow-functions&origPath=%2Fdocs%2Fsql-reference%2Fwindow-functions&utm_ga=GA1.1.1971283203.1775876958) © 2016–2026 ClickHouse, Inc. [Trademark](https://clickhouse.com/legal/trademark-policy)·[Privacy](https://clickhouse.com/legal/privacy-policy)·[Security](https://trust.clickhouse.com/)·[Terms of Service](https://clickhouse.com/legal/agreements/terms-of-service) ![](https://static.scarf.sh/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91) © 2016–2026 ClickHouse, Inc. [Trademark](https://clickhouse.com/legal/trademark-policy)·[Privacy](https://clickhouse.com/legal/privacy-policy)·[Security](https://trust.clickhouse.com/)·[Terms of Service](https://clickhouse.com/legal/agreements/terms-of-service) ![](https://static.scarf.sh/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91) [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) EN - Get started▼ - Cloud▼ - Manage data▼ - Server admin▼ - Reference▼ - Integrations▼ - ClickStack▼ - chDB▼ - About▼ [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) EN main-menu - Introduction▼ - [Syntax](https://clickhouse.com/docs/sql-reference/syntax) - [Input and Output Formats](https://clickhouse.com/docs/sql-reference/formats) - Data types▼ - Statements▼ - Operators▼ - Engines▼ - Functions▼ - Formats▼ - [Data Lakes](https://clickhouse.com/docs/sql-reference/datalakes)
Readable Markdown	Window functions let you perform calculations across a set of rows that are related to the current row. Some of the calculations that you can do are similar to those that can be done with an aggregate function, but a window function doesn't cause rows to be grouped into a single output - the individual rows are still returned. ## Standard window functions[](https://clickhouse.com/docs/sql-reference/window-functions#standard-window-functions "Direct link to Standard window functions") ClickHouse supports the standard grammar for defining windows and window functions. The table below indicates whether a feature is currently supported. \| Feature \| Supported? \| \|---\|---\| \| ad hoc window specification (`count() over (partition by id order by time desc)`) \| ✅ \| \| expressions involving window functions, e.g. `(count() over ()) / 2)` \| ✅ \| \| `WINDOW` clause (`select ... from table window w as (partition by id)`) \| ✅ \| \| `ROWS` frame \| ✅ \| \| `RANGE` frame \| ✅ (the default) \| \| `INTERVAL` syntax for `DateTime` `RANGE OFFSET` frame \| ❌ (specify the number of seconds instead (`RANGE` works with any numeric type).) \| \| `GROUPS` frame \| ❌ \| \| Calculating aggregate functions over a frame (`sum(value) over (order by time)`) \| ✅ (All aggregate functions are supported) \| \| `rank()`, `dense_rank()`, `row_number()` \| ✅ Alias: `denseRank()` \| \| `percent_rank()` \| ✅ Efficiently computes the relative standing of a value within a partition in a dataset. This function effectively replaces the more verbose and computationally intensive manual SQL calculation expressed as `ifNull((rank() OVER(PARTITION BY x ORDER BY y) - 1) / nullif(count(1) OVER(PARTITION BY x) - 1, 0), 0)` Alias: `percentRank()` \| \| `cume_dist()` \| ✅ Computes the cumulative distribution of a value within a group of values. Returns the percentage of rows with values less than or equal to the current row's value. \| \| `lag/lead(value, offset)` \| ✅ You can also use one of the following workarounds: 1) `any(value) over (.... rows between <offset> preceding and <offset> preceding)`, or `following` for `lead` 2) `lagInFrame/leadInFrame`, which are analogous, but respect the window frame. To get behavior identical to `lag/lead`, use `rows between unbounded preceding and unbounded following` \| \| ntile(buckets) \| ✅ Specify window like, (partition by x order by y rows between unbounded preceding and unbounded following). \| ## ClickHouse-specific window functions[](https://clickhouse.com/docs/sql-reference/window-functions#clickhouse-specific-window-functions "Direct link to ClickHouse-specific window functions") There is also the following ClickHouse specific window function: ### nonNegativeDerivative(metric\_column, timestamp\_column\[, INTERVAL X UNITS\])[](https://clickhouse.com/docs/sql-reference/window-functions#nonnegativederivativemetric_column-timestamp_column-interval-x-units "Direct link to nonNegativeDerivative(metric_column, timestamp_column[, INTERVAL X UNITS])") Finds non-negative derivative for given `metric_column` by `timestamp_column`. `INTERVAL` can be omitted, default is `INTERVAL 1 SECOND`. The computed value is the following for each row: - `0` for 1st row, - metric i − metric i − 1 timestamp i − timestamp i − 1 ∗ interval {\\text{metric}\_i - \\text{metric}\_{i-1} \\over \\text{timestamp}\_i - \\text{timestamp}\_{i-1}} \* \\text{interval} for i t h i\_{th} row. ## Syntax[](https://clickhouse.com/docs/sql-reference/window-functions#syntax "Direct link to Syntax") ``` aggregate_function (column_name) OVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column] [ROWS or RANGE expression_to_bound_rows_within_the_group]] \| [window_name]) FROM table_name WINDOW window_name as ([ [PARTITION BY grouping_column] [ORDER BY sorting_column] [ROWS or RANGE expression_to_bound_rows_within_the_group] ]) ``` - `PARTITION BY` - defines how to break a resultset into groups. - `ORDER BY` - defines how to order rows inside the group during calculation aggregate\_function. - `ROWS or RANGE` - defines bounds of a frame, aggregate\_function is calculated within a frame. - `WINDOW` - allows multiple expressions to use the same window definition. ``` PARTITION ┌─────────────────┐ <-- UNBOUNDED PRECEDING (BEGINNING of the PARTITION) │ │ │ │ │=================│ <-- N PRECEDING <─┐ │ N ROWS │ │ F │ Before CURRENT │ │ R │~~~~~~~~~~~~~~~~~│ <-- CURRENT ROW │ A │ M ROWS │ │ M │ After CURRENT │ │ E │=================│ <-- M FOLLOWING <─┘ │ │ │ │ └─────────────────┘ <--- UNBOUNDED FOLLOWING (END of the PARTITION) ``` ### Functions[](https://clickhouse.com/docs/sql-reference/window-functions#functions "Direct link to Functions") These functions can be used only as a window function. - [`row_number()`](https://clickhouse.com/docs/sql-reference/window-functions/row_number) - Number the current row within its partition starting from 1. - [`first_value(x)`](https://clickhouse.com/docs/sql-reference/window-functions/first_value) - Return the first value evaluated within its ordered frame. - [`last_value(x)`](https://clickhouse.com/docs/sql-reference/window-functions/last_value) - Return the last value evaluated within its ordered frame. - [`nth_value(x, offset)`](https://clickhouse.com/docs/sql-reference/window-functions/nth_value) - Return the first non-NULL value evaluated against the nth row (offset) in its ordered frame. - [`rank()`](https://clickhouse.com/docs/sql-reference/window-functions/rank) - Rank the current row within its partition with gaps. - [`dense_rank()`](https://clickhouse.com/docs/sql-reference/window-functions/dense_rank) - Rank the current row within its partition without gaps. - [`lagInFrame(x)`](https://clickhouse.com/docs/sql-reference/window-functions/lagInFrame) - Return a value evaluated at the row that is at a specified physical offset row before the current row within the ordered frame. - [`leadInFrame(x)`](https://clickhouse.com/docs/sql-reference/window-functions/leadInFrame) - Return a value evaluated at the row that is offset rows after the current row within the ordered frame. ## Examples[](https://clickhouse.com/docs/sql-reference/window-functions#examples "Direct link to Examples") Let's have a look at some examples of how window functions can be used. ### Numbering rows[](https://clickhouse.com/docs/sql-reference/window-functions#numbering-rows "Direct link to Numbering rows") ``` CREATE TABLE salaries ( `team` String, `player` String, `salary` UInt32, `position` String ) Engine = Memory; INSERT INTO salaries FORMAT Values ('Port Elizabeth Barbarians', 'Gary Chen', 195000, 'F'), ('New Coreystad Archdukes', 'Charles Juarez', 190000, 'F'), ('Port Elizabeth Barbarians', 'Michael Stanley', 150000, 'D'), ('New Coreystad Archdukes', 'Scott Harrison', 150000, 'D'), ('Port Elizabeth Barbarians', 'Robert George', 195000, 'M'); ``` ``` SELECT player, salary, row_number() OVER (ORDER BY salary ASC) AS row FROM salaries; ``` ``` ┌─player──────────┬─salary─┬─row─┐ │ Michael Stanley │ 150000 │ 1 │ │ Scott Harrison │ 150000 │ 2 │ │ Charles Juarez │ 190000 │ 3 │ │ Gary Chen │ 195000 │ 4 │ │ Robert George │ 195000 │ 5 │ └─────────────────┴────────┴─────┘ ``` ``` SELECT player, salary, row_number() OVER (ORDER BY salary ASC) AS row, rank() OVER (ORDER BY salary ASC) AS rank, dense_rank() OVER (ORDER BY salary ASC) AS denseRank FROM salaries; ``` ``` ┌─player──────────┬─salary─┬─row─┬─rank─┬─denseRank─┐ │ Michael Stanley │ 150000 │ 1 │ 1 │ 1 │ │ Scott Harrison │ 150000 │ 2 │ 1 │ 1 │ │ Charles Juarez │ 190000 │ 3 │ 3 │ 2 │ │ Gary Chen │ 195000 │ 4 │ 4 │ 3 │ │ Robert George │ 195000 │ 5 │ 4 │ 3 │ └─────────────────┴────────┴─────┴──────┴───────────┘ ``` ### Aggregation functions[](https://clickhouse.com/docs/sql-reference/window-functions#aggregation-functions "Direct link to Aggregation functions") Compare each player's salary to the average for their team. ``` SELECT player, salary, team, avg(salary) OVER (PARTITION BY team) AS teamAvg, salary - teamAvg AS diff FROM salaries; ``` ``` ┌─player──────────┬─salary─┬─team──────────────────────┬─teamAvg─┬───diff─┐ │ Charles Juarez │ 190000 │ New Coreystad Archdukes │ 170000 │ 20000 │ │ Scott Harrison │ 150000 │ New Coreystad Archdukes │ 170000 │ -20000 │ │ Gary Chen │ 195000 │ Port Elizabeth Barbarians │ 180000 │ 15000 │ │ Michael Stanley │ 150000 │ Port Elizabeth Barbarians │ 180000 │ -30000 │ │ Robert George │ 195000 │ Port Elizabeth Barbarians │ 180000 │ 15000 │ └─────────────────┴────────┴───────────────────────────┴─────────┴────────┘ ``` Compare each player's salary to the maximum for their team. ``` SELECT player, salary, team, max(salary) OVER (PARTITION BY team) AS teamMax, salary - teamMax AS diff FROM salaries; ``` ``` ┌─player──────────┬─salary─┬─team──────────────────────┬─teamMax─┬───diff─┐ │ Charles Juarez │ 190000 │ New Coreystad Archdukes │ 190000 │ 0 │ │ Scott Harrison │ 150000 │ New Coreystad Archdukes │ 190000 │ -40000 │ │ Gary Chen │ 195000 │ Port Elizabeth Barbarians │ 195000 │ 0 │ │ Michael Stanley │ 150000 │ Port Elizabeth Barbarians │ 195000 │ -45000 │ │ Robert George │ 195000 │ Port Elizabeth Barbarians │ 195000 │ 0 │ └─────────────────┴────────┴───────────────────────────┴─────────┴────────┘ ``` ### Partitioning by column[](https://clickhouse.com/docs/sql-reference/window-functions#partitioning-by-column "Direct link to Partitioning by column") ``` CREATE TABLE wf_partition ( `part_key` UInt64, `value` UInt64, `order` UInt64 ) ENGINE = Memory; INSERT INTO wf_partition FORMAT Values (1,1,1), (1,2,2), (1,3,3), (2,0,0), (3,0,0); SELECT part_key, value, order, groupArray(value) OVER (PARTITION BY part_key) AS frame_values FROM wf_partition ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [1,2,3] │ <┐ │ 1 │ 2 │ 2 │ [1,2,3] │ │ 1-st group │ 1 │ 3 │ 3 │ [1,2,3] │ <┘ │ 2 │ 0 │ 0 │ [0] │ <- 2-nd group │ 3 │ 0 │ 0 │ [0] │ <- 3-d group └──────────┴───────┴───────┴──────────────┘ ``` ### Frame bounding[](https://clickhouse.com/docs/sql-reference/window-functions#frame-bounding "Direct link to Frame bounding") ``` CREATE TABLE wf_frame ( `part_key` UInt64, `value` UInt64, `order` UInt64 ) ENGINE = Memory; INSERT INTO wf_frame FORMAT Values (1,1,1), (1,2,2), (1,3,3), (1,4,4), (1,5,5); ``` ``` -- Frame is bounded by bounds of a partition (BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) SELECT part_key, value, order, groupArray(value) OVER ( PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS frame_values FROM wf_frame ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [1,2,3,4,5] │ │ 1 │ 2 │ 2 │ [1,2,3,4,5] │ │ 1 │ 3 │ 3 │ [1,2,3,4,5] │ │ 1 │ 4 │ 4 │ [1,2,3,4,5] │ │ 1 │ 5 │ 5 │ [1,2,3,4,5] │ └──────────┴───────┴───────┴──────────────┘ ``` ``` -- short form - no bound expression, no order by, -- an equalent of `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` SELECT part_key, value, order, groupArray(value) OVER (PARTITION BY part_key) AS frame_values_short, groupArray(value) OVER (PARTITION BY part_key ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS frame_values FROM wf_frame ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values_short─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [1,2,3,4,5] │ [1,2,3,4,5] │ │ 1 │ 2 │ 2 │ [1,2,3,4,5] │ [1,2,3,4,5] │ │ 1 │ 3 │ 3 │ [1,2,3,4,5] │ [1,2,3,4,5] │ │ 1 │ 4 │ 4 │ [1,2,3,4,5] │ [1,2,3,4,5] │ │ 1 │ 5 │ 5 │ [1,2,3,4,5] │ [1,2,3,4,5] │ └──────────┴───────┴───────┴────────────────────┴──────────────┘ ``` ``` -- frame is bounded by the beginning of a partition and the current row SELECT part_key, value, order, groupArray(value) OVER ( PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS frame_values FROM wf_frame ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [1] │ │ 1 │ 2 │ 2 │ [1,2] │ │ 1 │ 3 │ 3 │ [1,2,3] │ │ 1 │ 4 │ 4 │ [1,2,3,4] │ │ 1 │ 5 │ 5 │ [1,2,3,4,5] │ └──────────┴───────┴───────┴──────────────┘ ``` ``` -- short form (frame is bounded by the beginning of a partition and the current row) -- an equalent of `ORDER BY order ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` SELECT part_key, value, order, groupArray(value) OVER (PARTITION BY part_key ORDER BY order ASC) AS frame_values_short, groupArray(value) OVER (PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS frame_values FROM wf_frame ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values_short─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [1] │ [1] │ │ 1 │ 2 │ 2 │ [1,2] │ [1,2] │ │ 1 │ 3 │ 3 │ [1,2,3] │ [1,2,3] │ │ 1 │ 4 │ 4 │ [1,2,3,4] │ [1,2,3,4] │ │ 1 │ 5 │ 5 │ [1,2,3,4,5] │ [1,2,3,4,5] │ └──────────┴───────┴───────┴────────────────────┴──────────────┘ ``` ``` -- frame is bounded by the beginning of a partition and the current row, but order is backward SELECT part_key, value, order, groupArray(value) OVER (PARTITION BY part_key ORDER BY order DESC) AS frame_values FROM wf_frame ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [5,4,3,2,1] │ │ 1 │ 2 │ 2 │ [5,4,3,2] │ │ 1 │ 3 │ 3 │ [5,4,3] │ │ 1 │ 4 │ 4 │ [5,4] │ │ 1 │ 5 │ 5 │ [5] │ └──────────┴───────┴───────┴──────────────┘ ``` ``` -- sliding frame - 1 PRECEDING ROW AND CURRENT ROW SELECT part_key, value, order, groupArray(value) OVER ( PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) AS frame_values FROM wf_frame ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [1] │ │ 1 │ 2 │ 2 │ [1,2] │ │ 1 │ 3 │ 3 │ [2,3] │ │ 1 │ 4 │ 4 │ [3,4] │ │ 1 │ 5 │ 5 │ [4,5] │ └──────────┴───────┴───────┴──────────────┘ ``` ``` -- sliding frame - ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING SELECT part_key, value, order, groupArray(value) OVER ( PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING ) AS frame_values FROM wf_frame ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values─┐ │ 1 │ 1 │ 1 │ [1,2,3,4,5] │ │ 1 │ 2 │ 2 │ [1,2,3,4,5] │ │ 1 │ 3 │ 3 │ [2,3,4,5] │ │ 1 │ 4 │ 4 │ [3,4,5] │ │ 1 │ 5 │ 5 │ [4,5] │ └──────────┴───────┴───────┴──────────────┘ ``` ``` -- row_number does not respect the frame, so rn_1 = rn_2 = rn_3 != rn_4 SELECT part_key, value, order, groupArray(value) OVER w1 AS frame_values, row_number() OVER w1 AS rn_1, sum(1) OVER w1 AS rn_2, row_number() OVER w2 AS rn_3, sum(1) OVER w2 AS rn_4 FROM wf_frame WINDOW w1 AS (PARTITION BY part_key ORDER BY order DESC), w2 AS ( PARTITION BY part_key ORDER BY order DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) ORDER BY part_key ASC, value ASC; ┌─part_key─┬─value─┬─order─┬─frame_values─┬─rn_1─┬─rn_2─┬─rn_3─┬─rn_4─┐ │ 1 │ 1 │ 1 │ [5,4,3,2,1] │ 5 │ 5 │ 5 │ 2 │ │ 1 │ 2 │ 2 │ [5,4,3,2] │ 4 │ 4 │ 4 │ 2 │ │ 1 │ 3 │ 3 │ [5,4,3] │ 3 │ 3 │ 3 │ 2 │ │ 1 │ 4 │ 4 │ [5,4] │ 2 │ 2 │ 2 │ 2 │ │ 1 │ 5 │ 5 │ [5] │ 1 │ 1 │ 1 │ 1 │ └──────────┴───────┴───────┴──────────────┴──────┴──────┴──────┴──────┘ ``` ``` -- first_value and last_value respect the frame SELECT groupArray(value) OVER w1 AS frame_values_1, first_value(value) OVER w1 AS first_value_1, last_value(value) OVER w1 AS last_value_1, groupArray(value) OVER w2 AS frame_values_2, first_value(value) OVER w2 AS first_value_2, last_value(value) OVER w2 AS last_value_2 FROM wf_frame WINDOW w1 AS (PARTITION BY part_key ORDER BY order ASC), w2 AS (PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) ORDER BY part_key ASC, value ASC; ┌─frame_values_1─┬─first_value_1─┬─last_value_1─┬─frame_values_2─┬─first_value_2─┬─last_value_2─┐ │ [1] │ 1 │ 1 │ [1] │ 1 │ 1 │ │ [1,2] │ 1 │ 2 │ [1,2] │ 1 │ 2 │ │ [1,2,3] │ 1 │ 3 │ [2,3] │ 2 │ 3 │ │ [1,2,3,4] │ 1 │ 4 │ [3,4] │ 3 │ 4 │ │ [1,2,3,4,5] │ 1 │ 5 │ [4,5] │ 4 │ 5 │ └────────────────┴───────────────┴──────────────┴────────────────┴───────────────┴──────────────┘ ``` ``` -- second value within the frame SELECT groupArray(value) OVER w1 AS frame_values_1, nth_value(value, 2) OVER w1 AS second_value FROM wf_frame WINDOW w1 AS (PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) ORDER BY part_key ASC, value ASC; ┌─frame_values_1─┬─second_value─┐ │ [1] │ 0 │ │ [1,2] │ 2 │ │ [1,2,3] │ 2 │ │ [1,2,3,4] │ 2 │ │ [2,3,4,5] │ 3 │ └────────────────┴──────────────┘ ``` ``` -- second value within the frame + Null for missing values SELECT groupArray(value) OVER w1 AS frame_values_1, nth_value(toNullable(value), 2) OVER w1 AS second_value FROM wf_frame WINDOW w1 AS (PARTITION BY part_key ORDER BY order ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) ORDER BY part_key ASC, value ASC; ┌─frame_values_1─┬─second_value─┐ │ [1] │ ᴺᵁᴸᴸ │ │ [1,2] │ 2 │ │ [1,2,3] │ 2 │ │ [1,2,3,4] │ 2 │ │ [2,3,4,5] │ 3 │ └────────────────┴──────────────┘ ``` ## Real world examples[](https://clickhouse.com/docs/sql-reference/window-functions#real-world-examples "Direct link to Real world examples") The following examples solve common real-world problems. ### Maximum/total salary per department[](https://clickhouse.com/docs/sql-reference/window-functions#maximumtotal-salary-per-department "Direct link to Maximum/total salary per department") ``` CREATE TABLE employees ( `department` String, `employee_name` String, `salary` Float ) ENGINE = Memory; INSERT INTO employees FORMAT Values ('Finance', 'Jonh', 200), ('Finance', 'Joan', 210), ('Finance', 'Jean', 505), ('IT', 'Tim', 200), ('IT', 'Anna', 300), ('IT', 'Elen', 500); ``` ``` SELECT department, employee_name AS emp, salary, max_salary_per_dep, total_salary_per_dep, round((salary / total_salary_per_dep) * 100, 2) AS `share_per_dep(%)` FROM ( SELECT department, employee_name, salary, max(salary) OVER wndw AS max_salary_per_dep, sum(salary) OVER wndw AS total_salary_per_dep FROM employees WINDOW wndw AS ( PARTITION BY department ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) ORDER BY department ASC, employee_name ASC ); ┌─department─┬─emp──┬─salary─┬─max_salary_per_dep─┬─total_salary_per_dep─┬─share_per_dep(%)─┐ │ Finance │ Jean │ 505 │ 505 │ 915 │ 55.19 │ │ Finance │ Joan │ 210 │ 505 │ 915 │ 22.95 │ │ Finance │ Jonh │ 200 │ 505 │ 915 │ 21.86 │ │ IT │ Anna │ 300 │ 500 │ 1000 │ 30 │ │ IT │ Elen │ 500 │ 500 │ 1000 │ 50 │ │ IT │ Tim │ 200 │ 500 │ 1000 │ 20 │ └────────────┴──────┴────────┴────────────────────┴──────────────────────┴──────────────────┘ ``` ### Cumulative sum[](https://clickhouse.com/docs/sql-reference/window-functions#cumulative-sum "Direct link to Cumulative sum") ``` CREATE TABLE warehouse ( `item` String, `ts` DateTime, `value` Float ) ENGINE = Memory INSERT INTO warehouse VALUES ('sku38', '2020-01-01', 9), ('sku38', '2020-02-01', 1), ('sku38', '2020-03-01', -4), ('sku1', '2020-01-01', 1), ('sku1', '2020-02-01', 1), ('sku1', '2020-03-01', 1); ``` ``` SELECT item, ts, value, sum(value) OVER (PARTITION BY item ORDER BY ts ASC) AS stock_balance FROM warehouse ORDER BY item ASC, ts ASC; ┌─item──┬──────────────────ts─┬─value─┬─stock_balance─┐ │ sku1 │ 2020-01-01 00:00:00 │ 1 │ 1 │ │ sku1 │ 2020-02-01 00:00:00 │ 1 │ 2 │ │ sku1 │ 2020-03-01 00:00:00 │ 1 │ 3 │ │ sku38 │ 2020-01-01 00:00:00 │ 9 │ 9 │ │ sku38 │ 2020-02-01 00:00:00 │ 1 │ 10 │ │ sku38 │ 2020-03-01 00:00:00 │ -4 │ 6 │ └───────┴─────────────────────┴───────┴───────────────┘ ``` ### Moving / sliding average (per 3 rows)[](https://clickhouse.com/docs/sql-reference/window-functions#moving--sliding-average-per-3-rows "Direct link to Moving / sliding average (per 3 rows)") ``` CREATE TABLE sensors ( `metric` String, `ts` DateTime, `value` Float ) ENGINE = Memory; insert into sensors values('cpu_temp', '2020-01-01 00:00:00', 87), ('cpu_temp', '2020-01-01 00:00:01', 77), ('cpu_temp', '2020-01-01 00:00:02', 93), ('cpu_temp', '2020-01-01 00:00:03', 87), ('cpu_temp', '2020-01-01 00:00:04', 87), ('cpu_temp', '2020-01-01 00:00:05', 87), ('cpu_temp', '2020-01-01 00:00:06', 87), ('cpu_temp', '2020-01-01 00:00:07', 87); ``` ``` SELECT metric, ts, value, avg(value) OVER ( PARTITION BY metric ORDER BY ts ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_temp FROM sensors ORDER BY metric ASC, ts ASC; ┌─metric───┬──────────────────ts─┬─value─┬───moving_avg_temp─┐ │ cpu_temp │ 2020-01-01 00:00:00 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:00:01 │ 77 │ 82 │ │ cpu_temp │ 2020-01-01 00:00:02 │ 93 │ 85.66666666666667 │ │ cpu_temp │ 2020-01-01 00:00:03 │ 87 │ 85.66666666666667 │ │ cpu_temp │ 2020-01-01 00:00:04 │ 87 │ 89 │ │ cpu_temp │ 2020-01-01 00:00:05 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:00:06 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:00:07 │ 87 │ 87 │ └──────────┴─────────────────────┴───────┴───────────────────┘ ``` ### Moving / sliding average (per 10 seconds)[](https://clickhouse.com/docs/sql-reference/window-functions#moving--sliding-average-per-10-seconds "Direct link to Moving / sliding average (per 10 seconds)") ``` SELECT metric, ts, value, avg(value) OVER (PARTITION BY metric ORDER BY ts RANGE BETWEEN 10 PRECEDING AND CURRENT ROW) AS moving_avg_10_seconds_temp FROM sensors ORDER BY metric ASC, ts ASC; ┌─metric───┬──────────────────ts─┬─value─┬─moving_avg_10_seconds_temp─┐ │ cpu_temp │ 2020-01-01 00:00:00 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:01:10 │ 77 │ 77 │ │ cpu_temp │ 2020-01-01 00:02:20 │ 93 │ 93 │ │ cpu_temp │ 2020-01-01 00:03:30 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:04:40 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:05:50 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:06:00 │ 87 │ 87 │ │ cpu_temp │ 2020-01-01 00:07:10 │ 87 │ 87 │ └──────────┴─────────────────────┴───────┴────────────────────────────┘ ``` ### Moving / sliding average (per 10 days)[](https://clickhouse.com/docs/sql-reference/window-functions#moving--sliding-average-per-10-days "Direct link to Moving / sliding average (per 10 days)") Temperature is stored with second precision, but using `Range` and `ORDER BY toDate(ts)` we form a frame with the size of 10 units, and because of `toDate(ts)` the unit is a day. ``` CREATE TABLE sensors ( `metric` String, `ts` DateTime, `value` Float ) ENGINE = Memory; insert into sensors values('ambient_temp', '2020-01-01 00:00:00', 16), ('ambient_temp', '2020-01-01 12:00:00', 16), ('ambient_temp', '2020-01-02 11:00:00', 9), ('ambient_temp', '2020-01-02 12:00:00', 9), ('ambient_temp', '2020-02-01 10:00:00', 10), ('ambient_temp', '2020-02-01 12:00:00', 10), ('ambient_temp', '2020-02-10 12:00:00', 12), ('ambient_temp', '2020-02-10 13:00:00', 12), ('ambient_temp', '2020-02-20 12:00:01', 16), ('ambient_temp', '2020-03-01 12:00:00', 16), ('ambient_temp', '2020-03-01 12:00:00', 16), ('ambient_temp', '2020-03-01 12:00:00', 16); ``` ``` SELECT metric, ts, value, round(avg(value) OVER (PARTITION BY metric ORDER BY toDate(ts) RANGE BETWEEN 10 PRECEDING AND CURRENT ROW),2) AS moving_avg_10_days_temp FROM sensors ORDER BY metric ASC, ts ASC; ┌─metric───────┬──────────────────ts─┬─value─┬─moving_avg_10_days_temp─┐ │ ambient_temp │ 2020-01-01 00:00:00 │ 16 │ 16 │ │ ambient_temp │ 2020-01-01 12:00:00 │ 16 │ 16 │ │ ambient_temp │ 2020-01-02 11:00:00 │ 9 │ 12.5 │ │ ambient_temp │ 2020-01-02 12:00:00 │ 9 │ 12.5 │ │ ambient_temp │ 2020-02-01 10:00:00 │ 10 │ 10 │ │ ambient_temp │ 2020-02-01 12:00:00 │ 10 │ 10 │ │ ambient_temp │ 2020-02-10 12:00:00 │ 12 │ 11 │ │ ambient_temp │ 2020-02-10 13:00:00 │ 12 │ 11 │ │ ambient_temp │ 2020-02-20 12:00:01 │ 16 │ 13.33 │ │ ambient_temp │ 2020-03-01 12:00:00 │ 16 │ 16 │ │ ambient_temp │ 2020-03-01 12:00:00 │ 16 │ 16 │ │ ambient_temp │ 2020-03-01 12:00:00 │ 16 │ 16 │ └──────────────┴─────────────────────┴───────┴─────────────────────────┘ ``` ## References[](https://clickhouse.com/docs/sql-reference/window-functions#references "Direct link to References") ### GitHub Issues[](https://clickhouse.com/docs/sql-reference/window-functions#github-issues "Direct link to GitHub Issues") The roadmap for the initial support of window functions is [in this issue](https://github.com/ClickHouse/ClickHouse/issues/18097). All GitHub issues related to window functions have the [comp-window-functions](https://github.com/ClickHouse/ClickHouse/labels/comp-window-functions) tag. ### Tests[](https://clickhouse.com/docs/sql-reference/window-functions#tests "Direct link to Tests") These tests contain the examples of the currently supported grammar: <https://github.com/ClickHouse/ClickHouse/blob/master/tests/performance/window_functions.xml> <https://github.com/ClickHouse/ClickHouse/blob/master/tests/queries/0_stateless/01591_window_functions.sql> ### Postgres Docs[](https://clickhouse.com/docs/sql-reference/window-functions#postgres-docs "Direct link to Postgres Docs") <https://www.postgresql.org/docs/current/sql-select.html#SQL-WINDOW> <https://www.postgresql.org/docs/devel/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS> <https://www.postgresql.org/docs/devel/functions-window.html> <https://www.postgresql.org/docs/devel/tutorial-window.html> ### MySQL Docs[](https://clickhouse.com/docs/sql-reference/window-functions#mysql-docs "Direct link to MySQL Docs") <https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html> <https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html> <https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html> ## Related Content[](https://clickhouse.com/docs/sql-reference/window-functions#related-content "Direct link to Related Content") - Blog: [Working with time series data in ClickHouse](https://clickhouse.com/blog/working-with-time-series-data-and-functions-ClickHouse) - Blog: [Window and array functions for Git commit sequences](https://clickhouse.com/blog/clickhouse-window-array-functions-git-commits) - Blog: [Getting Data Into ClickHouse - Part 3 - Using S3](https://clickhouse.com/blog/getting-data-into-clickhouse-part-3-s3)
Shard	89 (laksa)
Root Hash	12633450985039531489
Unparsed URL	com,clickhouse!/docs/sql-reference/window-functions s443