帝王谷资源网

PostgreSQL完成按月累加的操作
数据库 2024/12/29 佚名
3 2
帝王谷资源网 Design By www.wdxyy.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.wdxyy.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
帝王谷资源网 Design By www.wdxyy.com
www.wdxyy.com 帝王谷资源网
139,976互联网资源
144,792高清电影
21,817无损音乐
631,128技术资源

《魔兽世界》大逃杀！60人新游玩模式《强袭风暴》3月21日上线

暴雪近日发布了《魔兽世界》10.2.6 更新内容，新游玩模式《强袭风暴》即将于3月21 日在亚服上线，届时玩家将前往阿拉希高地展开一场 60 人大逃杀对战。

艾泽拉斯的冒险者已经征服了艾泽拉斯的大地及遥远的彼岸。他们在对抗世界上最致命的敌人时展现出过人的手腕，并且成功阻止终结宇宙等级的威胁。当他们在为即将于《魔兽世界》资料片《地心之战》中来袭的萨拉塔斯势力做战斗准备时，他们还需要在熟悉的阿拉希高地面对一个全新的敌人──那就是彼此。在《巨龙崛起》10.2.6 更新的《强袭风暴》中，玩家将会进入一个全新的海盗主题大逃杀式限时活动，其中包含极高的风险和史诗级的奖励。

《强袭风暴》不是普通的战场，作为一个独立于主游戏之外的活动，玩家可以用大逃杀的风格来体验《魔兽世界》，不分职业、不分装备（除了你在赛局中捡到的），光是技巧和战略的强弱之分就能决定出谁才是能坚持到最后的赢家。本次活动将会开放单人和双人模式，玩家在加入海盗主题的预赛大厅区域前，可以从强袭风暴角色画面新增好友。游玩游戏将可以累计名望轨迹，《巨龙崛起》和《魔兽世界：巫妖王之怒 经典版》的玩家都可以获得奖励。

更新日志

友情链接

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