蝙蝠岛资源网

PostgreSQL完成按月累加的操作
数据库 2024/12/23 佚名
2 0
蝙蝠岛资源网 Design By www.hbtsch.com

背景

统计某个指标，指标按照月进行累加，注意需要按省份和年份进行分组。

PostgreSQL完成按月累加的操作

方法一、使用自关联

-- with 按月统计得到中间结果
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;

查询的结果如下：

PostgreSQL完成按月累加的操作

方法二、使用窗口函数

更多关于窗口函数的用法，可以参考以前的文章。窗口函数十分适合这样的场景：

 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 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;

PostgreSQL完成按月累加的操作

后记

可以使用子查询、可以使用窗口函数完成上面业务场景。

补充：PostgreSQL实现按秒按分按时按日按周按月按年统计数据

提取时间（年月日时分秒）：

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)

结果：

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

昨日每时：

select s.acceptDate, s.data_num
 from (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

PostgreSQL完成按月累加的操作

本月每天：

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

PostgreSQL完成按月累加的操作

本年每月：

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

PostgreSQL完成按月累加的操作

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_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

PostgreSQL完成按月累加的操作

里面时间参数进行传参即可。

补充：

统计今天（查询当天或者指定某天数量）

select count(1) FROM "shequjz_jibenxx" where to_char(zhongzhiriqi,'yyyy-mm-dd')='2019-11-11'

PostgreSQL完成按月累加的操作

最近七天每天的数量：

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天、一个月、一年、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

SELECT EXTRACT(DOW FROM CURRENT_DATE)

提取当前日期： 2020-11-16 00:00:00

SELECT 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_为一周中的第一天即星期一

PostgreSQL完成按月累加的操作

按周统计数据二：

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完成按月累加的操作

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')

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。

标签：
PostgreSQL,按月累加
蝙蝠岛资源网 Design By www.hbtsch.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
蝙蝠岛资源网 Design By www.hbtsch.com

评论“PostgreSQL完成按月累加的操作”

暂无PostgreSQL完成按月累加的操作的评论...
www.hbtsch.com 蝙蝠岛资源网
139,976影音资源
144,792福利资源
21,817软件资源
631,128技术资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
蝙蝠岛资源网 Design By www.hbtsch.com