Postgres version 9.4.18, PostGIS Version 2.2.
Here are the tables I'm working with (and can unlikely make significant changes to the table structure):
Table ltg_data (spans 1988 to 2018):
Column | Type | Modifiers ----------+--------------------------+----------- intensity | integer | not null time | timestamp with time zone | not null lon | numeric(9,6) | not null lat | numeric(8,6) | not null ltg_geom | geometry(Point,4269) | Indexes: "ltg_data2_ltg_geom_idx" gist (ltg_geom) "ltg_data2_time_idx" btree ("time") Size of ltg_data (~800M rows): ltg=# select pg_relation_size('ltg_data'); pg_relation_size ------------------ 149729288192 Table counties:
Column | Type | Modifiers -----------+-----------------------------+--------------------------------- ----------------------- gid | integer | not null default nextval('counties_gid_seq'::regclass) objectid_1 | integer | objectid | integer | state | character varying(2) | cwa | character varying(9) | countyname | character varying(24) | fips | character varying(5) | time_zone | character varying(2) | fe_area | character varying(2) | lon | double precision | lat | double precision | the_geom | geometry(MultiPolygon,4269) | Indexes: "counties_pkey" PRIMARY KEY, btree (gid) "counties_gix" gist (the_geom) "county_cwa_idx" btree (cwa) "countyname_cwa_idx" btree (countyname) I have a query that calculates the total number of rows per day of the year (month-day) spanning the 30 years. With the help of Stackoverflow, the query to get these counts is working fine. Here's the query and results, using the following function.
Function:
CREATE FUNCTION f_mmdd(date) RETURNS int LANGUAGE sql IMMUTABLE AS $$SELECT to_char($1, 'MMDD')::int$$; Query:
SELECT d.mmdd, COALESCE(ct.ct, 0) AS total_count FROM ( SELECT f_mmdd(d::date) AS mmdd -- ignoring the year FROM generate_series(timestamp '2018-01-01' -- any dummy year , timestamp '2018-12-31' , interval '1 day') d ) d LEFT JOIN ( SELECT f_mmdd(time::date) AS mmdd, count(*) AS ct FROM counties c JOIN ltg_data d ON ST_contains(c.the_geom, d.ltg_geom) WHERE cwa = 'MFR' GROUP BY 1 ) ct USING (mmdd) ORDER BY 1; Results:
mmdd total_count 725 | 2126 726 | 558 727 | 2 728 | 2 729 | 2 730 | 0 731 | 0 801 | 0 802 | 10 Desired Results: I'm trying to find other statistical information about the counts for the days of the year. For instance, I know on July 25 (725 in the table below) that the total count over the many years that are in the table is 2126. What I'm looking for is the max daily count for July 25 (725), percent of years that that day is not zero, the min, percent years where count(*) is not zero, percentiles (10th percentile, 25th percentile, 50th percentile, 75th percentile, 90th percentile, and stdev would be useful too). It would be good to see what year the max_daily occurred. I guess if there haven't been any counts for that day in all the years, the year_max_daily would be blank or zero.
mmdd total_count max daily year_max_daily percent_years_count_not_zero 10th percentile_daily 90th percentile_daily 725 | 2126 1000 1990 30 15 900 726 | 558 120 1992 20 10 80 727 | 2 1 1991 2 0 1 728 | 2 1 1990 2 0 1 729 | 2 1 1989 2 0 1 730 | 0 0 0 0 0 731 | 0 0 0 0 0 801 | 0 0 0 0 0 802 | 10 10 1990 0 1 8 What I've tried thus far just isn't working. It returns the same results as total. I think it's because I'm just trying to get an avg after the totals have already been calculated, so I'm not really looking at the counts for each day of each year and finding the average.
Attempt:
SELECT AVG(CAST(total_count as FLOAT)), day FROM ( SELECT d.mmdd as day, COALESCE(ct.ct, 0) as total_count FROM ( SELECT f_mmdd(d::date) AS mmdd FROM generate_series(timestamp '2018-01-01', timestamp '2018-12-31', interval '1 day') d ) d LEFT JOIN ( SELECT mmdd, avg(q.ct) FROM ( SELECT f_mmdd((time at time zone 'utc+12')::date) as mmdd, count(*) as ct FROM counties c JOIN ltg_data d on ST_contains(c.the_geom, d.ltg_geom) WHERE cwa = 'MFR' GROUP BY 1 ) ) as q ct USING (mmdd) ORDER BY 1 Thanks for any help!
1 Answers
Answers 1
I haven't included calculations for all requested stats - there is too much in one question, but I hope that you'd be able to extend the query below and add extra stats that you need.
I'm using CTE below to make to query readable. If you want, you can put it all in one huge query. I'd recommend to run the query step-by-step, CTE-by-CTE and examine intermediate results to understand how it works.
CTE_Dates is a simple list of all possible dates for 30 years.
CTE_DailyCounts is a list of basic counts for each day for 30 years (I took your existing query for that).
CTE_FullStats is again a list of all dates together with some stats calculated for each (month,day) using window functions with partitioning by month,day. ROW_NUMBER there is used to get a date where the count was the largest for each year.
Final query selects only one row with the largest count for the year along with the rest of the information.
I didn't try to run the query, because the question doesn't have sample data, so there may be some typos.
WITH CTE_Dates AS ( SELECT d::date AS dt ,EXTRACT(MONTH FROM d::date) AS dtMonth ,EXTRACT(DAY FROM d::date) AS dtDay ,EXTRACT(YEAR FROM d::date) AS dtYear FROM generate_series(timestamp '1988-01-01', timestamp '2018-12-31', interval '1 day') AS d -- full range of possible dates ) ,CTE_DailyCounts AS ( SELECT time::date AS dt ,count(*) AS ct FROM counties c INNER JOIN ltg_data d ON ST_contains(c.the_geom, d.ltg_geom) WHERE cwa = 'MFR' GROUP BY time::date ) ,CTE_FullStats AS ( SELECT CTE_Dates.dt ,CTE_Dates.dtMonth ,CTE_Dates.dtDay ,CTE_Dates.dtYear ,CTE_DailyCounts.ct ,SUM(CTE_DailyCounts.ct) OVER (PARTITION BY dtMonth, dtDay) AS total_count ,MAX(CTE_DailyCounts.ct) OVER (PARTITION BY dtMonth, dtDay) AS max_daily ,SUM(CASE WHEN CTE_DailyCounts.ct > 0 THEN 1 ELSE 0 END) OVER (PARTITION BY dtMonth, dtDay) AS nonzero_day_count ,COUNT(*) OVER (PARTITION BY dtMonth, dtDay) AS years_count ,100.0 * SUM(CASE WHEN CTE_DailyCounts.ct > 0 THEN 1 ELSE 0 END) OVER (PARTITION BY dtMonth, dtDay) / COUNT(*) OVER (PARTITION BY dtMonth, dtDay) AS percent_years_count_not_zero ,ROW_NUMBER() OVER (PARTITION BY dtMonth, dtDay ORDER BY CTE_DailyCounts.ct DESC) AS rn FROM CTE_Dates LEFT JOIN CTE_DailyCounts ON CTE_DailyCounts.dt = CTE_Dates.dt ) SELECT dtMonth ,dtDay ,total_count ,max_daily ,dtYear AS year_max_daily ,percent_years_count_not_zero FROM CTE_FullStats WHERE rn = 1 ORDER BY dtMonth ,dtDay ; 
0 comments:
Post a Comment