Les Quartiles de requête SQL

J'ai une table très très simple comme ça:

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

Il contient des milliers de journaux à partir de différents capteurs.

Je voudrais avoir Q1 et Q3 pour tous les capteurs.

Je peux faire une requête pour chaque donnée, mais il serait mieux pour moi d'avoir une requête pour tous les capteurs (T1 et T3 arrière à partir d'une requête)

J'ai pensé que ce serait une opération assez simple, comme les quartiles sont largement utilisés et l'une des principales variables statistiques dans le calcul de fréquence. La vérité est que j'ai trouvé des charges de trop compliqué de solutions, alors que j'espérais trouver quelque chose de propre et simple.

Quelqu'un peut me donner un indice?

Edit: C'est un morceau de code que j'ai trouvé en ligne, mais il ne fonctionne pas pour moi:

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

Je suis 1 comme valeur de retour, alors que ce devrait être un nombre qui peut être divisé par 10 (10,20,30.....1000)

N'avez-vous pas poser cette question il y a quelques heures? Pas de bonnes réponses?
"J'ai pensé que ce serait une opération assez simple, comme les quartiles sont largement utilisés et l'une des principales variables statistiques relatives à la fréquence de calcul." Ce n'est pas une très bonne base pour la prévision de la facilité de la tâche. Le calcul des quartiles (et même juste médianes) est opérationnel compliqué par rapport à, disons, le calcul de moyens.
Vous avez raison, mais je me rapproche de SQL maintenant et depuis que je suis habitué à d'autres de haut niveau des langages de programmation l'absence d'un logiciel de statistiques est de me faire souffrir.
SQL n'est pas bien caractérisé comme un programmation langue. C'est une définition de données et langage de manipulation de données, basé sur un modèle de données relationnel. Bien que SQL en général et plus certaines implémentations ne présente certaines caractéristiques réalisé à la commande, le modèle sous-jacent est basé sur la (non ordonnée). Cela ne correspond pas bien avec certains types de tâches, telles que l'informatique quartiles. Cela ne signifie pas que vous pas effectuer de tels calculs en SQL, mais pour certaines tâches, vous êtes mieux de couplage SQL avec une autre langue.
MySQL en particulier se trouve être particulièrement limitée pour de telles tâches, comme il lui manque plusieurs caractéristiques communes qui rendra la tâche plus facile (les fonctions de la fenêtre, les expressions de table communes, diverses autres fonctions: même en faisant abstraction de NTILE()).

OriginalL'auteur Hamma | 2015-07-03

3

Voir SqlFiddle : http://sqlfiddle.com/#!9/accca6/2/6
Remarque : pour les sqlfiddle j'ai généré 100 lignes, chaque entier compris entre 1 et 100 est doté d'une ligne, mais c'est un ordre aléatoire (dans excel).

Voici le code :
```
SET @number_of_rows := (SELECT COUNT(*) FROM LuxLog);
SET @quartile := (ROUND(@number_of_rows*0.25));
SET @sql_q1 := (CONCAT('(SELECT "Q1" AS quartile_name , Lux, Sensor FROM LuxLog ORDER BY Lux DESC LIMIT 1 OFFSET ', @quartile,')'));
SET @sql_q3 := (CONCAT('( SELECT "Q3" AS quartile_name , Lux, Sensor FROM LuxLog ORDER BY Lux ASC LIMIT 1 OFFSET ', @quartile,');'));
SET @sql := (CONCAT(@sql_q1,' UNION ',@sql_q3));
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
```
EDIT :
```
SET @current_sensor := 101;
SET @quartile := (ROUND((SELECT COUNT(*) FROM LuxLog WHERE Sensor = @current_sensor)*0.25));
SET @sql_q1 := (CONCAT('(SELECT "Q1" AS quartile_name , Lux, Sensor FROM LuxLog WHERE Sensor=', @current_sensor,' ORDER BY Lux DESC LIMIT 1 OFFSET ', @quartile,')'));
SET @sql_q3 := (CONCAT('( SELECT "Q3" AS quartile_name , Lux, Sensor FROM LuxLog WHERE Sensor=', @current_sensor,' ORDER BY Lux ASC LIMIT 1 OFFSET ', @quartile,');'));
SET @sql := (CONCAT(@sql_q1,' UNION ',@sql_q3));
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
```
Raisonnement sous-jacent est comme suit :
Pour le quartile 1 nous voulons obtenir 25% à partir du haut, donc nous voulons savoir combien de lignes il y a, c'est :
```
SET @number_of_rows := (SELECT COUNT(*) FROM LuxLog);
```
Maintenant que nous savons le nombre de lignes, nous voulons savoir ce qui est de 25%, c'est cette ligne :
```
SET @quartile := (ROUND(@number_of_rows*0.25));
```
Puis de trouver un quartile nous voulons commander le LuxLog table par Lux, puis pour obtenir le numéro de ligne "@quartile", pour ce faire nous avons fixé le DÉCALAGE de @quartile-à-dire que nous voulons commencer notre sélectionnez le numéro de ligne @quartile et nous dire la limite de 1-à-dire que nous voulons récupérer qu'une seule ligne. C'est :
```
SET @sql_q1 := (CONCAT('(SELECT "Q1" AS quartile_name , Lux, Sensor FROM LuxLog ORDER BY Lux DESC LIMIT 1 OFFSET ', @quartile,')'));
```
Nous font (presque) la même chose pour l'autre quartile, mais plutôt que de commencer par le haut (à partir des valeurs supérieures à la plus faible) nous commençons par le bas (il explique l'ASC).

Mais pour l'instant nous avons juste les chaînes stockées dans les variables @sql_q1 et @sql_q3, de sorte que les concaténer, nous avons de l'union les résultats des requêtes, nous avons préparer la requête et de l'exécuter.

J'ai construit le Violon (sqlfiddle.com/#!9/a14a4/3) avec une petite partie de mes données (j'ai environ 50k lignes par jour). J'ai aussi ajouté "OÙ Capteur=x' depuis que j'ai besoin de données pour chaque capteur, mais je ne peux pas comprendre comment utiliser les violons. Aimeriez-vous lui donner un look?
désolé, je pensais qu'il n'y avait pas 2 lignes avec le même capteur ^^ Voici un code qui fonctionne pour un Capteur (voir édité code), je suis en train de travailler sur l'une des sorties pour tous les capteurs, je vous dis quand c'est fait.
Je vais essayer ça dès que je peux. Ne vous embêtez pas pour la sortie de tous les capteurs, comme je l'ai peut exécuter des requêtes différentes pour chaque capteur. L'important pour moi est d'obtenir les quartiles de la requête au lieu d'obtenir des milliers de raws arrière à partir d'une requête, puis calculer moi-même.
Si nous prenons la définition de w3schools par exemple (sur Google) : "La contrainte de CLÉ PRIMAIRE identifie de manière unique chaque enregistrement dans une table de base de données.". Il signifie que chaque ligne a une valeur différente dans le champ de clé primaire. Il vous permet de vous identifier de manière unique une ligne. C'est pourquoi il est étrange d'avoir la CLÉ PRIMAIRE qui est redondant. Il est plus probable une clé étrangère, cela signifie que le champ de clé étrangère dans la table des liens vers une clé primaire dans une autre table. Voir : stackoverflow.com/questions/1692538/...
Peut-être que vous n'avez pas besoin d'une clé primaire, MAIS vous avez probablement besoin d'un index (voir l'utilité de l'index : stackoverflow.com/questions/1108/... ). Il fera les résultats de votre requête plus rapide. En outre, vous ne peut tout simplement pas utiliser d'index primaire sur votre base de données puisque vous avez plusieurs lignes qui ont la même clé. Il retourne une erreur "Duplicate entry '101' for key 'PRIMARY'" quand j'ai essayé.

OriginalL'auteur Pholochtairze
2

Bien à une utilisation en NTILE est très simple, mais il est un Postgres Fonction. En gros, vous avez juste à faire quelque chose comme ceci:
```
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);
```
Voici un exemple simple que j'ai fait pour vous sur SQLFiddle: http://sqlfiddle.com/#!15/7f05a/1

Dans MySQL, vous devez utiliser RANG... Voici le SQLFiddle: http://www.sqlfiddle.com/#!2/d5587/1 (cela vient de la Question sur le lien ci-dessous)

Cette utilisation de MySQL RANG() provient de la Stackoverflow la réponse ici: Fonction rang dans MySQL

Chercher la réponse par Salman A.

Eh bien, le plus gros problème avec NTILE() pour ce travail, c'est que MySQL ne l'ont pas fait (et la question est balisé mysql).
Oui, vous avez raison. Je suis de l'utilisation de MySQL et je viens de remarquer que NTILE() n'est pas une fonction MySQL. Je suis désolé pour perdre votre temps.
Ce n'est pas une perte de temps. J'ai ajouté un lien vers la discussion de la fonction RANG dans Mysql. Que vous offre ce que vous cherchez.
J'ai essayé l'exemple sur ma table, et j'ai eu les résultats regroupés avec le grade. Je suppose que pour obtenir les quartiles-je demander à un numéro de rang totalranks*0,25 et totalranks*0.75?
Je suppose que NTILE a été ajouté pour MySQL 8

OriginalL'auteur

Quelque chose comme ça devrait le faire:

select
    ll.*,
    if (a.position is not null, 1,
        if (b.position is not null, 2, 
        if (c.position is not null, 3, 
        if (d.position is not null, 4, 0)))
    ) as quartile
from
    luxlog ll
    left outer join luxlog a on ll.position = a.position and a.lux > (select count(*)*0.00 from luxlog) and a.lux <= (select count(*)*0.25 from luxlog)
    left outer join luxlog b on ll.position = b.position and b.lux > (select count(*)*0.25 from luxlog) and b.lux <= (select count(*)*0.50 from luxlog)
    left outer join luxlog c on ll.position = c.position and c.lux > (select count(*)*0.50 from luxlog) and c.lux <= (select count(*)*0.75 from luxlog)
    left outer join luxlog d on ll.position = d.position and d.lux > (select count(*)*0.75 from luxlog)
;

Voici l'exemple complet:

use example;
drop table if exists luxlog;
CREATE TABLE LuxLog (
Sensor TINYINT,
Lux INT,
position int,
PRIMARY KEY(Position)
);
insert into luxlog values (0, 1, 10);
insert into luxlog values (0, 2, 20);
insert into luxlog values (0, 3, 30);
insert into luxlog values (0, 4, 40);
insert into luxlog values (0, 5, 50);
insert into luxlog values (0, 6, 60);
insert into luxlog values (0, 7, 70);
insert into luxlog values (0, 8, 80);
select count(*)*.25 from luxlog;
select count(*)*.50 from luxlog;
select
ll.*,
a.position,
b.position,
if(
a.position is not null, 1,
if (b.position is not null, 2, 0)
) as quartile
from
luxlog ll
left outer join luxlog a on ll.position = a.position and a.lux >= (select count(*)*0.00 from luxlog) and a.lux < (select count(*)*0.25 from luxlog)
left outer join luxlog b on ll.position = b.position and b.lux >= (select count(*)*0.25 from luxlog) and b.lux < (select count(*)*0.50 from luxlog)
left outer join luxlog c on ll.position = c.position and c.lux >= (select count(*)*0.50 from luxlog) and c.lux < (select count(*)*0.75 from luxlog)
left outer join luxlog d on ll.position = d.position and d.lux >= (select count(*)*0.75 from luxlog) and d.lux < (select count(*)*1.00 from luxlog)
;    
select
ll.*,
if (a.position is not null, 1,
if (b.position is not null, 2, 
if (c.position is not null, 3, 
if (d.position is not null, 4, 0)))
) as quartile
from
luxlog ll
left outer join luxlog a on ll.position = a.position and a.lux > (select count(*)*0.00 from luxlog) and a.lux <= (select count(*)*0.25 from luxlog)
left outer join luxlog b on ll.position = b.position and b.lux > (select count(*)*0.25 from luxlog) and b.lux <= (select count(*)*0.50 from luxlog)
left outer join luxlog c on ll.position = c.position and c.lux > (select count(*)*0.50 from luxlog) and c.lux <= (select count(*)*0.75 from luxlog)
left outer join luxlog d on ll.position = d.position and d.lux > (select count(*)*0.75 from luxlog)
;

Cette requête a pris 80 secondes à s'exécuter et il m'a donné tous les 10k lignes comme résultat. La principale raison pour laquelle je suis en train de faire c'est d'éviter d'obtenir toutes les données transférées. J'attends seulement le résultat de l'opération de retour de la sélectionner.
Édité le rang réponse que j'ai donné pour revenir juste une ligne pour chaque ntile.
Je reçois toujours une table avec des milliers de lignes à votre requête, avec 25s pour la requête avec une fraction des données que j'ai. Je ne comprends pas comment ces deux select count(*) au début, devrait aider.

OriginalL'auteur John

Ou vous pouvez utiliser le rang comme ceci:

select
ll.*,
@curRank := @curRank + 1 as rank,
if (@curRank <= (select count(*)*0.25 from luxlog), 1,
if (@curRank <= (select count(*)*0.50 from luxlog), 2, 
if (@curRank <= (select count(*)*0.75 from luxlog), 3, 4))
) as quartile
from
luxlog ll,
(SELECT @curRank := 0) r
;

Et cela donnera juste un enregistrement pour chaque quartile:

select
x.quartile, group_concat(position)
from (
select
ll.*,
@curRank := @curRank + 1 as rank,
if (@curRank > 0 and @curRank <= (select count(*)*0.25 from luxlog), 1,
if (@curRank > 0 and @curRank <= (select count(*)*0.50 from luxlog), 2, 
if (@curRank > 0 and @curRank <= (select count(*)*0.75 from luxlog), 3, 4))
) as quartile
from
luxlog ll,
(SELECT @curRank := 0) r
) x
group by quartile
+ ------------- + --------------------------- +
| quartile      | group_concat(position)      |
+ ------------- + --------------------------- +
| 1             | 10,20                       |
| 2             | 30,40                       |
| 3             | 50,60                       |
| 4             | 70,80                       |
+ ------------- + --------------------------- +
4 rows

EDIT:
Le sqlFiddle exemple (http://sqlfiddle.com/#!9/a14a4/17) ressemble à ça après c'est supprimée

/*SET @number_of_rows := (SELECT COUNT(*) FROM LuxLog);
SET @quartile := (ROUND(@number_of_rows*0.25));
SET @sql_q1 := (CONCAT('(SELECT "Q1" AS quartile_name , Lux, Sensor FROM LuxLog WHERE Sensor=101 ORDER BY Lux DESC LIMIT 1 OFFSET ', @quartile,')'));
SET @sql_q3 := (CONCAT('( SELECT "Q3" AS quartile_name , Lux, Sensor FROM LuxLog WHERE Sensor=101 ORDER BY Lux ASC LIMIT 1 OFFSET ', @quartile,');'));
SET @sql := (CONCAT(@sql_q1,' UNION ',@sql_q3));
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;*/

Les Quartiles de requête SQL

Je suis en train de le tester, mais il ne fonctionne pas. Vous pouvez voir un tableau avec un petit échantillon de mes données ici: sqlfiddle.com/#!9/a14a4/6
Si je supprime le commentaire sur le code (/* */) en haut de la sqlfiddle exemple que vous donnez, ça marche pour moi.

OriginalL'auteur John

Voici une requête je suis venu pour le calcul des quartiles; il fonctionne dans ~0.04 s w/~5000 lignes de la table. J'ai inclus les valeurs min/max, comme je suis en fin de compte à l'aide de ces données pour construire les quatre quartile des plages:

   SELECT percentile_table.percentile, avg(ColumnName) AS percentile_values
FROM   
(SELECT @rownum := @rownum + 1 AS `row_number`, 
d.ColumnName 
FROM   PercentileTestTable d, 
(SELECT @rownum := 0) r 
WHERE  ColumnName IS NOT NULL 
ORDER  BY d.ColumnName
) AS t1, 
(SELECT count(*) AS total_rows 
FROM   PercentileTestTable d 
WHERE  ColumnName IS NOT NULL 
) AS t2, 
(SELECT 0 AS percentile 
UNION ALL 
SELECT 0.25
UNION ALL 
SELECT 0.5
UNION ALL 
SELECT 0.75
UNION ALL 
SELECT 1
) AS percentile_table  
WHERE  
(percentile_table.percentile != 0 
AND percentile_table.percentile != 1 
AND t1.row_number IN 
( 
floor(( total_rows + 1 ) * percentile_table.percentile), 
floor(( total_rows + 2 ) * percentile_table.percentile)
) 
) OR (
percentile_table.percentile = 0 
AND t1.row_number = 1
) OR (
percentile_table.percentile = 1 
AND t1.row_number = total_rows
)
GROUP BY percentile_table.percentile;

Violon ici: http://sqlfiddle.com/#!9/58c0e2/1

Il y a certainement des problèmes de performances; j'aimerais, si quelqu'un a des commentaires sur la façon de l'améliorer.

Exemple de liste de données:

 3, 4, 4, 4, 7, 10, 11, 12, 14, 16, 17, 18

Exemple de requête de sortie:

| percentile | percentile_values |
|------------ | -------------------|
|          0 |                 3 |
|       0.25 |                 4 |
|        0.5 |              10.5 |
|       0.75 |                15 |
|          1 |                18 |

OriginalL'auteur icats

- Je utiliser cette solution avec une fonction MYSQL :

x est le centile vous voulez

array_values votre group_concat valeurs de l'ordre et séparés par des ,

DROP FUNCTION IF EXISTS centile;
delimiter $$
CREATE FUNCTION `centile`(x Text, array_values TEXT) RETURNS text
BEGIN
Declare DIFF_RANK TEXT;
Declare RANG_FLOOR INT;
Declare COUNT INT;
Declare VALEUR_SUP TEXT;
Declare VALEUR_INF TEXT;
SET COUNT = LENGTH(array_values) - LENGTH(REPLACE(array_values, ',', '')) + 1;
SET RANG_FLOOR = FLOOR(ROUND((x) * (COUNT-1),2));
SET DIFF_RANK = ((x) * (COUNT-1)) - FLOOR(ROUND((x) * (COUNT-1),2));
SET VALEUR_SUP = CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(array_values,',', RANG_FLOOR+2),',',-1) AS DECIMAL);
SET VALEUR_INF = CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(array_values,',', RANG_FLOOR+1),',',-1) AS DECIMAL);
/****
https://fr.wikipedia.org/wiki/Quantile
x_j+1 + g (x_j+2 - x_j+1)       
***/
RETURN  Round((VALEUR_INF + (DIFF_RANK* (VALEUR_SUP-VALEUR_INF) ) ),2);
END$$

Exemple :

Select centile(3/4,GROUP_CONCAT(lux ORDER BY lux SEPARATOR ',')) as quartile_3
FROM LuxLog
WHERE Sensor=12 AND Lux<>0

OriginalL'auteur dartaloufe

Vous devez vous connecter pour publier un commentaire.