Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
This query is marked as a draft
This query has been published
by
Solidest
.
Toggle Highlighting
SQL
WITH base_titles AS ( SELECT -- Base title processing for grouping CASE WHEN page_title REGEXP '[0-9]{1,2}$' THEN LEFT(page_title, LENGTH(page_title) - IF(page_title REGEXP '[0-9][0-9]$', 2, 1)) ELSE page_title END AS raw_base_title, -- Display version of base title with special char trimming CONCAT('[[Ш:', REGEXP_REPLACE( REGEXP_REPLACE( CASE WHEN page_title REGEXP '[0-9]{1,2}$' THEN LEFT(page_title, LENGTH(page_title) - IF(page_title REGEXP '[0-9][0-9]$', 2, 1)) ELSE page_title END, '[-+№_/]+$', ''), -- Удаляем символы -, +, № и _ '[-+№_/][-+№_/]+$', '') -- Удаляем повторяющиеся символы в конце , ']]') AS base_title_display, CONCAT('[[Ш:', page_title, ']]') AS full_title, page_title FROM ruwiki_p.page WHERE page_namespace = 10 AND page_is_redirect = 0 -- Исключение для четырёхзначных годов вконце названия AND page_title NOT REGEXP '[0-9]{4}$' -- Исключения по префиксу AND page_title NOT LIKE 'CC-BY-%' AND page_title NOT LIKE 'Cite_doi/%' AND page_title NOT LIKE 'Cite_pmid/%' AND page_title NOT LIKE 'Kepler-%' AND page_title NOT LIKE 'Lang-%' AND page_title NOT LIKE 'Potd/%' AND page_title NOT LIKE 'User_lang/%' AND page_title NOT LIKE 'Userbox/Пользователь_Windows%' AND page_title NOT LIKE 'АТХ_код_%' AND page_title NOT LIKE 'Автодорога_%' AND page_title NOT LIKE 'Манкала_%' AND page_title NOT LIKE 'Источник:%' AND page_title NOT LIKE 'Книга:%' AND page_title NOT LIKE 'Публикация:%' AND page_title NOT LIKE 'События_дня/%' AND page_title NOT LIKE 'Шахматная_диаграмма_%' -- Исключения для названий короче трёх символов AND LENGTH( CASE WHEN page_title REGEXP '[0-9]{1,2}$' THEN LEFT(page_title, LENGTH(page_title) - IF(page_title REGEXP '[0-9][0-9]$', 2, 1)) ELSE page_title END ) >= 3 ), duplicates AS ( SELECT raw_base_title, COUNT(*) as count FROM base_titles GROUP BY raw_base_title HAVING count > 1 ) SELECT b.base_title_display, b.full_title FROM base_titles b JOIN duplicates d ON b.raw_base_title = d.raw_base_title ORDER BY b.raw_base_title, b.page_title;
By running queries you agree to the
Cloud Services Terms of Use
and you irrevocably agree to release your SQL under
CC0 License
.
Submit Query
Stop Query
All SQL code is licensed under
CC0 License
.
Checking query status...