1min 36s

declare

start_time varchar:= time_extend->'start_time';

end_time varchar:= time_extend->'end_time';

max_zero_row integer;

max_zero_col integer;

begin

--構(gòu)建完整的行\(zhòng)列序列

max_zero_row := (select max(d.row_zero) from crimestat.datacluster(time_extend,space_extend,layerth) as d);

max_zero_col := (select max(d.col_zero) from crimestat.datacluster(time_extend,space_extend,layerth) as d);

create temp table row_series on commit drop as (select generate_series as row_series from generate_series(0,max_zero_row,1));

create temp table col_series on commit drop as (select generate_series as col_series from generate_series(0,max_zero_col,1));

--構(gòu)建完整的時(shí)間序列

create temp table time_series on commit drop as (

select to_char(generate_series,'YYYYMMDDHH24')::varchar as time_series from generate_series(to_timestamp(start_time,'YYYYMMDDHH24'), to_timestamp(end_time,'YYYYMMDDHH24'),'1 hour'));

create temp table full_series on commit drop as (

select time_series,row_series,col_series from row_series cross join col_series cross join time_series order by time_series,row_series,col_series);

return query select? time_series,row_series,col_series, COALESCE(d.counts, 0) from full_series f left outer join? crimestat.datacluster(time_extend,space_extend,layerth) d on f.time_series = d.p_time and f.row_series = d.row_zero and f.col_series = d.col_zero;

end;

1min47s

declare

start_time varchar:= time_extend->'start_time';

end_time varchar:= time_extend->'end_time';

max_zero_row integer;

max_zero_col integer;

begin

--構(gòu)建完整的行\(zhòng)列序列

max_zero_row := (select max(d.row_zero) from crimestat.datacluster(time_extend,space_extend,layerth) as d);

max_zero_col := (select max(d.col_zero) from crimestat.datacluster(time_extend,space_extend,layerth) as d);

--create temp table row_series on commit drop as (select generate_series as row_series from generate_series(0,max_zero_row,1));

--create temp table col_series on commit drop as (select generate_series as col_series from generate_series(0,max_zero_col,1));

--構(gòu)建完整的時(shí)間序列

--create temp table time_series on commit drop as

--(select to_char(generate_series,'YYYYMMDDHH24')::varchar as time_series from generate_series(to_timestamp(start_time,'YYYYMMDDHH24'), to_timestamp(end_time,'YYYYMMDDHH24'),'1 hour'));

create temp table full_series on commit drop as (

select time_series,row_series,col_series from (select generate_series as row_series from generate_series(0,max_zero_row,1)) row_series cross join? (select generate_series as col_series from generate_series(0,max_zero_col,1)) col_series cross join? (select to_char(generate_series,'YYYYMMDDHH24')::varchar as time_series from generate_series(to_timestamp(start_time,'YYYYMMDDHH24'), to_timestamp(end_time,'YYYYMMDDHH24'),'1 hour')) time_series order by time_series,row_series,col_series);

return query select? time_series,row_series,col_series, COALESCE(d.counts, 0) from full_series f left outer join? crimestat.datacluster(time_extend,space_extend,layerth) d on f.time_series = d.p_time and f.row_series = d.row_zero and f.col_series = d.col_zero;

end;