SQL для Вікіпроектів: відмінності між версіями

Матеріал з Вікіпідручника
Вилучено вміст Додано вміст
Ilya (обговорення | внесок)
Немає опису редагування
Ilya (обговорення | внесок)
Немає опису редагування
Рядок 14: Рядок 14:
Він дозволяє писати запити мовою SQL до копій баз даних вікіпроектів.
Він дозволяє писати запити мовою SQL до копій баз даних вікіпроектів.


=== Кількість сторінок ===
== Список сторінок ==
[https://quarry.wmflabs.org/query/14107 Запит у Quarry]
Виведемо всі колонки із таблиці сторінок page ([https://quarry.wmflabs.org/query/14106 Запит у Quarry])
<tt>
SELECT
count(*)
FROM
ukwikibooks_p.page
</tt>

У Вікіпідручнику запит видає цифру 2773, тоді як статистика самого сайту 3596. Кількість сторінок — доволі технічна, а не змістовна статистика і схоже рахується дещо по різному. Оскільки сторінок взагалі у Вікіпдіручнику небагато, то відмінність вийшла досить значна


Виведемо кількість сторінок у Вікіпедії: ([https://quarry.wmflabs.org/query/14108 Запит у Quarry])
<tt>
SELECT
count(*)
FROM
ukwiki_p.page
</tt>

За нашим запитом — 1 986 487, за статистикою сайту — 1 986 749.
Тут уже відмінність всього на <code>1 986 749 - 1 986 487 = 262</code> сторінки, або лише 0.01%. Тобто за великої кількості сторінок різниця між різними способами практично непомітна.

=== Кількість статей ===

Тепер виведемо кількість статей.

Спочатку зрозуміємо, що таке стаття, і чим вона відрізняється від сторінки.
У [[:mw:Help:Magic_words/uk#Статистика|довідці зі статистичних службових слів]] пише ''"Кількість сторінок [[:mw:Manual:Using_custom_namespaces#Content_namespaces|в основному просторі назв]]."''

Проте за посиланням ''[[:mw:Manual:Using_custom_namespaces#Content_namespaces|в основному просторі назв]]'' дається точніше визначення:
#Be in the main namespace, or a defined content namespace ''// Бути в основному [[:mw:Help:Namespaces/uk|просторі назв]], або у визначеному просторі імен для вмісту''
#Not be a redirect page ''// не бути [[:w:Довідка:Перенаправлення|сторінкою перенаправлення]]''
#Contain at least one internal link ''// Містити принаймні одне [[:mw:Help:Links/uk|внутрішнє посилання]]''

Для початку виведемо всі колонки із таблиці сторінок page ([https://quarry.wmflabs.org/query/14106 Запит у Quarry])


<tt>
<tt>
Рядок 94: Рядок 60:


==== Простори назв ====
==== Простори назв ====
Спочатку розберемося із першою умовою:
*Бути в основному [[:mw:Help:Namespaces/uk|просторі назв]]

Для цього виведемо із таблиці page лише колонки, які нас цікавлять: page_namespace і page_title ([https://quarry.wmflabs.org/query/14110 Запит у Quarry])
Для цього виведемо із таблиці page лише колонки, які нас цікавлять: page_namespace і page_title ([https://quarry.wmflabs.org/query/14110 Запит у Quarry])


Рядок 395: Рядок 358:
|1659||14||Українська_мова||[[:{{ns:14}}:Українська_мова]]
|1659||14||Українська_мова||[[:{{ns:14}}:Українська_мова]]
|}
|}


=== Кількість сторінок ===
[https://quarry.wmflabs.org/query/14107 Запит у Quarry]
<tt>
SELECT
count(*)
FROM
ukwikibooks_p.page
</tt>

У Вікіпідручнику запит видає цифру 2773, тоді як статистика самого сайту 3596. Кількість сторінок — доволі технічна, а не змістовна статистика і схоже рахується дещо по різному. Оскільки сторінок взагалі у Вікіпдіручнику небагато, то відмінність вийшла досить значна


Виведемо кількість сторінок у Вікіпедії: ([https://quarry.wmflabs.org/query/14108 Запит у Quarry])
<tt>
SELECT
count(*)
FROM
ukwiki_p.page
</tt>

За нашим запитом — 1 986 487, за статистикою сайту — 1 986 749.
Тут уже відмінність всього на <code>1 986 749 - 1 986 487 = 262</code> сторінки, або лише 0.01%. Тобто за великої кількості сторінок різниця між різними способами практично непомітна.

=== Кількість статей ===

Тепер виведемо кількість статей.

Спочатку зрозуміємо, що таке стаття, і чим вона відрізняється від сторінки.
У [[:mw:Help:Magic_words/uk#Статистика|довідці зі статистичних службових слів]] пише ''"Кількість сторінок [[:mw:Manual:Using_custom_namespaces#Content_namespaces|в основному просторі назв]]."''

Проте за посиланням ''[[:mw:Manual:Using_custom_namespaces#Content_namespaces|в основному просторі назв]]'' дається точніше визначення:
#Be in the main namespace, or a defined content namespace ''// Бути в основному [[:mw:Help:Namespaces/uk|просторі назв]], або у визначеному просторі імен для вмісту''
#Not be a redirect page ''// не бути [[:w:Довідка:Перенаправлення|сторінкою перенаправлення]]''
#Contain at least one internal link ''// Містити принаймні одне [[:mw:Help:Links/uk|внутрішнє посилання]]''

Версія за 09:04, 17 листопада 2016

SQL — абревіатура Structured Query Language — Структурована мова Запитів.


Є інструмент Quarry (англійською Quarry — Кар'єр, слово також подібне до Query, що англійською означає — Запит (до бази даних))

Він дозволяє писати запити мовою SQL до копій баз даних вікіпроектів.

Список сторінок

Виведемо всі колонки із таблиці сторінок page (Запит у Quarry)

SELECT * 
FROM 
  ukwikibooks_p.page

Запит повертає всі рядки та всі колонки із таблиці сторінок page. Декілька перших рядків виглядають так:

page_id page_namespace page_title page_restrictions page_counter page_is_redirect page_is_new page_random page_touched page_links_updated page_latest page_len page_no_title_convert page_content_model
1 0 Main_Page 0 1 0 0.425922502496 20130412175651 None 7988 45 0 wikitext
2 0 Головна_сторінка 0 0 0 0.429793028827 20161101043156 20161101043221 19411 1441 0 wikitext
931 10 Wikibrothers 0 0 0 0.085830608583 20140817194306 None 16493 1304 0 wikitext
938 3 195.39.196.2 0 0 0 0.207369529163 20050703143510 None 2057 571 0 wikitext
942 2 Ashapochka 0 0 1 0.301798192112 20130407223156 None 2061 128 0 wikitext
972 3 Cl 0 0 0 0.208406955811 20080426213654 None 5216 3014 0 wikitext
991 2 Ilya 0 0 0 0.059346930577 20161112184738 None 3756 40 0 wikitext
1467 14 Головна 0 0 0 0.841395774183 20161103103038 20161103103755 22576 189 0 wikitext
1495 2 Albedo 0 0 0 0.659403327642 20161011202019 None 14295 914 0 wikitext
1549 4 Портал_спільноти 0 0 0 0.140178074975 20161113235301 20161113235301 25528 393727 0 wikitext
1557 2 Zigger 0 0 1 0.460886007336 20130407223200 None 2921 25 0 wikitext
1558 3 Zigger 0 0 1 0.15922569047 20051203022443 None 2922 30 0 wikitext
1578 3 Gangleri 0 0 0 0.646923746659 20151216234438 20151020233343 24471 12378 0 wikitext
1579 2 Gangleri 0 0 0 0.817194667656 20151020232220 None 4599 5249 0 wikitext
1659 14 Українська_мова 0 0 0 0.281435888335 20160709072326 20160709072329 22587 586 0 wikitext

Простори назв

Для цього виведемо із таблиці page лише колонки, які нас цікавлять: page_namespace і page_title (Запит у Quarry)

SELECT 
  page_namespace, page_title 
FROM 
  ukwikibooks_p.page

Сортування

Несподівано ми отримали зовсім інші перші рядки:

page_namespace page_title
0 AutoIt
0 Be_able_to
0 Bison
0 Blender
0 Blender._Стартовий_посібник
0 Blender/Вступ
0 Blender/Стартовий_посібник
0 Blender/Стартовий_посібник_(український_інтерфейс)
0 C++
0 C++/Віртуальний_конструктор_С++
0 C++/Ключові_слова
0 C++/Ключові_слова/const_cast
0 C++/Ключові_слова/dynamic_cast
0 C++/Ключові_слова/reinterpret_cast
0 C++/Ключові_слова/static_cast
0 CSS

Чому так? Якщо не вказати явно порядок сортування, база даних чи інструмент для виконання запитів використовує певний порядок сортування за умовчанням.

В нашому випадку сортування відбулося за тими колонками, які мі запитували — page_namespace і page_title.

Ми можемо вказати це явно, додавши до запиту ORDER BY page_namespace, page_title, і отримати такий самий результат (Запит у Quarry)

SELECT 
  page_namespace, page_title 
FROM 
  ukwikibooks_p.page 
ORDER BY 
  page_namespace, page_title

Тепер отримаємо такий же порядок сортування, як був у нас спочатку, коли ми отримували всі колонки. Для цього додамо сортування за колонкою page_id: ORDER BY page_id (Запит у Quarry)

SELECT 
  page_id, page_namespace, page_title 
FROM 
  ukwikibooks_p.page 
ORDER BY 
  page_id

Тепер результат відсортовано за page_id і перші рядки виглядають так:

page_id page_namespace page_title
1 0 Main_Page
2 0 Головна_сторінка
931 10 Wikibrothers
938 3 195.39.196.2
942 2 Ashapochka
972 3 Cl
991 2 Ilya
1467 14 Головна
1495 2 Albedo
1549 4 Портал_спільноти
1557 2 Zigger
1558 3 Zigger
1578 3 Gangleri
1579 2 Gangleri
1659 14 Українська_мова
Які є простори назв?

Отже, що означають цифри 0, 2, 3, 4, 10, 14 у колонці page_namespace? Це коди, які відповідають різним просторам імен. У довідці з просторів імен є табличка із номерами стандартних просторів імен:

Індекс

Назва

Обговорення
0 (Основний) Обговорення 1
2 Користувач Обговорення користувача 3
4 Вікіпідручник Обговорення Вікіпідручника 5
6 Файл Обговорення файлу 7
8 MediaWiki Обговорення MediaWiki 9
10 Шаблон Обговорення шаблону 11
12 Довідка Обговорення довідки 13
14 Категорія Обговорення категорії 15
-1 Спеціальна N/A
Медіа Media

У Вікірозмітці можна вивести префікс простору назв із допомогою магічного слова {{ns}}:

Простори імен для статей Простори імен для сторінок обговорення
Використання Результат Використання Результат
{{ns:-2}} / {{ns:Media}} Медіа {{ns:-1}} / {{ns:Special}} Спеціальна
(no talk page)
{{ns:0}} / {{ns:}} {{ns:1}} / {{ns:Talk}} Обговорення
{{ns:2}} / {{ns:User}} Користувач {{ns:3}} / {{ns:User talk}} Обговорення користувача
{{ns:4}} / {{ns:Project}} Вікіпідручник
Varies between wikis
{{ns:5}} / {{ns:Project talk}} Обговорення Вікіпідручника
{{ns:6}} / {{ns:File}} or {{ns:Image}} Файл {{ns:7}} / {{ns:File talk}} or {{ns:Image talk}} Обговорення файлу
{{ns:8}} / {{ns:MediaWiki}} MediaWiki {{ns:9}} / {{ns:MediaWiki talk}} Обговорення MediaWiki
{{ns:10}} / {{ns:Template}} Шаблон {{ns:11}} / {{ns:Template talk}} Обговорення шаблону
{{ns:12}} / {{ns:Help}} Довідка {{ns:13}} / {{ns:Help talk}} Обговорення довідки
{{ns:14}} / {{ns:Category}} Категорія {{ns:15}} / {{ns:Category talk}} Обговорення категорії
Список сторінок із посиланням, що включає префікс простору назв

SQL Функція CONCAT об’єднує в один рядок значення своїх параметрів

Наприклад

 CONCAT('[[', page_title, ']]') 

поверне рядок який складається із двох квадратних дужок, які відкриваються, значення колонки page_title та дві квадратні дужки, які закриваються, що разом дасть вікірозмітку для посилання на сторінку з назвою page_title

Додамо умову, що вибираємо лише сторінки із основного простору (простору статей)

 WHERE page_namespace = 0 

і виведемо вікірозмітку посилання на статті (код у Quarry)

 SELECT 
   CONCAT('[[', page_title, ']]') AS page_link
 FROM 
   ukwikibooks_p.page 
 WHERE 
   page_namespace = 0

Тепер ми можемо вивести список сторінок, що містить повне посилання на сторінку, яке включає і префікс простору назв (код у Quarry)

 SELECT 
   page_id, 
   page_namespace,
   page_title, 
   CONCAT(
     '[[:{{ns:', page_namespace, '}}:', page_title, ']]'
   ) AS page_link
 FROM 
   ukwikibooks_p.page 
 ORDER BY 
   page_id

Результат:

page_id page_namespace page_title page_link
1 0 Main_Page [[::Main_Page]]
2 0 Головна_сторінка [[::Головна_сторінка]]
931 10 Wikibrothers Шаблон:Wikibrothers
938 3 195.39.196.2 Обговорення користувача:195.39.196.2
942 2 Ashapochka Користувач:Ashapochka
972 3 Cl Обговорення користувача:Cl
991 2 Ilya Користувач:Ilya
1467 14 Головна Категорія:Головна
1495 2 Albedo Користувач:Albedo
1549 4 Портал_спільноти Вікіпідручник:Портал_спільноти
1557 2 Zigger Користувач:Zigger
1558 3 Zigger Обговорення користувача:Zigger
1578 3 Gangleri Обговорення користувача:Gangleri
1579 2 Gangleri Користувач:Gangleri
1659 14 Українська_мова Категорія:Українська_мова


Кількість сторінок

Запит у Quarry

SELECT 
  count(*) 
FROM 
  ukwikibooks_p.page

У Вікіпідручнику запит видає цифру 2773, тоді як статистика самого сайту 3596. Кількість сторінок — доволі технічна, а не змістовна статистика і схоже рахується дещо по різному. Оскільки сторінок взагалі у Вікіпдіручнику небагато, то відмінність вийшла досить значна


Виведемо кількість сторінок у Вікіпедії: (Запит у Quarry)

SELECT 
  count(*)
FROM 
  ukwiki_p.page

За нашим запитом — 1 986 487, за статистикою сайту — 1 986 749. Тут уже відмінність всього на 1 986 749 - 1 986 487 = 262 сторінки, або лише 0.01%. Тобто за великої кількості сторінок різниця між різними способами практично непомітна.

Кількість статей

Тепер виведемо кількість статей.

Спочатку зрозуміємо, що таке стаття, і чим вона відрізняється від сторінки. У довідці зі статистичних службових слів пише "Кількість сторінок в основному просторі назв."

Проте за посиланням в основному просторі назв дається точніше визначення:

  1. Be in the main namespace, or a defined content namespace // Бути в основному просторі назв, або у визначеному просторі імен для вмісту
  2. Not be a redirect page // не бути сторінкою перенаправлення
  3. Contain at least one internal link // Містити принаймні одне внутрішнє посилання