sql - 如何在主查询中遍历CTE

我正在尝试根据用户的totalarticleviews和用户的totalarticles对系统中的用户进行排名。排名应基于公式(totalArticleViews + ( totalArticles * 500 )) / 100
我有一个允许用户发布文章的系统，每当任何人阅读这些文章时都会创建一个记录。我的数据库有以下表格。users，articles，reads。
我试图将视图插入到公式中，但在获取所有用户文章并将其乘以500以插入公式中对其进行排序时遇到问题

with article_views AS (
 SELECT article_id, COUNT(reads.id) AS views, 1 * 500 AS points
 FROM reads
 WHERE article_id IN (
 SELECT id FROM articles WHERE articles.published_on IS NOT NULL AND
  articles.deleted_at IS NULL
 )
GROUP BY article_id
),
published AS (
SELECT COUNT(articles.id) AS TotalArticle, COUNT(articles.id) * 500 AS
points
FROM articles
WHERE published_on IS NOT NULL AND deleted_at IS NULL
GROUP BY articles.user_id
)

SELECT
 users.id AS user_id,
 ROUND((SUM(article_views.views) + () ) / 100.0, 2)  AS points,
 ROW_NUMBER() OVER (ORDER BY ROUND((SUM(article_views.views) + ()) /
100.0, 2) DESC)
FROM users
 LEFT JOIN articles ON users.id = articles.user_id
 LEFT JOIN reads ON articles.id = reads.article_id
 LEFT JOIN article_views ON reads.article_id = article_views.article_id
WHERE
 users.id IN (SELECT user_id FROM role_user WHERE role_id = 2)
 AND status = 'ACTIVE'
GROUP BY users.id
ORDER BY points DESC NULLS LAST

我被困在这一点上

(SUM(article_views.views) + () ) / 100.0, 2)

最佳答案

只需使用已发布的cte，方法是在GROUP BY中包含SELECT列user_id，然后在主级查询中将此字段加入已发布给用户。

WITH article_views AS (
    SELECT r.article_id,
           COUNT(r.id) AS views,
           1 * 500 AS points
    FROM reads r
    WHERE r.article_id IN (
         SELECT id
         FROM articles a
         WHERE a.published_on IS NOT NULL
           AND a.deleted_at IS NULL
    )
    GROUP BY r.article_id
),
published AS (
   SELECT a.user_id,
          COUNT(a.id) AS TotalArticle,
          COUNT(a.id) * 500 AS points
   FROM articles a
   WHERE a.published_on IS NOT NULL
     AND a.deleted_at IS NULL
   GROUP BY a.user_id
)

SELECT u.id AS user_id,
       ROUND((SUM(av.views) + (p.TotalArticle)) / 100.0, 2)  AS points,
       ROW_NUMBER() OVER (ORDER BY ROUND((SUM(av.views) + (p.points))
                                          / 100.0, 2) DESC) AS rn
FROM users u
LEFT JOIN articles a ON u.id = a.user_id
LEFT JOIN reads r ON a.id = r.article_id
LEFT JOIN article_views av ON r.article_id = av.article_id
LEFT JOIN published p ON u.id = p.user_id
WHERE u.id IN (
     SELECT user_id FROM role_user WHERE role_id = 2
)
AND u.status = 'ACTIVE'
GROUP BY u.id
ORDER BY points DESC NULLS LAST

关于sql - 如何在主查询中遍历CTE，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57150017/