如何从子查询中选择多个列而不是多个相似的子查询？

Question

我正在尝试按查看/下载对图像列表进行排序。我将统计信息存储在另一个表中，每天在其中保存一行。经过几天的搜索，我已经成功地获得了一个有效的 SQL，但它似乎不是很有效，因为我几乎执行了 3 次相同的查询。这是我的表和 SQL：

CREATE TABLE "images_stats" (
    "id" integer NOT NULL PRIMARY KEY,
    "image_id" integer NOT NULL REFERENCES "images_image" ("id"),
    "date" date NOT NULL,
    "view_count" integer unsigned NOT NULL,
    "download_count" integer unsigned NOT NULL
)

images_list = Images.objects.raw('''
    SELECT *, 
        (SELECT 1.0*SUM(s.view_count)/SUM(s.download_count) 
         FROM images_stats AS s
         WHERE s.image_id = w.id AND s.date < %s AND s.date >= %s) AS ratio,
        (SELECT 1.0*SUM(s.view_count)/SUM(s.download_count) 
         FROM images_stats AS s
         WHERE s.image_id = w.id AND s.date < %s) AS global_ratio,
        (SELECT COUNT(*)==0
         FROM images_stats AS s
         WHERE s.image_id = w.id AND s.date < %s) AS count
    FROM images_image as w
    WHERE w.category_id = %s
    ORDER BY count, ratio, global_ratio
''', [date.today(), date.today()-timedelta(days=1), date.today(), date.today(), category.id])

有人知道我如何优化这个 SQL 吗？我有一些 SQL 知识，但显然还不够。

Answer 1

SELECT *, 
    SUM(case when s.date < %s AND s.date >= %s then s.view_count else 0 end)/
    SUM(case when s.date < %s AND s.date >= %s then s.download_count else 0 end) 
        AS ratio,
    SUM(case when s.date < %s then s.view_count else 0 end)/
    SUM(case when s.date < %s then s.download_count else 0 end) 
        AS global_ratio,
    COUNT(*)==0 AS count
FROM images_image as w
LEFT JOIN images_stats AS s 
    ON s.image_id = w.id
WHERE w.category_id = %s
GROUP BY w.id
ORDER BY count, ratio, global_ratio