Enable Box Plot with In-Database Connections

For Oracle, Microsoft SQL, and SAP HANA (these are the ones I checked) the window function percentile_cont and median exist.

SELECT DISTINCT [Category],

     PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY [Measure])

          OVER (PARTITION BY [Category]) AS q1,

     MEDIAN([Measure])

          OVER (PARTITION BY [Category]) AS median,

     PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY [Measure])

          OVER (PARTITION BY [Category]) AS q3

FROM [Table]

WHERE [Measure] IS NOT NULL

For Spark this exist as well with very little difference in syntax

SELECT DISTINCT [Category],

     PERCENTILE([Measure], 0.25) 

          OVER (PARTITION BY [Category]) AS q1,

     MEDIAN([Measure])

          OVER (PARTITION BY [Category]) AS median,

     PERCENTILE([Measure], 0.75) 

          OVER (PARTITION BY [Category]) AS q3

FROM [Table]

WHERE [Measure] IS NOT NULL

Since there is a nice, structured way to calculate values for Boxplot via SQL, a boxplot for in-database connections should be possible.

This SQL approach enables box plot for big data, pushing the calculation to the external database and no need for import of data in-memory.

In most of modern databases the SQL function PERCENTILE_CONT is supported and with this the calculation of the median and quartiles should be possible without loading all the data into in-memory. Then only the rows for the outliers needs to be loaded.

I generalized the title of this Idea so that it covers all external data sources so that users also with other databases will be interested in voting for the Idea.