结合生成系列并计入一个查询

Question

Postgres版本9.4.18，PostGis版本2.2。

我从这个问题中删除了有关表格的一些细节，因为我怀疑需要回答这个问题。如有必要，我可以添加这些细节。

所需结果：我想要每年的每小时和每小时的总数（0100至5223）。我能够成功产生一系列的0100至5223（实际上最高5300），并且我能够单独每年的每年一周和每小时都能获得总数，但是我无法将查询结合在一起因此，每年的几周/小时数零县仍然出现。我想将计数结果与generate_series（理想情况下除以30）的结合，以获取以下类似的东西。

MM-DD  | count_not_zero | count_not_zero_divided_by_30
-------+----------------+----------------------------
0100  |       10        |  33.3
0101  |       0         |  0
0102  |       0         |  0
...
0123  |       0         |  0
0200  |       3         |  10
0201  |       10        |  33.3
...
5223  |       20        |  66.6

这是我要组合的个人查询：

SELECT DISTINCT f_woyhh(d::timestamp) as woyhh 
FROM generate_series(timestamp '2018-01-01', timestamp '2018-12-31', interval '1 hour') d 
GROUP BY woyhh
ORDER by woyhh asc;
SELECT dt, count(*) FROM 
(SELECT f_woyhh((time)::timestamp at time zone 'utc' at time zone 'america/chicago') 
AS dt, 
EXTRACT(YEAR FROM time) AS ctYear, count(*) 
AS ct 
FROM counties c 
INNER JOIN ltg_data d ON ST_contains(c.the_geom, d.ltg_geom) 
WHERE countyname = 'Milwaukee' AND state = 'WI' AND EXTRACT(YEAR from time) > '1987' GROUP BY dt, EXTRACT(YEAR from time)) 
AS count group by dt;

上面的第二个查询的结果是（我不需要跳过零数DT）：

dt  | count
-------+-------
0100  |    10      
0104  |    5       
0108  |    4     

...

结论：我正在尝试将上述单独的查询组合到一个单个查询中，该查询提供了三个三列结果 - woyhh，count和count分别除以30。我有一套完整的woyhh。

感谢您的任何帮助！

Answer 1

我找到了答案。我明天将发布它，但我想今天就把它放在这个问题上，所以没有人在这个问题上不必要。对于格式，我深表歉意。

WITH CTE_Dates AS (SELECT DISTINCT f_woyhh(d::timestamp) as dt 
FROM generate_series(timestamp '2018-01-01', timestamp '2018-12-31', interval '1 hour') d), 
CTE_WeeklyHourlyCounts AS (SELECT dt, count(*) as ct 
FROM (SELECT f_woyhh((time)::timestamp at time zone 'utc' at time zone 'america/chicago') as dt, 
EXTRACT(YEAR FROM time) as ctYear, count(*) as ct 
FROM counties c 
INNER JOIN ltg_data d on ST_contains(c.the_geom, d.ltg_geom) 
WHERE countyname = 'Milwaukee' AND state = 'WI' AND EXTRACT(YEAR from time) > '1987' 
GROUP BY dt,
EXTRACT(YEAR from time)) as count group by dt), 
CTE_FullSTats AS (SELECT CTE_Dates.dt as dt, CAST(CTE_WeeklyHourlyCounts.ct as decimal) as ct 
FROM CTE_Dates LEFT JOIN CTE_WeeklyHourlyCounts ON CTE_WeeklyHourlyCounts.dt = CTE_Dates.dt 
GROUP BY CTE_Dates.dt, CTE_WeeklyHourlyCounts.ct, CTE_WeeklyHourlyCounts.dt) SELECT dt, COALESCE(ct, 0) 
AS count, round(((COALESCE(ct,0) * 100) / 30),0) as percent FROM CTE_FullStats 
GROUP BY dt, ct ORDER BY dt;