Page Menu
Home
Phabricator
Search
Configure Global Search
Log In
Files
F190564
page_title_hist.hql
JAllemandou (joal)
Actions
View File
Edit File
Delete File
View Transforms
Subscribe
Mute Notifications
Award Token
Flag For Later
Authored By
JAllemandou
Jul 9 2015, 12:50 PM
2015-07-09 12:50:52 (UTC+0)
Size
1 KB
Referenced Files
None
Subscribers
None
page_title_hist.hql
View Options
SELECT
dt
,
SUM
(
CASE
WHEN
(
view_count
<
2
)
THEN
1
ELSE
0
END
)
AS
1
dist
,
SUM
(
CASE
WHEN
((
view_count
>=
2
)
AND
((
view_count
<
5
)))
THEN
1
ELSE
0
END
)
AS
2
_4dist
,
SUM
(
CASE
WHEN
((
view_count
>=
5
)
AND
((
view_count
<
10
)))
THEN
1
ELSE
0
END
)
AS
5
_9dist
,
SUM
(
CASE
WHEN
((
view_count
>=
10
)
AND
((
view_count
<
25
)))
THEN
1
ELSE
0
END
)
AS
10
_24dist
,
SUM
(
CASE
WHEN
((
view_count
>=
25
)
AND
((
view_count
<
50
)))
THEN
1
ELSE
0
END
)
AS
25
_49dist
,
SUM
(
CASE
WHEN
((
view_count
>=
50
)
AND
((
view_count
<
100
)))
THEN
1
ELSE
0
END
)
AS
50
_99dist
,
SUM
(
CASE
WHEN
(
view_count
>=
100
)
THEN
1
ELSE
0
END
)
AS
100
_plusdist
,
SUM
(
CASE
WHEN
(
view_count
<
2
)
THEN
view_count
ELSE
0
END
)
AS
1
agg
,
SUM
(
CASE
WHEN
((
view_count
>=
2
)
AND
((
view_count
<
5
)))
THEN
view_count
ELSE
0
END
)
AS
2
_4agg
,
SUM
(
CASE
WHEN
((
view_count
>=
5
)
AND
((
view_count
<
10
)))
THEN
view_count
ELSE
0
END
)
AS
5
_9agg
,
SUM
(
CASE
WHEN
((
view_count
>=
10
)
AND
((
view_count
<
25
)))
THEN
view_count
ELSE
0
END
)
AS
10
_24agg
,
SUM
(
CASE
WHEN
((
view_count
>=
25
)
AND
((
view_count
<
50
)))
THEN
view_count
ELSE
0
END
)
AS
25
_49agg
,
SUM
(
CASE
WHEN
((
view_count
>=
50
)
AND
((
view_count
<
100
)))
THEN
view_count
ELSE
0
END
)
AS
50
_99agg
,
SUM
(
CASE
WHEN
(
view_count
>=
100
)
THEN
view_count
ELSE
0
END
)
AS
100
_plusagg
FROM
(
SELECT
CONCAT
(
CONCAT
(
LPAD
(
year
,
4
,
"0"
),
"-"
,
LPAD
(
month
,
2
,
"0"
),
"-"
,
LPAD
(
day
,
2
,
"0"
),
"T"
,
LPAD
(
hour
,
2
,
"0"
),
":00:00"
)
)
AS
dt
,
project
,
language_variant
,
page_title
,
SUM
(
view_count
)
AS
view_count
FROM
wmf
.
pageview_hourly
WHERE
year
=
2015
AND
month
=
7
AND
day
=
7
AND
agent_type
=
'user'
AND
page_title
<>
'-'
GROUP
BY
year
,
month
,
day
,
hour
,
project
,
language_variant
,
page_title
)
pages
GROUP
BY
dt
ORDER
BY
dt
LIMIT
1000000000
;
File Metadata
Details
Attached
Mime Type
text/plain
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
182137
Default Alt Text
page_title_hist.hql (1 KB)
Attached To
Mode
T101785: Generate test data for Pageview API {slug} [5 pts]
Attached
Detach File
Event Timeline
Log In to Comment