1
votes

Rechercher les vraies dates de début et de fin pour les clients qui ont plusieurs comptes dans SQL Server 2014

J'ai une table de compte courant qui contient les colonnes Cust_id (identifiant client), Open_Date (date de début) et Closed_Date (date de fin ). Il y a une ligne pour chaque compte. Un client peut ouvrir plusieurs comptes à tout moment. Je voudrais savoir depuis combien de temps la personne est cliente.

Exemple 1:

insert into [Cust] values ('b245', '07/01/2019', '09/15/2019')
insert into [Cust] values ('b245', '10/12/2019', '12/01/2019')

Idéalement, je voudrais insérer ceci dans un tableau avec une seule ligne, qui dit que cette personne a été client du 10/01/2019 au 11/01/2019. (comme il a ouvert son deuxième compte avant de fermer son précédent.

De même, par exemple 2:

CREATE TABLE [Cust]
(
    [Cust_id] [varchar](10) NULL,
    [Open_Date] [date] NULL,
    [Closed_Date] [date] NULL
)

insert into [Cust] values ('a123', '10/01/2019', '10/15/2019')
insert into [Cust] values ('a123', '10/12/2019', '11/01/2019')

Je voudrais voir 2 lignes dans ce cas - celui qui montre qu'il était client du 01/07 au 15/09, puis à nouveau du 12/10 au 01/12.

Pouvez-vous m'indiquer la meilleure façon de l'obtenir? p >

sql sql-server window-functions gaps-and-islands tsql

1 commentaires

Avez-vous examiné les lacunes et les problèmes insulaires? Qu'as-tu fatigué?

3 Réponses :

0
votes

vous pouvez essayer quelque chose comme ça:

select distinct
  cust_id,
  (select min(Open_Date)
   from Cust as b
   where b.cust_id = a.cust_id and 
   a.Open_Date <= b.Closed_Date and
   a.Closed_Date >= b.Open_Date
  ),
  (select max(Closed_Date)
   from Cust as b
   where b.cust_id = a.cust_id and 
   a.Open_Date <= b.Closed_Date and
   a.Closed_Date >= b.Open_Date
  )
from Cust as a

donc, pour chaque ligne - vous sélectionnez des dates minimales et maximales de toutes les plages qui se chevauchent, plus tard, des filtres distincts éliminent les doublons

0 commentaires

3
votes

Je considérerais cela comme un problème de lacunes et d'îles. Vous souhaitez regrouper des groupes de lignes adjacentes dont les périodes se chevauchent.

Voici une façon de le résoudre en utilisant lag () et un sum () cumulatif. Chaque fois que la date d'ouverture est supérieure à la date de clôture de l'enregistrement précédent, un nouveau groupe démarre.

cust_id | open_date  | closed_date
:------ | :--------- | :----------
a123    | 2019-10-01 | 2019-11-01 
b245    | 2019-07-01 | 2019-09-15 
b245    | 2019-10-12 | 2019-12-01

Dans this db fiddle avec vos exemples de données, la requête produit:

select 
    cust_id,
    min(open_date) open_date,
    max(closed_date) closed_date
from (
    select 
        t.*,
        sum(case when not open_date <= lag_closed_date then 1 else 0 end) 
            over(partition by cust_id order by open_date) grp
    from (
        select 
            t.*,
            lag(closed_date) over (partition by cust_id order by open_date) lag_closed_date
        from cust t
    ) t
) t
group by cust_id, grp

3 commentaires

Cela fonctionnera bien pour une progression de compte linéaire, mais échouera avec des délais plus complexes. Par exemple. Le compte A ouvre 2010 et ne ferme jamais; Le compte B ouvre et ferme en 2012; Le compte C s'ouvre et se ferme en 2014. Étant donné que votre requête ne se compare qu'aux voisins, l'écart entre B et C provoquera un nouveau groupe bien que le compte le plus ancien (A) soit toujours ouvert.

@ChrisHep: oui, peut-être. Mais je ne vois pas de tels cas dans les exemples de données fournis par OP (bien que je convienne que 4 enregistrements ne peuvent pas être considérés comme un ensemble de données complet), d'où cette réponse.

Vous faites un excellent point, la solution vers laquelle je travaillais supposait des données plus compliquées que celles présentes dans le post. J'ai mentionné dans ma réponse que la vôtre est préférable si les données de l'échantillon étaient indicatives de la complexité attendue.

1
votes

Je résoudrais cela avec la récursivité. Bien que cela soit certainement très lourd, il devrait s'adapter aux délais de compte les plus complexes (en supposant que vos données en aient). Cependant, si les exemples de données fournis sont aussi complexes que vous devez les résoudre, je vous recommande vivement de vous en tenir à la solution fournie ci-dessus. C'est beaucoup plus concis et clair.

WITH x (cust_id, open_date, closed_date, lvl, grp) AS (
   SELECT cust_id, open_date, closed_date, 1, 1
     FROM (
             SELECT cust_id
                  , open_date
                  , closed_date
                  , row_number()
                    OVER (PARTITION BY cust_id ORDER BY closed_date DESC, open_date) AS rn
               FROM cust
          ) AS t
    WHERE rn = 1
    UNION ALL
   SELECT cust_id, open_date, closed_date, lvl, grp
     FROM (
             SELECT c.cust_id
                  , c.open_date
                  , c.closed_date
                  , x.lvl + 1                                                              AS lvl
                  , x.grp + CASE WHEN c.closed_date < x.open_date THEN 1 ELSE 0 END        AS grp
                  , row_number() OVER (PARTITION BY c.cust_id ORDER BY c.closed_date DESC) AS rn
               FROM cust c
                    JOIN x
                         ON x.cust_id = c.cust_id
                            AND c.open_date < x.open_date
          ) AS t
    WHERE t.rn = 1
     )
SELECT cust_id, min(open_date) AS first_open_date, max(closed_date) AS last_closed_date
  FROM x
 GROUP BY cust_id, grp
 ORDER BY cust_id, grp

J'ajouterais également la mise en garde que je ne cours pas sur SQL Server, donc il pourrait y avoir des différences de syntaxe dont je n'ai pas tenu compte. J'espère qu'ils sont mineurs, s'ils sont présents.

1 commentaires

Cette solution fonctionne aussi - bien que la récursion soit un tueur de performances, cela conviendrait à des cas plus complexes que celui indiqué dans les exemples de données.