uff-dbii-indices/queries.sql at main · danielcmo/uff-dbii-indices · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
use polroute_with_index;


EXPLAIN SELECT
       SUM(crime.total_feminicide) as soma_total_feminicide,
       SUM(crime.total_homicide) as soma_total_homicide,
       SUM(crime.total_felony_murder) as soma_total_felony_murder,
       SUM(crime.total_bodily_harm) as soma_total_bodily_harm,
       SUM(crime.total_theft_cellphone) as soma_total_theft_cellphone,
       SUM(crime.total_armed_robbery_cellphone ) as soma_total_armed_robbery_cellphone,
       SUM(crime.total_theft_auto) as soma_total_theft_auto,
       SUM(crime.total_armed_robbery_auto) as soma_total_armed_robbery_auto,
       segment.id as 'segment_id'
FROM crime
JOIN segment ON segment.id = crime.segment_id
WHERE
    crime.time_id IN (SELECT id from time where year = 2016)
    AND
        (
                segment.start_vertice_id IN ( SELECT id FROM vertice where district_id = (SELECT id from district where district.name = 'IGUATEMI') )
            OR
                segment.final_vertice_id IN ( SELECT id FROM vertice where district_id = (SELECT id from district where district.name = 'IGUATEMI') )
        )
GROUP BY segment.id;

#1.1

EXPLAIN SELECT
       SUM(crime.total_feminicide) as soma_total_feminicide,
       SUM(crime.total_homicide) as soma_total_homicide,
       SUM(crime.total_felony_murder) as soma_total_felony_murder,
       SUM(crime.total_bodily_harm) as soma_total_bodily_harm,
       SUM(crime.total_theft_cellphone) as soma_total_theft_cellphone,
       SUM(crime.total_armed_robbery_cellphone ) as soma_total_armed_robbery_cellphone,
       SUM(crime.total_theft_auto) as soma_total_theft_auto,
       SUM(crime.total_armed_robbery_auto) as soma_total_armed_robbery_auto,
       segment.id as 'segment_id'
FROM crime
JOIN segment ON segment.id = crime.segment_id
JOIN time ON time.id = crime.time_id
WHERE
        (
                segment.start_vertice_id IN ( SELECT id FROM vertice where district_id = (SELECT id from district where district.name = 'IGUATEMI') )
            OR
                segment.final_vertice_id IN ( SELECT id FROM vertice where district_id = (SELECT id from district where district.name = 'IGUATEMI') )
        )
        and time.year = 2016
GROUP BY segment.id;


#1.2
SELECT SUM(soma_total_feminicide)              as soma_total_feminicide,
       SUM(soma_total_homicide)                as soma_total_homicide,
       SUM(soma_total_felony_murder)           as soma_total_felony_murder,
       SUM(soma_total_bodily_harm)             as soma_total_bodily_harm,
       SUM(soma_total_theft_cellphone)         as soma_total_theft_cellphone,
       SUM(soma_total_armed_robbery_cellphone) as soma_total_armed_robbery_cellphone,
       SUM(soma_total_theft_auto)              as soma_total_theft_auto,
       SUM(soma_total_armed_robbery_auto)      as soma_total_armed_robbery_auto,
       segment_id
from (SELECT SUM(crime.total_feminicide)              as soma_total_feminicide,
             SUM(crime.total_homicide)                as soma_total_homicide,
             SUM(crime.total_felony_murder)           as soma_total_felony_murder,
             SUM(crime.total_bodily_harm)             as soma_total_bodily_harm,
             SUM(crime.total_theft_cellphone)         as soma_total_theft_cellphone,
             SUM(crime.total_armed_robbery_cellphone) as soma_total_armed_robbery_cellphone,
             SUM(crime.total_theft_auto)              as soma_total_theft_auto,
             SUM(crime.total_armed_robbery_auto)      as soma_total_armed_robbery_auto,
             segment.id                               as 'segment_id'
      FROM crime
               JOIN segment ON segment.id = crime.segment_id
               JOIN time ON time.id = crime.time_id
               JOIN vertice start_vertice on segment.start_vertice_id = start_vertice.id
               JOIN district on start_vertice.district_id = district.id
      WHERE district.name = 'IGUATEMI' and time.year = 2016
      GROUP BY segment.id

      UNION

      SELECT SUM(crime.total_feminicide)              as soma_total_feminicide,
             SUM(crime.total_homicide)                as soma_total_homicide,
             SUM(crime.total_felony_murder)           as soma_total_felony_murder,
             SUM(crime.total_bodily_harm)             as soma_total_bodily_harm,
             SUM(crime.total_theft_cellphone)         as soma_total_theft_cellphone,
             SUM(crime.total_armed_robbery_cellphone) as soma_total_armed_robbery_cellphone,
             SUM(crime.total_theft_auto)              as soma_total_theft_auto,
             SUM(crime.total_armed_robbery_auto)      as soma_total_armed_robbery_auto,
             segment.id                               as 'segment_id'
      FROM crime
               JOIN segment ON segment.id = crime.segment_id
               JOIN time ON time.id = crime.time_id
               JOIN vertice start_vertice on segment.start_vertice_id = start_vertice.id
               JOIN district on start_vertice.district_id = district.id
      WHERE district.name = 'IGUATEMI' and time.year = 2016
      GROUP BY segment.id) as union_tables
group by union_tables.segment_id;


# 2. Qual o total de crimes por tipo e por segmento das ruas do distrito de IGUATEMI entre 2006 e 2016?
SELECT
       SUM(crime.total_feminicide) as soma_total_feminicide,
       SUM(crime.total_homicide) as soma_total_homicide,
       SUM(crime.total_felony_murder) as soma_total_felony_murder,
       SUM(crime.total_bodily_harm) as soma_total_bodily_harm,
       SUM(crime.total_theft_cellphone) as soma_total_theft_cellphone,
       SUM(crime.total_armed_robbery_cellphone ) as soma_total_armed_robbery_cellphone,
       SUM(crime.total_theft_auto) as soma_total_theft_auto,
       SUM(crime.total_armed_robbery_auto) as soma_total_armed_robbery_auto,
       segment.id as 'segment_id'
FROM crime
JOIN segment ON segment.id = crime.segment_id
WHERE
    crime.time_id IN (SELECT id from time where year > 2006 and year < 2016)
    AND
        (
                segment.start_vertice_id IN ( SELECT id FROM vertice where district_id = (SELECT id from district where district.name = 'IGUATEMI') )
            OR
                segment.final_vertice_id IN ( SELECT id FROM vertice where district_id = (SELECT id from district where district.name = 'IGUATEMI') )
        )
GROUP BY segment.id;

# 2.1

EXPLAIN SELECT
       SUM(crime.total_feminicide) as soma_total_feminicide,
       SUM(crime.total_homicide) as soma_total_homicide,
       SUM(crime.total_felony_murder) as soma_total_felony_murder,
       SUM(crime.total_bodily_harm) as soma_total_bodily_harm,
       SUM(crime.total_theft_cellphone) as soma_total_theft_cellphone,
       SUM(crime.total_armed_robbery_cellphone ) as soma_total_armed_robbery_cellphone,
       SUM(crime.total_theft_auto) as soma_total_theft_auto,
       SUM(crime.total_armed_robbery_auto) as soma_total_armed_robbery_auto,
       segment.id as 'segment_id'
FROM crime
JOIN segment ON segment.id = crime.segment_id
JOIN time ON time.id = crime.time_id
WHERE
        (
                segment.start_vertice_id IN ( SELECT id FROM vertice where district_id = (SELECT id from district where district.name = 'IGUATEMI') )
            OR
                segment.final_vertice_id IN ( SELECT id FROM vertice where district_id = (SELECT id from district where district.name = 'IGUATEMI') )
        ) AND
        time.year > 2006 and time.year < 2016
GROUP BY segment.id;

# 2.2

SELECT SUM(soma_total_feminicide)              as soma_total_feminicide,
       SUM(soma_total_homicide)                as soma_total_homicide,
       SUM(soma_total_felony_murder)           as soma_total_felony_murder,
       SUM(soma_total_bodily_harm)             as soma_total_bodily_harm,
       SUM(soma_total_theft_cellphone)         as soma_total_theft_cellphone,
       SUM(soma_total_armed_robbery_cellphone) as soma_total_armed_robbery_cellphone,
       SUM(soma_total_theft_auto)              as soma_total_theft_auto,
       SUM(soma_total_armed_robbery_auto)      as soma_total_armed_robbery_auto,
       segment_id
from (SELECT SUM(crime.total_feminicide)              as soma_total_feminicide,
             SUM(crime.total_homicide)                as soma_total_homicide,
             SUM(crime.total_felony_murder)           as soma_total_felony_murder,
             SUM(crime.total_bodily_harm)             as soma_total_bodily_harm,
             SUM(crime.total_theft_cellphone)         as soma_total_theft_cellphone,
             SUM(crime.total_armed_robbery_cellphone) as soma_total_armed_robbery_cellphone,
             SUM(crime.total_theft_auto)              as soma_total_theft_auto,
             SUM(crime.total_armed_robbery_auto)      as soma_total_armed_robbery_auto,
             segment.id                               as 'segment_id'
      FROM crime
               JOIN segment ON segment.id = crime.segment_id
               JOIN time ON time.id = crime.time_id
               JOIN vertice start_vertice on segment.start_vertice_id = start_vertice.id
               JOIN district on start_vertice.district_id = district.id
      WHERE district.name = 'IGUATEMI' and (time.year > 2006 and time.year <2016)
      GROUP BY segment.id
      UNION
      SELECT SUM(crime.total_feminicide)              as soma_total_feminicide,
             SUM(crime.total_homicide)                as soma_total_homicide,
             SUM(crime.total_felony_murder)           as soma_total_felony_murder,
             SUM(crime.total_bodily_harm)             as soma_total_bodily_harm,
             SUM(crime.total_theft_cellphone)         as soma_total_theft_cellphone,
             SUM(crime.total_armed_robbery_cellphone) as soma_total_armed_robbery_cellphone,
             SUM(crime.total_theft_auto)              as soma_total_theft_auto,
             SUM(crime.total_armed_robbery_auto)      as soma_total_armed_robbery_auto,
             segment.id                               as 'segment_id'
      FROM crime
               JOIN segment ON segment.id = crime.segment_id
               JOIN time ON time.id = crime.time_id
               JOIN vertice start_vertice on segment.start_vertice_id = start_vertice.id
               JOIN district on start_vertice.district_id = district.id
      WHERE district.name = 'IGUATEMI' and (time.year > 2006 and time.year <2016)
      GROUP BY segment.id) as union_tables
group by union_tables.segment_id;


#3. Qual o total de ocorrências de Roubo de Celular e roubo de carro no bairro de SANTA EFIGÊNIA em 2015?

SELECT SUM(soma_total_theft_cellphone), SUM(soma_total_theft_auto), name
FROM (SELECT SUM(crime.total_theft_cellphone) AS soma_total_theft_cellphone,
             SUM(crime.total_theft_auto)      AS soma_total_theft_auto,
             n_final.name                       AS name
      FROM crime
               JOIN segment ON segment.id = crime.segment_id
               JOIN vertice AS vertice_final ON vertice_final.id = segment.final_vertice_id
               JOIN neighborhood n_final ON vertice_final.neighborhood_id = n_final.id
      WHERE n_final.name = 'Santa Efigenia'
      GROUP BY n_final.name
      UNION
      SELECT SUM(crime.total_theft_cellphone) AS soma_total_theft_cellphone,
             SUM(crime.total_theft_auto)      AS soma_total_theft_auto,
             n_start.name                       AS name
      FROM crime
               JOIN segment ON segment.id = crime.segment_id
               JOIN vertice as vertice_inicial ON vertice_inicial.id = segment.start_vertice_id
               JOIN neighborhood n_start on vertice_inicial.neighborhood_id = n_start.id
      WHERE n_start.name = 'Santa Efigenia'
      GROUP BY n_start.name) AS union_tables
GROUP BY name;

#4. Qual o total de crimes por tipo em vias de mão única da cidade durante o ano de 2012?

EXPLAIN SELECT SUM(crime.total_feminicide)              as soma_total_feminicide,
             SUM(crime.total_homicide)                as soma_total_homicide,
             SUM(crime.total_felony_murder)           as soma_total_felony_murder,
             SUM(crime.total_bodily_harm)             as soma_total_bodily_harm,
             SUM(crime.total_theft_cellphone)         as soma_total_theft_cellphone,
             SUM(crime.total_armed_robbery_cellphone) as soma_total_armed_robbery_cellphone,
             SUM(crime.total_theft_auto)              as soma_total_theft_auto,
             SUM(crime.total_armed_robbery_auto)      as soma_total_armed_robbery_auto
      FROM crime
               JOIN segment ON segment.id = crime.segment_id
      WHERE segment.oneway = 'yes'
        and crime.time_id IN (SELECT id from time where year = 2012);

#5. Qual o total de roubos de carro e celular em todos os segmentos durante o ano de 2017?

EXPLAIN SELECT
             (SUM(crime.total_theft_cellphone) + SUM(crime.total_armed_robbery_cellphone) )         as soma_total_cellphone,
             (SUM(crime.total_theft_auto) + SUM(crime.total_armed_robbery_auto) )            as soma_total_theft_auto
      FROM crime
               JOIN segment ON segment.id = crime.segment_id
      WHERE crime.time_id IN (SELECT id from time where year = 2017);

# 6. Quais os IDs de segmentos que possuíam o maior índice criminal (soma de ocorrências de todos os tipos de crimes), durante o mês de Novembro de 2010?
EXPLAIN SELECT (SUM(crime.total_feminicide) + SUM(crime.total_homicide) + SUM(crime.total_felony_murder) +
        SUM(crime.total_bodily_harm) + SUM(crime.total_theft_cellphone) + SUM(crime.total_armed_robbery_cellphone) +
        SUM(crime.total_theft_auto) + SUM(crime.total_armed_robbery_auto)) as total_crimes,
        segment.id
FROM crime
         JOIN segment ON segment.id = crime.segment_id
WHERE crime.time_id IN (SELECT id from time where year = 2010 and month = 11)
GROUP BY segment.id
ORDER BY total_crimes DESC
LIMIT 10;

# 7. Quais os IDs dos segmentos que possuíam o maior índice criminal (soma de ocorrências de todos os tipos de crimes) durante os finais de semana do ano de 2018?

EXPLAIN SELECT (SUM(crime.total_feminicide) + SUM(crime.total_homicide) + SUM(crime.total_felony_murder) +
        SUM(crime.total_bodily_harm) + SUM(crime.total_theft_cellphone) + SUM(crime.total_armed_robbery_cellphone) +
        SUM(crime.total_theft_auto) + SUM(crime.total_armed_robbery_auto)) as total_crimes,
        segment.id
FROM crime
         JOIN segment ON segment.id = crime.segment_id
WHERE crime.time_id IN (SELECT id from time where year = 2018 and (weekday = 'saturday' or weekday = 'sunday'))
GROUP BY segment.id
ORDER BY total_crimes DESC
LIMIT 10