Page Menu
Home
Phabricator
Search
Configure Global Search
Log In
Files
F37141688
summary.sql
matmarex (Bartosz Dziewoński)
Actions
View File
Edit File
Delete File
View Transforms
Subscribe
Authored By
•
matmarex
Jul 17 2023, 7:59 PM
2023-07-17 19:59:15 (UTC+0)
Size
1 KB
Referenced Files
None
Subscribers
None
summary.sql
View Options
with
_discussiontools
as
(
select
it_id
,
it_itemname
,
itp_page_id
,
itid_id
from
discussiontools_items
join
discussiontools_item_pages
on
itp_items_id
=
it_id
join
discussiontools_item_revisions
on
itr_items_id
=
it_id
and
itr_revision_id
=
itp_newest_revision_id
join
discussiontools_item_ids
on
itid_id
=
itr_itemid_id
join
page
on
page_id
=
itp_page_id
and
page_latest
=
itr_revision_id
-- Ignore placeholder headings on pages with no headings, which would all be duplicates of each other
where
it_itemname
!=
'h-'
-- Ignore items where the newest revision containing them is not the latest revision of the page
and
page_id
is
not
null
-- Ignore items which are transcluded from another page
and
itr_transcludedfrom
is
null
),
_counts
as
(
select
left
(
it_itemname
,
1
)
as
type
,
number_of_copies
,
count
(
*
)
as
number_of_items
,
group_concat
(
it_itemname
order
by
rand
()
separator
' '
limit
3
)
as
examples
from
(
select
it_itemname
,
count
(
*
)
as
number_of_copies
from
_discussiontools
group
by
it_itemname
)
x
group
by
type
,
number_of_copies
),
_summary
as
(
select
(
select
number_of_items
from
_counts
where
number_of_copies
=
1
and
type
=
'c'
)
as
uniq_comments
,
-- (select sum(number_of_items) from _counts where number_of_copies!=1 and type='c') as nonuniq_comments,
(
select
sum
(
number_of_items
*
number_of_copies
)
from
_counts
where
number_of_copies
!=
1
and
type
=
'c'
)
as
nonuniq_comments
,
(
select
number_of_items
from
_counts
where
number_of_copies
=
1
and
type
=
'h'
)
as
uniq_headings
,
-- (select sum(number_of_items) from _counts where number_of_copies!=1 and type='h') as nonuniq_headings,
(
select
sum
(
number_of_items
*
number_of_copies
)
from
_counts
where
number_of_copies
!=
1
and
type
=
'h'
)
as
nonuniq_headings
)
select
uniq_comments
,
nonuniq_comments
,
nonuniq_comments
/
(
uniq_comments
+
nonuniq_comments
)
as
'%nonuniq_comments'
,
uniq_headings
,
nonuniq_headings
,
nonuniq_headings
/
(
uniq_headings
+
nonuniq_headings
)
as
'%nonuniq_headings'
from
_summary
File Metadata
Details
Attached
Mime Type
text/plain
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
10923824
Default Alt Text
summary.sql (1 KB)
Attached To
Mode
T321228: Analyze permalinks table to see how many duplicates exist
Attached
Detach File
Event Timeline
Log In to Comment