PostgreSQL完成按月累加的操作
项目招商找A5 快速获取精准代理名单
这篇文章主要介绍了PostgreSQL完成按月累加的操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧。
背景
统计某个指标，指标按照月进行累加，注意需要按省份和年份进行分组。
方法一、使用自关联
-- with 按月统计得到中间结果WITH yms AS (SELECT regionid,SUM(getnum) AS getnum,SUM(dealnum) AS dealnum,to_char(qndate,'yyyy-MM') AS yearmonthFROM t_queuenumberGROUP 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 accumulatednumFROM yms s1;
查询的结果如下：
方法二、使用窗口函数
更多关于窗口函数的用法，可以参考以前的文章。窗口函数十分适合这样的场景：
WITH yms AS (SELECT regionid,SUM(getnum) AS getnum,SUM(dealnum) AS dealnum,to_char(qndate,'yyyy-MM') AS yearmonthFROM t_queuenumberGROUP BY regionid,to_char(qndate,'yyyy-MM')ORDER BY regionid,yearmonth)-- 窗口函数的使用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 dealaccumulatednumFROM yms;
后记
可以使用子查询、可以使用窗口函数完成上面业务场景。
补充：PostgreSQL实现按秒按分按时按日按周按月按年统计数据
提取时间（年月日时分秒）：
import datetimefrom dateutil.relativedelta import relativedeltatoday = 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)
结果：
2020-03-05 13:49:59.9825552020 2020-03 2020-03-05 2020-03-05 13************分隔符***************2020-03-04 13:49:592020-03-03 13:49:592020-042020-0220192021
昨日每时：
select s.acceptDate, s.data_numfrom (select to_char(acceptDate, 'yyyy-mm-dd hh24') || '点' 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') || '点') s
本月每天：
select s.acceptDate, s.data_numfrom (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_numfrom (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月中每月的人数统计：
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') """
统计每年：
select s.acceptDate, s.data_numfrom (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
里面时间参数进行传参即可。
补充：
统计今天（查询当天或者指定某天数量）
1select count(1) FROM "shequjz_jibenxx" where to_char(zhongzhiriqi,'yyyy-mm-dd')='2019-11-11'
最近七天每天的数量：
select s.acceptDate, s.data_numfrom (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天、一个月、一年、1h、1min、60s）的数量（总量）：
# 包括今天向前推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'# 最近一个月（当前时间向前推进一个月）SELECT current_timestamp - interval '1 month'# 最近一年（当前时间向前推进一年）SELECT current_timestamp - interval '1 year'# 最近一小时（当前时间向前推一小时）SELECT current_timestamp - interval '1 hour'# 最近一分钟（当前时间向前推一分钟）SELECT current_timestamp - interval '1 min'# 最近60秒（当前时间向前推60秒）SELECT current_timestamp - interval '60 second'
最近七天中每天的累计历史总量：
步骤：
1）先统计出近7天每天的数量
2）后统计出7天前的累计历史总量
3）再对第(1)步中获取的结果进行累计求和，使用cumsum()函数
4）最后在第(3)步结果的基础上，加上7天前的累计历史总量（也就是第2步的结果）
# 趋势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() # 累计求和 res_df['cumsum'] = res_df['cumsum'] + res_total[0] res_df = res_df[['zhongzhi_date', 'cumsum']].to_dict(orient='records') res = {'code': 1, 'message': '数据获取成功', 'data': res_df} print(res) return res except Exception as e: error_info = '数据获取错误：{}'.format(e) logger.error(error_info) res = {'code': 0, 'message': error_info} return res{'code': 1, 'message': '数据获取成功', '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按周统计数据
（实际统计的是 上周日到周六 7天的数据）：
因为外国人的习惯是一周从周日开始，二我们中国人的习惯一周的开始是星期一，这里 -1 即将显示日期从周日变成了周一，但是内部统计的数量还是从 上周日到周六进行 统计的，改变的仅仅是显示星期一的时间。
提取当前星期几： 1
1SELECT EXTRACT(DOW FROM CURRENT_DATE)
提取当前日期： 2020-11-16 00:00:00
1SELECT CURRENT_DATE-(EXTRACT(DOW FROM CURRENT_DATE)-1||'day')::interval diffday;
按周统计数据一：
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_为一周中的第一天即星期一
按周统计数据二：
SELECTto_char ( cda.jiaozheng_jieshushijian, 'yyyy ' ) || EXTRACT ( WEEK FROM cda.jiaozheng_jieshushijian ) :: INTEGER AS date_,count( cda.id ) AS count,cda.jiaozheng_jieshushijian AS times FROMshequjz_jibenxx AS cdaWHERE1 = 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 BYdate_,times ORDER BYdate_,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')
文章来源：脚本之家
来源地址：https://www.jb51.net/article/204263.htm
尊敬的看官您对PostgreSQL完成按月累加的操作有什么看法呢？互联网品牌制作专家愿与您共同探讨！版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容， 请加微信号oem365 举报，一经查实，本站将立刻删除。上一篇: PostgreSQL实现给查询列表增加序号操作 返 回 下一篇:基于postgresql查询某个字段属于哪个表的说明
