SQL

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
                            """)