Obtenir les valeurs de la première et de la dernière ligne par groupe

Je suis nouveau sur Postgres, venant de MySQL et en espérant que l'un de y'all serait en mesure de m'aider.

J'ai un tableau avec trois colonnes: name, week, et value. Cette table a un dossier contenant les noms, la semaine au cours de laquelle ils ont enregistré la hauteur, et la valeur de leur hauteur.
Quelque chose comme ceci:

Name  |  Week  | Value
------+--------+-------
John  |  1     | 9
Cassie|  2     | 5
Luke  |  6     | 3
John  |  8     | 14
Cassie|  5     | 7
Luke  |  9     | 5
John  |  2     | 10
Cassie|  4     | 4
Luke  |  7     | 4

Ce que je veux, c'est une liste par l'utilisateur de la valeur à la semaine minimum et le maximum de la semaine. Quelque chose comme ceci:

Name  |minWeek | Value |maxWeek | value
------+--------+-------+--------+-------
John  |  1     | 9     | 8      | 14
Cassie|  2     | 5     | 5      | 7
Luke  |  6     | 3     | 9      | 5

Dans Postgres, j'utilise cette requête:

select name, week, value
from table t
inner join(
select name, min(week) as minweek
from table
group by name)
ss on t.name = ss.name and t.week = ss.minweek
group by t.name
;

Cependant, j'ai un message d'erreur:

colonne "w.semaine" doit apparaître dans la clause GROUP BY ou être utilisé dans une fonction d'agrégation
Position: 20

Cela a bien fonctionné pour moi dans MySQL donc je me demande ce que je fais mal?

Alors, comment vous attendez-vous à GROUP BY la deuxième colonne si son résultat n'est connu que APRÈS regroupement?
Cette déclaration, logiquement, n'a aucun sens. Passe de MySql vers Postgresql, vous devrez vous habituer au fait que vous ne pouvez plus faire des choses qui n'ont pas de sens.
"Cela a bien fonctionné pour moi dans MySQL donc je me demande ce que je fais de mal?" - MySQL ne gère pas de regroupement bien et va faire la mauvaise chose, sans avoir à retourner une erreur lors de la Postgres est assez intelligent pour renvoyer une erreur. Il ne fonctionne pas correctement sur MySQL, c'est de faire la mauvaise chose sans une erreur
MySql littéralement tout à fait au hasard des choses à faire si vous lui donner ces sortes de requêtes.
Dans MySQL-je obtenir ERROR 1052 (23000): Column 'name' in field list is ambiguous avec votre requête.

OriginalL'auteur user3915795 | 2014-08-06

  1. 6

    C'est un peu de douleur, parce que Postgres a nice les fonctions de la fenêtre first_value() et last_value(), mais ce ne sont pas des fonctions d'agrégation. Donc, en voici une:

    select t.name, min(t.week) as minWeek, max(firstvalue) as firstvalue,
       max(t.week) as maxWeek, max(lastvalue) as lastValue
from (select t.*, first_value(value) over (partition by name order by week) as firstvalue,
             last_value(value) over (partition by name order by week) as lastvalue
      from table t
     ) t
group by t.name;
    Qui a fait le tour! Merci beaucoup!
    Cela fonctionne, mais il n'est pas élégant, pas très sympa... Et peut-être perdu la performance (pas besoin max() comparaisons). Pourquoi PostgreSQL ne pas utiliser (ou de sa communauté aime pas) la première/dernière avec construire-dans des fonctions d'agrégation?? Il y a lib externe rapide pour le premier/dernier, un problème avec elle?
    Pensez-vous que cela mérite quelques indices pour que cela fonctionne mieux? J'ai un index par ce qui pourrait être ici " nom " et "semaine" (date dans mon cas), mais la requête est de prendre les âges de 60 millions de lignes de la table. Peut-être un index composé par le nom et la date?
    Un index sur (name, week, value) pourrait aider à la requête.
    REMARQUE: cela ne fonctionne pas nécessairement avec toutes les fonctions de la fenêtre (c'est à dire la somme) où MAX pourrait être le dernier enregistrement lorsque certaines ou toutes les valeurs sont négatives. pour y remédier, vous devrez utiliser row_number() avec la partition, puis utiliser une autre fenêtre d'obtenir la dernière ligne (la plus haute fonction row_number). l'2x Distinctes solution de erwin est mieux imo.

    OriginalL'auteur Gordon Linoff

  2. 11

    Il existe différents plus simple et plus rapide des moyens.

    2x DISTINCT ON

    SELECT *
FROM  (
   SELECT DISTINCT ON (name)
          name, week AS first_week, value AS first_val
   FROM   tbl
   ORDER  BY name, week
   ) f
JOIN (
   SELECT DISTINCT ON (name)
          name, week AS last_week, value AS last_val
   FROM   tbl
   ORDER  BY name, week DESC
   ) l USING (name);

    Ou moins:

    SELECT *
FROM  (SELECT DISTINCT ON (1) name, week AS first_week, value AS first_val
       FROM   tbl ORDER BY 1,2) f
JOIN  (SELECT DISTINCT ON (1) name, week AS last_week, value AS last_val
       FROM   tbl ORDER BY 1,2 DESC) l USING (name);

    Simple et facile à comprendre. La plus rapide dans mes tests. Explication détaillée pour DISTINCT ON:

    first_value() de type composite

    La les fonctions d'agrégation min() ou max() ne pas accepter les types de composé à l'entrée. Vous devez créer des fonctions d'agrégation (ce qui n'est pas dur).

    Mais le les fonctions de la fenêtre première_valeur() et last_value() ne. La construction que nous pouvons concevoir une solution simple:

    Simple requête

    SELECT DISTINCT ON (name)
       name, week AS first_week, value AS first_value
     ,(first_value((week, value)) OVER (PARTITION BY name
                                        ORDER BY week DESC))::text AS l
FROM   tbl t
ORDER  BY name, week;

    La sortie a toutes les données, mais les valeurs de la semaine dernière sont en peluche dans un rapport anonyme. Vous pouvez avoir besoin décomposé valeurs.

    Décomposé résultat avec l'utilisation opportuniste de type de table

    Pour cela nous avons besoin d'un type bien connu que les registres les types d'éléments contenus dans le système. Un modèle adapté de la définition de la table serait de permettre l'utilisation opportuniste de la table type lui-même directement:

    CREATE TABLE tbl (week int, value int, name text) -- note optimized column order

    weeket value venir en premier.

    SELECT (l).name, first_week, first_val
     , (l).week AS last_week, (l).value AS last_val
FROM (
   SELECT DISTINCT ON (name)
          week AS first_week, value AS first_val
         ,first_value(t) OVER (PARTITION BY name ORDER BY week DESC) AS l
   FROM   tbl t
   ORDER  BY name, week
   ) sub;

    Décomposé résultat de défini par l'utilisateur type de ligne

    Cependant, ce n'est probablement pas possible dans la plupart des cas. Il suffit d'utiliser un type défini par l'utilisateur à partir de CREATE TYPE (permanente) ou de CREATE TEMP TABLE (ad-hoc):

    CREATE TEMP TABLE nv(last_week int, last_val int);  -- register composite type

SELECT name, first_week, first_val, (l).last_week, (l).last_val
FROM (
   SELECT DISTINCT ON (name)
          name, week AS first_week, value AS first_val
         ,first_value((week, value)::nv) OVER (PARTITION BY name
                                               ORDER BY week DESC) AS l
   FROM   tbl t
   ORDER  BY name, week
   ) sub;

    Dans un local de test sur Postgresql 9.3 avec une table similaire de 50k lignes, chaque de ces requêtes a été sensiblement plus rapide que celle actuellement accepté de répondre. Test avec EXPLAIN ANALYZE.

    SQL Violon affichage de tous.

    Elle a demandé ci-dessus, mais le répéter ici pour cette réponse: pensez-vous que cela mérite quelques indices pour que cela fonctionne mieux? J'ai un index par ce qui pourrait être ici " nom " et "semaine" (date dans mon cas), mais la requête est de prendre les âges de 60 millions de lignes de la table. Peut-être un index composé par le nom et la date, dans l'ordre?
    Considérer cette réponse de discuter de la performance de "les plus grands n par le groupe" les problèmes de profondeur de: "Optimiser GROUPE PAR la requête pour récupérer le dernier enregistrement par utilisateur"> - y compris les moyens de l'optimiser et l'index.

    OriginalL'auteur Erwin Brandstetter