article_value_view

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

int8

√

null

title

varchar

255

√

null

created_at

timestamptz

35,6

√

null

score

float8

17,17

√

null

base_score1

float8

17,17

√

null

base_score2

numeric

131089

√

null

boost_score1

int8

√

null

boost_score2

float8

17,17

√

null

View Definition

SELECT scores.id,
    scores.title,
    scores.created_at,
    (((scores.base_score1 + (scores.base_score2)::double precision) + (scores.boost_score1)::double precision) + scores.boost_score2) AS score,
    scores.base_score1,
    scores.base_score2,
    scores.boost_score1,
    scores.boost_score2
   FROM ( SELECT article.id,
            article.title,
            article.created_at,
            LEAST(COALESCE(d1.donation_sum, (0)::double precision), (2000)::double precision) AS base_score1,
            LEAST((((COALESCE(r1.read_2_days, (0)::numeric) / (5000)::numeric) * ((2 + pd.post_days))::numeric) / ((1 + pd.post_days))::numeric), (1000)::numeric) AS base_score2,
            ((100 * COALESCE(lc2.like_30_mins, (0)::bigint)) + (500 * COALESCE(d2.donation_30_mins, (0)::bigint))) AS boost_score1,
            ((GREATEST(((400)::double precision - ((2)::double precision * fc.since_comment)), (0)::double precision) + GREATEST(((600)::double precision - ((3)::double precision * fl.since_like)), (0)::double precision)) + GREATEST(((1000)::double precision - ((5)::double precision * fd.since_donate)), (0)::double precision)) AS boost_score2
           FROM ((((((((article
             LEFT JOIN ( SELECT transaction.target_id,
                    sum(((1000)::double precision / power((((CURRENT_DATE - (transaction.created_at)::date) + 1))::double precision, (2)::double precision))) AS donation_sum
                   FROM transaction
                  WHERE ((transaction.purpose = 'donation'::text) AND (transaction.currency = 'HKD'::text))
                  GROUP BY transaction.target_id) d1 ON ((d1.target_id = article.id)))
             LEFT JOIN ( SELECT article_read_count.article_id,
                    sum(article_read_count.read_time) AS read_2_days
                   FROM article_read_count
                  WHERE (article_read_count.updated_at >= (now() - '2 days'::interval))
                  GROUP BY article_read_count.article_id) r1 ON ((r1.article_id = article.id)))
             LEFT JOIN ( SELECT appreciation.reference_id,
                    sum(appreciation.amount) AS like_30_mins
                   FROM appreciation
                  WHERE (appreciation.created_at >= (now() - '00:30:00'::interval))
                  GROUP BY appreciation.reference_id) lc2 ON ((lc2.reference_id = article.id)))
             LEFT JOIN ( SELECT transaction.target_id,
                    count(transaction.id) AS donation_30_mins
                   FROM transaction
                  WHERE ((transaction.purpose = 'donation'::text) AND (transaction.currency = 'HKD'::text) AND (transaction.created_at >= (now() - '00:30:00'::interval)))
                  GROUP BY transaction.target_id) d2 ON ((d2.target_id = article.id)))
             LEFT JOIN ( SELECT article_1.id,
                    (CURRENT_DATE - (article_1.created_at)::date) AS post_days
                   FROM article article_1) pd ON ((pd.id = article.id)))
             LEFT JOIN ( SELECT transaction.target_id,
                    (date_part('epoch'::text, (now() - min(transaction.created_at))) / (60)::double precision) AS since_donate
                   FROM transaction
                  WHERE ((transaction.purpose = 'donation'::text) AND (transaction.currency = 'HKD'::text) AND (transaction.created_at >= (now() - '30 days'::interval)))
                  GROUP BY transaction.target_id) fd ON ((fd.target_id = article.id)))
             LEFT JOIN ( SELECT comment.article_id,
                    (date_part('epoch'::text, (now() - min(comment.created_at))) / (60)::double precision) AS since_comment
                   FROM comment
                  WHERE (comment.created_at >= (now() - '30 days'::interval))
                  GROUP BY comment.article_id) fc ON ((fc.article_id = article.id)))
             LEFT JOIN ( SELECT appreciation.reference_id,
                    (date_part('epoch'::text, (now() - min(appreciation.created_at))) / (60)::double precision) AS since_like
                   FROM appreciation
                  WHERE (appreciation.created_at >= (now() - '30 days'::interval))
                  GROUP BY appreciation.reference_id) fl ON ((fl.reference_id = article.id)))) scores;

Columns

View Definition

Possibly Referenced Tables/Views