mysql - Quartiles in SQL query

Question

I have a very simple table like that:

CREATE TABLE IF NOT EXISTS LuxLog (
  Sensor TINYINT,
  Lux INT,
  PRIMARY KEY(Sensor)
)

It contains thousands of logs from different sensors.

I would like to have Q1 and Q3 for all sensors.

I can do one query for every data, but it would be better for me to have one query for all sensors (getting Q1 and Q3 back from one query)

I though it would be a fairly simple operation, as quartiles are broadly used and one of the main statistical variables in frequency calculation. The truth is that I found loads of overcomplicated solutions, while I was hoping to find something neat and simple.

Anyone can give me a hint?

Edit: This is a piece of code that I found online, but it is not working for me:

SELECT  SUBSTRING_INDEX(
        SUBSTRING_INDEX(
            GROUP_CONCAT(                 -- 1) make a sorted list of values
                Lux
                ORDER BY Lux
                SEPARATOR ','
            )
        ,   ','                           -- 2) cut at the comma
        ,   75/100 * COUNT(*)        --    at the position beyond the 90% portion
        )
    ,   ','                               -- 3) cut at the comma
    ,   -1                                --    right after the desired list entry
    )                 AS `75th Percentile`
    FROM    LuxLog
    WHERE   Sensor=12
    AND     Lux<>0

I am getting 1 as return value, while it should be a number that can be divided by 10 (10,20,30.....1000)

Answer 1

Well to use NTILE is very simple but it is a Postgres Function. You basically just do something like this:

SELECT value_you_are_NTILING,
    NTILE(4) OVER (ORDER BY value_you_are_NTILING DESC) AS tiles
FROM
(SELECT math_that_gives_you_the_value_you_are_NTILING_here AS value_you_are_NTILING FROM tablename);

Here is a simple example I made for you on SQLFiddle: http://sqlfiddle.com/#!15/7f05a/1

In MySQL you would use RANK... Here is the SQLFiddle for that: http://www.sqlfiddle.com/#!2/d5587/1 (this comes from the Question linked below)

This use of MySQL RANK() comes from the Stackoverflow answered here: Rank function in MySQL

Look for the answer by Salman A.