PostgreSQL实现时间按月,日,小时分组查询
程序员文章站
2022-05-03 19:22:37
...
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
最后结果