postgresql - 具有多列和星期几的PostgreSQL交叉表查询

我正试图通过对几个列进行分组来创建一个轴心点：用户id、名称、周号和日名称。
当前请求没有给出所需的结果。
我需要帮助。
这是我的桌子：

user_id name    week_number day_name    price
2       Luc     8           Sunday      10
2       Luc     8           Monday      15
2       Luc     8           Tuesday     8
2       Luc     8           Wednesday   2
2       Luc     8           Thursday    9
2       Luc     8           Friday      9
2       Luc     8           Saturday    11
2       Luc     9           Saturday    1
2       Luc     9           Friday      13
3       Mathieu 8           Sunday      22
3       Mathieu 8           Monday      13
3       Mathieu 8           Tuesday     9
3       Mathieu 8           Wednesday   3

这是我当前的请求：

SELECT *
FROM   crosstab(
'SELECT user_id, name, week_number,day_name,price
FROM   table_1
ORDER  BY 1,2,3,4'
) AS ct (
"user_id" integer,
"day_name" text,
"Sunday" integer,
"Monday" integer,
"Tuesday" integer,
"Wednesday" integer,
"Thursday" integer,
"Friday" integer,
"Saturday" integer
);

这是我想要的结果。

postgresql - 具有多列和星期几的PostgreSQL交叉表查询-LMLPHP

最佳答案

您可以只使用条件聚合：

SELECT user_id, name, week_number
       MAX(price) FILTER (WHERE day_name = 'Sunday') as Sunday,
       MAX(price) FILTER (WHERE day_name = 'Monday') as Monday,
       MAX(price) FILTER (WHERE day_name = 'Tuesday') as Tuesday,
       MAX(price) FILTER (WHERE day_name = 'Wednesday') as Wednesday,
       MAX(price) FILTER (WHERE day_name = 'Thursday') as Thursday,
       MAX(price) FILTER (WHERE day_name = 'Friday') as Friday,
       MAX(price) FILTER (WHERE day_name = 'Saturday') as Saturday
FROM table_1
GROUP BY user_id, name, week_number
ORDER BY user_id, name, week_number;

编辑：
您可以在不FILTER的情况下编写相同的逻辑：

       MAX(CASE WHEN day_name = 'Sunday' THEN price END) as Sunday,