메타 사이트에서 신뢰도 3(TL3) 설정이 적절한지, 그리고 궁극적으로 기본값을 어떻게 설정해야 할지 조사하고 있습니다.
질문하신 쿼리를 출발점으로 삼아, 설정을 변경하면 실행할 수 있도록 사이트 설정을 참조하도록 수정했습니다:
새로운 tl3_reqs CTE를 확인해 보세요:
WITH
tl3_reqs as (
SELECT
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_time_period'), 100) as tl3_time_period,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_days_visited'), 50) as tl3_days_visited,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_requires_topics_replied_to'), 10) as tl3_requires_topics_replied_to,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_requires_topics_viewed'), 25) as tl3_requires_topics_viewed,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_requires_topics_viewed_cap'), 500) as tl3_requires_topics_viewed_cap,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_requires_posts_read'), 25) as tl3_requires_posts_read,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_requires_posts_read_cap'), 20000) as tl3_requires_posts_read_cap,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_requires_topic_viewed_all_time'), 200) as tl3_requires_topic_viewed_all_time,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_requires_posts_read_all_time'), 500) as tl3_requires_posts_read_all_time,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_requires_max_flagged'), 5) as tl3_requires_max_flagged,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_promotion_min_duration'), 14) as tl3_promotion_min_duration,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_promotion_likes_given'), 30) as tl3_promotion_likes_given,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_promotion_likes_received'), 20) as tl3_promotion_likes_received
),
t as (
SELECT
CURRENT_TIMESTAMP - ((0 + MIN(tl3_time_period)) * (INTERVAL '1 days')) AS start,
CURRENT_TIMESTAMP - (0 * (INTERVAL '1 days')) AS end
FROM tl3_reqs
),
-- 기간 내 열람한 토픽
-- 기간 내 생성된 토픽의 x%(기본값 25) (기본값 100일)
-- 또는 상한선(기본값 500) 중 작은 값
tvtp AS (
SELECT LEAST(floor(count(id)*(MIN(tl3_requires_topics_viewed)/100.0))::REAL,MIN(tl3_requires_topics_viewed_cap)) as all_topics
FROM topics, t, tl3_reqs
WHERE created_at > t.start
AND archetype = 'regular'
AND deleted_at is null
),
-- 기간 내 읽은 게시물
-- 기간 내 생성된 토픽의 x%(기본값 25) (기본값 100일)
-- 또는 상한선(기본값 20K) 중 작은 값
prtp AS (
SELECT LEAST(FLOOR(count(id)*(MIN(tl3_requires_posts_read)/100.0))::REAL,MIN(tl3_requires_posts_read_cap)) AS all_posts
FROM t, posts, tl3_reqs
WHERE posts.created_at > start
AND posts.deleted_at IS NULL
AND posts.hidden_at IS NULL
AND posts.last_editor_id >0 -- Discobot 및 System 제외
AND (action_code IS NULL OR action_code != 'assigned')
),
-- 신뢰도 2(TL2) 사용자
tl AS (
SELECT id AS user_id, username
FROM users
WHERE trust_level = 2
),
-- 사용자 + 최근 100일 방문 및 읽은 게시물
pr AS (
SELECT user_id,
COUNT(1) AS visits,
SUM(posts_read) AS posts_read
FROM t, user_visits
INNER JOIN tl USING (user_id)
WHERE visited_at > t.start
AND visited_at < t.end
GROUP BY user_id
ORDER BY visits DESC
),
-- 전체 기간 읽은 게시물
prat AS (
SELECT user_id,
SUM(posts_read) AS posts_read
FROM t, user_visits
INNER JOIN tl USING (user_id)
GROUP BY user_id
),
-- 답변한 토픽
trt AS (
SELECT posts.user_id,
count(distinct topic_id) as replied_count
FROM t, posts
INNER JOIN tl USING (user_id)
INNER JOIN topics ON topics.id = posts.topic_id
WHERE posts.created_at > t.start
AND posts.created_at < t.end
AND topics.user_id <> posts.user_id
AND posts.deleted_at IS NULL AND topics.deleted_at IS NULL
AND archetype = 'regular'
GROUP BY posts.user_id
),
-- 전체 기간 열람한 토픽
tvat AS (
SELECT tv.user_id,
COUNT(distinct tv.topic_id) AS topic_id
FROM topic_views tv
LEFT JOIN topics t ON tv.topic_id=t.id
INNER JOIN tl ON tv.user_id=tl.user_id
WHERE t.archetype = 'regular'
AND t.deleted_at IS NULL
GROUP BY tv.user_id
),
-- 열람한 토픽
tva AS (
SELECT tv.user_id,
COUNT(distinct tv.topic_id) AS topic_id
FROM t, topic_views tv
LEFT JOIN topics on topic_id=topics.id
INNER JOIN tl on tv.user_id=tl.user_id
WHERE
topics.archetype = 'regular'
AND topics.deleted_at is null
AND viewed_at > t.start
AND viewed_at < t.end
GROUP BY tv.user_id
),
likes_received_lhd AS (
SELECT ua.user_id
, count(*) as likes_received_lhd
FROM t, user_actions ua
JOIN posts p on p.id=ua.target_post_id
JOIN tl on ua.user_id=tl.user_id
WHERE ua.action_type=1
AND ua.created_at > t.start
AND ua.created_at < t.end
GROUP BY ua.user_id
),
likes_given_lhd AS (
SELECT user_id, count(*) as likes_given_lhd
FROM t, given_daily_likes
INNER JOIN tl using (user_id)
WHERE given_date > t.start
AND given_date < t.end
GROUP BY user_id
)
SELECT pr.user_id,
tl.username,
GREATEST(tl3_days_visited-COALESCE(pr.visits,0),0) as "기간 내 방문일수 갭",
GREATEST(tl3_requires_topics_replied_to-COALESCE(trt.replied_count,0), 0) as "토픽 답변 갭",
GREATEST(tvtp.all_topics-COALESCE(tva.topic_id,0),0) AS "기간 내 열람 토픽 갭 (150 기준)",
GREATEST(tl3_requires_topic_viewed_all_time-COALESCE(tvat.topic_id,0),0) as "열람 토픽 (전체) 갭",
GREATEST(prtp.all_posts - COALESCE(pr.posts_read,0),0) as "읽은 게시물 갭 (250 기준)",
GREATEST(tl3_requires_posts_read_all_time-COALESCE(prat.posts_read,0),0) as "읽은 게시물 (전체) 갭",
GREATEST(tl3_promotion_likes_given-COALESCE(likes_given_lhd,0),0) as "기간 내 좋아요 제공 갭",
GREATEST(tl3_promotion_likes_received-COALESCE(likes_received_lhd,0),0) as "좋아요 수락 갭"
FROM prtp, tvtp, tl3_reqs, pr
LEFT JOIN tl USING (user_id)
LEFT JOIN trt USING (user_id)
LEFT JOIN tva USING (user_id)
LEFT JOIN tvat USING (user_id)
LEFT JOIN prat USING (user_id)
LEFT JOIN likes_received_lhd USING (user_id)
LEFT JOIN likes_given_lhd USING (user_id)
ORDER BY pr.visits DESC
LIMIT 25