Comment obtenir le premier et le dernier élément par appareil ?

Question

J'essaie de trouver une réponse à la question de savoir quel est le moyen le plus efficace d'obtenir le premier élément et le dernier élément dans un intervalle de temps donné. J'ai un tableau interval_data (y compris les données iot) qui a une relation avec device table. Je veux obtenir un résultat pour le premier et le dernier élément de chaque appareil.

Table de données d'intervalles :

    id           device_id          created_at           value
    15269665      1000206      2018-07-21 00:10:00    5099.550000
    15270533      1000206      2018-07-21 00:20:00    5099.610000
    15271400      1000206      2018-07-21 00:30:00    5099.760000
    15272269      1000206      2018-07-21 00:40:00    5099.850000
    15273132      1000206      2018-07-21 00:50:00    5099.910000
    15274040      1000206      2018-07-21 01:00:00    5099.970000
    15274909      1000206      2018-07-21 01:10:00    5100.030000
    15275761      1000206      2018-07-21 01:20:00    5100.110000
    15276629      1000206      2018-07-21 01:30:00    5100.160000
    15277527      1000206      2018-07-21 01:40:00    5100.340000
    15278351      1000206      2018-07-21 01:50:00    5100.400000
    15279219      1000206      2018-07-21 02:00:00    5100.450000
    15280085      1000206      2018-07-21 02:10:00    5100.530000
    15280954      1000206      2018-07-21 02:20:00    5100.590000
    15281858      1000206      2018-07-21 02:30:00    5100.640000
    15282724      1000206      2018-07-21 02:40:00    5100.750000
    15283627      1000206      2018-07-21 02:50:00    5100.870000
    15284495      1000206      2018-07-21 03:00:00    5100.930000
      ...           ...                ...                ...

J'ai essayé des requêtes comme :

select created_at, value from interval_data i inner join
(select min(created_at) minin, max(created_at) maxin, d.device_id from device 
d
inner join interval_data i on i.device_id = d.device_id
where d.device_id in (1000022, 1000023, 1000024)
and i.created_at between '2018-01-01 00:00:00' and '2019-01-01 00:00:00' 
group by d.device_id) s
on s.device_id = i.device_id and (s.minin = i.created_at or s.maxin = 
i.created_at)

Mais lorsque le nombre de dispositifs augmente, le temps de réponse prend beaucoup de temps. Avez-vous une suggestion à faire ? Comment puis-je trouver plus rapidement le premier et le dernier élément de chaque appareil ?

Answer 1

La requête la plus efficace dépend des détails de votre installation. Vous pouvez vous baser sur une table existante device et mentionner beaucoup de dispositifs et afficher beaucoup de données d'intervalle par dispositif. Donc généralement une requête avec deux LATERAL les sous-requêtes devraient être les plus rapides :

SELECT *  -- or just the columns you need
FROM device d
LEFT JOIN LATERAL (
   SELECT id AS first_intv_id, created_at AS first_created_at, value AS first_value
   FROM   interval_data
   WHERE  device_id = d.id
   ORDER  BY created_at
   LIMIT  1
   ) f ON true
LEFT JOIN LATERAL (
   SELECT id AS last_intv_id, created_at AS last_created_at, value AS last_value
   FROM   interval_data
   WHERE  device_id = d.id
   ORDER  BY created_at DESC  -- NULLS LAST if column isn't NOT NULL
   LIMIT  1
   ) l ON true;

Postgres peut traduire cela en un plan de requête ne comportant que des balayages d'index rapides sur la grande table. interval_data .

À propos de LATERAL :

• Quelle est la différence entre LATERAL et une sous-requête dans PostgreSQL ?

Assurez-vous d'avoir un indice sur interval_data(device_id, created_at) . Si vous n'avez besoin que d'un ensemble limité de colonnes dans le résultat, il peut être utile d'ajouter d'autres colonnes à cet index pour obtenir Index seulement des scans.

LEFT JOIN ... ON true conserve les appareils sans données d'intervalle dans le résultat.

Pour restreindre à un ensemble donné d'identifiants de dispositifs, ajoutez-les à la requête :

...
WHERE  d.id IN (1000022, 1000023, 1000024);

Et avoir un index sur device(id) - ce qui serait de toute façon le cas typique.

En supposant que la version actuelle de Postgres et une configuration comme ça :

CREATE TABLE device (
   id     serial PRIMARY KEY
 , device text NOT NULL
);

CREATE TABLE interval_data (
   id         serial PRIMARY KEY
 , device_id  int NOT NULL
 , created_at timestamp NOT NULL
 , value      numeric NOT NULL
 , CONSTRAINT device_fkey FOREIGN KEY (device_id) REFERENCES device (id)
);

Si certaines colonnes impliquées ne sont pas définies NOT NULL vous devrez peut-être ajuster les détails.

La contrainte FK est facultative pour cette solution.

Explication détaillée et discussion des alternatives :

• Sélectionner la première ligne dans chaque groupe GROUP BY ?
• Optimiser la requête GROUP BY pour récupérer le dernier enregistrement par utilisateur
• PostgreSQL : calcul du nombre de lignes pour une requête 'par minute'.

Alternative pour un petit ensemble d'identifiants de dispositifs donnés

Si vous êtes à l'aise avec fonctions de fenêtre à l'aide de cadres de fenêtre personnalisés cette alternative ne nécessite pas de tableau supplémentaire device et pourrait être plus rapide pour un petit ensemble d'identifiants :

SELECT DISTINCT ON (device_id)
       device_id
     , first_value(created_at) OVER w AS first_created_at
     , first_value(value)      OVER w AS first_value
     , last_value (created_at) OVER w AS last_created_at
     , last_value (value)      OVER w AS last_value
FROM   interval_data
WHERE  device_id IN (1000022, 1000023, 1000024)
WINDOW w AS (PARTITION BY device_id ORDER BY created_at
             RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

Même chose que dans la première requête ci-dessus :

• Aucun résultat pour les identifiants de périphériques passés qui n'existent pas.

Mais différents de la première requête ci-dessus :

• Aucun résultat pour les ID de dispositifs passés qui hacer existent, mais sans aucune donnée d'intervalle.

À propos des cadres de fenêtres :

• Requête PostgreSQL avec les dates max et min et l'identifiant associé par ligne
• Comment utiliser une structure de données en anneau dans des fonctions de fenêtre

_db<>fidèle aquí_

Answer 2

Vous pouvez utiliser row_number pour attribuer un nombre croissant à chaque rangée avec le même device_id . Si vous effectuez cette opération deux fois, une fois en ordre croissant et une fois en ordre décroissant, vous pouvez saisir la première et la dernière ligne par groupe :

select  device_id
,       created_at
,       value
from    (
        select  row_number() over (partition by device_id order by created_at) rn1
        ,       row_number() over (partition by device_id order by created_at desc) rn2
        ,       *
        from    interval_data
        ) i
where   device_id in (1, 3, 4)
        and (rn1 = 1 or rn2 = 1) -- First or last row per device
        and created_at between '2018-01-01 00:00:00' and '2019-01-01 00:00:00' 

Exemple chez SQL Fiddle.