skip to Main Content

I have the following very simplified situation in a table:

datetime id   event
-------- --   -----
dt01     67   1
dt02     67   2
dt03     106  1
dt04     106  4
dt05     106  2
dt06     654  1
dt07     654  4 
dt08     654  3
dt09     654  3
dt10     654  2
dt11     213  1
dt12     213  3
dt13     213  2
dt14     654  1
dt15     654  3
dt16     654  2

I want to count the number of 3’s between the 1 and the 2.
The table is orderend on datetime and grouped by id (I can make it appear that way).

The answer is 0 for id 67, 0 for id 106, 2 for id 654, 1 for id 213 and (again!) 1 for id 654.

The 0’s aren’t important but ok if they show up in the result. I am looking for a result that has id 654 listed twice. I can not use the sum of the answers (3 in this case). It must be 2 and 1.

I have read several tickets on stackoverflow but I do not understand them (to be honest) or they are not applicable.

I might have overlooked possibilities with window functions but I did not see a solution.
I can imagine a solution using a stored procedure but I am not allowed to add a sp.

2

Answers


  1. For example, this can be

    SELECT id, COUNT(*)
    FROM table
    JOIN ( SELECT id, 
                  MIN(CASE WHEN event = 1 THEN datetime END) mindatetime, 
                  MAX(CASE WHEN event = 2 THEN datetime END) maxdatetime
           FROM table
           WHERE event IN (1, 2)
           GROUP BY 1
           ) subquery USING (id)
    WHERE table.datetime BETWEEN subquery.mindatetime AND subquery.maxdatetime
      AND table.event = 3
    GROUP BY 1;
    

    https://dbfiddle.uk/hSxExR3P

    Login or Signup to reply.
  2. Since there are multiple groups of rows for the same id you should flag the first row of each group and then populate the flag to the rest of the group.

    --      S a m p l e    D a t a :
    CREATE TABLE tbl (datetime CHAR(4), id INT, event INT);
    INSERT INTO tbl VALUES
    ('dt01',      67,  1),
    ('dt02',      67,  2),
    ('dt03',     106,  1),
    ('dt04',     106,  4),
    ('dt05',     106,  2),
    ('dt06',     654,  1),
    ('dt07',     654,  4),
    ('dt08',     654,  3),
    ('dt09',     654,  3),
    ('dt10',     654,  2),
    ('dt11',     213,  1),
    ('dt12',     213,  3),
    ('dt13',     213,  2),
    ('dt14',     654,  1),
    ('dt15',     654,  3),
    ('dt16',     654,  2);
    

    … the grid cte flags the first row of each group of id rows …

    WITH 
        grid AS
          ( Select     datetime, id, event,
                       Case When id != Coalesce(LAG(id) Over(Order By datetime ), 'dt00')
                            Then datetime
                       End as grp
            From       tbl
            Where      event < 4
          )
    

    … the inner query populates the group flag to the reamining rows of the same group and outer query aggregates the data conditionaly grouping them by grp, id …

    --      M a i n    S Q L : 
    SELECT    id, Count(Case When event = 3 Then 1 End) as cnt
    FROM    ( Select     datetime, id, event, 
                         Max(grp) 
                             Over( Order By datetime 
                                   Rows Between Unbounded Preceding And Current Row ) as grp
              From       grid
              Order By   datetime
            ) x
    GROUP BY    grp, id
    HAVING      Min(event) = 1  And 
                Max(Case When event != 3 Then event End) = 2
    ORDER BY    grp, id 
    

    … having clause should exclude the groups that doesn’t containe events 1 and/or 2 …

    /*
     id  cnt
    ---  ---
     67    0
    106    0
    654    2
    213    1
    654    1    */
    

    If the event 3 should be between 1 and 2 (ordered by datetime) then the HAVING clause should be changed into:

    ... ...
    HAVING    ( Min(Case When event = 1 Then datetime End) < Min(Case When event = 3 Then datetime End) And 
                Max(Case When event = 2 Then datetime End) > Min(Case When event = 3 Then datetime End) 
              ) OR Count(Case When event = 3 Then 1 End) = 0
    ... ...
    

    … the result is the same as above …

    See the fiddle here.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search