Geek asks and answers

Oracle, how to get the most common column value for multiple columns?

Suppose I have the following table, how can I group by ID and get the most common value in each column of the postscript table is large, and I need to do this for many columns

ID  Col1  Col2  Col3....
1   A     null
1   A     X
1   B     null
1   A     Y
2   C     X
2   C     Y
2   A     Y
3   B     Z
3   A     Z
3   A     Z
3   B     X
3   B     Y

Expected Result:

ID  Col1  Col2  Col3....
1   A     null
2   C     Y
3   B     Z
+3
source share
1 answer

Here is one way using analytic functions and keep:

select id,
       min(col1) keep(dense_rank first order by cnt_col1 desc) as col1_mode,
       min(col2) keep(dense_rank first order by cnt_col2 desc) as col2_mode,
       min(col3) keep(dense_rank first order by cnt_col3 desc) as col3_mode
from (select id,
             count(*) over (partition by id, col1) as cnt_col1,
             count(*) over (partition by id, col2) as cnt_col2,
             count(*) over (partition by id, col3) as cnt_col3
      from t
     ) t
group by id;

The most common value is called "mode" in statistics, and Oracle offers a function to calculate this. Thus, it is easier to use stats_mode():

   select id,
         stats_mode(col1) as mode_col1,
         stats_mode(col2) as mode_col2,
         stats_mode(col3) as mode_col3
  from table t
  group by id;

EDIT:

, stats_mode() NULL. - - , :

   select id,
          stats_mode(coalesce(col1, '<null>')) as mode_col1,
          stats_mode(coalesce(col2, '<null>')) as mode_col2,
          stats_mode(coalesce(col3, '<null>')) as mode_col3
  from table t
  group by id;

- :

select id,
       (case when sum(case when col1 = mode_col1 then 1 else 0 end) >= sum(case when col1 is null then 1 else 0 end)
             then mode_col1
             else NULL
        end) as mode_col1,
       (case when sum(case when col2 = mode_col2 then 1 else 0 end) >= sum(case when col2 is null then 1 else 0 end)
             then mode_col2
             else NULL
        end) as mode_col2,
       (case when sum(case when col3 = mode_col13 then 1 else 0 end) >= sum(case when col3 is null then 1 else 0 end)
             then mode_col3
             else NULL
        end) as mode_col3
from (select t.*,
             stats_mode(col1) over (partition by id) as mode_col1,
             stats_mode(col2) over (partition by id) as mode_col2,
             stats_mode(col3) over (partition by id) as mode_col3
      from table t
     ) t
group by id;
+4

More articles:


All Articles