postgresql - 用于可变列数查询的 Postgres CrossTab

Question

上下文

我正在为一个站点运行车辆路径问题的变体。布局是这样的。您有取货点、卡车和正在取货的元素。真正的诀窍是客户一天的轮类次数不同，因此这是可定制的。所以我会在运行时知道这一点，但不会提前。我正在尝试创建查询，该查询返回一个结果，其中包含每个公交车站的行、每个类次的列和每个项目 ID 的 json 数组，如下所示:

id  |     address      |   city    | postalcode |  shift1  | shift2 | shift3
-----+------------------+-----------+------------+----------+--------+--------
 220 | 1471 N Lymestone | Anytown  | 12345      | [14, 16] | [12]   | [14]
 221 | 108 D Ave        | Anytown  | 12345      | [15, 17] |        | [15,16]
 222 | 1434 Bryan Ave   | Anytown  | 12345      | [16]     | [1,19] |

表结构

我这里有三个相关的表； stops 表仅包含停靠点的地理位置，items 表包含取件元素的 ID，shifts 表包含类次和itemstopassignemnt 表(请原谅命名，我继承了它)有一个 stop_id shift_id 和 item_id 分配一个在那个类次的那个站点要取的元素。

查询

所以在 SO 上四处寻找之后，我提出了以下查询:

SELECT * FROM crosstab(
     $$SELECT stop_id, 
              'shift' || shift_id, 
              json_agg(item_id) 
       FROM itemstopassignemnt 
       GROUP BY stop_id, shift_id 
       ORDER BY 1,2$$
) AS (stop_id INTEGER, shift1 JSON, shift2 JSON, shift3 JSON);

这适用于三类倒的情况。我可以将其放入以编程方式生成 sql 的函数中，以便它为用户添加的许多类次动态构建它。我认为该函数必须返回 setof record 或 table()。这是我的问题:

1. 这是解决问题的最佳方法吗？
2. 是否可以仅使用交叉表而不使用动态生成的 sql 来完成此操作？
3. 如果没有，那么设置函数的最佳方法是什么？具体来说，我应该使用哪种返回类型，因为无论如何我都可以在没有交叉表的情况下以编程方式完成此操作(遍历类次表并创建子选择)，在这里使用交叉表是否有优势？

Answer 1

http://rextester.com/RPV59686

select
    stop_id,
    jsonb_object_agg('shift' || shift_id, items) as shift_items
from (
    select
        stop_id,
        shift_id, 
        coalesce (
            array_agg (item_id) filter (where item_id is not null),
            array[]::int[]
        ) as items
    from
        itemstopassignemnt
        right join
        (
            select shift_id, stop_id
            from
                (select distinct shift_id from itemStopAssignemnt) a
                cross join
                (select distinct stop_id from itemStopAssignemnt) b
        ) r using (stop_id, shift_id)
    group by 1,2 
) s
group by 1
;
 stop_id |                      shift_items                       
---------+--------------------------------------------------------
     220 | {"shift1": [14, 16], "shift2": [12], "shift3": [14]}
     221 | {"shift1": [15, 17], "shift2": [], "shift3": [15, 16]}
     222 | {"shift1": [16], "shift2": [1, 9], "shift3": []}

我没有将它加入 stops 来缩短它，但应该是微不足道的。