SQL: sélectionnez l'événement le plus récent d'un certain type

Question

Ma base de données (sqlite3) comporte deux tables avec les schémas suivants :

CREATE TABLE log(d date, usr text, tag text, bytes int);
CREATE TABLE valid(tag text, filesize int);

Supposons que j'ai les données d'exemple suivantes :

      d    | usr  |  tag | bytes
--------------------------------
2012-01-19 | bob  |  foo | 990
2012-01-18 | bob  |  foo | 1000
2012-01-17 | joe  |  bar | 2000
2012-01-16 | joe  |  bar | 1800
2012-01-15 | joe  |  baz | 0

tag  | size
-----------
foo  | 1000
bar  | 2000

J'aimerais obtenir une liste des événements les plus récents qui ont conduit à un utilisateur ne pouvant pas accéder à tous les octets d'un fichier valide. Dans l'exemple ci-dessus, la sélection devrait renvoyer 2012-01-19 | bob | foo | 990.

Actuellement, j'utilise deux instructions SELECT pour obtenir les résultats. La première obtient l'événement le plus récent par utilisateur, et la seconde vérifie si tous les octets ont été consultés.

CREATE VIEW tmp AS 
SELECT * FROM log JOIN (SELECT max(d) AS maxd, usr FROM log GROUP BY usr) AS 
tmplog ON (tmplog.usr=log.usr and tmplog.maxd=log.d);

SELECT usr,d FROM tmp 
WHERE tag IN (SELECT tag FROM valid) AND bytes NOT IN (SELECT size FROM valid);

Y a-t-il un moyen de faire cela avec une seule SELECT ou de manière plus efficace ?

Mise à jour :

Dans l'exemple ci-dessus, la requête ne devrait pas récupérer l'utilisateur "joe" car sa dernière entrée de journal montre un accès complet à un fichier.

Answer 1

Trouvez les plus récents d par usr et tag (où bytes < filesize), puis sélectionnez les entrées de log correspondant à ces valeurs les plus récentes de d/usr/tag:

    SELECT log.*
      FROM log
INNER JOIN (    SELECT usr, tag, MAX(d) AS d
                  FROM log
              GROUP BY 1, 2) most_recent
        ON most_recent.usr = log.usr
            AND
           most_recent.tag = log.tag
            AND
           most_recent.d = log.d
INNER JOIN valid
        ON log.tag = valid.tag
     WHERE log.bytes < valid.filesize;

Answer 2

SELECT log.* FROM
  log
  JOIN
  (SELECT usr,tag,MAX(d)
   FROM log
   GROUP BY usr,tag) log2
  USING(usr,tag)
  JOIN valid ON (log.tag = valid.tag)
  WHERE d=`max(d)` AND bytes != valid.filesize

Answer 3

Sélectionner
log.*
De log
Intérieur Joindre valable Sur valable.tag = log.tag
Où log.octets < valable.taille Trier Par log.d Desc

Devrait fonctionner, mais ne semble pas très efficace pour moi.

C'est légèrement différent en ce sens qu'il retournera plusieurs lignes par utilisateur si le même utilisateur a eu plus d'un problème - est-ce que vous ne voulez spécifiquement pas cela ?

Answer 4

La suggestion la plus proche que je puisse faire jusqu'à présent :

1. Joindre log et valid pour obtenir toutes les lignes où bytes <= size et pour trouver MAX(d) par tag.

2. Joindre l'ensemble de résultats à nouveau à log sur tag & d pour obtenir les lignes entières correspondant aux événements correspondants.

3. Joindre à nouveau à valid pour obtenir uniquement les lignes où bytes < size. Il est nécessaire de filtrer les dates max où les octets correspondent à la taille.

Voici à quoi pourrait ressembler la requête :

SELECT
  log.*
FROM log
  INNER JOIN valid ON log.tag = valid.tag AND log.bytes < valid.size
  INNER JOIN (
    SELECT
      log.tag,
      MAX(log.d) AS d
    FROM log
      INNER JOIN valid ON log.tag = valid.tag AND log.bytes <= valid.size
    GROUP BY
      log.tag
  ) last
    ON log.tag = last.tag AND log.d = last.d

Ce qui précède suppose qu'à une date donnée, il ne peut y avoir qu'un seul événement pour un tag particulier. Sinon, le schéma présenté dans la question ne permet pas de déterminer lequel des événements était le plus récent s'il y en a eu plusieurs le même jour.