当前位置: 首页 > article >正文

PGSQL基本使用

PGSQL基本使用

文章目录

  • PGSQL基本使用
  • 日期转换
    • 长度不够补数
    • 获取上下行
    • 取连续的开始和结束的值


日期转换

格式说明
YYYY
MM
DD
hh2424小时制
mi分钟
ss
-- 日期字符串转指定日期字符串
-- 20250101123000 转为 2025-01-01 12:30:00
select to_char(to_timestamp('20250101123000','YYYYMMDDhh24miss'),'YYYY-MM-DD hh24:mi:ss') as t;

长度不够补数

不足前面补数

-- 得到字符串 '09300000'
select lpad(cast(9300000 as text),8,'0') as t;

不足前面补数

-- 得到字符串 '15300000'
select lpad(cast(153000 as text),8,'0') as t;

获取上下行

获取上一行

-- 第一行 pre_name 为 null
 select *,
 	lag(name) over ( order by rn) as pre_name
 from table

-- 第一行 pre_name 为 ''
--说明: 1,第一行, '' 为第一行的值
 select *,
 	lag(name,1,'') over ( order by rn) as pre_name
 from table

获取下一行

-- 最后一行 pre_name 为 null
 select *,
 	lead(name) over ( order by rn) as pre_name
 from table

-- 第一行 pre_name 为 ''
--说明: 1,最后一行, '' 为最后一行的值
 select *,
 	lead(name,1,'') over ( order by rn) as pre_name
 from table

取连续的开始和结束的值

同一个账户,订单编号需要去除,5,9 。计算订单编号连续

-- 基础数据
with base as (
 select  1 as order_num, 'A01' as acct_id
 union all 
 select  2 as order_num, 'A01' as acct_id
 union all 
 select  3 as order_num, 'A02' as acct_id
 union all 
 select  4 as order_num, 'A02' as acct_id
 union all 
 select  5 as order_num, 'A02' as acct_id
 union all 
 select  6 as order_num, 'A02' as acct_id
 union all 
 select  7 as order_num, 'A01' as acct_id
 union all 
 select  8 as order_num, 'A01' as acct_id
 union all 
 select  9 as order_num, 'A01' as acct_id
 union all 
 select  10 as order_num, 'A01' as acct_id
 union all 
 select  11 as order_num, 'A01' as acct_id
 ),
 -- 根据acct_id分组,根据order_num排序
base_order as (
 select *, 
 row_number() over (partition by acct_id order by order_num) as rn 
 from base
),
-- 获取上下行的rn
-- lag(rn,1,null) 表示第一行的 rn值为空
-- lead(rn,1,null) 表示最后一行的 rn值为空
base_rn as (
select *,
  lag(rn,1,null) over (partition by acct_id order by order_num) as pre_rn,
  lead(rn,1,null) over (partition by acct_id order by order_num) as next_rn
from base_order
where order_num != 5 and order_num != 9
),
-- 计算acct_id连续的 order_num
base_link as (
select *,
  case when pre_rn is null or pre_rn + 1 != rn then  order_num else null end as start_order_num, /**连续的开始*/
  case when next_rn is null or next_rn -1 != rn then order_num else null end as end_order_num, /**连续的结束*/
  sum(case when pre_rn is null or pre_rn + 1 != rn then 1 else 0 end) over (partition by acct_id order by order_num) as link_cnt /**连续的分为一组*/
from base_rn
)
-- 获取连续的开始订单编号和对应的结束订单编号
select max(start_order_num) as start_order_num,
 max(end_order_num) as end_order_num, acct_id
from base_link
group by acct_id, link_cnt

得到结果

start_order_numend_order_numacct_id
12A01
34A02
66A02
78A01
1011A01

http://www.kler.cn/a/585574.html

相关文章:

  • SQLite?低调不是小众...
  • 红色警戒2:共和国之辉红警语音台词是什么?
  • 自适应二值化及伪影
  • RabbitMQ消息持久化与Lazy模式对比分析
  • 每日一题——逆波兰表达式
  • DeepSeek API 客户端使用文档
  • Spring Boot对接twilio发送邮件信息
  • Docker 部署Spring boot + Vue(若依为例)
  • Linux系统下安装Gedit文本编辑器的完整指南
  • C++能力测试题
  • 深入 Python 网络爬虫开发:从入门到实战
  • 完善 Django 框架以实现传递视频、图片给算法模块进行识别分析
  • 防止手机验证码被刷:React + TypeScript 与 Node.js + Express 的全面防御策略
  • WebLogic XMLDecoder反序列化漏洞(CVE-2017-10271)深度解析与实战复现
  • JVM 的不同组成部分分别有什么作用?
  • URL 中的参数通常用于跟踪和传递信息,特别是在在线广告和营销活动中。
  • 鸿蒙初学者学习手册(HarmonyOSNext_API14)_UIContext(@ohos.arkui.UIContext (UIContext))
  • tensorflow与torch并行读取数据机制
  • 浅谈StarRocks数据库简介及应用
  • 阿里巴巴发布 R1-Omni:首个基于 RLVR 的全模态大语言模型,用于情感识别