按年最高发生次数对事件进行排序

Question

我想找出每年发生多少件事。目前，我有这样的查询，这基本上是在事件有访问者时计算在内的：

SELECT 
count(visitors_y_2016) as y_16,
count(visitors_y_2017) as y_17,
count(visitors_y_2018) as y_18,
count(visitors_y_2019) as y_19, 
count(visitors_y_2020) as y_20
FROM event
;

y16  y17  y18  y19  y20 
23   25   26   27   19

但我想要的是一年中有更多事件的订单：

Y19 27
Y18 26
y17 25
y16 23
y20 19

知道怎么做到吗？

Answer 1

您可以在VALUES子查询中使用LATERAL表达式“解除枢轴”：

SELECT t.*
FROM  (
   SELECT count(visitors_y_2016) AS y16
        , count(visitors_y_2017) AS y17
        , count(visitors_y_2018) AS y18
        , count(visitors_y_2019) AS y19 
        , count(visitors_y_2020) AS y20
   FROM   event
   ) e, LATERAL (
   VALUES
     (16, e.y16)
   , (17, e.y17)
   , (18, e.y18)
   , (19, e.y19)
   , (20, e.y20)
   ) t(year, count)
ORDER  BY count DESC;  -- your desired sort order

db<>fiddle https://dbfiddle.uk/?rdbms=postgres_14&fiddle=f40e145b4bf01926d1fe8eb93733733a

因为这只需要表上的单扫描，所以它比单独聚合输出值要快很多倍。

VALUES表达式中的每一行都由两列组成：year (数字默认值为integer)和count (引用列的类型)。

请参见：

• 交叉表视图查询
• 在多列上选择DISTINCT

关于LATERAL子查询：

• 在PostgreSQL中横向连接和子查询有什么区别？

但是你的桌子设计引起了问题。通常您有一个日期或时间戳列visitors，而不是visitors_y_2016、visitors_y_2017等-以及基于此的更简单的查询……

Answer 2

我不认为你每年都需要一个选择。我不太了解你的表，但是应该有一个更好的wayu来组织你的数据。另外，如果你想对数据排序的话，排序应该是你的朋友。您只需有一个选择就可以使用它，例如：

SORT BY
VISITOR_COUNT