sql - PostgreSQL - 使用数组排序。我可以动态创建数组吗？

Question

我正在使用 PostgreSQL 9.1.13

这是我到目前为止所做的:

/* First Array ==> SELECT raggruppamento FROM ok_filiali GROUP BY raggruppamento ORDER by count(raggruppamento) DESC */
/* Second Array ==> SELECT banker FROM ok_filiali GROUP BY banker ORDER by count(banker) DESC */

SELECT mandato, raggruppamento, banker
FROM ok_filiali
ORDER BY
idx(array['MI','FI','BS','RM','BO','TO','GE','*','PD','PR'], ok_filiali.raggruppamento), 
idx(array['120','MF','28','921','30','29','56','38','76','33', '27', '8037', '5038', '173', '5984', '45', '104', '46', 
    '84', '284', '176', '5311', '209', '248', '5138', '5064', '325', 
    '171', '348', '255', '274', '373', '5915', '318', '5076'], ok_filiali.banker)

现在我已经使用了上述查询并手动创建了 2 个数组。

我需要做的是动态创建用于 Order By 的 2 个数组(直接使用 2 个查询)

这有可能吗？

谢谢

Answer 1

如果我理解正确的话，您的目标是根据某些值出现的频率对结果进行排序。使用 window functions 更容易完成此操作

SELECT mandato, raggruppamento, banker, 
    COUNT(*) OVER (PARTITION BY raggruppamento) AS order1.
    COUNT(*) OVER (PARTITION BY banker) AS order2
FROM ok_filiali
ORDER BY order1 DESC, order2 DESC;