31 lines
954 B
PL/PgSQL
31 lines
954 B
PL/PgSQL
-- Author: fruit.com.cn
|
|
-- K110 校准互动计数字段。
|
|
-- 历史压测数据把部分计数直接改成 100000 量级，与真实行数不符；
|
|
-- 这里按实际有效互动行重算剧集点赞数与剧收藏数，可重复执行。
|
|
BEGIN;
|
|
|
|
-- 剧集点赞数 = 该集未删除的 EPISODE 点赞行数
|
|
UPDATE drama_episode e
|
|
SET like_count = sub.cnt
|
|
FROM (
|
|
SELECT e2.id, COUNT(l.id) AS cnt
|
|
FROM drama_episode e2
|
|
LEFT JOIN content_like l
|
|
ON l.target_type = 'EPISODE' AND l.target_id = e2.id AND l.deleted = 0
|
|
GROUP BY e2.id
|
|
) sub
|
|
WHERE e.id = sub.id AND e.like_count IS DISTINCT FROM sub.cnt;
|
|
|
|
-- 剧收藏数 = 该剧未删除的收藏行数
|
|
UPDATE drama d
|
|
SET favorite_count = sub.cnt
|
|
FROM (
|
|
SELECT d2.id, COUNT(f.id) AS cnt
|
|
FROM drama d2
|
|
LEFT JOIN user_favorite f ON f.drama_id = d2.id AND f.deleted = 0
|
|
GROUP BY d2.id
|
|
) sub
|
|
WHERE d.id = sub.id AND d.favorite_count IS DISTINCT FROM sub.cnt;
|
|
|
|
COMMIT;
|