Всем привет,
Возможно ли создать запрос в Data Explorer, который выводит «топ X» тем по «оценочному времени чтения»?
Мне бы очень хотелось узнать, какие темы на нашем Discourse занимают больше всего времени для чтения 
(И, кстати, продолжая этот пост от @simon, я не могу добавить тег data-explorer к этому сообщению?)
Думаю, подход, который я предложил в той теме, нужно доработать. Одна из проблем в том, что только пользователи уровня TL3 и выше могут добавлять теги к сообщениям на Meta. Это означает, что большинство пользователей сайта не смогут следовать моим инструкциям. Вторая проблема заключается в том, что тег data-explorer будет присвоен как темам без ответов, так и темам с ответами. Это мало поможет в поиске запросов.
Извините за поздний ответ. Я увлёкся вопросом о том, как организовать запросы Data Explorer на сайте. Использование тега data-explorer кажется идеальным решением, но темы, содержащие запрос Data Explorer, должны помечаться пользователем со статусом TL3.
Думаю, что запрос, подобный приведённому ниже, предоставит вам нужную информацию:
SELECT
topic_id,
category_id,
SUM(total_msecs_viewed) / 60000 AS estimated_minutes_read
FROM topic_users tu
JOIN topics t ON t.id = tu.topic_id
WHERE t.deleted_at IS NULL
AND t.archetype = 'regular'
GROUP BY tu.topic_id, category_id
ORDER BY estimated_minutes_read DESC
LIMIT 100
Оператор LIMIT 100 в последней строке запроса можно изменить или удалить, если вы хотите получить больше результатов.
Интересно, что темой с самым большим зафиксированным временем чтения на Meta является Setup DiscourseConnect - Official Single-Sign-On for Discourse (sso). На данный момент оно составляет 126048 минут.
Привет, @simon
Эта формула верна?
Если я случайным образом выберу четыре или пять записей и сравню результат столбца «Ориентировочное время чтения» в этом запросе с ориентировочным временем чтения в самой теме, я получаю два очень разных числа? 
Похоже, этот запрос возвращает темы, которые читали дольше всего, а не темы, на чтение которых требуется больше всего времени?
Ах, это может объяснить проблему.
Полагаю, total_msecs_viewed — не тот столбец, который нужно использовать здесь?
Вы можете использовать среднее время, которое пользователи тратят на чтение темы.
В этом случае просто замените функцию SUM на AVG, это будет выглядеть так:
SELECT
topic_id,
category_id,
AVG(total_msecs_viewed) / 60000 AS estimated_minutes_read
FROM topic_users tu
JOIN topics t ON t.id = tu.topic_id
WHERE t.deleted_at IS NULL
AND t.archetype = 'regular'
GROUP BY tu.topic_id, category_id
ORDER BY estimated_minutes_read DESC
LIMIT 100
Спасибо за предложение @michebs, но, боюсь, это тоже совсем не то.
Несколько примеров:
| Что говорит запрос | Что говорит тема |
|---|---|
| 438 | 61 |
| 353 | 58 |
| 335 | 40 |
| 196 | 24 |
Но это означало бы, что в среднем человеку требуется 438 минут, чтобы прочитать эту главную тему? Это кажется маловероятным. Возможно, это звучит глупо, но не забыли ли вы добавить достаточное количество нулей в число 60 000?
Редактирование: Или, может быть, среднее значение включает все повторные чтения темы? То есть один проход занимает 61 минуту, но на самом деле пользователи проводят в этой теме в среднем 438 минут.
Хотя теперь мне очень интересно узнать, как рассчитывается «Ориентировочное время чтения» для резюме, поскольку в идеале эти значения должны совпадать. Даже если уменьшить их в десять раз, это даст лишь приблизительную оценку.
Да, именно так 
Я немного поискал и нашёл это: "There are 84 replies with an estimated read time of 0 minutes." - #9 by nbianca.
Мне сложно разобраться в таких вещах, но, похоже, используется формула: количество слов × время (плюс минимальное время для постов без слов, например, с изображениями).
Также нашёл вот это, где есть намёк на то, как может называться итоговое значение: (хотя это старый материал, возможно, что-то изменилось?)
Не очень-то это помогло, конечно, но решил поделиться на всякий случай. 
Надеюсь, вы найдёте ответы, которые ищете. 
[quote=“Richie, пост:1, тема:196761”]
Можно ли создать запрос в Data Explorer, который выведет «топ X» тем по «оценочному времени чтения»?[/quote]
Я ещё раз посмотрел на это, и кажется (в самой простой форме), что это количество слов в теме (topic.word_count), умноженное на настройку администратора «количество слов в минуту для чтения» (по умолчанию 500 слов/мин). Поэтому я думаю, что этот запрос выдаст топ X тем, которые «дольше всего читать»:
-- [params]
-- integer :limit = 10
SELECT t.id as topic_id, (t.word_count)/500+1 AS estimated_read_time
FROM topics t
WHERE t.word_count IS NOT NULL
AND t.archetype = 'regular'
ORDER BY t.word_count DESC
LIMIT :limit
Хотя есть и альтернативное «минимум 4 секунды»: (количество постов × 4)/60. Это нужно для учёта тем с фотографиями, где нет подсчёта слов. Таким образом, система вычисляет оба значения и отображает то, которое больше. Но я пока не до конца разобрался, как добавить это в запрос.
К сожалению, у меня нет достаточно большого сайта, чтобы правильно протестировать это. На небольшой тестовой выборке всё работало, но, возможно, потребуется доработка.
Редактирование: Я добавил параметр «limit», чтобы приблизить запрос к требованиям исходного поста. 
Черт возьми, кажется, он понял!
@JammyDodger Я выполнил ваш запрос, вот несколько скриншотов для справки.
Сначала «топ-10»:
И, как и следовало ожидать:
Есть пара цифр, которые не совсем совпадают, но это очень близко!
Похоже, мне точно нужно разобраться, как добавить фото. Я ещё не сдался.
Я попробовал ещё раз. Пока не уверен на 100%, так как у меня недостаточно данных для проверки, но мои тестовые темы были найдены.
-- [params]
-- integer :limit = 10
WITH read_time AS (
SELECT t.id as topic_id,
(t.word_count)/500+1 as word_count_time,
(t.posts_count*4)/60+1 as post_count_time
FROM topics t
WHERE t.word_count IS NOT NULL
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
)
SELECT topic_id, CONCAT (CASE WHEN word_count_time > post_count_time THEN word_count_time ELSE post_count_time END, ' мин') AS estimated_reading_time
FROM read_time
ORDER BY estimated_reading_time DESC
LIMIT :limit