What is the difference between percentileDisc and percentileCount in Apache Age aggregation functions?

Difference between percentileDisc and percentileCount aggregation functions in Apache AGE.

I am unable to understand the difference between percentileDisc and percentileCount aggregation functions. It says in the documentation that we use percentileDisc calculates "the nearest value to the percentile. For interpolated values, see percentileCont." Could someone please explain the difference between the two with an example. What result would these two queries give

SELECT *
FROM cypher('graph_name', $$
    MATCH (n:Person)
    RETURN percentileCont(n.age, 0.4)
$$) as (percentile_cont_age agtype);

and

SELECT *
FROM cypher('graph_name', $$
    MATCH (n:Person)
    RETURN percentileDisc(n.age, 0.5)
$$) as (percentile_disc_age agtype);

Thank you in advance!

Both percentileCont and percentileDisc functions in Apache AGE are used to calculate percentiles, but they do so in slightly different ways. Here's how:

• percentileDisc: This function calculates the discrete percentile.
  Discrete percentile does not interpolate values and will always
  return a value that is present in the given dataset. It returns the
  value below which a given percentage falls into. For example, if you
  want to find out the 50th percentile (median) of ages in your data,
  percentileDisc will return the age at the exact middle of your
  dataset (when sorted in ascending order). If there is an even number
  of data points, it will return the lower of the two middle values.

• percentileCont: This function calculates the continuous percentile.
  Unlike percentileDisc, percentileCont may interpolate between values
  in the dataset when the requested percentile lies between two data
  points. This results in a more "continuous" measure that can provide
  a more accurate picture when the dataset is large.

Let's consider an example dataset of ages: 10, 20, 30, 40, 50.

If you execute percentileDisc(n.age, 0.5), it will return 30 because 30 is the exact middle value of this dataset.

But if you execute percentileCont(n.age, 0.4), it will interpolate between 20 and 30 because the 40th percentile is not exactly on a specific data point. This results in a return value of 24 (0.6 of the way between 20 and 30).

So, the percentileDisc is usually used when you want to find an actual data point in your dataset that represents the Nth percentile, while percentileCont is used when you want to calculate the Nth percentile based on the continuous distribution of your data, even if the resulting value is not an actual data point in your dataset.

In here, you have little confusion but percentileDisc and percentileCount aggregation functions both are different. <br>

Let's assume that ages is a set of values with [10, 20, 35, 50, 60, 70].

• Calculate percentileDisc(ages, 0.5):
  If we use percentileDisc with a percentile of 0.5 (50%), it will give us the value closest to the 50th percentile.
  In this case, the 50th percentile would be the median. Since we have an even number of ages, the median is (35 + 50) / 2 = 42.5. So, using percentileDisc with a percentile of 0.5 would return 42.5.

• Calculate percentileCount(ages, 0.4): PercentileCount will count the number of values that are less than or equal to the given(such 40th) percentile.<br>
  To calculate the 40th percentile, In this case, 40% of 6 data points is 2.4. Since we need to find a value greater than or equal to 2.4, we look for the third value in the sorted list, which is 35. So, using percentileCount with a percentile of 0.4 would return the count of values less than or equal to 35, which is 3.

percentileDisc (Percentile Discrete):
The percentileDisc function returns the value at the specified percentile. It works by sorting the values in ascending order and selecting the value at the desired percentile. This means that the returned value might not be present in the original dataset.

percentileCount (Percentile Count):
The percentileCount function returns the count of values less than or equal to the specified percentile. It calculates the cumulative distribution function (CDF) and returns the number of values that fall within the percentile range.

According to the documentation, percentileDisc calculates "the nearest value to the percentile.". It returns the exact value of the nth percentile. For example, if we have a set of numbers {1, 2, 3, 4, 5} and we want to find the 50th percentile, percentileDisc will return the value 3.
On the other hand, percentileCont calculates the percentile using linear interpolation between adjacent values. For example, if we have a set of numbers {1, 2, 3, 4, 5} and we want to find the 40th percentile, percentileCont will return the value 2.5, which is the interpolated value between 2 and 3.

In the given example queries, the first query uses percentileCont to find the 40th percentile of the age of all Person nodes in the graph, while the second query uses percentileDisc to find the 50th percentile of the age of all Person nodes in the graph. The first query will return an interpolated value between two adjacent values, while the second query will return the exact value of the 50th percentile.

Using the data provided by the AGE documentation as an example,

SELECT create_graph('graph_name');

SELECT * FROM cypher('graph_name', $$
	CREATE (a:Person {name: 'A', age: 13}),
	(b:Person {name: 'B', age: 33, eyes: "blue"}),
	(c:Person {name: 'C', age: 44, eyes: "blue"}),
	(d1:Person {name: 'D', eyes: "brown"}),
	(d2:Person {name: 'D'}),
	(a)-[:KNOWS]->(b),
	(a)-[:KNOWS]->(c),
	(a)-[:KNOWS]->(d1),
	(b)-[:KNOWS]->(d2),
	(c)-[:KNOWS]->(d2)
$$) as (a agtype);

percentileCont

Running the percentileCont() function will produce an output:

SELECT *
FROM cypher('graph_name', $$
    MATCH (n:Person)
    RETURN percentileCont(n.age, 0.4)
$$) as (percentile_cont_age agtype);

 percentile_cont_age
---------------------
 29.0
(1 row)

Having taken a look at how the percentileCount() is calculated from the 'agtype.c' file, linear interpolation is calculated where,

result = y1 + [(x - x1) * (y2 - y1)] / (x2 - x1)

x = percentile * (number_of_rows - 1) - x1
x1 = floor(percentile * (number_of_rows - 1))
x2 = ceil(percentile * (number_of_rows - 1))
y1 = value_of_x1
y2 = value_of_x2

In this example, as percentile = 0.4 and number_of_rows = 3 (with ages 13, 33, and 44), this would result to:

x = 0.4 * (3 - 1) - 0 = 0.8
x1 = floor(0.4 * (3 - 1)) = floor(0.8) = 0
x2 = ceil(0.4 * (3 - 1)) = ceil(0.8) = 1
y1 = value_of_x1 = 13
y2 = value_of_x2 = 33

result = 13 + [(0.8 - 0) * (33 - 13)] / (1 - 0) = 29

Which is exactly what we got when using the percentileCont() function.

percentileDisc

Running the percentileDisc() function will produce an output:

SELECT *
FROM cypher('graph_name', $$
    MATCH (n:Person)
    RETURN percentileDisc(n.age, 0.5)
$$) as (percentile_disc_age agtype);

 percentile_disc_age
---------------------
 33.0
(1 row)

This function uses a simpler method of calculation, using a rounding method and calculating the nearest value to the percentile.

result = round_to_nearest_val(percentile * (max_val - min_val) + min_val)

In this example, as percentile = 0.5, max_val = 44, and min_val = 13 (with ages 13, 33, and 44), this would result to:

result = round_to_nearest_val(0.5 * (44 - 13) + 13) = round_to_nearest_val(28.5) = 33

Which is exactly what we got when using the percentileDisc() function.

Hope this helps!

percentileCont: calculates the percentile of a given value over a group using a value from 0.1 to 1.0.

• This uses linear interpolation method if the percentile doesn't align with a specific data point.

SELECT *
FROM cypher('graph_name', $$
    MATCH (n:Person)
    RETURN percentileCont(n.age, 0.4)
$$) as (percentile_cont_age agtype);

percentileDisc: also calculates the percentile of a given value over a group. However, it uses a rounding method to calculate the nearest value to the percentile if the percentile does not fall on a specific data point.

SELECT *
FROM cypher('graph_name', $$
    MATCH (n:Person)
    RETURN percentileDisc(n.age, 0.5)
$$) as (percentile_disc_age agtype);

Visit the Apache Age Official Documentation for more on this

Basically PercentileCont uses linear interpolation between adjacent values, while PercentileDisc returns the nearest value to the percentile without interpolation.

We use percentileDisc and percentileCount to find percentiles in a dataset. PercentileDisc produces an exact number from the dataset that corresponds to the specified percentile, whereas percentileCount gives an approximate count of values that fall below or equal the supplied percentile.

In age percentileDisc and percentileCount are 2 aggregate functions used for calculating percentiles.

percentileDisc: percentileDisc stands for percentile discrete and returns the nearest value to the specified percentile. It goes through the dataset and returns the suitable percentile value. If there is no exact match it will return the closest value.

percentileCount: The percentile Count is a function that is used to return the count of values below a certain percentile. It returns the count of the values below the percentile that was specified.

In short, percentileDisc returns the value at a certain percentile or closest to the certain percentile and percentileCount returns the count of values below the specified percentile.

According to the documentation, "percentileDisc() returns the percentile of the given value over a group, with a percentile from 0.0 to 1.0.
It uses a rounding method and calculates the nearest value to the percentile". This means that it returns the value very close to or the exact specified
percentile passed into the function for a group with odd or even number of values.

However, percentileCont() are used for interpolated values. This means that in the case of a group with even number of values, percentileCont()
considers the two values closest to the specified percentile and returns the weighted average between these two values. For a group with odd number of values, it simply just returns the exact value at that percentile just like percentileDisc().

The percentileDisc function calculates the value that represents the specified percentile in the dataset.
It selects the value at the position closest to the specified percentile rank. If there are multiple values at the same rank, the function chooses the smallest value.

The percentileCont function calculates the value at the specified percentile using linear interpolation between adjacent values.
It returns a value that lies between two data points, based on the specified percentile. This provides a more precise result when compared to percentileDisc.

Query using percentileCont:

   SELECT *
    FROM cypher('graph_name', $$
        MATCH (n:Person)
        RETURN percentileCont(n.age, 0.4)
    $$) as (percentile_cont_age agtype);

This query calculates the value at the 40th percentile using linear interpolation. The result will be a specific value from the dataset.

Query using percentileDisc:

SELECT *
FROM cypher('graph_name', $$
    MATCH (n:Person)
    RETURN percentileDisc(n.age, 0.5)
$$) as (percentile_disc_age agtype);

This query calculates the value that represents the 50th percentile using the nearest-rank method. The result will be a specific value that separates the lower 50% of ages from the upper 50% (the median).

The difference between the two is mostly that the percentileCont() function returns the average between two values if the sought percentile is between them, while the percentileDisc() function returns the value that is less than or equal to the sought percentile, in order words it rounds off to the nearest value to the percentile.

percentileDisc is a function that is used to find the value in a dataset that corresponds to a specific percentile.

For example, you have a dataset [10,20,30,40,50] and we want to find the 60th percentile using the percentileDisc function.
The result would be 30 because it corresponds to the 60th percentile in dataset.

percentileCount is a function that is used to count the values that are below or equal to a specific percentile in the dataset.

For example, you have a dataset [10,20,30,40,50] and we want to calculate its 60th percentileCount. It will be [10,20,30] because they are less than or equal to 60th percentile.

Here is what I understood about the difference between them.

Disc stands for discrete values.

Cont stands for continuous values.

percentileDisc: It uses a rounding method and calculates the nearest value to the percentile.

For example: percentileDisc(ages, 0.5) will compute exactly the 50th percentile (that is, the median) of an expression.

percentileDisc calculates the percentile based on a discrete distribution of the column values.

percentileCont: It uses a linear interpolation method, calculating a weighted average between two values if the desired percentile lies between them.

For example: percentileCont(ages, 0.5) will compute the weighted average of an expression.

percentileCont calculates the percentile based on a continuous distribution of the column values.

check out the official documentation for a better understanding.

percentileDisc helps you find the value at a particular percentile in a dataset. It gives you the actual value.

percentileCount helps you count the number of values in a dataset that are less than or equal to a specific percentile. It gives you a count, not the actual value.

Both of these are Apache AGE's aggregation features. We may state that percentileDisc() returns the percentile of the supplied number across a group, with a percentile ranging from 0.0 to 1.0, by referring to the documentation. It calculates the number that is closest to the percentile using a rounding approach. While percentileCont() delivers the value's percentile inside a group, ranging from 0.0 to 1.0. If the desired percentile falls between two values, it employs a linear interpolation approach to calculate a weighted average between the two values. seeking the closest numbers by employing a rounding technique.The Examples stated above:

SELECT *
FROM cypher('graph_name', $$
    MATCH (n:Person)
    RETURN percentileDisc(n.age, 0.5)
$$) as (percentile_disc_age agtype);

The 50th percentile of data for the property's age will be the answer in this case.

SELECT *
FROM cypher('graph_name', $$
    MATCH (n:Person)
    RETURN percentileCont(n.age, 0.4)
$$) as (percentile_cont_age agtype);

A weighted average is used to get the 40th percentile of the values in the property age. 0.4 is the median or 40th percentile in this situation.

The value at the provided percentile is returned by percentileDisc, whereas the count of values below the specified percentile is returned by percentileCount. percentileDisc provides a precise computation, whereas percentileCount is a faster estimation method.