Data Explorer를 사용하여 점수를 더 편리하게 표시하도록 쿼리도 수정했습니다.
신경은 ChatGPT와 Leonardo의 PostgreSQL 힌트에 감사합니다:
SELECT
json_extract_path_text(pcf.value::json, 'classification', 'toxicity') AS toxicity,
json_extract_path_text(pcf.value::json, 'classification', 'severe_toxicity') AS severe_toxicity,
json_extract_path_text(pcf.value::json, 'classification', 'obscene') AS obscene,
json_extract_path_text(pcf.value::json, 'classification', 'identity_attack') AS identity_attack,
json_extract_path_text(pcf.value::json, 'classification', 'insult') AS insult,
json_extract_path_text(pcf.value::json, 'classification', 'threat') AS threat,
json_extract_path_text(pcf.value::json, 'classification', 'sexual_explicit') AS sexual_explicit,
json_extract_path_text(pcf.value::json, 'model') AS model,
pcf.created_at,
p.raw
FROM
post_custom_fields AS pcf
INNER JOIN
posts AS p ON p.id = pcf.post_id
INNER JOIN
topics AS t ON t.id = p.topic_id
WHERE
pcf.name = 'disorder'
AND t.archetype = 'regular'
ORDER BY created_at DESC
이 수정 사항은 분류 값 중 50(또는 설정한 값)보다 큰 값이 있는 행을 반환합니다
-- [params]
-- int :threshold = 50
SELECT DISTINCT ON (p.id, pcf.created_at)
json_extract_path_text(pcf.value::json, 'classification', 'toxicity') AS toxicity,
json_extract_path_text(pcf.value::json, 'classification', 'severe_toxicity') AS severe_toxicity,
json_extract_path_text(pcf.value::json, 'classification', 'obscene') AS obscene,
json_extract_path_text(pcf.value::json, 'classification', 'identity_attack') AS identity_attack,
json_extract_path_text(pcf.value::json, 'classification', 'insult') AS insult,
json_extract_path_text(pcf.value::json, 'classification', 'threat') AS threat,
json_extract_path_text(pcf.value::json, 'classification', 'sexual_explicit') AS sexual_explicit,
json_extract_path_text(pcf.value::json, 'model') AS model,
p.id as post_id,
pcf.created_at,
p.raw
FROM
post_custom_fields AS pcf
INNER JOIN
posts AS p ON p.id = pcf.post_id
INNER JOIN
topics AS t ON t.id = p.topic_id
WHERE
pcf.name = 'disorder'
AND t.archetype = 'regular'
GROUP BY p.id, pcf.value, pcf.created_at
HAVING
CAST(json_extract_path_text(pcf.value::json, 'classification', 'toxicity') AS FLOAT) > :threshold
OR CAST(json_extract_path_text(pcf.value::json, 'classification', 'severe_toxicity') AS FLOAT) > :threshold
OR CAST(json_extract_path_text(pcf.value::json, 'classification', 'obscene') AS FLOAT) > :threshold
OR CAST(json_extract_path_text(pcf.value::json, 'classification', 'identity_attack') AS FLOAT) > :threshold
OR CAST(json_extract_path_text(pcf.value::json, 'classification', 'insult') AS FLOAT) > :threshold
OR CAST(json_extract_path_text(pcf.value::json, 'classification', 'threat') AS FLOAT) > :threshold
OR CAST(json_extract_path_text(pcf.value::json, 'classification', 'sexual_explicit') AS FLOAT) > :threshold
ORDER BY pcf.created_at DESC, p.id
Data Explorer를 사용하여 보고서를 생성할 때 다른 임계값을 설정할 수 있도록 여러 매개변수를 도입하여 이를 수정할 수도 있습니다.
참고: 이 쿼리는 개인 메시지 접근 없이 공개 게시글만 반환합니다.
텍스트 형태라도 정말 NSFW일 수 있으니까.