postgresql数据库结合generate_series函数对数据按一天、七天、月份分组

直接贴上sql了：

select
            aa.theDate
            <choose>
                <when test="param.type eq 'day'">
                    ,aa.theDate as startTime
                    ,aa.theDate as endTime
                </when>
                <when test="param.type eq 'week'">
                    ,aa.theDate as startTime
                    ,to_char((aa.theDate::date + interval '6 Days'),'yyyy-MM-DD') as endTime
                </when>
                <otherwise>
                    ,to_date(aa.theDate,'YYYY-MM-DD') as startTime
                    ,to_char(date_trunc('month',to_date(aa.theDate,'YYYY-MM-DD')) + interval'1 month - 1 day','YYYY-MM-DD') as endTime
                </otherwise>
            </choose>
            ,count(case when ww.rank in ('1','2','3') then 1 end) as rankAll
            ,count(case when ww.rank = '1' then 1 end) as rankOne
            ,count(case when ww.rank = '2' then 1 end) as rankTwo
            ,count(case when ww.rank = '3' then 1 end) as rankThree
            ,#{param.companyCode} as companyCode
        <choose>
            <when test="param.type eq 'day'">
                from (select to_char(a,'yyyy-MM-dd') as theDate,row_number() over (order by a asc) rowNum
                     from generate_series(#{param.startTime}::date, #{param.endTime}::date, '1 days') a) aa
                left join (select to_char(w.create_time, 'YYYY-MM-DD') createDate,w.* from tb_risk w
                          where w.type = 'warn' and w.rank in ('1','2','3') and w.company_code = #{param.companyCode} and w.create_time between #{param.startTime} and #{param.endTime}) ww
                    on ww.createDate = aa.theDate
            </when>
            <when test="param.type eq 'week'">
                from (select to_char(a,'yyyy-MM-DD') as theDate,row_number() over (order by a asc) rowNum
                     from generate_series(#{param.startTime}::date, #{param.endTime}::date, '7 days') a) aa
                left join (select * from tb_risk w
                          where w.type = 'warn' and w.rank in ('1','2','3') and w.company_code = #{param.companyCode} and w.create_time between #{param.startTime} and #{param.endTime}) ww
                    on to_char(ww.create_time,'yyyy-MM-DD') &gt;= aa.theDate and to_char(ww.create_time,'yyyy-MM-DD') &lt;= to_char((aa.theDate::date + interval '6 Days'),'yyyy-MM-DD')
            </when>
            <otherwise>
                from (select to_char(a,'YYYY-MM') as theDate,row_number() over (order by a asc) rowNum
                     from generate_series(#{param.startTime}::date, #{param.endTime}::date, '1 mon') a) aa
                left join (select to_char(w.create_time, 'YYYY-MM') createDate,w.* from tb_risk w
                          where w.type = 'warn' and w.rank in ('1','2','3') and w.company_code = #{param.companyCode} and w.create_time between #{param.startTime} and #{param.endTime}) ww
                    on ww.createDate = aa.theDate
            </otherwise>
        </choose>
        group by theDate
        order by theDate;

注：generate_series函数会根据传入的起止时间得到对应的时间段，关联业务表，并按时间段分组即可统计。