PostgreSQL 中的 SQL 窗口函数

Question

我是 SQL 新手，我尝试使用 PostgreSQL (9.6) 查询数据库。

当我编写以下代码时，出现了 windows 函数的语法错误:

/* Ranking total of rental movie by film category (I use the sakila database) */

SELECT category_name, rental_count
FROM
    (SELECT c.name category_name, Count(r.rental_id) rental_count
    FROM category c
    JOIN film_category USING (category_id)
    JOIN inventory USING (film_id)
    JOIN rental r USING (inventory_id)
    JOIN film f USING (film_id)
    GROUP BY 1, 2
    ORDER by 2, 1
     ) sub
RANK() OVER (PARTITION BY category_name ORDER BY rental_count DESC) AS total_rank

Answer 1

你不需要子查询:

SELECT c.name as category_name, COUNT(*) as rental_count,
       ROW_NUMBER() OVER (PARTITION BY c.name ORDER BY COUNT(*) DESC)
FROM category c JOIN
     film_category USING (category_id) JOIN
     inventory USING (film_id) JOIN
     rental r USING (inventory_id) JOIN
     film f USING (film_id)
GROUP BY 1
ORDER by 2, 1;

您也不需要连接到 film，因为您没有使用该表中的任何内容。

您的查询失败，因为列列表位于 SELECT 子句中。 FROM 列表在 SELECT 之后。