背景
統(tǒng)計(jì)某個(gè)指標(biāo)，指標(biāo)按照月進(jìn)行累加，注意需要按省份和年份進(jìn)行分組。
方法一、使用自關(guān)聯(lián)
-- with 按月統(tǒng)計(jì)得到中間結(jié)果
WITH yms AS (SELECT regionid,SUM(getnum) AS getnum,SUM(dealnum) AS dealnum,to_char(qndate,'yyyy-MM') AS yearmonth
FROM t_queuenumber
GROUP BY regionid,to_char(qndate,'yyyy-MM')
ORDER BY regionid,yearmonth)-- 查用子查詢解決。
SELECT s1.regionid,s1.yearmonth, getnum,dealnum,
(SELECT SUM(getnum) FROM yms s2 WHERE s2.regionid = s1.regionid AND s2.yearmonth = s1.yearmonth AND SUBSTRING(s1.yearmonth,0,5) = SUBSTRING(s2.yearmonth,0,5) ) AS getaccumulatednum,
(SELECT SUM(dealnum) FROM yms s2 WHERE s2.regionid = s1.regionid AND s2.yearmonth = s1.yearmonth AND SUBSTRING(s1.yearmonth,0,5) = SUBSTRING(s2.yearmonth,0,5) ) AS accumulatednum
FROM yms s1;
查詢的結(jié)果如下：
方法二、使用窗口函數(shù)
更多關(guān)于窗口函數(shù)的用法，可以參考以前的文章。窗口函數(shù)十分適合這樣的場(chǎng)景：
WITH yms AS (SELECT regionid,SUM(getnum) AS getnum,SUM(dealnum) AS dealnum,to_char(qndate,'yyyy-MM') AS yearmonth
FROM t_queuenumber
GROUP BY regionid,to_char(qndate,'yyyy-MM')
ORDER BY regionid,yearmonth)
-- 窗口函數(shù)的使用
SELECT regionid,yearmonth,
SUM(getnum) OVER(PARTITION BY regionid,SUBSTRING(yearmonth,0,5) ORDER BY yearmonth) AS getaccumulatednum,
SUM(dealnum) OVER(PARTITION BY regionid ,SUBSTRING(yearmonth,0,5) ORDER BY yearmonth) AS dealaccumulatednum
FROM yms;
后記
可以使用子查詢、可以使用窗口函數(shù)完成上面業(yè)務(wù)場(chǎng)景。
補(bǔ)充：PostgreSQL實(shí)現(xiàn)按秒按分按時(shí)按日按周按月按年統(tǒng)計(jì)數(shù)據(jù)
提取時(shí)間（年月日時(shí)分秒）：
import datetime
from dateutil.relativedelta import relativedelta
today = str(datetime.datetime.now())
print(today)
print(today[:4], today[:7], today[:10],today[:13])
print("************分隔符***************")
yesterday = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime("%Y-%m-%d %H:%M:%S")
yesterday2 = (datetime.datetime.now() + datetime.timedelta(days=-2)).strftime("%Y-%m-%d %H:%M:%S")
nextmonths = str(datetime.date.today() - relativedelta(months=-1))[:7]
lastmonths = str(datetime.date.today() - relativedelta(months=+1))[:7]
lastyears = str(datetime.date.today() - relativedelta(years=+1))[:4]
nextyears = str(datetime.date.today() - relativedelta(years=-1))[:4]
print(yesterday)
print(yesterday2)
print(nextmonths)
print(lastmonths)
print(lastyears)
print(nextyears)
結(jié)果：
2020-03-05 13:49:59.982555
2020 2020-03 2020-03-05 2020-03-05 13
************分隔符***************
2020-03-04 13:49:59
2020-03-03 13:49:59
2020-04
2020-02
2019
2021
昨日每時(shí)：
select s.acceptDate, s.data_num
from (select to_char(acceptDate, 'yyyy-mm-dd hh24') || '點(diǎn)' as acceptDate,
count(1) as data_num
from table_name t
where t.acceptDate >= to_date('20190506', 'yyyymmdd')
and t.acceptDate to_date('20190507', 'yyyymmdd') and organization_ = 'abcdefghijklmnopqrstuvwxyz'
group by to_char(acceptDate, 'yyyy-mm-dd hh24') || '點(diǎn)') s
本月每天：
select s.acceptDate, s.data_num
from (select to_char(acceptDate, 'yyyy-mm-dd') as acceptDate,
count(1) as data_num
from table_name t
where t.acceptDate >= to_date('201905', 'yyyymm')
and t.acceptDate to_date('201906', 'yyyymm') and organization_ = 'abcdefghijklmnopqrstuvwxyz'
group by to_char(acceptDate, 'yyyy-mm-dd') ) s
本年每月：
select s.acceptDate, s.data_num
from (select to_char(acceptDate, 'yyyy-mm') as acceptDate,
count(1) as data_num
from table_name t
where t.acceptDate >= to_date('2019', 'yyyy')
and t.acceptDate to_date('2020', 'yyyy') and organization_ = 'abcdefghijklmnopqrstuvwxyz'
group by to_char(acceptDate, 'yyyy-mm') ) s
2月-7月中每月的人數(shù)統(tǒng)計(jì)：
sql = """SELECT to_char(rujiaoriqi, 'yyyy-mm') as month,count(1) num
FROM jibenxx where rujiaoriqi is not null and zhongzhiriqi is null
AND to_char(rujiaoriqi,'yyyy-mm-dd')>='2020-02-01'
GROUP BY to_char(rujiaoriqi, 'yyyy-mm') order by to_char(rujiaoriqi, 'yyyy-mm') """
統(tǒng)計(jì)每年：
select s.acceptDate, s.data_num
from (select to_char(acceptDate, 'yyyy') as acceptDate,
count(1) as data_num
from table_name t
where t.acceptDate >= to_date('2015', 'yyyy')
and t.acceptDate to_date('2021', 'yyyy') and organization_ = 'abcdefghijklmnopqrstuvwxyz'
group by to_char(acceptDate, 'yyyy') ) s
里面時(shí)間參數(shù)進(jìn)行傳參即可。
補(bǔ)充：
統(tǒng)計(jì)今天（查詢當(dāng)天或者指定某天數(shù)量）
select count(1) FROM "shequjz_jibenxx" where to_char(zhongzhiriqi,'yyyy-mm-dd')='2019-11-11'
最近七天每天的數(shù)量：
select s.acceptDate, s.data_num
from (select to_char(jiaozheng_jieshushijian, 'yyyy-mm-dd') as acceptDate,
count(1) as data_num
from shequjz_jibenxx t
where t.jiaozheng_jieshushijian >= to_date('2020-11-06', 'yyyy-mm-dd')
and t.jiaozheng_jieshushijian to_date('2020-11-13', 'yyyy-mm-dd')
group by to_char(jiaozheng_jieshushijian, 'yyyy-mm-dd') ) s ORDER BY acceptDate ASC
最近七天（1天、3天、7天、一個(gè)月、一年、1h、1min、60s）的數(shù)量（總量）：
# 包括今天向前推6天的總量
select count(1) from shequjz_jibenxx where jiaozheng_jieshushijian
between (SELECT current_timestamp - interval '7 day')
and current_timestamp
# 最近一天（昨天）
SELECT current_timestamp - interval '1 day'
# 最近三天
SELECT current_timestamp - interval '3 day'
# 最近一周
SELECT current_timestamp - interval '7 day'
# 最近一個(gè)月（當(dāng)前時(shí)間向前推進(jìn)一個(gè)月）
SELECT current_timestamp - interval '1 month'
# 最近一年（當(dāng)前時(shí)間向前推進(jìn)一年）
SELECT current_timestamp - interval '1 year'
# 最近一小時(shí)（當(dāng)前時(shí)間向前推一小時(shí)）
SELECT current_timestamp - interval '1 hour'
# 最近一分鐘（當(dāng)前時(shí)間向前推一分鐘）
SELECT current_timestamp - interval '1 min'
# 最近60秒（當(dāng)前時(shí)間向前推60秒）
SELECT current_timestamp - interval '60 second'
最近七天中每天的累計(jì)歷史總量：
步驟：
1）先統(tǒng)計(jì)出近7天每天的數(shù)量
2）后統(tǒng)計(jì)出7天前的累計(jì)歷史總量
3）再對(duì)第(1)步中獲取的結(jié)果進(jìn)行累計(jì)求和，使用cumsum()函數(shù)
4）最后在第(3)步結(jié)果的基礎(chǔ)上，加上7天前的累計(jì)歷史總量（也就是第2步的結(jié)果）
# 趨勢(shì)
def getWeekTrends(self):
try:
database = DataBase()
sql = """select s.zhongzhi_Date, s.data_num
from (select to_char(jiaozheng_jieshushijian, 'yyyy-mm-dd') as zhongzhi_Date,
count(1) as data_num
from shequjz_jibenxx t
where t.jiaozheng_jieshushijian >= to_date('{}', 'yyyy-mm-dd')
and t.jiaozheng_jieshushijian to_date('{}', 'yyyy-mm-dd')
group by to_char(jiaozheng_jieshushijian, 'yyyy-mm-dd') ) s""".format(lastweek, today[:10])
res_df = database.queryData(sql, flag=True)
sql_total = """select count(1) FROM "shequjz_jibenxx" where rujiaoriqi is not null
and zhongzhiriqi is null and to_char(rujiaoriqi,'yyyy-mm-dd')'{}'""".format(lastweek)
res_total = database.queryData(sql_total, count=1, flag=False) #7131
res_df['cumsum'] = res_df['data_num'].cumsum() # 累計(jì)求和
res_df['cumsum'] = res_df['cumsum'] + res_total[0]
res_df = res_df[['zhongzhi_date', 'cumsum']].to_dict(orient='records')
res = {'code': 1, 'message': '數(shù)據(jù)獲取成功', 'data': res_df}
print(res)
return res
except Exception as e:
error_info = '數(shù)據(jù)獲取錯(cuò)誤：{}'.format(e)
logger.error(error_info)
res = {'code': 0, 'message': error_info}
return res
{'code': 1, 'message': '數(shù)據(jù)獲取成功', 'data': [
{'zhongzhi_date': '2020-11-13', 'cumsum': 7148},
{'zhongzhi_date': '2020-11-10', 'cumsum': 7161},
{'zhongzhi_date': '2020-11-11', 'cumsum': 7195},
{'zhongzhi_date': '2020-11-12', 'cumsum': 7210},
{'zhongzhi_date': '2020-11-09', 'cumsum': 7222},
{'zhongzhi_date': '2020-11-14', 'cumsum': 7229},
{'zhongzhi_date': '2020-11-15', 'cumsum': 7238}]}
postgresql按周統(tǒng)計(jì)數(shù)據(jù)
（實(shí)際統(tǒng)計(jì)的是 上周日到周六 7天的數(shù)據(jù)）：
因?yàn)橥鈬说牧?xí)慣是一周從周日開始，二我們中國人的習(xí)慣一周的開始是星期一，這里 -1 即將顯示日期從周日變成了周一，但是內(nèi)部統(tǒng)計(jì)的數(shù)量還是從 上周日到周六進(jìn)行 統(tǒng)計(jì)的，改變的僅僅是顯示星期一的時(shí)間。
提取當(dāng)前星期幾： 1
SELECT EXTRACT(DOW FROM CURRENT_DATE)
提取當(dāng)前日期： 2020-11-16 00:00:00
SELECT CURRENT_DATE-(EXTRACT(DOW FROM CURRENT_DATE)-1||'day')::interval diffday;
按周統(tǒng)計(jì)數(shù)據(jù)一：
select to_char(jiaozheng_jieshushijian::DATE-(extract(dow from "jiaozheng_jieshushijian"::TIMESTAMP)-1||'day')::interval, 'YYYY-mm-dd') date_,
count(1) from shequjz_jibenxx where jiaozheng_jieshushijian BETWEEN '2020-01-01' and '2020-11-16'
GROUP BY date_ order by date_
其中date_為一周中的第一天即星期一
按周統(tǒng)計(jì)數(shù)據(jù)二：
SELECT
to_char ( cda.jiaozheng_jieshushijian, 'yyyy ' ) || EXTRACT ( WEEK FROM cda.jiaozheng_jieshushijian ) :: INTEGER AS date_,
count( cda.id ) AS count,
cda.jiaozheng_jieshushijian AS times
FROM
shequjz_jibenxx AS cda
WHERE
1 = 1
AND to_char ( cda.jiaozheng_jieshushijian, 'YYYY-MM-DD HH24:MI:SS' ) BETWEEN '2020-10-01 00:00:00' AND '2020-11-12 00:00:00'
GROUP BY
date_,
times
ORDER BY
date_,
times DESC
postgresql中比較日期的四種方法
select * from user_info where create_date >= '2020-11-01' and create_date = '2020-11-16'
select * from user_info where create_date between '2020-11-01' and '2020-11-16'
select * from user_info where create_date >= '2020-11-01'::timestamp and create_date '2020-11-16'::timestamp
select * from user_info where create_date between to_date('2020-11-01','YYYY-MM-DD') and to_date('2020-11-16','YYYY-MM-DD')
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- postgreSql分組統(tǒng)計(jì)數(shù)據(jù)的實(shí)現(xiàn)代碼
- Postgresql 存儲(chǔ)過程(plpgsql)兩層for循環(huán)的操作
- postgresql 循環(huán)函數(shù)的簡(jiǎn)單實(shí)現(xiàn)操作
- 淺談PostgreSQL和SQLServer的一些差異
- 解決PostgreSQL 執(zhí)行超時(shí)的情況
- postgresql 中的幾個(gè) timeout參數(shù) 用法說明
