PostgreSQL实现时间按月,日,小时分组查询

1.按小时分组查询

需求：根据小时统计，每小时发生的数据量
实现效果

解释：
1. 左边查询

SELECT
        afsj_ks , ajlb_mx
FROM
    XTBA_AJ
WHERE
    afdgxs LIKE '340503%'
AND '2017-05-01 00:00:00' <= afsj_ks
AND afsj_ks < '2017-05-31 00:00:00'
AND ajlb_mx = '扒窃'

改进

SELECT
        afsj_ks , ajlb_mx,date_part('hour',  afsj_ks) as myhour
    FROM
        XTBA_AJ
    WHERE
        afdgxs LIKE '340503%'
    AND '2017-05-01 00:00:00' <= afsj_ks
    AND afsj_ks < '2017-05-31 00:00:00'
    AND ajlb_mx = '扒窃'

继续改进

SELECT count(*) as mycount,myhour FROM
(
    SELECT
        afsj_ks , ajlb_mx,date_part('hour',  afsj_ks) as myhour
    FROM
        XTBA_AJ
    WHERE
        afdgxs LIKE '340503%'
    AND '2017-05-01 00:00:00' <= afsj_ks
    AND afsj_ks < '2017-05-31 00:00:00'
    AND ajlb_mx = '扒窃'
) as hour_table
GROUP BY  myhour
ORDER BY myhour ASC

最终实现

这里主要使用函数date_part('hour', afsj_ks)

函数	返回类型	描述	例子	结果
date_part(text, timestamp)	double	获取子域(等效于extract)	date_part(‘hour’, timestamp ‘2001-02-16 20:38:40’)	20

2.按年月分组查询

    select substring((fkdwdm || ''),1,6) as tjfield,bjxl_mx,bjsj
    from jjdb a, fkdb b
    where a.jjdbh=b.jjdbh
        and  bjxl_mx = '入室盗窃' 
        and '2015-04-01' < bjsj
        and bjsj < CURRENT_DATE

优化

    select substring((fkdwdm || ''),1,6) as tjfield,bjxl_mx,bjsj,date_trunc('month',bjsj) as year_month
    from jjdb a, fkdb b
    where a.jjdbh=b.jjdbh
        and  bjxl_mx = '入室盗窃' 
        and '2015-04-01' < bjsj
        and bjsj < CURRENT_DATE

这里其实已经可以group了，但是为了更好看，获取年月继续优化
优化

select substring((fkdwdm || ''),1,6) as tjfield,bjxl_mx,bjsj,to_char( date_trunc('month',bjsj), 'yyyy-mm')  as year_month
    from jjdb a, fkdb b
    where a.jjdbh=b.jjdbh
        and  bjxl_mx = '入室盗窃' 
        and '2015-04-01' < bjsj
        and bjsj < CURRENT_DATE

SELECT COUNT(*) as myconut,year_month
from (
    select substring((fkdwdm || ''),1,6) as tjfield,bjxl_mx,bjsj,to_char( date_trunc('month',bjsj), 'yyyy-mm')  as year_month
    from jjdb a, fkdb b
    where a.jjdbh=b.jjdbh
        and  bjxl_mx = '入室盗窃' 
        and '2015-04-01' < bjsj
        and bjsj < CURRENT_DATE
) as temp_table
GROUP BY year_month
ORDER BY year_month ASC

最后结果