Finde die Nutzer, die am wahrscheinlichsten TL3 werden

Daten & Berichterstattung
,
17

Dies ist etwas effizienter. Wenn es bei Ihnen immer noch nicht funktioniert, können Sie versuchen, einige der Spalten zusammen mit den zugehörigen Joins und Abfragen zu entfernen.

EDIT Okay, ich habe endlich meine Join-Typen richtig hinbekommen (es ist eine Weile her). Diese aktualisierte Abfrage ist viel effizienter

with
t as (
  select
    CURRENT_TIMESTAMP - ((0 + 100) * (INTERVAL '1 days')) as start,
    CURRENT_TIMESTAMP - (0 * (INTERVAL '1 days')) as end
  ),

-- Themaanzahl letzte 100 Tage 25%
-- kleiner von 25% Themen, die in den letzten 100 Tagen erstellt wurden
-- ODER 500, die standardmäßige maximale Systemanforderung für TL3
tclhd AS (
    SELECT LEAST(floor(count(id)*.25)::REAL,500) as all_topics
    FROM topics, t
    WHERE created_at > t.start
        AND archetype = 'regular'
        AND deleted_at is null
    ),

-- Beitraganzahl letzte 100 Tage 25%
-- kleiner von 25% Beiträgen, die in den letzten 100 Tagen erstellt wurden
-- ODER 20k, die standardmäßige maximale Systemanforderung für TL3
pclhd AS (
SELECT LEAST(FLOOR(count(id)*.25)::REAL,20000) AS all_posts
FROM t, posts
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 weglassen
    AND (action_code is null OR action_code != 'assigned')
    ),

-- Vertrauensstufe 2 Benutzer
tl AS (
SELECT id as user_id, trust_level
FROM users
WHERE trust_level = 2
    ),

-- Benutzer, Besuche & gelesene Beiträge letzte 100 Tage
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
    ),

-- Gelesene Beiträge aller Zeiten
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
    ),

-- Antworten auf Themen
trt as (
    select user_id,
           count(distinct topic_id) as topic_id
    from t, posts
    INNER JOIN tl using (user_id)
    where posts.created_at > t.start
      and posts.created_at < t.end
    group by user_id
    ),

-- Angesehene Themen aller Zeiten
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
    ),

-- Angesehene Themen
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 AS (
    SELECT user_id,
        likes_given, likes_received
    from user_stats
    INNER JOIN tl using (user_id)
)


SELECT  pr.user_id,
        greatest(50-coalesce(pr.visits,0),0) as "Tage Lücken Besuche",
        greatest(10-coalesce(trt.topic_id,0), 0)  as "Themen Antwort Lücke",
        greatest(tclhd.all_topics-coalesce(tva.topic_id,0),0) AS "Angesehene Themen Lücke",
        greatest(200-coalesce(tvat.topic_id,0),0) as "Angesehene Themen (AT) Lücke",
        greatest(pclhd.all_posts - coalesce(pr.posts_read,0),0) as "Gelesene Beiträge Lücke",
        greatest(500-coalesce(prat.posts_read,0),0) as "Gelesene Beiträge (AT) Lücke",
        greatest(30-likes.likes_given,0) as "Gegebene Likes Lücke",
        greatest(20-likes.likes_received,0) as "Erhaltene Likes Lücke"

FROM pclhd, tclhd, pr
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 using (user_id)


ORDER BY
  pr.visits DESC

LIMIT 50
8 „Gefällt mir“