{"id":7089,"date":"2018-08-30T05:54:18","date_gmt":"2018-08-30T12:54:18","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=7089"},"modified":"2024-01-04T06:40:07","modified_gmt":"2024-01-04T13:40:07","slug":"mysql-cume_dist-function","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/mysql-cume_dist-function\/","title":{"rendered":"MySQL CUME_DIST Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL CUME_DIST()<\/code> function to calculate cumulative distribution value.<\/p>\n\n\n\n

Overview of MySQL CUME_DIST()\u00a0Function<\/h2>\n\n\n\n

The CUME_DIST()<\/code> is a window function<\/a> that returns the cumulative distribution of a value within a set of values. It represents the number of rows with values less than or equal to that row’s value divided by the total number of rows.<\/p>\n\n\n\n

The returned value of the CUME_DIST()<\/code> function is greater than zero and less than or equal to one (0 < CUME_DIST()<\/code> <= 1). The repeated column values receive the same CUME_DIST()<\/code> value.<\/p>\n\n\n\n

The following shows the syntax of the CUME_DIST()<\/code> function:<\/p>\n\n\n

CUME_DIST() \nOVER (\n   PARTITION BY expr\n   ORDER BY expr [ASC | DESC]\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
PARTITION BY<\/h3>\n\n\n\n
The \u00a0PARTITION BY<\/code>\u00a0clause divides the result set into partitions to which the CUME_DIST()<\/code> function is applied independently. If you omit the PARTITION BY clause, the function is applied to the whole result set.<\/p>\n\n\n\n
ORDER BY<\/h3>\n\n\n\n
The ORDER BY<\/code> clause specifies the order of the rows in each partition or the whole result set in case the PARTITION BY<\/code> is omitted. <\/p>\n\n\n\n
The CUME_DIST()<\/code> function calculates the cumulative distribution value of each row based on its order in the partition.<\/p>\n\n\n\n
The approximate formula of the CUME_DIST()<\/code> function is as follows:<\/p>\n\n\n
ROW_NUMBER() \/ total_rows<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL CUME_DIST() function example<\/h2>\n\n\n\n
Let’s create a table<\/a> called scores<\/code> and populate some data for the demonstration:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> scores (\n    name<\/span> VARCHAR<\/span>(20<\/span>) PRIMARY KEY<\/span>,\n    score INT<\/span> NOT<\/span> NULL<\/span>\n);\n\nINSERT<\/span> INTO<\/span>\n\tscores(name<\/span>, score)\nVALUES<\/span>\n\t('Smith'<\/span>,81<\/span>),\n\t('Jones'<\/span>,55<\/span>),\n\t('Williams'<\/span>,55<\/span>),\n\t('Taylor'<\/span>,62<\/span>),\n\t('Brown'<\/span>,62<\/span>),\n\t('Davies'<\/span>,84<\/span>),\n\t('Evans'<\/span>,87<\/span>),\n\t('Wilson'<\/span>,72<\/span>),\n\t('Thomas'<\/span>,72<\/span>),\n\t('Johnson'<\/span>,100<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement uses the CUME_DIST()<\/code> function to find the cumulative distribution of the score in the result set:<\/p>\n\n\n
SELECT<\/span> \n  name<\/span>, \n  score, \n  ROW_NUMBER() OVER<\/span> (\n    ORDER<\/span> BY<\/span> \n      score\n  ) row_num, \n  CUME_DIST<\/span>() OVER<\/span> (\n    ORDER<\/span> BY<\/span> \n      score\n  ) cume_dist_val \nFROM<\/span> \n  scores;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
In this example, the score is sorted in ascending order from 55 to 100. Note that the ROW_NUMBER()<\/a><\/code> function was added for reference.<\/p>\n\n\n\n
So how does the CUME_DIST()<\/code> function perform calculation?<\/p>\n\n\n\n
In the initial step, the function identifies the number of rows in the result set where the values are less than or equal to 55. This count is found to be 2 for the first row. Next, the CUME_DIST()<\/code> function computes the cumulative distribution by dividing this count (2) by the total number of rows in the set, which is 10: 2\/10. The result is 0.2 or 20%. The same procedure is then repeated for the second row.<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
In the case of the third row, the CUME_DIST()<\/code> function identifies four rows within the result set where the values are less than or equal to 62. then, the CUME_DIST()<\/code> function computes the cumulative distribution by dividing this count (4) by the total number of rows in the set, which is 10: 4\/10. The result is 0.4 or 40%.<\/p>\n\n\n\n
The same calculation logic is applied to the remaining rows.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the MySQL CUME_DIST()<\/code> function to calculate the cumulative distribution of a value in a set of values.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t