darcs - gombemi

1 <?xml version='1.0'?>

2 <!DOCTYPE para PUBLIC "-//OASIS//DTD DocBook XML 5.0//EN"

3 "http://www.oasis-open.org/docbook/xml/5.0/dtd/docbook.dtd" [

4

5 <!ENTITY % entities SYSTEM "../entities">

6 %entities;

7 <!ENTITY % constants SYSTEM "../constants">

8 %constants;

9 ]>

11 - See document output for full copyright information. -->

13 <!-- Core documentation on the EVENT_BOUTS view.

15 - This is indeed the query that makes the view, so don't break it!

17 - The "code" element must have a 'role="view"' attribute

18 - so that Gombe-MI code generation can get the code

19 - and actually make the view!

21 -->

23 <para xmlns="http://docbook.org/ns/docbook"

24 version="5.0">

26 <figure xml:id="event_bouts-code">

27 <title>Query Defining the EVENT_BOUTS View: Part I</title>

28 <literallayout><code role="view">

29 -- Note that we don't worry about leap seconds given

30 -- the time values in the db.

32 WITH detail AS

33 -- Compute mins_in and mins_out

34 (SELECT bouts.followid AS followid

35 , bouts.intid AS intid

36 , bouts.function AS function

37 , bouts.animid AS animid

38 , bouts.role AS role

39 , bouts.chimpid AS chimpid

40 , bouts.bout AS bout

41 , bouts.behavior AS behavior

42 , bouts.uncertain AS uncertain

43 , bouts.time AS time

44 , (EXTRACT(EPOCH FROM bouts.time)

45 - EXTRACT(EPOCH FROM FIRST_VALUE(bouts.time) OVER bout_w)

46 ) / 60

47 + 1

48 AS mins_in

49 , (EXTRACT(EPOCH FROM LAST_VALUE(bouts.time)

50 OVER (bout_w RANGE BETWEEN CURRENT ROW

51 AND UNBOUNDED FOLLOWING))

52 - EXTRACT(EPOCH FROM bouts.time)

53 ) / 60

54 AS mins_out

55 FROM

56 -- Compute bout number by summing transition ticks

57 (SELECT ticks.followid AS followid

58 , ticks.intid AS intid

59 , ticks.function AS function

60 , ticks.animid AS animid

61 , ticks.role AS role

62 , ticks.chimpid AS chimpid

63 , ticks.behavior AS behavior

64 , ticks.uncertain AS uncertain

65 , ticks.time AS time

66 , SUM(ticks.bouttick)

67 OVER (PARTITION BY ticks.followid

68 , ticks.behavior

69 , ticks.animid

70 ORDER BY ticks.time

71 , ticks.bouttick DESC)

72 AS bout</code></literallayout>

73 </figure>

74 <figure xml:id="event_bouts-code-part2">

75 <title>Query Defining the EVENT_BOUTS View: Part II</title>

76 <literallayout><code role="view">

77 FROM

78 -- Distinguish one bout from another

79 (SELECT intervals.followid AS followid

80 , intervals.intid AS intid

81 , followroles.function AS function

82 , part_cids.animid AS animid

83 , followparts.role AS role

84 , followparts.chimpid AS chimpid

85 , events.behavior AS behavior

86 , BOOL_AND(events.uncertain) AS uncertain

87 , intervals.time AS time

88 , CASE

89 WHEN LAG(MIN(intervals.time)) OVER tick_w

90 = MIN(intervals.time) - '1 minute'::INTERVAL

91 THEN 0

92 ELSE

93 1

94 END

95 AS bouttick

96 FROM intervals

97 JOIN events

98 ON (events.intid = intervals.intid)

99 JOIN parts

100 ON (parts.eid = events.eid)

101 JOIN chimpids AS part_cids

102 ON (part_cids.chimpid = parts.participant)

103 JOIN chimpids AS fp_cids

104 ON (fp_cids.animid = part_cids.animid)

105 JOIN followparts

106 ON (followparts.followid = intervals.followid

107 AND followparts.chimpid = fp_cids.chimpid)

108 JOIN followroles

109 ON (followroles.role = followparts.role)

110 WHERE NOT(events.baboon)

111 GROUP BY intervals.followid

112 , intervals.intid

113 , part_cids.animid

114 , events.behavior

115 -- The values that are unique per above anyway

116 , followroles.function

117 , followparts.role

118 , followparts.chimpid

119 , intervals.time

120 WINDOW tick_w AS (PARTITION BY intervals.followid

121 , events.behavior

122 , part_cids.animid

123 ORDER BY intervals.time)

124 ) AS ticks

125 ) AS bouts

126 WINDOW bout_w AS (PARTITION BY bouts.followid

127 , bouts.behavior

128 , bouts.animid

129 , bouts.bout

130 ORDER BY bouts.time)

131 )</code></literallayout>

132 </figure>

133 <figure xml:id="event_bouts-code-part3">

134 <title>Query Defining the EVENT_BOUTS View: Part III</title>

135 <literallayout><code role="view">

136 -- Distribute aggregated bout info over the bout's minutes

137 SELECT detail.*

138 , summary.prior_gap AS prior_gap

139 , summary.gap_noobs AS gap_noobs

140 , summary.left_censored AS left_censored

141 , summary.right_censored AS right_censored

142 , summary.day_start AS day_start

143 , summary.day_end AS day_end

144 FROM

145 detail

146 JOIN

147 (-- Aggregate per-bout information

148 SELECT bout_ends.followid AS followid

149 , bout_ends.animid AS animid

150 , bout_ends.behavior AS behavior

151 , bout_ends.bout AS bout

152 , CASE

153 WHEN bout_ends.bout = 1 THEN

154 NULL

155 ELSE

156 SUM(bout_ends.prior_gap)

157 END

158 AS prior_gap

159 , CASE

160 WHEN bout_ends.bout = 1 THEN

161 NULL

162 ELSE

163 SUM(bout_ends.prior_gap)

164 -- Subtract number of minutes the focal had

165 -- any behaviors during the gap.

166 - (SELECT COUNT(DISTINCT intervals.intid)

167 FROM intervals

168 JOIN events

169 ON (events.intid = intervals.intid)

170 JOIN parts

171 ON (parts.eid = events.eid)

172 JOIN chimpids

173 ON (chimpids.chimpid

174 = parts.participant)

175 WHERE intervals.followid = bout_ends.followid

176 AND MIN(bout_ends.time) > intervals.time

177 AND intervals.time

178 >= MIN(bout_ends.time)

179 - (SUM(bout_ends.prior_gap)

180 || ' minutes')::INTERVAL

181 AND chimpids.animid = bout_ends.animid)

182 END

183 AS gap_noobs

184 , BOOL_OR(bout_ends.left_censored) AS left_censored

185 , BOOL_OR(bout_ends.right_censored) AS right_censored</code></literallayout>

186 </figure>

187 <figure xml:id="event_bouts-code-part4">

188 <title>Query Defining the EVENT_BOUTS View: Part IV</title>

189 <literallayout><code role="view">

190 , CASE

191 WHEN bout_ends.bout = 1 THEN

192 NOT EXISTS

193 -- Behavioral data on the focal before the bout

194 (SELECT 1

195 FROM intervals

196 JOIN events

197 ON (events.intid = intervals.intid)

198 JOIN parts

199 ON (parts.eid = events.eid)

200 JOIN chimpids

201 ON (chimpids.chimpid

202 = parts.participant)

203 WHERE intervals.followid = bout_ends.followid

204 AND intervals.time < MIN(bout_ends.time)

205 AND chimpids.animid = bout_ends.animid

206 LIMIT 1)

207 ELSE

208 FALSE

209 END

210 AS day_start

211 , CASE

212 WHEN bout_ends.bout = LAST_VALUE(bout_ends.bout)

213 OVER (PARTITION BY

214 bout_ends.followid

215 , bout_ends.animid

216 , bout_ends.behavior

217 ORDER BY bout_ends.bout

218 RANGE BETWEEN CURRENT ROW

219 AND UNBOUNDED FOLLOWING)

220 THEN

221 NOT EXISTS

222 -- Behavioral data on the focal after the bout

223 (SELECT 1

224 FROM intervals

225 JOIN events

226 ON (events.intid = intervals.intid)

227 JOIN parts

228 ON (parts.eid = events.eid)

229 JOIN chimpids

230 ON (chimpids.chimpid

231 = parts.participant)

232 WHERE intervals.followid = bout_ends.followid

233 AND intervals.time

234 > MAX(bout_ends.time)

235 AND chimpids.animid = bout_ends.animid

236 LIMIT 1)

237 ELSE

238 FALSE

239 END

240 AS day_end</code></literallayout>

241 </figure>

242 <figure xml:id="event_bouts-code-part5">

243 <title>Query Defining the EVENT_BOUTS View: Part V</title>

244 <literallayout><code role="view">

245 FROM

246 -- Examine the bout's endpoints to compute prior_gap, and

247 -- left and right censoring.

248 (SELECT detail.followid

249 , detail.animid

250 , detail.behavior

251 , detail.bout

252 , detail.mins_in AS mins_in

253 , detail.mins_out AS mins_out

254 , CASE

255 WHEN detail.mins_in = 1 THEN

256 (EXTRACT(EPOCH FROM detail.time)

257 - EXTRACT(EPOCH FROM

258 LAG(detail.time)

259 OVER (PARTITION BY

260 detail.followid

261 , detail.animid

262 , detail.behavior

263 ORDER BY detail.time))

264 ) / 60 - 1

265 ELSE

266 0

267 END

268 AS prior_gap

269 , CASE

270 WHEN detail.mins_in = 1 THEN

271 NOT EXISTS

272 (SELECT 1

273 FROM intervals AS i

274 JOIN events AS e

275 ON (e.intid = i.intid)

276 JOIN parts AS p

277 ON (p.eid = e.eid)

278 JOIN chimpids AS p_cids

279 ON (p_cids.chimpid = p.participant)

280 WHERE i.followid = detail.followid

281 AND i.time = detail.time

282 - '1 minute'::INTERVAL

283 AND p_cids.animid = detail.animid

284 LIMIT 1)

285 ELSE

286 FALSE

287 END

288 AS left_censored</code></literallayout>

289 </figure>

290 <figure xml:id="event_bouts-code-part6">

291 <title>Query Defining the EVENT_BOUTS View: Part VI</title>

292 <literallayout><code role="view">

293 , CASE

294 WHEN detail.mins_out = 0 THEN

295 NOT EXISTS

296 (SELECT 1

297 FROM intervals AS i

298 JOIN events AS e

299 ON (e.intid = i.intid)

300 JOIN parts AS p

301 ON (p.eid = e.eid)

302 JOIN chimpids AS p_cids

303 ON (p_cids.chimpid = p.participant)

304 WHERE i.followid = detail.followid

305 AND i.time = detail.time

306 + '1 minute'::INTERVAL

307 AND p_cids.animid = detail.animid

308 LIMIT 1)

309 ELSE

310 FALSE

311 END

312 AS right_censored

313 , detail.time AS time

314 FROM detail

315 WHERE detail.mins_in = 1

316 OR detail.mins_out = 0

317 ) AS bout_ends

318 GROUP BY bout_ends.followid

319 , bout_ends.animid

320 , bout_ends.behavior

321 , bout_ends.bout)

322 AS summary

323 ON (summary.followid = detail.followid

324 AND summary.animid = detail.animid

325 AND summary.behavior = detail.behavior

326 AND summary.bout = detail.bout)</code><code>;</code></literallayout>

327 </figure>

328

329

330

331 <!-- Use mediaobject role attribute to select either xhtml (html)

332 or pdf (dblatex) file paths. -->

333

334 <figure xml:id="event_bouts-erd">

335 <title>Entity Relationship Diagram of the EVENT_BOUTS View</title>

336 <mediaobject>

337 <textobject> <phrase>ER diagram intentionally omitted.</phrase>

338 </textobject>

339 </mediaobject>

340 </figure>

341 </para>

342 <!--

343 Local Variables:

344 mode: xml

345 End:

346 -->