# 가장 긴 '예상 읽기 시간' 주제를 나열하는 데이터 탐색기 쿼리?
**URL:** https://meta.discourse.org/t/data-explorer-query-to-list-the-longest-estimated-read-time-topics/196761
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [7월 11, 2021, 9:39오전 UTC](https://meta.discourse.org/t/data-explorer-query-to-list-the-longest-estimated-read-time-topics/196761 "2021-07-11T09:39:44Z")
**Posts on this page:** 15
**Page:** 1
### Author:  [@Richie](https://meta.discourse.org/u/Richie)
#### Post date: [7월 11, 2021, 9:39오전 UTC](https://meta.discourse.org/t/data-explorer-query-to-list-the-longest-estimated-read-time-topics/196761/1 "2021-07-11T09:39:44Z")
여러분 안녕하세요,
“추정 읽기 시간” 기준으로 “상위 X개” 주제를 나열할 수 있는 데이터 탐색기 쿼리를 만들 수 있을까요?
저희 Discourse에서 읽는 데 가장 시간이 많이 걸리는 주제들이 무엇인지 궁금합니다. 😃
_(그리고 주제에서 살짝 벗어난 이야기지만, @simon 님의 [이 게시물](https://meta.discourse.org/t/what-cool-data-explorer-queries-have-you-come-up-with/43516/290?u=richie)에 이어서, 이 게시물에 data-explorer 태그를 추가하는 방법을 찾을 수 없네요.)_
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [7월 12, 2021, 11:34오후 UTC](https://meta.discourse.org/t/data-explorer-query-to-list-the-longest-estimated-read-time-topics/196761/2 "2021-07-12T23:34:31Z")
> [@Richie](#):
>
> (그리고 약간 주제에서 벗어난 이야기인데, @simon 님의 [이 게시물](https://meta.discourse.org/t/what-cool-data-explorer-queries-have-you-come-up-with/43516/290)에 이어서, 이 게시물에 data-explorer 태그를 추가하는 법을 찾지 못하겠습니다?)
그 주제에서 제가 제안한 방식은 개선이 필요하다고 생각합니다. 이 방식의 문제점 중 하나는 Meta에서 게시물을 태그할 수 있는 사용자가 TL3 이상만이라는 것입니다. 이는 사이트 사용자의 대다수가 제 지시를 따를 수 없음을 의미합니다. 또 다른 문제는 답변이 달리지 않은 주제와 답변이 달린 주제 모두에 data-explorer 태그가 붙게 된다는 점입니다. 이는 쿼리를 검색하는 데 큰 도움이 되지 않을 것입니다.
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [8월 13, 2021, 9:22오후 UTC](https://meta.discourse.org/t/data-explorer-query-to-list-the-longest-estimated-read-time-topics/196761/3 "2021-08-13T21:22:51Z")
> [@Richie](#):
>
> “추정 읽기 시간” 기준으로 “상위 X개” 주제를 나열할 수 있는 데이터 탐색기 쿼리를 만들 수 있을까요?
답변이 늦어서 죄송합니다. 사이트에서 데이터 탐색기 쿼리를 어떻게 정리할지에 대한 질문에 몰두하고 있었습니다. `data-explorer` 태그를 사용하는 것이 이상적인 해결책으로 보이지만, 데이터 탐색기 쿼리를 포함하는 주제는 TL3 상태인 사용자가 태그를 지정해야 합니다.
아래와 같은 쿼리를 사용하면 원하는 정보를 얻을 수 있을 것입니다:
```sql
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)](https://meta.discourse.org/t/discourseconnect-official-single-sign-on-for-discourse-sso/13045) 입니다. 현재 126,048분입니다.
---
### Author:  [@Richie](https://meta.discourse.org/u/Richie)
#### Post date: [8월 16, 2021, 12:41오후 UTC](https://meta.discourse.org/t/data-explorer-query-to-list-the-longest-estimated-read-time-topics/196761/4 "2021-08-16T12:41:34Z")
안녕하세요 @simon
그 수식이 맞나요?
무작위로 4개나 5개를 골라서 이 쿼리의 예상 읽기 시간 열 결과를 토픽 자체의 예상 읽기 시간과 비교해 보면, 두 수가 매우 다릅니다. 🤔
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [8월 16, 2021, 1:51오후 UTC](https://meta.discourse.org/t/data-explorer-query-to-list-the-longest-estimated-read-time-topics/196761/5 "2021-08-16T13:51:43Z")
그 쿼리는 읽는 데 가장 오래 걸리는 주제(Topics)가 아니라, 가장 오래 읽힌 주제(Topics)를 반환하는 것 같습니다.
---
### Author:  [@Richie](https://meta.discourse.org/u/Richie)
#### Post date: [8월 16, 2021, 2:23오후 UTC](https://meta.discourse.org/t/data-explorer-query-to-list-the-longest-estimated-read-time-topics/196761/6 "2021-08-16T14:23:30Z")
아, 그거라면 문제가 설명될 수 있겠네요.
`total_msecs_viewed`가 여기서는 잘못된 컬럼인 것 같지 않나요?
---
### Author:  [@michebs](https://meta.discourse.org/u/michebs)
#### Post date: [8월 16, 2021, 3:28오후 UTC](https://meta.discourse.org/t/data-explorer-query-to-list-the-longest-estimated-read-time-topics/196761/8 "2021-08-16T15:28:50Z")
사용자가 주제를 읽는 데 걸리는 평균 시간을 사용할 수 있습니다.
이 경우 **SUM** 함수를 **AVG** 로만 변경하면 됩니다. 다음과 같습니다:
```sql
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
```
---
### Author:  [@Richie](https://meta.discourse.org/u/Richie)
#### Post date: [8월 17, 2021, 6:17오전 UTC](https://meta.discourse.org/t/data-explorer-query-to-list-the-longest-estimated-read-time-topics/196761/9 "2021-08-17T06:17:10Z")
제안을 해 주셔서 감사합니다, @michebs. 하지만 그 방법도 완전히 빗나간 것 같습니다.
몇 가지 예시:
| 쿼리가 말하는 것 | 토픽이 말하는 것 |
| --- | --- |
| 438 | 61 |
| 353 | 58 |
| 335 | 40 |
| 196 | 24 |
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [8월 17, 2021, 6:30오전 UTC](https://meta.discourse.org/t/data-explorer-query-to-list-the-longest-estimated-read-time-topics/196761/10 "2021-08-17T06:30:23Z")
하지만 그렇게 되면 평균적으로 한 사람이 그 상위 주제를 읽는 데 438분이 걸린다는 뜻이 됩니다. 그럴 리가 없는데요. 어리석은 질문일 수 있지만, 60,000에 0이 충분히 있었나요?
**수정:** 아니면 AVG(평균) 값에 해당 주제를 여러 번 다시 읽은 시간도 포함되는 걸까요? 한 번 읽는 데 61분이 걸리지만, 실제로는 사용자가 평균 438분을 그곳에 머물러 있는 것일 수도 있습니다.
그래서 이제 요약(Summary)의 예상 읽기 시간이 어떻게 계산되는지 꽤 궁금해졌습니다. 이상적으로는 두 값이 일치해야 하니까요. 10분의 1로 줄여도 대략적인 수준으로만 맞출 수 있을 것 같습니다. 🤔
---
### Author:  [@Richie](https://meta.discourse.org/u/Richie)
#### Post date: [8월 17, 2021, 6:56오전 UTC](https://meta.discourse.org/t/data-explorer-query-to-list-the-longest-estimated-read-time-topics/196761/11 "2021-08-17T06:56:36Z")
> [@JammyDodger](#):
>
> 요약의 예상 읽기 시간이 어떻게 계산되는지 궁금합니다. 이상적으로는 두 값이 일치해야 하니까요.
네, 정확합니다 😊
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [8월 17, 2021, 8:33오전 UTC](https://meta.discourse.org/t/data-explorer-query-to-list-the-longest-estimated-read-time-topics/196761/12 "2021-08-17T08:33:33Z")
조금 검색해 보다가 이 링크를 찾았습니다: ["There are 84 replies with an estimated read time of 0 minutes." - #9 by nbianca](https://meta.discourse.org/t/there-are-84-replies-with-an-estimated-read-time-of-0-minutes/119628/9?u=jammydodger).
이런 것들을 해석하는 데는 좀 애를 먹지만, 단어 수에 시간 계수를 곱하는 방식(이미지와 같이 단어가 없는 게시글을 커버하기 위한 최소 시간 포함)을 사용하는 것 같습니다.
최종 값이 무엇으로 불릴 수 있는지에 대한 힌트를 주는 이 내용도 있었습니다: (다만 오래된 정보라 변경되었을 수도 있겠죠?)
> [@zogstrip](#):
>
> ```plaintext
> summary:
> description: "There are {{replyCount}} replies."
> description_time: "There are {{replyCount}} replies with an estimated read time of {{readingTime}} minutes."
>
> ```
큰 도움이 되지는 않을 거라 생각하지만, 혹시 모르니 공유해 봅니다. 🙂
원하시는 답변을 찾으시길 바랍니다. 🤞
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [11월 26, 2021, 9:23오후 UTC](https://meta.discourse.org/t/data-explorer-query-to-list-the-longest-estimated-read-time-topics/196761/13 "2021-11-26T21:23:48Z")
> [@Richie](#):
>
> “예상 읽기 시간” 기준으로 “상위 X개” 주제를 나열할 수 있는 데이터 탐색기 쿼리를 만들 수 있을까요?
다시 한번 확인해 보니, (가장 단순한 형태로) 이는 topic.word\_count에 관리자 설정인 ‘읽기 시간 단어 수’(기본값 500 단어/분)를 곱한 값인 것 같습니다. 따라서 이 쿼리는 ‘가장 오래 걸리는’ 상위 X개 주제를 생성할 것으로 보입니다:
```plaintext
-- [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초’라는 대안도 있습니다: (게시물 수 x 4)/60. 이는 단어 수가 없는 사진 주제 등을 고려하기 위한 것입니다. 따라서 둘 다 계산하여 더 큰 값을 표시합니다. 하지만 아직 이 부분을 어떻게 추가해야 할지 정확히 파악하지 못했습니다. 🙂
불행히도 제대로 테스트해 볼 만큼 규모가 큰 사이트를 가지고 있지 않습니다. 작은 테스트 샘플에서는 작동하는 것 같았지만, 조정 작업이 필요할 수 있습니다. 🙂
**수정:** OP의 사양에 더 가깝게 만들기 위해 ‘limit’ 매개변수를 추가했습니다. 👍
---
### Author:  [@Richie](https://meta.discourse.org/u/Richie)
#### Post date: [11월 26, 2021, 9:42오후 UTC](https://meta.discourse.org/t/data-explorer-query-to-list-the-longest-estimated-read-time-topics/196761/14 "2021-11-26T21:42:32Z")
아니, 정말로 성공한 것 같네요!
@JammyDodger 님의 쿼리를 실행해 보았고, 참고로 몇 가지 스크린샷을 첨부합니다.
먼저, "상위 10개"입니다:
그리고 과연:
😱👏🏻
완전히 일치하지 않는 숫자가 몇 개 있지만, _정말_ 아주 가깝습니다!
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [11월 26, 2021, 9:51오후 UTC](https://meta.discourse.org/t/data-explorer-query-to-list-the-longest-estimated-read-time-topics/196761/15 "2021-11-26T21:51:19Z")
확실히 사진을 어떻게 추가해야 하는지 알아내야 할 것 같아. 🙂 아직 포기하지 않았거든. 🤞
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [11월 27, 2021, 10:49오전 UTC](https://meta.discourse.org/t/data-explorer-query-to-list-the-longest-estimated-read-time-topics/196761/16 "2021-11-27T10:49:05Z")
다시 한번 시도해 보았습니다. 🙂 이 부분은 100% 확신할 수 없는데, 테스트에 사용할 만큼 충분한 샘플이 없기 때문입니다. 하지만 제 테스트 주제들은 잘 인식하고 있습니다. 👍
```plaintext
-- [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, ' min') AS estimated_reading_time
FROM read_time
ORDER BY estimated_reading_time DESC
LIMIT :limit
```