{"id":6941,"date":"2018-08-23T07:55:40","date_gmt":"2018-08-23T14:55:40","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=6941"},"modified":"2023-10-19T23:24:11","modified_gmt":"2023-10-20T06:24:11","slug":"mysql-window-functions","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/","title":{"rendered":"MySQL Window Functions"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about MySQL window functions and their practical applications for solving analytical query challenges.<\/p>\n\n\n\n

MySQL has supported window functions since version 8.0, allowing you to solve query problems more easily and with better performance.<\/p>\n\n\n\n

Suppose that we have the sales<\/code> table which stores the sales by employees and fiscal years:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> sales(\n    sales_employee VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n    fiscal_year INT<\/span> NOT<\/span> NULL<\/span>,\n    sale DECIMAL<\/span>(14<\/span>,2<\/span>) NOT<\/span> NULL<\/span>,\n    PRIMARY KEY<\/span>(sales_employee,fiscal_year)\n);\n\nINSERT<\/span> INTO<\/span> sales(sales_employee,fiscal_year,sale)\nVALUES<\/span>('Bob'<\/span>,2016<\/span>,100<\/span>),\n      ('Bob'<\/span>,2017<\/span>,150<\/span>),\n      ('Bob'<\/span>,2018<\/span>,200<\/span>),\n      ('Alice'<\/span>,2016<\/span>,150<\/span>),\n      ('Alice'<\/span>,2017<\/span>,100<\/span>),\n      ('Alice'<\/span>,2018<\/span>,200<\/span>),\n       ('John'<\/span>,2016<\/span>,200<\/span>),\n      ('John'<\/span>,2017<\/span>,150<\/span>),\n      ('John'<\/span>,2018<\/span>,250<\/span>);\n\nSELECT<\/span> * FROM<\/span> sales;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
It’s probably easier to understand window functions is to start with aggregate functions<\/a>.<\/p>\n\n\n\n
Aggregate functions summarize data from multiple rows into a single result row. For example, the following SUM()<\/code><\/a>function returns the total sales of all employees in the recorded years:<\/p>\n\n\n
SELECT<\/span> \n    SUM<\/span>(sale)\nFROM<\/span>\n    sales;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
The GROUP BY<\/a><\/code> clause allows you to apply aggregate functions to a subset of rows. For example, you may want to calculate the total sales by fiscal years:<\/p>\n\n\n
SELECT<\/span> \n    fiscal_year, \n    SUM<\/span>(sale)\nFROM<\/span>\n    sales\nGROUP<\/span> BY<\/span> \n    fiscal_year;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
In both examples, the aggregate functions reduce the number of rows returned by the query.<\/p>\n\n\n\n
Like the aggregate functions with the GROUP BY<\/a><\/code> clause, window functions also operate on a subset of rows but they do not reduce the number of rows returned by the query.<\/p>\n\n\n\n
For example, the following query returns the sales for each employee, along with the total sales of the employees by fiscal year:<\/p>\n\n\n
SELECT<\/span> \n    fiscal_year, \n    sales_employee,\n    sale,\n    SUM<\/span>(sale) OVER<\/span> (PARTITION<\/span> BY<\/span> fiscal_year) total_sales\nFROM<\/span>\n    sales;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
In this example, the SUM()<\/code> function works as a window function that operates on a set of rows defined by the contents of the OVER<\/code> clause. A set of rows to which the SUM()<\/code> function applies is referred to as a window.<\/p>\n\n\n\n
The SUM()<\/code> window function reports not only the total sales by fiscal year as it does in the query with the GROUP BY<\/code> clause, but also the result in each row, rather than the total number of rows returned.<\/p>\n\n\n\n
Note that window functions are performed on the result set after all JOIN<\/a><\/code>, WHERE<\/a><\/code>, GROUP BY<\/code><\/a>, and HAVING<\/a><\/code> clauses and before the ORDER BY<\/a><\/code>, LIMIT<\/code><\/a> and SELECT DISTINCT<\/a><\/code>.<\/p>\n\n\n\n
Window function syntax<\/h2>\n\n\n\n
The general syntax of calling a window function is as follows:<\/p>\n\n\n
window_function_name(expression) OVER ( \n   [partition_defintion]\n   [order_definition]\n   [frame_definition]\n)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
    \n
  • First, specify the window function name followed by an expression.<\/li>\n\n\n\n
  • Second, specify the OVER<\/code> clause which has three possible elements: partition definition, order definition, and frame definition.<\/li>\n<\/ul>\n\n\n\n
    The opening and closing parentheses, which appear after the OVER<\/code> clause, is mandatory, even with no expression, for example:<\/p>\n\n\n
    window_function_name(expression) OVER()<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    partition_clause\u00a0syntax<\/h3>\n\n\n\n
    The partition_clause<\/code> breaks up the rows into chunks or partitions. Two partitions are separated by a partition boundary.<\/p>\n\n\n\n
    The window function is performed within partitions and re-initialized when crossing the partition boundary.<\/p>\n\n\n\n
    The partition_clause<\/code> syntax looks like the following:<\/p>\n\n\n
    PARTITION BY <expression>[{,<expression>...}]<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    You can specify one or more expressions in the PARTITION BY<\/code> clause. Multiple expressions are separated by commas.<\/p>\n\n\n\n
    order_by_clause syntax<\/h3>\n\n\n\n
    The order_by_clause<\/code> has the following syntax:<\/p>\n\n\n
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The ORDER BY<\/code> clause specifies how the rows are ordered within a partition. It is possible to order data within a partition on multiple keys, each key is specified by an expression. Multiple expressions are also separated by commas.<\/p>\n\n\n\n
    Similar to the PARTITION BY<\/code> clause, the ORDER BY<\/code> clause is also supported by all the window functions. However, it only makes sense to use the ORDER BY<\/code> clause for order-sensitive window functions.<\/p>\n\n\n\n
    frame_clause syntax<\/h3>\n\n\n\n
    A frame is a subset of the current partition. To define the subset, you use the frame clause as follows:<\/p>\n\n\n
    frame_unit {<frame_start>|<frame_between>}<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    A frame is defined with respect to the current row, which allows a frame to move within a partition depending on the position of the current row within its partition.<\/p>\n\n\n\n
    The frame unit specifies the type of relationship between the current row and frame rows. It can be ROWS<\/code> or RANGE<\/code>. The offsets of the current row and frame rows are the row numbers if the frame unit is ROWS<\/code> and row values the frame unit is RANGE<\/code>.<\/p>\n\n\n\n
    The frame_start<\/code> and frame_between<\/code> define the frame boundary.<\/p>\n\n\n\n
    The frame_start<\/code> contains one of the following:<\/p>\n\n\n\n
      \n
    • UNBOUNDED PRECEDING<\/code>: frame starts at the first row of the partition.<\/li>\n\n\n\n
    • N PRECEDING<\/code>: a physical N of rows before the first current row. N can be a literal number or an expression that evaluates to a number.<\/li>\n\n\n\n
    • CURRENT ROW<\/code>: the row of the current calculation<\/li>\n<\/ul>\n\n\n\n
      The frame_between<\/code> is as follows:<\/p>\n\n\n
      BETWEEN frame_boundary_1 AND frame_boundary_2   <\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      The frame_boundary_1<\/code> and frame_boundary_2<\/code> can each contain one of the following:<\/p>\n\n\n\n
        \n
      • frame_start<\/code>: as mentioned previously.<\/li>\n\n\n\n
      • UNBOUNDED FOLLOWING<\/code>: the frame ends at the final row in the partition.<\/li>\n\n\n\n
      • N FOLLOWING<\/code>: a physical N of rows after the current row.<\/li>\n<\/ul>\n\n\n\n
        If you don’t specify the frame_definition<\/code> in the OVER<\/code> clause, then MySQL uses the following frame by default:<\/p>\n\n\n
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
        \"mysql<\/figure>\n\n\n\n
        MySQL Window Function list<\/h2>\n\n\n\n
        The following table shows the window functions in MySQL:
        \n<\/p>\n\n\n\n
        Name<\/th>Description<\/th><\/tr><\/thead>
        CUME_DIST<\/a><\/td>Calculates the cumulative distribution of a value in a set of values.<\/td><\/tr>
        DENSE_RANK<\/a><\/td>Assigns a rank to every row within its partition based on the ORDER BY<\/code> clause. It assigns the same rank to the rows with equal values. If two or more rows have the same rank, then there will be no gaps in the sequence of ranked values.<\/td><\/tr>
        FIRST_VALUE<\/a><\/td>Returns the value of the specified expression with respect to the first row in the window frame.<\/td><\/tr>
        LAG<\/a><\/td>Returns the value of the Nth row before the current row in a partition. It returns NULL if no preceding row exists.<\/td><\/tr>
        LAST_VALUE<\/a><\/td>Returns the value of the specified expression with respect to the last row in the window frame.<\/td><\/tr>
        LEAD<\/a><\/td>Returns the value of the Nth row after the current row in a partition. It returns NULL if no subsequent row exists.<\/td><\/tr>
        NTH_VALUE<\/a><\/td>Returns value of argument from Nth row of the window frame<\/td><\/tr>
        NTILE<\/a><\/td>Distributes the rows for each window partition into a specified number of ranked groups.<\/td><\/tr>
        PERCENT_RANK<\/a><\/td>Calculates the percentile rank of a row in a partition or result set<\/td><\/tr>
        RANK<\/a><\/td>Similar to the DENSE_RANK()<\/code> function except that there are gaps in the sequence of ranked values when two or more rows have the same rank.<\/td><\/tr>
        ROW_NUMBER<\/a><\/td>Assigns a sequential integer to every row within its partition<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
        In this tutorial, you have learned about the MySQL window functions and their syntax. In the next tutorials, you will learn more about each window function and its applications in more detail.<\/p>\n","protected":false},"excerpt":{"rendered":"
        This tutorial provides you with some practical and motivational examples of the window functions.<\/p>\n","protected":false},"author":2,"featured_media":10069,"parent":0,"menu_order":20,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-6941","page","type-page","status-publish","has-post-thumbnail","hentry"],"yoast_head":"\nMySQL Window Functions<\/title>\n<meta name=\"description\" content=\"In this tutorial, you will learn about MySQL window functions and their practical applications for solving analytical query challenges.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL Window Functions\" \/>\n<meta property=\"og:description\" content=\"In this tutorial, you will learn about MySQL window functions and their practical applications for solving analytical query challenges.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/\" \/>\n<meta property=\"og:site_name\" content=\"MySQL Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2023-10-20T06:24:11+00:00\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-window-functions\\\/\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-window-functions\\\/\",\"name\":\"MySQL Window Functions\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-window-functions\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-window-functions\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.mysqltutorial.org\\\/wp-content\\\/uploads\\\/2021\\\/04\\\/mysql-window-functions.svg\",\"datePublished\":\"2018-08-23T14:55:40+00:00\",\"dateModified\":\"2023-10-20T06:24:11+00:00\",\"description\":\"In this tutorial, you will learn about MySQL window functions and their practical applications for solving analytical query challenges.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-window-functions\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-window-functions\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-window-functions\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/wp-content\\\/uploads\\\/2021\\\/04\\\/mysql-window-functions.svg\",\"contentUrl\":\"https:\\\/\\\/www.mysqltutorial.org\\\/wp-content\\\/uploads\\\/2021\\\/04\\\/mysql-window-functions.svg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-window-functions\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.mysqltutorial.org\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL Window Functions\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/#website\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/\",\"name\":\"MySQL Tutorial\",\"description\":\"A comprehensive MySQL Tutorial\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.mysqltutorial.org\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MySQL Window Functions","description":"In this tutorial, you will learn about MySQL window functions and their practical applications for solving analytical query challenges.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/","og_locale":"en_US","og_type":"article","og_title":"MySQL Window Functions","og_description":"In this tutorial, you will learn about MySQL window functions and their practical applications for solving analytical query challenges.","og_url":"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/","og_site_name":"MySQL Tutorial","article_modified_time":"2023-10-20T06:24:11+00:00","twitter_misc":{"Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/","url":"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/","name":"MySQL Window Functions","isPartOf":{"@id":"https:\/\/www.mysqltutorial.org\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/#primaryimage"},"image":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/#primaryimage"},"thumbnailUrl":"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2021\/04\/mysql-window-functions.svg","datePublished":"2018-08-23T14:55:40+00:00","dateModified":"2023-10-20T06:24:11+00:00","description":"In this tutorial, you will learn about MySQL window functions and their practical applications for solving analytical query challenges.","breadcrumb":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.mysqltutorial.org\/mysql-window-functions\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/#primaryimage","url":"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2021\/04\/mysql-window-functions.svg","contentUrl":"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2021\/04\/mysql-window-functions.svg"},{"@type":"BreadcrumbList","@id":"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.mysqltutorial.org\/"},{"@type":"ListItem","position":2,"name":"MySQL Window Functions"}]},{"@type":"WebSite","@id":"https:\/\/www.mysqltutorial.org\/#website","url":"https:\/\/www.mysqltutorial.org\/","name":"MySQL Tutorial","description":"A comprehensive MySQL Tutorial","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.mysqltutorial.org\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/6941","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/comments?post=6941"}],"version-history":[{"count":3,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/6941\/revisions"}],"predecessor-version":[{"id":11472,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/6941\/revisions\/11472"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/media\/10069"}],"wp:attachment":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/media?parent=6941"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}