다음과 같은 Data-Explorer SQL 쿼리를 작성해야 합니다:
연도별 요청을 수행해야 합니다. 예를 들어 2019-01-01부터 2019-12-31까지의 기간을 입력으로 사용합니다.
카테고리별(TOPIC 및 POST) 답변 수
연도별 상위 태그
월평균 신규 토픽 수 (관리자 리포트 관리자 패널에서 이 값을 확인했습니다)
월평균 답변 수
월평균 신규 사용자 수
이 작업에 도움을 주실 수 있는 분이나 방향을 제시해 주실 분 계신가요?
감사합니다.
@michebs 님이 여기서 도움을 줄 수 있을 것 같습니다.
안녕하세요,
다른 쿼리를 설정하기 전에 이것이 필요한 내용인지 확인해 주세요:
WITH data AS (SELECT
id,
EXTRACT(MONTH FROM created_at) AS month,
EXTRACT(YEAR FROM created_at) AS year
FROM topics)
SELECT
ROUND(AVG(qt_topic_month)) AS avg_topic,
year
FROM
(SELECT COUNT(id) AS qt_topic_month,
month,
year
FROM data
GROUP BY month, year) AS top_m
GROUP BY year
ORDER BY year DESC
| avg_topic | year |
|---|---|
| 694 | 2020 |
| 1011 | 2019 |
| 284 | 2018 |
| 79 | 2017 |
@michebs.. 네, 맞습니다. 정말 빨리 파악하셨네요.
부담이 되지 않는다면, 아래와 같은 형식이면 좋겠습니다.
2020 01 숫자
2020 02 숫자
2020 03 숫자
등등…
미리 감사드립니다.
감사합니다.
문제가 아닙니다. 찾고 계신 정보가 이것입니다:
WITH data AS (SELECT
id,
EXTRACT(DAY FROM created_at) AS day,
EXTRACT(MONTH FROM created_at) AS month,
EXTRACT(YEAR FROM created_at) AS year
FROM topics)
SELECT
year,
month,
ROUND(AVG(qt_topic_day)) AS avg_topic
FROM
(SELECT COUNT(id) AS qt_topic_day,
day,
month,
year
FROM data
GROUP BY day, month, year) AS top_day
GROUP BY month, year
ORDER BY year DESC, month ASC
| year | month | avg_topic |
|---|---|---|
| 2020 | 1 | 23 |
| 2020 | 2 | 26 |
| 2020 | 3 | 24 |
| 2020 | 4 | 35 |
| 2020 | 5 | 31 |
이 패러다임을 기반으로 다른 쿼리들을 설정할 예정입니다. 
안녕하세요,
답변이 늦어 죄송합니다. 요청하신 쿼리를 따라가 보았는데, 일부는 평소보다 복잡해서요. 궁금한 점이 있으시면 언제든 문의해 주세요.
미셸
WITH post AS (SELECT
id AS post_id,
topic_id,
EXTRACT(YEAR FROM created_at) AS year
FROM posts
WHERE post_type = 1
AND deleted_at ISNULL
AND post_number != 1)
SELECT
p.year,
t.category_id AS id,
c.name category,
COUNT(p.post_id) AS qt
FROM post p
INNER JOIN topics t ON t.id = p.topic_id
LEFT JOIN categories c ON c.id = t.category_id
WHERE t.deleted_at ISNULL
-- AND t.category_id NOT NULL --> *** 카테고리 없는 포스트를 삭제하려면 활성화 ***
GROUP BY t.category_id, c.name, p.year
ORDER BY p.year DESC, qt DESC
| year | id | category | qt |
|---|---|---|---|
| 2020 | 13 | General | 14 |
| 2020 | 16 | Knowledge Base | 3 |
| 2020 | 15 | Staff | 3 |
| 2020 | 1 | Uncategorized | 2 |
| 2020 | 17 | Ideas | 1 |
| 2019 | 18 | Builds | 10 |
| 2019 | 1 | Uncategorized | 8 |
| 2019 | 11 | CS001x: Intro to Computer Science | 7 |
| 2019 | 13 | General | 5 |
WITH data AS (SELECT
tag_id,
EXTRACT(YEAR FROM created_at) AS year
FROM topic_tags)
SELECT year, rank, name, qt FROM (
SELECT
tag_id,
COUNT(tag_id) AS qt,
year,
rank() OVER (PARTITION BY year ORDER BY COUNT(tag_id) DESC) AS rank
FROM
data
GROUP BY year, tag_id) as rnk
INNER JOIN tags ON tags.id = rnk.tag_id
WHERE rank <= 5 -- *** 순위 제한 선택 ***
ORDER BY year DESC, qt DESC
| year | rank | tag_name | quantity |
|---|---|---|---|
| 2020 | 1 | featured | 7 |
| 2020 | 2 | human-resources | 3 |
| 2020 | 3 | demo | 1 |
| 2019 | 1 | demo | 12 |
| 2019 | 2 | human-resources | 4 |
| 2019 | 3 | featured | 3 |
| 2019 | 3 | customer | 3 |
| 2019 | 3 | milestones-2019 | 3 |
WITH data AS (SELECT
id,
EXTRACT(DAY FROM created_at) AS day,
EXTRACT(MONTH FROM created_at) AS month,
EXTRACT(YEAR FROM created_at) AS year
FROM topics
WHERE deleted_at ISNULL)
SELECT
year,
month,
ROUND(AVG(qt_topic_day)) AS avg_topic_by_day
FROM
(SELECT COUNT(id) AS qt_topic_day,
day,
month,
year
FROM data
GROUP BY day, month, year) AS top_day
GROUP BY month, year
ORDER BY year DESC, month ASC
| year | month | avg_topic_by_day |
|---|---|---|
| 2020 | 1 | 1 |
| 2020 | 2 | 1 |
| 2020 | 3 | 2 |
| 2020 | 4 | 3 |
| 2020 | 5 | 2 |
| 2019 | 4 | 9 |
| 2019 | 5 | 4 |
| 2019 | 6 | 4 |
| 2019 | 7 | 1 |
| 2019 | 8 | 2 |
| 2019 | 9 | 3 |
| 2019 | 10 | 1 |
WITH data AS (SELECT
id,
EXTRACT(DAY FROM created_at) AS day,
EXTRACT(MONTH FROM created_at) AS month,
EXTRACT(YEAR FROM created_at) AS year
FROM posts
WHERE post_type = 1
AND deleted_at ISNULL
AND post_number != 1)
SELECT
year,
month,
ROUND(AVG(qt_reply_day)) AS avg_reply_by_day
FROM
(SELECT COUNT(id) AS qt_reply_day,
day,
month,
year
FROM data
GROUP BY day, month, year) AS top_reply
GROUP BY month, year
ORDER BY year DESC, month ASC
| year | month | avg_reply_by_day |
|---|---|---|
| 2020 | 1 | 7 |
| 2020 | 3 | 2 |
| 2020 | 4 | 5 |
| 2020 | 5 | 6 |
| 2019 | 4 | 3 |
| 2019 | 5 | 2 |
| 2019 | 6 | 4 |
| 2019 | 7 | 2 |
| 2019 | 8 | 15 |
| 2019 | 9 | 3 |
| 2019 | 10 | 5 |
| 2019 | 12 | 2 |
WITH data AS (SELECT
id,
EXTRACT(DAY FROM created_at) AS day,
EXTRACT(MONTH FROM created_at) AS month,
EXTRACT(YEAR FROM created_at) AS year
FROM users)
SELECT
year,
month,
ROUND(AVG(qt_new_user)) AS avg_new_user_by_day
FROM
(SELECT COUNT(id) AS qt_new_user,
day,
month,
year
FROM data
GROUP BY day, month, year) AS top_new_user
GROUP BY month, year
ORDER BY year DESC, month ASC
| year | month | avg_new_user_by_day |
|---|---|---|
| 2020 | 1 | 1 |
| 2020 | 2 | 1 |
| 2020 | 3 | 1 |
| 2020 | 4 | 3 |
| 2020 | 5 | 1 |
| 2019 | 4 | 4 |
| 2019 | 5 | 2 |
| 2019 | 6 | 2 |
| 2019 | 7 | 1 |
완벽하게 작동해요…
정말 감사해요…
감사합니다…