Posts Tagged ‘padb’

Quick Tip: How to Extract a Month as an int in PaDB

select extract(month from ds)::int
from cool_db_name.cool_table limit 10;

Postgres / PaDB: Statistics

corr(YX)double precisiondouble precisioncorrelation coefficient
covar_pop(YX)double precisiondouble precisionpopulation covariance
covar_samp(YX)double precisiondouble precisionsample covariance
regr_avgx(YX)double precisiondouble precisionaverage of the independent variable (sum(X)/N)
regr_avgy(YX)double precisiondouble precisionaverage of the dependent variable (sum(Y)/N)
regr_count(YX)double precisionbigintnumber of input rows in which both expressions are nonnull
regr_intercept(YX)double precisiondouble precisiony-intercept of the least-squares-fit linear equation determined by the (XY) pairs
regr_r2(YX)double precisiondouble precisionsquare of the correlation coefficient
regr_slope(YX)double precisiondouble precisionslope of the least-squares-fit linear equation determined by the (XY) pairs
regr_sxx(YX)double precisiondouble precisionsum(X^2) – sum(X)^2/N (“sum of squares” of the independent variable)
regr_sxy(YX)double precisiondouble precisionsum(X*Y) – sum(X) * sum(Y)/N (“sum of products” of independent times dependent variable)
regr_syy(YX)double precisiondouble precisionsum(Y^2) – sum(Y)^2/N (“sum of squares” of the dependent variable)
stddev(expression)smallintintbigintrealdouble precision, or numericdouble precision for floating-point arguments, otherwise numerichistorical alias for stddev_samp
stddev_pop(expression)smallintintbigintrealdouble precision, or numericdouble precision for floating-point arguments, otherwise numericpopulation standard deviation of the input values
stddev_samp(expression)smallintintbigintrealdouble precision, or numericdouble precision for floating-point arguments, otherwise numericsample standard deviation of the input values
variance(expression)smallintintbigintrealdouble precision, or numericdouble precision for floating-point arguments, otherwise numerichistorical alias for var_samp
var_pop(expression)smallintintbigintrealdouble precision, or numericdouble precision for floating-point arguments, otherwise numericpopulation variance of the input values (square of the population standard deviation)
var_samp(expression)smallintintbigintrealdouble precision, or numericdouble precision for floating-point arguments, otherwise numericsample variance of the input values (square of the sample standard deviation)

Quick Tip: Check PaDB Disk Usage in 1 Line

select owner, host, diskno, used, capacity from stv_partitions order by 1, 2, 3, 4, 5;
or
select sum( used), sum(capacity) from stv_partitions;

Quick Tip: Create a Binary Column Conditionally in Postgres/PaDB/SQL


create table c as
    select a.id, tall, funny, weight,
           (case when b.id is null then 0 else 1 end) as rich
    from tablea a left outer join
         tableb b 
         on a.id = b.id;
or
select
    a.id, tall, funny, weight,
    (b.id is not null)::integer as rich
from
    tablea a
    left outer join
    tableb b on a.id = b.id

Quick-Tip: Connect to a ParAccell (aka PaDB) DB from Bash in 1 Line

psql -h xen-01.yourhostname.com –username paraccel hsa -p 5439