# 대시보드 보고서 - 좋아요
**URL:** https://meta.discourse.org/t/dashboard-report-likes/291092
**Category:** Data & reporting
**Tags:** sql-query, dashboard-reports, dashboard-sql
**Created:** [1월 9, 2024, 11:56오후 UTC](https://meta.discourse.org/t/dashboard-report-likes/291092 "2024-01-09T23:56:53Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [1월 9, 2024, 11:56오후 UTC](https://meta.discourse.org/t/dashboard-report-likes/291092/1 "2024-01-09T23:56:54Z")
이것은 좋아요 대시보드 보고서의 SQL 버전입니다.
이 쿼리는 지정된 날짜 범위 내에서 사이트의 모든 게시물이 받은 좋아요의 총 개수를 일일 기준으로 보고합니다.
```sql
-- [params]
-- date :start_date = 2023-12-08
-- date :end_date = 2024-01-10
WITH date_range AS (
SELECT date_trunc('day', series) AS date
FROM generate_series(
:start_date::timestamp,
:end_date::timestamp,
'1 day'::interval
) series
)
SELECT
dr.date::date,
COALESCE(pa.likes_count, 0) AS likes_count
FROM
date_range dr
LEFT JOIN (
SELECT
date_trunc('day', pa.created_at) AS action_date,
COUNT(*) AS likes_count
FROM post_actions pa
WHERE pa.post_action_type_id = 2
AND pa.created_at >= :start_date
AND pa.created_at <= (:end_date::date + 1)
GROUP BY action_date
) pa ON dr.date = pa.action_date
ORDER BY dr.date
```
### SQL 쿼리 설명
쿼리의 주요 구조는 `date_range`라는 이름의 CTE(공용 테이블 표현식)를 기반으로 구성됩니다. 이 CTE는 사용자가 정의한 기간 내의 각 날짜를 나타내는 타임스탬프 시리즈를 생성하는 데 사용됩니다.
#### 매개변수
쿼리는 두 개의 매개변수를 받습니다:
- `:start_date`: 보고서를 생성할 기간의 시작일.
- `:end_date`: 보고서를 생성할 기간의 종료일.
#### 공용 테이블 표현식: `date_range`
- `generate_series`는 `:start_date`부터 `:end_date`까지 ‘1 day’ 간격으로 증가하는 타임스탬프 집합을 생성하는 함수입니다.
- `date_trunc('day', series)`는 타임스탬프를 해당 날짜의 시작(00:00:00)으로 잘라내어 모든 타임스탬프를 정규화합니다.
- 그 결과, `:start_date`부터 `:end_date`까지의 전체 범위를 덮는 날짜가 한 줄에 하나씩 포함된 집합이 생성됩니다.
#### 서브쿼리: 좋아요 카운트
서브쿼리는 `post_actions` 테이블의 행을 카운트하여 각 날짜별 좋아요 수를 계산하는 데 사용됩니다.
- 이 쿼리는 액션 유형이 좋아요를 의미하는 항목(`post_action_type_id = 2`인 경우)으로 `post_actions`를 필터링합니다.
- 액션을 해당 날짜 범위로 필터링하되, 마지막 날에 주어진 좋아요를 포함하도록 종료일에 하루를 더합니다.
- 결과를 날짜별로 그룹화하고 각 날짜별 좋아요 수를 카운트합니다.
#### 메인 쿼리: 결과 병합
쿼리의 마지막 섹션은 `date_range` CTE의 모든 날짜 집합과 서브쿼리의 좋아요 카운트를 병합합니다.
- `LEFT JOIN`을 사용하면 특정 날짜에 해당하는 좋아요 액션이 없더라도(서브쿼리에서 조인되지 않더라도) `date_range`의 모든 날짜가 결과에 포함되도록 보장합니다.
- `COALESCE`는 좋아요가 없는 날짜의 `NULL` 카운트를 0으로 대체하여, 좋아요 활동이 없는 날짜도 보고서가 정확히 반영하도록 합니다.
- 최종 결과 집합은 날짜 순으로 정렬되어 지정된 기간 동안의 좋아요 추이를 시간 순으로 보여줍니다.
### 결과 예시
| date | likes\_count |
| --- | --- |
| 2023-12-08 | 123 |
| 2023-12-09 | 156 |
| 2023-12-10 | 278 |
| 2023-12-11 | 134 |
| 2023-12-12 | 89 |
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [1월 10, 2024, 12:04오전 UTC](https://meta.discourse.org/t/dashboard-report-likes/291092/2 "2024-01-10T00:04:29Z")
이 쿼리에 `AND pa.deleted_at IS NULL`을 추가하여 Likes 캐스트를 필터링한 후 삭제된 항목을 제거해야 하는지, 아니면 대시보드 쿼리 자체를 수정해야 하는 가능한 변경 사항인지 궁금합니다.
---
### Author:  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [1월 10, 2024, 12:14오전 UTC](https://meta.discourse.org/t/dashboard-report-likes/291092/3 "2024-01-10T00:14:41Z")
현재 상태에서는 대시보드 리포트에 삭제된 좋아요가 포함되어 있으므로, `AND pa.deleted IS NULL`을 추가하면 이 쿼리가 대시보드 리포트와 매칭되는 방식이 변경됩니다.
다만, 대시보드 리포트 자체를 수정하여 삭제된 좋아요를 포함하지 않도록 하는 것은 고려해 볼 만한 좋은 변경 사항일 수 있습니다.
---
### Author:  [@NiceOldGuy](https://meta.discourse.org/u/NiceOldGuy)
#### Post date: [1월 22, 2025, 10:03오후 UTC](https://meta.discourse.org/t/dashboard-report-likes/291092/4 "2025-01-22T22:03:34Z")
우리 포럼은 크지 않고, 대부분의 좋아요 반응은 “스태프”(관리자, 모더레이터, TL=4)에서 옵니다. 일반 사용자와 "스태프"의 좋아요를 비교해 보고, 일일 게시물 수를 나열하여 어떤 일이 일어나고 있는지, 그리고 반응 사용 개선을 위해 어디에 노력을 집중해야 하는지 파악하고 싶었습니다.
**나와 내 친구 ChatGPT가 이것을 만들었습니다:**
```plaintext
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-12-31
WITH date_range AS (
SELECT date_trunc('day', series) AS date
FROM generate_series(
:start_date::timestamp,
:end_date::timestamp,
'1 day'::interval
) series
),
staff_users AS (
SELECT id
FROM users
WHERE admin = true OR moderator = true OR trust_level = 4
)
SELECT
dr.date::date,
COALESCE(pa_non_staff.regular_likes_count, 0) AS regular_likes_count,
COALESCE(pa_staff.staff_likes_count, 0) AS staff_likes_count,
COALESCE(pa_non_staff.regular_likes_count, 0) + COALESCE(pa_staff.staff_likes_count, 0) AS total_likes,
COALESCE(posts_count.posts_per_day, 0) AS posts_per_day
FROM
date_range dr
LEFT JOIN (
SELECT
date_trunc('day', pa.created_at) AS action_date,
COUNT(*) AS regular_likes_count
FROM post_actions pa
WHERE pa.post_action_type_id = 2
AND pa.user_id NOT IN (SELECT id FROM staff_users)
AND pa.created_at >= :start_date
AND pa.created_at <= (:end_date::date + 1)
GROUP BY action_date
) pa_non_staff ON dr.date = pa_non_staff.action_date
LEFT JOIN (
SELECT
date_trunc('day', pa.created_at) AS action_date,
COUNT(*) AS staff_likes_count
FROM post_actions pa
WHERE pa.post_action_type_id = 2
AND pa.user_id IN (SELECT id FROM staff_users)
AND pa.created_at >= :start_date
AND pa.created_at <= (:end_date::date + 1)
GROUP BY action_date
) pa_staff ON dr.date = pa_staff.action_date
LEFT JOIN (
SELECT
date_trunc('day', p.created_at) AS post_date,
COUNT(*) AS posts_per_day
FROM posts p
WHERE p.created_at >= :start_date
AND p.created_at <= (:end_date::date + 1)
GROUP BY post_date
) posts_count ON dr.date = posts_count.post_date
ORDER BY dr.date
```
**@SaraDev의 원래 쿼리에 대한 변경 사항 (고맙습니다, Sara!):**
**SQL 변경 사항 요약**
1. **스태프 그룹 생성** :
`users` 테이블에서 스태프 사용자를 식별하기 위해 `staff_users` CTE를 추가했습니다. 스태프 사용자는 다음 중 하나에 해당하면 됩니다:
- `admin = true`
- `moderator = true`
- `trust_level = 4`
1. **스태프 좋아요 분리** :
`staff_users` 그룹의 `user_id`로 `post_actions`를 필터링하여 스태프 사용자의 좋아요 수(`staff_likes_count`)를 계산하는 서브쿼리를 추가했습니다.
2. **비(非)스태프 좋아요 컬럼 이름 변경**:
비(非)스태프 좋아요의 출력 라벨을 `likes_count`에서 `regular_likes_count`로 변경했습니다.
3. **총 좋아요 추가** :
`regular_likes_count`와 `staff_likes_count`를 합산하는 `total_likes` 컬럼을 도입했습니다.
4. **일일 게시물 수 추가** :
일일 게시물 수(`posts_per_day`)를 계산하는 서브쿼리를 추가하고 날짜 범위에 조인했습니다.
(네, ChatGPT가 이 변경 사항 목록도 저를 위해 만들어 주었습니다.)
**결과 예시:**
| 날짜 | regular\_likes\_count | staff\_likes\_count | posts\_per\_day |
| --- | --- | --- | --- |
| 1/1/24 | 0 | 6 | 7 |
| 1/2/24 | 0 | 5 | 3 |
| 1/3/24 | 1 | 0 | 4 |
| 1/4/24 | 1 | 2 | 5 |
| 1/5/24 | 9 | 9 | 30 |
| 1/6/24 | 0 | 1 | 11 |
| 1/7/24 | 2 | 4 | 11 |
| 1/8/24 | 0 | 5 | 18 |
| 1/9/24 | 0 | 0 | 2 |
| 1/10/24 | 0 | 0 | 7 |
| 1/11/24 | 0 | 4 | 5 |
| 1/12/24 | 4 | 0 | 4 |
| 1/13/24 | 6 | 0 | 10 |
| 1/14/24 | 1 | 7 | 18 |
| 1/15/24 | 2 | 4 | 7 |
> **주 단위로 보고하여 부드럽게 하기 위해 동일한 쿼리**
>
> ```plaintext
> -- [params]
> -- integer :weeks_ago = 52
>
> WITH date_range AS (
> SELECT date_trunc('week', series) AS week_start
> FROM generate_series(
> date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval,
> date_trunc('week', now()),
> '1 week'::interval
> ) series
> ),
> staff_users AS (
> SELECT id
> FROM users
> WHERE admin = true OR moderator = true OR trust_level = 4
> )
>
> SELECT
> dr.week_start::date AS week_start,
> COALESCE(pa_non_staff.regular_likes_count, 0) AS regular_likes_count,
> COALESCE(pa_staff.staff_likes_count, 0) AS staff_likes_count,
> COALESCE(pa_non_staff.regular_likes_count, 0) + COALESCE(pa_staff.staff_likes_count, 0) AS total_likes,
> COALESCE(posts_count.posts_per_week, 0) AS posts_per_week
> FROM
> date_range dr
> LEFT JOIN (
> SELECT
> date_trunc('week', pa.created_at) AS action_week,
> COUNT(*) AS regular_likes_count
> FROM post_actions pa
> WHERE pa.post_action_type_id = 2
> AND pa.user_id NOT IN (SELECT id FROM staff_users)
> AND pa.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
> AND pa.created_at <= date_trunc('week', now())
> GROUP BY action_week
> ) pa_non_staff ON dr.week_start = pa_non_staff.action_week
> LEFT JOIN (
> SELECT
> date_trunc('week', pa.created_at) AS action_week,
> COUNT(*) AS staff_likes_count
> FROM post_actions pa
> WHERE pa.post_action_type_id = 2
> AND pa.user_id IN (SELECT id FROM staff_users)
> AND pa.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
> AND pa.created_at <= date_trunc('week', now())
> GROUP BY action_week
> ) pa_staff ON dr.week_start = pa_staff.action_week
> LEFT JOIN (
> SELECT
> date_trunc('week', p.created_at) AS post_week,
> COUNT(*) AS posts_per_week
> FROM posts p
> WHERE p.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
> AND p.created_at <= date_trunc('week', now())
> GROUP BY post_week
> ) posts_count ON dr.week_start = posts_count.post_week
> ORDER BY dr.week_start
>
> ```
* * *
> **관심있을 경우를 위해, Sara의 쿼리를 수정한 최종 프롬프트는 다음과 같습니다:**
>
> 두 날짜 사이의 일일 좋아요 수(`likes_count`)를 보고하는 SQL 쿼리가 있습니다. 주 단위로 데이터를 집계하고 추가 세부 정보를 포함하는 최종 출력을 생성하기 위해 다음과 같은 강화가 필요합니다:
>
> 1. **스태프 그룹 정의** :
>
> - `users` 테이블에서 `staff_users` 그룹을 생성합니다. 사용자가 다음 기준 중 하나를 충족하면 스태프로 간주해야 합니다:
> - `admin = true`
> - `moderator = true`
> - `trust_level = 4`
>
> 1. **스태프/비(非)스태프별 좋아요 분리**:
>
> - 두 개의 별도 컬럼을 추가합니다:
> - `regular_likes_count`: 비(非)스태프 사용자의 좋아요 수.
> - `staff_likes_count`: 스태프 사용자의 좋아요 수.
>
> - `regular_likes_count` 컬럼이 스태프 사용자가 생성한 좋아요를 제외하도록 보장합니다.
>
> 1. **총 좋아요 추가** :
>
> - `regular_likes_count`와 `staff_likes_count`를 합산하는 `total_likes` 컬럼을 포함합니다.
>
> 1. **기간별 게시물 수 추가** :
>
> - 각 주 동안 생성된 게시물 수를 세는 `posts_per_week` 컬럼을 추가합니다.
>
> 1. **주 단위 집계** :
>
> - 쿼리를 수정하여 모든 데이터를 일 단위 대신 주 간격으로 그룹화합니다.
> - 각 주의 시작 날짜를 나타내는 `week_start` 컬럼을 포함합니다.
>
> 1. **주 단위 제한** :
>
> - 결과를 지난 N주로 제한하기 위해 `:weeks_ago` 매개변수를 도입합니다. 기본값은 52주(1년)여야 합니다.
>
> 1. **정렬 및 최종 컬럼** :
>
> - 출력이 `week_start` 기준으로 정렬되고 다음 컬럼이 이 순서로 포함되도록 보장합니다:
> 1. `week_start`: 주의 시작 날짜.
> 2. `regular_likes_count`: 비(非)스태프 사용자의 좋아요 수.
> 3. `staff_likes_count`: 스태프 사용자의 좋아요 수.
> 4. `total_likes`: `regular_likes_count`와 `staff_likes_count`의 합.
> 5. `posts_per_week`: 주 동안 생성된 게시물 수.