[SQL one-liners] SQL one-liners #sql #oneliner #database

sql-one-liners.sql

-- pg dump of a specific schema from metrics db on index
pg_dump metrics -h 10.1.24.39 -p 5433 -U cdt_user -n <schema_name> > <out>.sql

-- select a random percentage of rows of table
select * from my_table tablesample bernoulli(<percentage in numbers, e.g. 10>)
-- or to select random rows in query
select * from query order by random() limit 100

-- useful aggregate functions to use with GROUP BY
string_agg(<field>, ';') /* equivalent to R's paste(<field>, collapse = ";") */

-- numbering rows by grouping
select participant_id
    ,row_number() over(partition by participant_id) as row_index
from blahdiblah
 
-- time functions
<some_date> > now() - interval '9 month' -- get only rows from last 9 months
date(date_trunc('week', <some_date>)) -- round down to week of some_date
date_part('year', age(<date_to_measure_age_at>, <date_of_birth>)) -- get age at date