SQL

From shell:

# log in using the server name, database name, and username
psql -h [myhost] -d [mydb] -U [myuser]
psql -h dbase1 drupal.dh03 (quit to quit)

Useful commands:

-- this is a comment

SELECT current_database();
SELECT pg_size_pretty(pg_database_size('drupal.dh03'));
SELECT pg_size_pretty(pg_total_relation_size('dh_feature'));

SELECT table_name
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_schema='public';

Window Functions:

# compute 7-day rolling average
gage <-sqldf(paste('SELECT *, 
                      AVG(Flow) OVER (ORDER BY Date ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS sql_rolling_avg
                    FROM gage',sep="")) 

CTE:

# format table using CTE temporary table
sw_status_df_all = sqldf("""WITH cte AS(
                                SELECT  CASE
                                        WHEN `drought_status_override` < `[nonex_pct]_propcode` THEN `drought_status_override`
                                            ELSE `[nonex_pct]_propcode`
                                        END AS final_status,
                                        COUNT(`containing_drought_region`) AS gage_count
                                FROM sw_df
                                WHERE `[nonex_pct]_propcode` > 0
                                GROUP BY final_status
                            )
   
                            SELECT  CASE
                                        WHEN `final_status` = 1 THEN 'watch'
                                        WHEN `final_status` = 2 THEN 'warning'
                                        WHEN `final_status` = 3 THEN 'emergency'
                                    END AS gage_status,
                                    gage_count
                            FROM cte
                            """)

Complex Examples:

• Some PostgreSQL functions
  • TO_TIMESTAMP(timestamp, format)
  • EXTRACT(field FROM source)

-- https://github.com/HARPgroup/vahydro/blob/master/sql/ann_ne_monthly.sql
select * from (
  select a.hydroid, a.name, 
    extract(year from to_timestamp(b.tstime)),
    b.tsvalue as annual,
    sum(c.tsvalue) as monthly
  from dh_feature as a
  left outer join dh_timeseries as b 
  on (
    a.hydroid = b.featureid
    and b.entity_type = 'dh_feature'
    and b.varid = 305
  )
  left outer join dh_timeseries as c
  on (
    a.hydroid = c.featureid
    and c.entity_type = 'dh_feature'
    and c.varid = 1021
  )
  where a.bundle in ('well', 'intake')
  and extract(year from to_timestamp(b.tstime)) = 2021
  and extract(year from to_timestamp(c.tstime)) = 2021
  group by a.hydroid, a.name, b.tsvalue, extract(year from to_timestamp(b.tstime))
) as foo
where (
  (annual > 1.01 * monthly )
  or (annual < 0.99 * monthly )
)
;