0

I am trying to run a SQL query to find a 50th percentile in a table within a certain group, but then i am also grouping the result over the same field. Here is my query, for example over the tpch's nation table:

SELECT 
   regionkey,
   PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY regionkey)
FROM "tpch/nation"
GROUP BY regionkey
ORDER BY regionkey

Is this a valid query? I am trying to QA my code by running all kinds of different percentile queries. Postgres returns this result for the above query,:

 regionkey    | percentile_cont 
 -------------+-----------------
            0 |               0
            1 |               1
            2 |               2
            3 |               3
            4 |               4
 (5 rows)

But my question is in the real world, would someone would try to run such type of queries? I am new to aggregate functions, specially the percentile functions.

Rafay
  • 92
  • 8
  • It is a valid query in that it runs. It is not a valid query in that the results are unstable and don't really mean anything. Sample data and desired results would help. It is not clear what you are really trying to do. – Gordon Linoff Jun 01 '21 at 21:25

1 Answers1

1

You would use percentile_cont() to get a percentage of some ordered value. For instance, if you had a population column for the region, then you would calculate the median population as:

SELECT regionkey,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY population)
FROM "tpch/nation"
GROUP BY regionkey
ORDER BY regionkey;

Using regionkey in the ORDER BY is non-sensical. Within each group, regionkey has only one value. Hence, the PERCENTILE_CONT() can only return that value.

However, it can be quite useful with almost any other column.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for the explanation. That is exactly the answer i wanted to double check and verify. Even though the query is non-sensicle, it is still a valid SQL syntactically, and should return the correct results as expected. I am just trying to test my system if it supports all the valid SQL, sensicle or not. – Rafay Jun 01 '21 at 21:52