Query Defining the EVENT_BOUTS View: Part I

%entities; %constants; ]>


-- Note that we don't worry about leap seconds given
-- the time values in the db.

WITH detail AS
     -- Compute mins_in and mins_out
     (SELECT bouts.followid AS followid
           , bouts.intid AS intid
           , bouts.function AS function
           , bouts.animid AS animid
           , bouts.role AS role
           , bouts.chimpid AS chimpid
           , bouts.bout AS bout
           , bouts.behavior AS behavior
           , bouts.uncertain AS uncertain
           , bouts.time AS time
           , (EXTRACT(EPOCH FROM bouts.time)
                - EXTRACT(EPOCH FROM FIRST_VALUE(bouts.time) OVER bout_w)
             ) / 60
             + 1
             AS mins_in           
           , (EXTRACT(EPOCH FROM LAST_VALUE(bouts.time)
                                   OVER (bout_w RANGE BETWEEN CURRENT ROW
                                                AND UNBOUNDED FOLLOWING))
                - EXTRACT(EPOCH FROM bouts.time)
             ) / 60
             AS mins_out
        FROM
          -- Compute bout number by summing transition ticks
          (SELECT ticks.followid AS followid
                   , ticks.intid AS intid
                   , ticks.function AS function
                   , ticks.animid AS animid
                   , ticks.role AS role
                   , ticks.chimpid AS chimpid
                   , ticks.behavior AS behavior
                   , ticks.uncertain AS uncertain
                   , ticks.time AS time
                   , SUM(ticks.bouttick)
                       OVER (PARTITION BY ticks.followid
                                        , ticks.behavior
                                        , ticks.animid
                             ORDER BY ticks.time
                                    , ticks.bouttick DESC)
                     AS bout

Query Defining the EVENT_BOUTS View: Part II


             FROM
               -- Distinguish one bout from another
               (SELECT intervals.followid AS followid
                     , intervals.intid AS intid
                     , followroles.function AS function
                     , part_cids.animid AS animid
                     , followparts.role AS role
                     , followparts.chimpid AS chimpid
                     , events.behavior AS behavior
                     , BOOL_AND(events.uncertain) AS uncertain
                     , intervals.time AS time
                     , CASE
                         WHEN LAG(MIN(intervals.time)) OVER tick_w
                                = MIN(intervals.time) - '1 minute'::INTERVAL
                           THEN 0
                         ELSE
                           1
                       END
                       AS bouttick
                  FROM intervals
                         JOIN events
                              ON (events.intid = intervals.intid)
                         JOIN parts
                              ON (parts.eid = events.eid)
                         JOIN chimpids AS part_cids
                              ON (part_cids.chimpid = parts.participant)
                         JOIN chimpids AS fp_cids
                              ON (fp_cids.animid = part_cids.animid)
                         JOIN followparts
                              ON (followparts.followid = intervals.followid
                                  AND followparts.chimpid = fp_cids.chimpid)
                         JOIN followroles
                              ON (followroles.role = followparts.role)
                  WHERE NOT(events.baboon)
                  GROUP BY intervals.followid
                     , intervals.intid
                     , part_cids.animid
                     , events.behavior
                     -- The values that are unique per above anyway
                     , followroles.function
                     , followparts.role
                     , followparts.chimpid
                     , intervals.time
                  WINDOW tick_w AS (PARTITION BY intervals.followid
                                               , events.behavior
                                               , part_cids.animid
                                    ORDER BY intervals.time)
               ) AS ticks
          ) AS bouts
        WINDOW bout_w AS (PARTITION BY bouts.followid
                                     , bouts.behavior
                                     , bouts.animid
                                     , bouts.bout
                          ORDER BY bouts.time)
      )

Query Defining the EVENT_BOUTS View: Part III


  -- Distribute aggregated bout info over the bout's minutes
  SELECT detail.*
       , summary.prior_gap AS prior_gap
       , summary.gap_noobs AS gap_noobs
       , summary.left_censored AS left_censored
       , summary.right_censored AS right_censored
       , summary.day_start AS day_start
       , summary.day_end AS day_end
    FROM
      detail
        JOIN
        (-- Aggregate per-bout information
         SELECT bout_ends.followid AS followid
              , bout_ends.animid AS animid
              , bout_ends.behavior AS behavior
              , bout_ends.bout AS bout
              , CASE
                  WHEN bout_ends.bout = 1 THEN
                    NULL
                  ELSE
                    SUM(bout_ends.prior_gap)
                END
                AS prior_gap
              , CASE
                  WHEN bout_ends.bout = 1 THEN
                    NULL
                  ELSE
                    SUM(bout_ends.prior_gap)
                      -- Subtract number of minutes the focal had
                      -- any behaviors during the gap.
                      - (SELECT COUNT(DISTINCT intervals.intid)
                           FROM intervals
                                  JOIN events
                                       ON (events.intid = intervals.intid)
                                  JOIN parts
                                       ON (parts.eid = events.eid)
                                  JOIN chimpids
                                       ON (chimpids.chimpid 
                                           = parts.participant)
                           WHERE intervals.followid = bout_ends.followid
                                 AND MIN(bout_ends.time) > intervals.time
                                 AND intervals.time
                                     >= MIN(bout_ends.time)
                                          - (SUM(bout_ends.prior_gap)
                                             || ' minutes')::INTERVAL
                                 AND chimpids.animid = bout_ends.animid)
                END
                AS gap_noobs
              , BOOL_OR(bout_ends.left_censored) AS left_censored
              , BOOL_OR(bout_ends.right_censored) AS right_censored

Query Defining the EVENT_BOUTS View: Part IV


              , CASE
                  WHEN bout_ends.bout = 1 THEN
                    NOT EXISTS
                          -- Behavioral data on the focal before the bout
                          (SELECT 1
                             FROM intervals
                                    JOIN events
                                         ON (events.intid = intervals.intid)
                                    JOIN parts
                                         ON (parts.eid = events.eid)
                                    JOIN chimpids
                                         ON (chimpids.chimpid
                                             = parts.participant)
                             WHERE intervals.followid = bout_ends.followid
                                   AND intervals.time < MIN(bout_ends.time)
                                   AND chimpids.animid = bout_ends.animid
                             LIMIT 1)
                  ELSE
                    FALSE
                END
                AS day_start
              , CASE
                  WHEN bout_ends.bout = LAST_VALUE(bout_ends.bout)
                                          OVER (PARTITION BY
                                                  bout_ends.followid
                                                , bout_ends.animid
                                                , bout_ends.behavior
                                                ORDER BY bout_ends.bout
                                                RANGE BETWEEN CURRENT ROW
                                                      AND UNBOUNDED FOLLOWING)
                    THEN
                    NOT EXISTS
                          -- Behavioral data on the focal after the bout
                          (SELECT 1
                             FROM intervals
                                    JOIN events
                                         ON (events.intid = intervals.intid)
                                    JOIN parts
                                         ON (parts.eid = events.eid)
                                    JOIN chimpids
                                         ON (chimpids.chimpid
                                             = parts.participant)
                             WHERE intervals.followid = bout_ends.followid
                                   AND intervals.time
                                       > MAX(bout_ends.time)
                                   AND chimpids.animid = bout_ends.animid
                             LIMIT 1)
                  ELSE
                    FALSE
                END
                AS day_end

Query Defining the EVENT_BOUTS View: Part V


           FROM
             -- Examine the bout's endpoints to compute prior_gap, and
             -- left and right censoring.
             (SELECT detail.followid
                   , detail.animid
                   , detail.behavior
                   , detail.bout
                   , detail.mins_in AS mins_in
                   , detail.mins_out AS mins_out
                   , CASE
                       WHEN detail.mins_in = 1 THEN
                         (EXTRACT(EPOCH FROM detail.time)
                            - EXTRACT(EPOCH FROM
                                      LAG(detail.time)
                                        OVER (PARTITION BY
                                                detail.followid
                                              , detail.animid
                                              , detail.behavior
                                              ORDER BY detail.time))
                         ) / 60 - 1
                       ELSE
                         0
                     END
                     AS prior_gap
                   , CASE
                       WHEN detail.mins_in = 1 THEN
                         NOT EXISTS
                           (SELECT 1
                              FROM intervals AS i
                                     JOIN events AS e
                                          ON (e.intid = i.intid)
                                     JOIN parts AS p
                                          ON (p.eid = e.eid)
                                     JOIN chimpids AS p_cids
                                          ON (p_cids.chimpid = p.participant)
                              WHERE i.followid = detail.followid
                                    AND i.time = detail.time
                                                 - '1 minute'::INTERVAL
                                    AND p_cids.animid = detail.animid
                                    LIMIT 1)
                       ELSE
                         FALSE
                       END
                     AS left_censored

Query Defining the EVENT_BOUTS View: Part VI


                   , CASE
                       WHEN detail.mins_out = 0 THEN
                         NOT EXISTS
                           (SELECT 1
                              FROM intervals AS i
                                     JOIN events AS e
                                          ON (e.intid = i.intid)
                                     JOIN parts AS p
                                          ON (p.eid = e.eid)
                                     JOIN chimpids AS p_cids
                                          ON (p_cids.chimpid = p.participant)
                              WHERE i.followid = detail.followid
                                    AND i.time = detail.time
                                                 + '1 minute'::INTERVAL
                                    AND p_cids.animid = detail.animid
                                    LIMIT 1)
                       ELSE
                         FALSE
                       END
                     AS right_censored
                   , detail.time AS time
                FROM detail
                WHERE detail.mins_in = 1
                      OR detail.mins_out = 0
             ) AS bout_ends
           GROUP BY bout_ends.followid
                  , bout_ends.animid
                  , bout_ends.behavior
                  , bout_ends.bout)
          AS summary
          ON (summary.followid = detail.followid
              AND summary.animid = detail.animid
              AND summary.behavior = detail.behavior
              AND summary.bout = detail.bout)

;

Entity Relationship Diagram of the EVENT_BOUTS View ER diagram intentionally omitted.