PostgreSQL 编写一个具体化视图查询以包括基本记录和记录数匹配

Question

我有两个表，一个是用户，另一个是postgresql中的订单。

用户表

userid  | username  | usertype
1       |   John    |  F
2       |   Bob     |  P

订单表

userid  |   orderid | ordername
1       |   001     |   Mobile
1       |   002     |   TV
1       |   003     |   Laptop
2       |   001     |   Book
2       |   002     |   Kindle

现在我想为 postgresql 物化视图编写一个查询，它会给我如下所示的输出

userid  | username | Base Order Name |No of Orders | User Type
1       | John     | Mobile          |  3          |  F - Free
2       | Bob      | Book            |  2          |  P- Premium

我尝试过下面的查询，但它给出了五条记录作为输出而不是两条记录，并且没有弄清楚如何显示用户类型 F - 免费/P - 高级

CREATE MATERIALIZED VIEW userorders
TABLESPACE pg_default
AS
SELECT
u.userid,
username,
(select count(orderid)  from orders where userid = u.userid)
as no_of_orders,
(select ordername  from orders where orderid=1 and userid = u.userid)
as baseorder
FROM users u
INNER JOIN orders o ON u.userid = o.userid
WITH DATA;

它给出的结果如下所示

userid | username | no_of_orders | baseorder
1      | John     | 3            | Mobile
1      | John     | 3            | Mobile
1      | John     | 3            | Mobile
2      | Bob      | 2            | Book
2      | Bob      | 2            | Book

假设基本订单 ID 始终为 001。在最终的物化视图中，用户类型将通过查询中的某些映射返回 F - 免费/P - 高级。

Answer 1

使用组 by，这变得非常简单。 唯一稍微复杂的部分是获取基本订单名称，但这可以使用 FILTER 完成：

select users.userid,
username,
max(ordername) FILTER (WHERE orderid='001') as "Base Order Name",
count(orderid) as "No of Orders",
CASE WHEN usertype = 'F' THEN 'F - Free'
WHEN usertype = 'P' THEN 'P- Premium'
END as "User Type"
FROM users
JOIN orders on users.userid = orders.userid
GROUP BY users.userid, users.username, users.usertype;