sql - postgreSQL - 从许多列中获取最频繁的值

Question

我有一个餐 table 爱好:

+++++++++++++++++++++++++++++++
+ hobby_1 | hobby_2 | hobby_3 +
+---------+---------+---------+
+ music   | soccer  | [null]  +
+ movies  | music   | cars    +
+ cats    | dogs    | music   +
+++++++++++++++++++++++++++++++

我想获取最常用的值。答案是音乐

我知道获取一列最频繁值的查询:

SELECT hobby_1, COUNT(*) FROM hobbies
    GROUP BY hobby_1
    ORDER BY count(*) DESC;

但是如何在组合所有列时得到最频繁的值。

Answer 1

您需要对数据进行逆透视。这是一种方法:

select h.hobby, count(*)
from ((select hobby_1 as hobby from hobbies) union all
      (select hobby_2 as hobby from hobbies) union all
      (select hobby_3 as hobby from hobbies) 
     ) h
group by h.hobby
order by count(*) desc;

但是，您确实应该修复您的数据结构。具有仅由数字区分的多个列通常表示数据结构存在问题。您应该有一个表格，每个爱好一行。