Monday, July 7, 2014

How to calculate gp_skew_coefficients.skccoeff manually in Greenplum

Per Greenplum Admin Guide: "The coefficient of variation (CV) is calculated as the standard deviation divided by the average.".
The "standard deviation" and "average" are based on (select gp_segment_id,count(*) from <table_name> group by gp_segment_id).
I did some tests and finally got a SQL to calculate it.
testdb=# select STDDEV(cnt)/avg(cnt)*100
testdb-# from
testdb-#    (
testdb(#      select gp_segment_id segid,count(*) cnt from testtable group by gp_segment_id
testdb(#      union all
testdb(#      (
testdb(#        select tmp2.content segid,0 cnt from
testdb(#        gp_segment_configuration tmp2
testdb(#        where tmp2.content not in (select gp_segment_id segid from testtable group by gp_segment_id)
testdb(#        and tmp2.role='p'
testdb(#        and tmp2.content<>-1
testdb(#       )
testdb(#     ) tmpc;
        ?column?         
-------------------------
 28.54846392774083598000
(1 row)
 
Time: 461.224 ms
testdb=# select * from gp_toolkit.gp_skew_coefficients where skcrelname='testtable';
 skcoid | skcnamespace | skcrelname |         skccoeff         
--------+--------------+------------+--------------------------
 273985 | inf          | testtable  | 28.548463927740835980000
(1 row)

No comments:

Post a Comment

Popular Posts