لقد قمت أيضًا بتعديل استعلامك لعرض النتائج بطريقة أكثر ملاءمة باستخدام مستكشف البيانات.
الفضل يعود إلى ChatGPT و PostgreSQL clues by Leonardo:
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
يمكنك أيضًا تعديله عن طريق إدخال المزيد من المعلمات لتتمكن من تعيين عتبات مختلفة للإبلاغ باستخدام مستكشف البيانات.
يرجى ملاحظة: سيعيد هذا المنشورات العامة فقط، دون الوصول إلى الرسائل الخاصة.
قد تكون حقًا غير آمنة للمحتوى، حتى في شكل نصي