-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathQueries.txt
223 lines (148 loc) · 6.72 KB
/
Queries.txt
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
Q1 =
select authors.name, count(*) as num
from authors join articles
on authors.id = articles.author
create view PATH as
select (regexp_split_to_array(path, E'/article/'))[2] as Path_Required ;
select (regexp_split_to_array(path, E'/article/'))[2] as Path_Required from log where path != '/' limit 10;
select articles.title, count(*) as views
from articles join log
on articles.slug = PATH
where path != '/'
group by PATH, articles.title
order by views desc limit 3;
//Answer 1
select articles.title, count(*) as views
from articles join log
on articles.slug = (regexp_split_to_array(path, E'/article/'))[2]
where path != '/'
group by (regexp_split_to_array(path, E'/article/'))[2], articles.title
order by views desc limit 3;
//Review 1
create view fr as
select path,
count(*) as views
from log
where path != '/'
group by path
order by views desc limit 5;
create view fr as select path, count(*) as views from log where path != '/' group by path order by views desc limit 5;
select (regexp_split_to_array(path, E'/article/'))[2] as Title,views from fr ;
create view PathRequired as
select (regexp_split_to_array(path, E'/article/'))[2] as path,
count(*) as views
from log
group by (regexp_split_to_array(path, E'/article/'))[2]
order by views desc limit 5;
select PathRequired.path,
count(*) as views
from PathRequired
where PathRequired.path != '/'
group by PathRequired.path
order by views desc limit 3;
var imp = "(regexp_split_to_array(path, E'/article/'))[2]";
select articles.title, count(*) as views
from articles join log
on articles.slug = imp
where path != '/'
group by imp, articles.title
order by views desc limit 3;
"select articles.title, count(*) as views from articles join log on articles.slug = "+imp+" where path != '/' group by "+imp+", articles.title order by views desc limit 3;";
//Answer
Q2:First view ( Joins the authors and articles table on author.id and articles.author)
create view First as select authors.name, articles.slug from authors join articles on authors.id = articles.author;
create view NameSlug as
select authors.name, articles.slug
from authors join articles
on authors.id = articles.author
select authors.name, count(*) as views
from authors join First
on authors.name = First.name
join log
on First.slug = (regexp_split_to_array(path, E'/article/'))[2]
where path != '/'
group by authors.name
order by views desc;
select authors.name, count(*) as views from authors join First on authors.name =
First.name join log on First.slug = (regexp_split_to_array(path, E'/article/'))
[2] where path != '/'group by authors.name order by views desc;
//r2
create view totalViews as
select date(time) as outputV,count(status) from log group by outputV;
select authors.name, count(*) as views
from authors join First
on authors.name = First.name
join log
on First.slug = (regexp_split_to_array(path, E'/article/'))[2]
where path != '/'
group by authors.name
order by views desc;
//Q2 Final
create view totalViews as
select to_char(time,'FMMonth FMDDth yyyy') as outputV,count(status) from log group by outputV;
create view totalErrors as
select to_char(time,'FMMonth FMDDth yyyy') as outputE,count(status) from log where status like '%404%' group by outputE;
create view percent as
select totalErrors.outputE,round(cast(cast(totalErrors.count as float)/ cast(totalViews.count as float)*100 as numeric),2) from totalViews join totalErrors on totalViews.outputV = totalErrors.outputE;
select * from percent where round > 1;
//Answer
Q3
create view totalViews as
select to_char(time,'FMMonth FMDDth yyyy') as outputV,count(status) from log group by outputV;
create view totalErrors as
select to_char(time,'FMMonth FMDDth yyyy') as outputE,count(status) from log where status like '%404%' group by outputE;
create view Answer as
select totalErrors.outputE,round(cast(cast(totalErrors.count as float)/ cast(totalViews.count as float)*100 as numeric),2) from totalViews join totalErrors on totalViews.outputV = totalErrors.outputE;
create view percent as
select totalErrors.outputE,round(cast(cast(totalErrors.count as float)/ cast(totalViews.count as float)*100 as numeric),2) from totalViews join totalErrors on totalViews.outputV = totalErrors.outputE;
select * from percent where round > 1;
select outputE,round( CAST(float Errorpercent as numeric), 2) where ErrorPercent > 1;
SELECT outputE,convert(decimal(10, 2), ); FROM Answer where ErrorPercent > 1;
CAST(float8 '3.1415927' as numeric), 2
select to_char(time,'FMMonth FMDDth yyyy'),status as output from ;
create view totalViews as
select to_char(time,'FMMonth FMDDth yyyy') as outputV,count(status) from log group by outputV;
create view totalErrors as
select to_char(time,'FMMonth FMDDth yyyy') as outputE,count(status) from log where status like '%404%' group by outputE;
create view Answer as
select totalErrors.outputE,cast(cast(totalErrors.count as float)/ cast(totalViews.count as float)*100 as float) as Errorpercent from totalViews join totalErrors on totalViews.outputV = totalErrors.outputE;
select * from Answer where ErrorPercent > 1;
create view totalViewsN as
select time,count(status) from log group by time;
create view totalErrorsN as
select time,count(status) from log where status like '%404%' group by time;
select cast(cast(te.count as float)/ cast(tv.count as float)*100 as float) as percent from tv join te on tv.outputV = te.output;
create view te as
select to_char(time,'dd') as output,count(status) from log where status like '%404%' group by output;
create view tv as
select to_char(time,'dd') as outputV,count(status) from log group by outputV;
select cast(cast(te.count as float)/ cast(tv.count as float)*100 as float) as percent from tv join te on tv.outputV = te.output;
select output,outputV,(te.count/tv.count) as er
from te join tv
on te.output = tv.outputV
group by er;
to_char(time,'FMMonth FMDDth yyyy') as output
select totalErrorsN.time,totalViewsN.time,((totalErrorsN.count / totalViewsN.count)*100) as Requests
from totalErrorsN join totalViewsN
on totalErrorsN.time = totalViewsN.time
group by totalErrorsN.time,totalViewsN.time,Requests;
limit 5;
select to_char(time,'FMMonth FMDDth yyyy') as output,(totalErrors.count / totalViews.count) as Requests
from log join totalErrors
on time = totalErrors.outputE
join totalViews
on totalErrors.outputE = totalViews.outputV
group by output;
select to_char(time,'FMMonth FMDDth yyyy') as output,(totalErrors.count / totalViews.count) as Requests
from log join totalErrors
on output = totalErrors.outputE
join totalViews
on totalErrors.outputE = totalViews.outputV
group by output;
select log.time ,((totalErrorsN.count / totalViewsN.count)*100) as Requests
from log join totalErrorsN
on log.time = totalErrorsN.time
join totalViewsN
on totalErrorsN.time = totalViewsN.time
group by log.time,Requests
having Requests > 1;