4
votes

Comptage d'un champ en double consécutif avec SQL

J'ai ces données dans maTable :

   count    personid
    -----------------
    2        2015
    3        2015
    1        2015
    1        1018
    1        1018
    2        1018
    2        1018
    4        1625
    1        1625
    1        1625

et je dois compter les valeurs en double consécutives comme ceci:

Voici le résultat J'ai besoin de:

  Date           Status    PersonID
-----------------------------------------
   2018/01/01         2        2015     â”  2
   2018/01/02         2        2015     â”˜
   2018/01/05         2        2015     â”
   2018/01/06         2        2015       3
   2018/01/07         2        2015     â”˜
   2018/01/11         2        2015     - 1
   2018/01/01         2        1018     - 1
   2018/01/03         2        1018     - 1
   2018/01/05         2        1018     â” 2
   2018/01/06         2        1018     â”˜
   2018/01/08         2        1018     â” 2
   2018/01/09         2        1018     â”˜
   2018/01/03         2        1625     â”
   2018/01/04         2        1625       4
   2018/01/05         2        1625     
   2018/01/06         2        1625     â”˜
   2018/01/17         2        1625     - 1
   2018/01/29         2        1625     - 1
-----------------------------------

J'utilise SQL Server 2016 - aidez-moi s'il vous plaît

sql sql-server

4 commentaires

SO n'est pas un service d'écriture de code, vous devez nous montrer votre tentative que nous pouvons ensuite vous aider.

Qu'as-tu déjà essayé?

pourquoi voter pour moi, si je peux résoudre, ne le demandez jamais ici

Lecture du texte alternatif de vote négatif "Cette question ne montre aucun effort de recherche".

5 Réponses :

1
votes

WITH T1 AS
(SELECT Date,
       Date - ROW_NUMBER() OVER (PARTITION BY Status, PersonID ORDER BY Date) AS Grp
FROM myTable)
SELECT personid,
       ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY Date) AS Consecutive
FROM T1
On this result you can apply a MAX(), to get the number of records for each personid.Refer this question to get breakdown details

3 commentaires

nous n'avons pas de colonne avec le nom C pour "ORDER BY C"

et ne peut pas réduire la date et le nombre comme: "Date - ROW_NUMBER () OVER (PARTITION BY Status, PersonID ORDER BY Date) AS Grp"

Corrigée. C'était une faute de frappe.

4
votes

Il s'agit d'un problème " Gaps and Islands ", vous pouvez essayer comme suit.

;with cte 
     as (select *, 
                dateadd(day, -row_number() 
                                over (partition by status, personid 
                                  order by [date] ), [date]) AS grp 
         FROM   @table
     )
     ,cte1 
     AS (select *,row_number() over(partition by  personid, grp,status order by [date]) rn,
                count(*) over(partition by personid, grp) ct 
         from   cte
        ) 

select  ct as count, personid 
from cte1 
where rn=1

Démo en ligne

Remarque: Il se peut que vous n'obteniez pas les lignes dans le même ordre que vous le faites Je n'ai pas de colonne qui puisse être utilisée pour classer la façon dont vous l'avez montré dans la sortie souhaitée.

0 commentaires

1
votes

Le principal défi pour connaître l'écart entre deux dates et pour chaque date, vous pouvez créer cet écart en utilisant la fonction d'analyse row_number () et la fonction dateiff

with cte as
(

select '2018-01-01' as d, 2 as id , 2015 as pid
union all
select '2018-01-02',2,2015
union all
select '2018-01-05',2,2015 union all
select '2018-01-06',2,2015 union all
select '2018-01-07',2,2015 
union all
select '2018-01-11',2,2015  


), cte1 as (SELECT *, 
                datediff(day, Row_number() 
                                OVER ( 
                                  partition BY id, pid 
                                  ORDER BY [d] ), [d]) AS dif
         FROM   cte
         ) select distinct pid,count(*) over(partition by pid,dif) as cnt from cte1

0 commentaires

2
votes

Ce type de problème est connu sous le nom de " Gaps and Islands ". Vous identifiez des ensembles de données consécutifs (îles) ou une plage de valeurs entre deux îles (intervalles). Il existe de nombreuses façons d'obtenir des résultats qui fonctionnent également bien avec de grands ensembles de données. Pour cela, vous pouvez consulter les articles bien écrits ci-dessous.

https://www.itprotoday.com/sql-server/solving-gaps-and-islands-enhanced-window-functions

https: // www .red-gate.com / conversation-simple / sql / programmation-t-sql / le-sql-des-lacunes-et-îlots en-séquences /

https://www.sqlshack.com/data-boundaries-finding-gaps -islands-and-more /

Voici une tentative de réponse à votre question.

CREATE TABLE #test 
(
     dt DATETIME
    ,Status INT
    ,PersonID INT
)

INSERT INTO #Test (dt, Status, PersonID) VALUES
('2018/01/01', 2, 2015),
('2018/01/02', 2, 2015),
('2018/01/05', 2, 2015),
('2018/01/06', 2, 2015),
('2018/01/07', 2, 2015),
('2018/01/11', 2, 2015),
('2018/01/01', 2, 1018),
('2018/01/03', 2, 1018),
('2018/01/05', 2, 1018),
('2018/01/06', 2, 1018),
('2018/01/08', 2, 1018),
('2018/01/09', 2, 1018),
('2018/01/03', 2, 1625),
('2018/01/04', 2, 1625),
('2018/01/05', 2, 1625),
('2018/01/06', 2, 1625),
('2018/01/17', 2, 1625),
('2018/01/29', 2, 1625)

;with cte_dt_from
AS
(
    SELECT PersonID, MIN(Dt) dt_from_start
    FROM #Test
    GROUP BY PersonID
),
cte_offset_num
AS
(
SELECT      T1.PersonID, T1.dt, DATEDIFF(DAY, T2.dt_from_start, T1.dt) dt_offset
FROM        #test T1
INNER JOIN  cte_dt_from T2 ON T2.PersonID = T1.PersonID
),
cte_starting_point
AS
(
    SELECT A.PersonID, A.dt_offset, ROW_NUMBER() OVER(PARTITION BY A.PersonID ORDER BY A.dt_offset) AS rownum
    FROM cte_offset_num AS A
    WHERE NOT EXISTS (
        SELECT *
        FROM cte_offset_num AS B
        WHERE B.PersonID = A.PersonID AND B.dt_offset = A.dt_offset - 1)
)
,
cte_ending_point
AS
(
    SELECT A.PersonID, A.dt_offset, ROW_NUMBER() OVER(PARTITION BY A.PersonID ORDER BY A.dt_offset) AS rownum
    FROM cte_offset_num AS A
    WHERE NOT EXISTS (
        SELECT *
        FROM cte_offset_num AS B
        WHERE B.PersonID = A.PersonID AND B.dt_offset = A.dt_offset + 1)
)
SELECT (E.dt_offset - S.dt_offset)  + 1 AS [count], S.PersonID
FROM cte_starting_point AS S
JOIN cte_ending_point AS E ON E.PersonID = S.PersonID AND E.rownum = S.rownum
ORDER BY S.PersonID;

DROP TABLE #Test;

0 commentaires

2
votes

Voici la requête la plus simple et la plus petite

 CREATE TABLE #T (
      [Date] date,
      [Status] int,
      PersonId int
    );
    INSERT #T
      VALUES ('2018/01/01', 2, 2015),
      ('2018/01/02', 2, 2015),
      ('2018/01/05', 2, 2015),
      ('2018/01/06', 2, 2015),
      ('2018/01/07', 2, 2015),
      ('2018/01/11', 2, 2015),
      ('2018/01/01', 2, 1018),
      ('2018/01/03', 2, 1018),
      ('2018/01/05', 2, 1018),
      ('2018/01/06', 2, 1018),
      ('2018/01/08', 2, 1018),
      ('2018/01/09', 2, 1018),
      ('2018/01/03', 2, 1625),
      ('2018/01/04', 2, 1625),
      ('2018/01/05', 2, 1625),
      ('2018/01/06', 2, 1625),
      ('2018/01/17', 2, 1625),
      ('2018/01/29', 2, 1625)


    SELECT
      MAX(cnt),
      personid
    FROM (SELECT
      ROW_NUMBER() OVER (PARTITION BY GRP ORDER BY [Date]) AS cnt,
      personid,
      GRP
    FROM (SELECT
      personid,
      [Date],
      DATEDIFF(DAY, '1900-01-01', [Date]) - ROW_NUMBER() OVER (ORDER BY Personid DESC) AS GRP
    FROM #T) A) AS B
    GROUP BY personid,
             GRP
    ORDER BY PersonId DESC

0 commentaires