I'm trying to summarize some statics properties from several numeral columns such as continuous quantiles, mean , standard deviation, etc., then wrap them into rows with original column name attached as an additional column. I know about using AVG, STDDEV_PO, PERCENTILE_CONT... to get them from single column, but didn't find article/recipe about doing them on several columns at once. Any ideas?

Example input:

Expected output:

Or 'transposed' version:

CodePudding user response：

Consider below

select distinct col, 
  percentile_cont(value, 0.25) over win as q1,
  percentile_cont(value, 0.50) over win as q2,
  percentile_cont(value, 0.75) over win as q3,
  avg(value) over win as avg, 
  stddev_pop(value) over win as std, 
from your_table
unpivot (value for col in (col1, col2, col3))
window win as (partition by col)                 

if applied to sample data in your question - output is

To get the 'transposed' version - use below

select * from (
  select * from (
    select distinct col, 
      percentile_cont(value, 0.25) over win as q1,
      percentile_cont(value, 0.50) over win as q2,
      percentile_cont(value, 0.75) over win as q3,
      avg(value) over win as avg, 
      stddev_pop(value) over win as std, 
    from data
    unpivot (value for col in (col1, col2, col3))
    window win as (partition by col)
  ) unpivot (value for stats in (q1, q2, q3, avg, std))
) pivot (any_value(value) for col in ('col1', 'col2', 'col3'))

in this case - output is