1


0

PostgreSQL: Comment retourner des lignes par rapport à une ligne trouvée (résultats relatifs)?

Pardonnez mon exemple si cela n’a pas de sens. Je vais essayer avec une version simplifiée pour encourager plus de participation.

Considérez un tableau comme celui-ci:

  • {Vide}

           dt     |    mnth    |  foo
    --------------+------------+--------
      2012-12-01  |  December  |
        ...
      2012-08-01  |  August    |
      2012-07-01  |  July      |
      2012-06-01  |  June      |
      2012-05-01  |  May       |
      2012-04-01  |  April     |
      2012-03-01  |  March     |
        ...
      1997-01-01  |  January   |

Si vous recherchez l’enregistrement avec dt le plus proche d’aujourd’hui sans passer par là, quelle serait la meilleure façon de renvoyer également les 3 enregistrements avant et 7 enregistrements après?

J’ai décidé d’essayer les fonctions de fenêtrage:

  • {Vide}

    WITH dates AS (
       select  row_number() over (order by dt desc)
             , dt
             , dt - now()::date as dt_diff
       from    foo
    )
    , closest_date AS (
       select * from dates
       where dt_diff = ( select max(dt_diff) from dates where dt_diff <= 0 )
    )
    
    SELECT *
    FROM   dates
    WHERE  row_number - (select row_number from closest_date) >= -3
       AND row_number - (select row_number from closest_date) <=  7 ;

Je pense qu’il doit y avoir un meilleur moyen de renvoyer des enregistrements relatifs avec une fonction de fenêtre, mais cela fait un certain temps que je ne les ai pas consultés.

3 Answer


3


create table foo (dt date);
insert into foo values
('2012-12-01'),
('2012-08-01'),
('2012-07-01'),
('2012-06-01'),
('2012-05-01'),
('2012-04-01'),
('2012-03-01'),
('2012-02-01'),
('2012-01-01'),
('1997-01-01'),
('2012-09-01'),
('2012-10-01'),
('2012-11-01'),
('2013-01-01')
;

select dt
from (
(
    select dt
    from foo
    where dt <= current_date
    order by dt desc
    limit 4
)
union all
(
    select dt
    from foo
    where dt > current_date
    order by dt
    limit 7
)) s
order by dt
;
     dt
------------
 2012-03-01
 2012-04-01
 2012-05-01
 2012-06-01
 2012-07-01
 2012-08-01
 2012-09-01
 2012-10-01
 2012-11-01
 2012-12-01
 2013-01-01
(11 rows)


1


Vous pouvez utiliser quelque chose comme ça:

select * from foo
where dt between now()- interval '7 months' and now()+ interval '3 months'

This et this peut T’aider.


1


Vous pouvez utiliser la fonction * window lead () *:

SELECT dt_lead7 AS dt
FROM  (
    SELECT *, lead(dt, 7) OVER (ORDER BY dt) AS dt_lead7
    FROM   foo
    ) d
WHERE  dt <= now()::date
ORDER  BY dt DESC
LIMIT  11;

Un peu plus court, mais la version UNION ALL sera plus rapide avec un index approprié.

Cela laisse un * cas d’angle * où "la date la plus proche d’aujourd’hui" se trouve dans les 7 premières lignes. Vous pouvez remplir les données d’origine avec 7 lignes de https://www.postgresql.org/docs/current/datatype-datetime.html#AEN5750 [-infinity] pour prendre soin de cela:

SELECT d.dt_lead7 AS dt
FROM  (
    SELECT *, lead(dt, 7) OVER (ORDER BY dt) AS dt_lead7
    FROM  (
        SELECT '-infinity'::date AS dt FROM generate_series(1,7)
        UNION ALL
        SELECT dt FROM foo
        ) x
    ) d
WHERE  d.dt <= now()::date -- same as: WHERE  dt <= now()::date1
ORDER  BY d.dt_lead7 DESC  -- same as: ORDER BY dt DESC 1
LIMIT  11;

J’ai qualifié les tables de la deuxième requête pour clarifier ce qui se passe. Voir ci-dessous. + Le résultat inclura les valeurs NULL si la" date la plus proche d’aujourd’hui "se trouve dans les 7 dernières lignes de la table de base. Vous pouvez filtrer ceux-ci avec une sous-sélection supplémentaire si vous en avez besoin.

'' '' '

^ 1 ^ Pour répondre à vos doutes concernant * les noms de sortie * par rapport aux * noms de colonne * dans les commentaires - tenez compte des citations suivantes du manuel.

_ Le nom d’une colonne de sortie peut être utilisé pour faire référence à la valeur de la colonne dans les clauses ORDER BY et` GROUP BY`, * mais pas dans les clauses WHERE * ou` HAVING`; là, vous devez écrire l’expression à la place. _

Accentuation sur moi. `WHERE dt ⇐ now ()

date` fait référence à la colonne` d.dt`, pas à la colonne de sortie du même nom - fonctionnant ainsi comme prévu.

_ Si une expression ORDER BY est un nom simple qui correspond à la fois à un nom de colonne de sortie et à un nom de colonne d’entrée, *` ORDER BY` l’interprétera comme le nom de la colonne de sortie *. C’est l’opposé du choix que "GROUP BY" fera dans la même situation. * Cette incohérence est conçue pour être compatible avec la norme SQL. * _

J’insiste à nouveau sur moi. ORDER BY dt DESC dans l’exemple fait référence au nom de la colonne de sortie - comme prévu. Quoi qu’il en soit, les deux colonnes trieraient la même chose. La seule différence pourrait être avec les valeurs NULL du cas d’angle. Mais cela tombe à plat aussi, car:

_ le comportement par défaut est «NULLS LAST» lorsque «ASC» est spécifié ou implicite, et «NULLS FIRST» lorsque «DESC» est spécifié _

Comme les valeurs «NULL» viennent après les plus grandes valeurs, l’ordre est identique dans les deux cas.

'' '' '

Ou, * sans LIMIT * (selon la demande en commentaire):

WITH x AS (
    SELECT *
         , row_number() OVER (ORDER BY dt)  AS rn
         , first_value(dt) OVER (ORDER BY (dt > '2011-11-02')
                                         , dt DESC) AS dt_nearest
    FROM   foo
    )
, y AS (
    SELECT rn AS rn_nearest
    FROM   x
    WHERE  dt = dt_nearest
    )
SELECT dt
FROM   x, y
WHERE  rn BETWEEN rn_nearest - 3 AND rn_nearest + 7
ORDER  BY dt;

Si les performances sont importantes, j’irais quand même avec la variante UNION ALL de @ Clodoaldo. Ce sera le plus rapide. La base de données SQL agnostique ne vous mènera que jusqu’à présent. D’autres SGBDR n’ont pas encore de fonctions de fenêtre (MySQL) ou des noms de fonctions différents (comme first_val au lieu de` first_value`). Vous pourriez tout aussi bien remplacer LIMIT par` TOP n` (MS SQL) ou quel que soit le dialecte local.