Postgresql常见（花式）操作完全示例

最新推荐文章于 2024-03-13 13:51:22 发布
最新推荐文章于 2024-03-13 13:51:22 发布
阅读量1.1k 收藏 17
点赞数 20
分类专栏： 数据库 文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/asgard_hu/article/details/135551471
版权

案例说明

将Excel数据导入Postgresql，并实现常见统计（数据示例如下）

导入Excel数据到数据库

使用Navicat工具连接数据库，使用导入功能可直接导入，此处不做过多介绍，详细操作请看下图：

点击“下一步”完成导入操作（导入完成后，我们将表名命名为“eatLog”）。

 给数据表添加自增主键

导入的数据是没有主键的，这样不利于我们对数据的管理（如：在查询时，没有数据主键不能对数据进行修改等），因此我们需要扩展主键字段

添加主键字段

修改表设计，增加主键id字段（此时请勿添加主键约束）

创建自增序列

Postgresql没有像Oracle、MySQL那样的默认自增序列，因此要实现自增，可以通过自定义序列来实现

create SEQUENCE seq_eatlog_id
start with 1
increment by 1
no MINVALUE
no MAXVALUE
cache 1;

语句说明：

seq_eatlog_id：自定义的自增序列名称，根据自己需要命名

start with 1：序列从1开始

increment 1：序列自增步长为1（每次加1）

no MINVALUE：没有最小值约束

no MAXVALUE：没有最大值约束

cache 1：在数据库中始终缓存下一个序列

更新序列到数据表

update "eatLog"
set id = nextval('seq_eatlog_id')

nextval函数可获取下一个序列，可使用  select nextval('seq_eatlog_id') 来查询下一个序列。

注：调用一次nextval()，序列将被消费掉，因此不要轻易使用nextval()来查询序列，避免序列顺序混乱。

此时id已填充为自增的序列值（此时可以再修改表设计，给该表增加主键非空约束，顺手把date字段的数据类型修改为日期类型，数据会自动转换）

 

 给表主键增加自增序列

上面将主键已填充，但是在新增数据时，仍需手动添加主键，否则会提示主键为空问题。

insert into "eatLog" values('乔峰',to_date('2024-01-04','YYYY-MM-DD'),'吃拉面',null,'xiaofeng@tianlong.com');

题外话：

Postgresql的表名和字段都是区分大小写的，因此针对驼峰名称必须添加双引号进行操作，否则会提示表或字段不存在 

全大写或全小写的表名可以省略双引号

因此需要给主键id字段添加自增序列，以便后续新增数据。

alter table "eatLog" alter COLUMN id set DEFAULT nextval('seq_eatlog_id');

再次执行插入语句，即可添加成功（以后添加数据无需再管主键id字段了）。

常见日期操作

获取周

查询数据中的日期在当年第几周，并将周信息保存到数据库中，以便后续按周统计

表设计中增加“周（week）”字段

select date_part('week',date::timestamp) week from "eatLog";

将周信息更新到表中

update "eatLog"
set week = date_part('week',date::timestamp)

获取月

查询月份方式一（格式化字符方式）：

select to_char(date,'MM') from "eatLog";

查询月份方式二（日期函数获取）：

select date_part('month',date::timestamp) from "eatLog";

查询月份方式三（提取函数获取）：

select extract(month from date) as month from "eatLog";

查询部分时段数据

select * from "eatLog" 
where date >= to_date('2024-01-05','YYYY-MM-DD')
AND date <= to_date('2024-01-08','YYYY-MM-DD')
and phone is not null

数据脱敏

姓名脱敏

使用“*”号代替姓名中除第一个字和最后一个字的所有字符，两个字的名字仅替换最后一个字。

----三字及以上姓名脱敏
update "eatLog" 
set name = concat(
	left(name,1),
	repeat('*', length(name) - 2),
	right(name,1)
)
where length(name) > 2;

----两字姓名脱敏
update "eatLog" 
set name = concat(
	left(name,1),
	repeat('*', length(name) - 1)
)
where length(name) = 2;

concat()函数：用于拼接字符串

left()函数：用于截取字符串，指定从左截取多少位

right()函数：用于截取字符串，指定从右截取多少位

repeat()函数：用于替换字符串，指定替换多少位

手机号脱敏

保留手机号前三位和后四位，其他信息用“*”号代替

update "eatLog"
set phone = concat(
	left(phone,3),
	repeat('*',length(phone) - 7),
	right(phone,4)
)

注：身份证、银行卡脱敏思路相同 

 邮箱脱敏

update "eatLog"
set email = concat(
	left(email,1),
	repeat('*',position('@' in email) - 2),
	substring(email from position('@' in email))
)

substring()函数：截取字符串

position()函数：定位字符或字符串所在下标位置

 

数据统计

分组统计

根据周、饮食类型分组查询

select extract(week from t1.date) as week,t1.food,count(1)
from "eatLog" t1
group by extract(week from t1.date),t1.food
order by extract(week from t1.date)

行转列统计

统计所有数据

select * from crosstab(
	'select extract(week from t1.date) as week,t1.food,count(1)
from "eatLog" t1
group by extract(week from t1.date),t1.food
order by extract(week from t1.date),t1.food',
 'select food from "eatLog" 
 group by food 
 order by food'
)
as (
	week int,
	吃火锅 NUMERIC,
	吃拉面 NUMERIC,
	吃馒头 NUMERIC,
	吃米饭 NUMERIC,
	吃米线 NUMERIC,
	吃面条 NUMERIC
)
order by week

行转列使用crosstab(sql1,sql2)函数

参数说明：

        sql1：统计数据的语句

        sql2：行转列的列查询SQL

crosstab的sql1返回值中必须有且只有三个字段：

        第一个字段表示行ID（可由分组生成），

        第二个字段表示分组目录（即待转换列），

        第三个字段表示统计数据

as中的内容是转换的列名及列值类型，此处的列明必须完全列出，与实际数据相符，否则会报错误。

注一：

postgresql默认未安装扩展函数，因此要使用crosstab()函数，必须先启用扩展

使用命令：

CREATE EXTENSION IF NOT EXISTS tablefunc;

注二：

行转列时，sql2参数必须进行排序，若不排序，虽然能转成功，但是会发现数据可能已经混乱，postgresql在行转列时，通过as中指定顺序匹配，而非是通过字段名称匹配，所以orader by固定数据位置，很容易造成匹配错误（as中的顺序可以使用sql2执行之后确认是否一致）

 统计部分数据

select * from crosstab(
	'select extract(week from t1.date) as week,t1.food,count(1)
from "eatLog" t1
where t1.date >= to_date(''2024-01-05'',''YYYY-MM-DD'')
AND t1.date <= to_date(''2024-01-08'',''YYYY-MM-DD'')
group by extract(week from t1.date),t1.food
order by extract(week from t1.date),t1.food',
 'select food from "eatLog" 
 group by food 
 order by food'
)
as (
	week int,
	吃火锅 NUMERIC,
	吃拉面 NUMERIC,
	吃馒头 NUMERIC,
	吃米饭 NUMERIC,
	吃米线 NUMERIC,
	吃面条 NUMERIC
)
order by week

在crosstab的sql参数中，若已经使用了单引号(')，则需要使用两个单引号（''）表示一个单引号，用于转义，否则SQL执行报错

另外，SQL查询时，若表名或字段使用驼峰时，必须使用双引号修饰，否则会找不到对象（Postgresql严格区分大小写，全大写或全小写时可以省略双引号修饰）

自定义统计列

select * from crosstab(
	'select 
	extract(week from t1.date) as week,
	t1.food,
	count(1) food_count
from "eatLog" t1
where t1.date >= to_date(''2024-01-05'',''YYYY-MM-DD'')
AND t1.date <= to_date(''2024-01-08'',''YYYY-MM-DD'')
group by extract(week from t1.date),t1.food
order by extract(week from t1.date)',
 $$values('吃火锅'),('吃米饭'),('吃米线'),('吃面条')$$
)
as (
	week int,
	吃火锅 NUMERIC,
	吃米饭 NUMERIC,
	吃米线 NUMERIC,
	吃面条 NUMERIC
)
order by week

可通过$$values()$$来指定转哪些列，注意values()的顺序必须与as中的顺序一致

 

其他操作

计算精度问题

试想，我们的数据是统计每周的饮食统计，那每种饮食在每周占比是多少呢？

select m1.week,m1.food,m1.food_count, 
	(
		select 
			count(1) week_count
		from "eatLog" t2
		where t2.date >= to_date('2024-01-05','YYYY-MM-DD')
		AND t2.date <= to_date('2024-01-08','YYYY-MM-DD')
		and extract(week from t2.date) = m1.week
		group by extract(week from t2.date)
		order by extract(week from t2.date)
	) week_count
from
(
select 
	extract(week from t1.date) as week,
	t1.food,
	count(1) food_count
from "eatLog" t1
where t1.date >= to_date('2024-01-05','YYYY-MM-DD')
AND t1.date <= to_date('2024-01-08','YYYY-MM-DD')
group by extract(week from t1.date),t1.food
order by extract(week from t1.date)
) m1
order by m1.week,m1.food

计算占比时请注意精度问题

select m1.week,m1.food,round(m1.food_count::numeric / 
	(
		select 
			count(1) week_count
		from "eatLog" t2
		where t2.date >= to_date('2024-01-05','YYYY-MM-DD')
		AND t2.date <= to_date('2024-01-08','YYYY-MM-DD')
		and extract(week from t2.date) = m1.week
		group by extract(week from t2.date)
		order by extract(week from t2.date)
	)::numeric * 100,2) "rate(%)"
from
(
select 
	extract(week from t1.date) as week,
	t1.food,
	count(1) food_count
from "eatLog" t1
where t1.date >= to_date('2024-01-05','YYYY-MM-DD')
AND t1.date <= to_date('2024-01-08','YYYY-MM-DD')
group by extract(week from t1.date),t1.food
order by extract(week from t1.date)
) m1
order by m1.week,m1.food

Postgresql在计算时默认使用int来计算，因此不会取小数，若需要保留小数，需指明参加运算的字段类型，可通过“::numeric”来指明运算字段为数字型，这样运算结果可以保留小数

要具体精确到多少位，需要使用round()函数

行转列后效果

select * from crosstab(
	'select m1.week,m1.food,round(m1.food_count::numeric / 
	(
		select 
			count(1) week_count
		from "eatLog" t2
		where t2.date >= to_date(''2024-01-05'',''YYYY-MM-DD'')
		AND t2.date <= to_date(''2024-01-08'',''YYYY-MM-DD'')
		and extract(week from t2.date) = m1.week
		group by extract(week from t2.date)
		order by extract(week from t2.date)
	)::numeric * 100,2) "rate(%)"
from
(
select 
	extract(week from t1.date) as week,
	t1.food,
	count(1) food_count
from "eatLog" t1
where t1.date >= to_date(''2024-01-05'',''YYYY-MM-DD'')
AND t1.date <= to_date(''2024-01-08'',''YYYY-MM-DD'')
group by extract(week from t1.date),t1.food
order by extract(week from t1.date)
) m1
order by m1.week,m1.food',
 'select food from "eatLog" 
 group by food 
 order by food'
)
as (
	week int,
	吃火锅 NUMERIC,
	吃拉面 NUMERIC,
	吃馒头 NUMERIC,
	吃米饭 NUMERIC,
	吃米线 NUMERIC,
	吃面条 NUMERIC
)
order by week

以上，就是Postgresql在使用中常见操作及示例说明，希望对您有所帮助。 

0xfather
关注
  • 20
    点赞
  • 踩
  • 17
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
sql动态行转列的两种方法
12-15
第一种方法： 代码如下:select *from ( select Url,case  when  Month=01 then  ‘1月’ when  Month=02 then ‘2月’ when  Month=03 then  ‘3月’ when  Month=04 then ‘4月’ when  Month=05 then  ‘5月’ when  Month=06 then ‘6月’ when  Month=07 then  ‘7月’ when  Month=08 then ‘8月’ when  Month=09 then  ‘9月’ when  Month=10 then ‘ 10月’
Windows 10 安装配置连接PostgreSQL教程
热门推荐
极客星云-CSDN博客
12-29 1万+
Windows 10 安装配置连接PostgreSQL教程
PostgreSQL数据库安装教程
最新发布
qq_41941900的博客
03-13 2033
查看查看参数unix_socket_directories[postgres@localhost ~]$ cat /usr/local/pgsql/data/postgresql.conf | grep unix_socket_directories。修改/usr/local/pgsql/data/postgresql.conf文件中的unix_socket_directories参数，修改如下：unix_socket_directories='/usr/local/pgsql/data'
PgSQL个人学习笔记（一）——PgSQL介绍
qq_42349305的博客
05-15 1万+
1、什么是PgSQL？ PgSQL（全称PostgreSQL）是一个功能强大的开源对象关系型数据库系统，结合了许多安全存储和扩展最复杂数据工作负载的功能。它支持大部分SQL标准并且提供了许多其他现代特性： ...
pgsql简介
黑乎乎的小升
06-01 3626
1、pg和mysql的优劣势2、yum下载安装2.1、启动初始化3、源码编译安装4、pg_ctl管理工具4.1、创建新的数据库，多实例5、常用选项5.1、psql6、配置postgresql6.1、配置文件6.1.1、开启远程登录6.2pg_hba.conf文件6.2.1、文件格式6.2.2、远程连接免密登录6.3、数据的组织结构6.4、创建数据库6.5、删除数据库6.6、数据库文件路径6.7、创建schema模式分类7、管理表7.1、函数7.2、增删改查7.2.1、增加insert。
PostgreSQL安装详细步骤（linux）
diaguo7961的博客
06-09 6069
官方安装文档：http://www.postgresql.org/download/linux/redhat/ 1. 检查PostgreSQL 是否已经安装 Linux-软件包管理-rpm命令管理-查询 rpm -qa | grep postgres 检查PostgreSQL 是否已经安装rpm -qal | grep postgres 检查PostgreSQL 安...
C# 操作PostgreSQL 数据库的示例代码
08-28
本篇文章主要介绍了C# 操作PostgreSQL 数据库的示例代码，小编觉得挺不错的，现在分享给大家，也给大家做个参考。一起跟随小编过来看看吧
C#连接操作浏览PostgreSQL数据库示例源码.rar
12-30
Visual Studio 2019编译WinForm示例全部源码，适合初学者。
Python 操作 PostgreSQL 数据库示例【连接、增删改查等】
09-08
主要介绍了Python 操作 PostgreSQL 数据库的方法,结合实例形式分析了Python 连接PostgreSQL及增删改查等相关操作技巧,需要的朋友可以参考下
pgsql和mysql哪个更早_pgsql与mysql有哪些区别
weixin_42403124的博客
01-18 603
pgsql与mysql有哪些区别发布时间：2020-09-16 14:40:50来源：亿速云阅读：124作者：小新这篇文章给大家分享的是有关pgsql与mysql有哪些区别的内容。小编觉得挺实用的，因此分享给大家做个参考。一起跟随小编过来看看吧。pgsql与mysql的区别有：在pgsql上的功能实现比mysql严谨，对表的连接支持更加完整；pgsql的优化器功能完整，支持索引类型多，而mysql...
Pgsql常用函数及方法（持续更新）
weixin_40863562的博客
05-18 4344
PostgreSQL是一个功能强大的开源对象关系型数据库系统，他使用和扩展了SQL语言，并结合了许多安全存储和扩展最复杂数据工作负载的功能，是目前功能最强大的开源数据库。
​PGSQL操作手册​
Java后端技术栈
12-27 3315
【代码】​PGSQL操作手册​。
PGSQL(PostgreSQL)数据库基础篇：PostgreSQL 的 主要优点 、 劣势 、体系结构 、核心功能 、安装教程。
一码归一码@
12-09 7090
1.最新版本和历史版本不分离存储，导致清理老旧版本时需要做更多的扫描，代价比较大但一般的数据库都有高峰期，如果合理安排VACUUM，这也不是很大的问题，而且在PostgreSQL9.0中VACUUM进一步被加强了。2.在PostgreSQL中，由于索引完全没有版本信息，不能实现Coverage index scan，即查询只扫描索引，不能直接从索引中返回所需的属性，还需要访问表，而Oracle与Innodb则可以。因为存储过程的存在也避免了在网络上大量原始的SQL语句的传输，这样的优势是显而易见的。
PostgreSql简介
qq_45903207的博客
08-28 9743
PostgreSQL简介
PGSQL(学习与操作)
qq_41627642的博客
08-05 1万+
1、Navicat连接数据库 192.168.31.0 密码：123456 port:5432 PGSQL数据库学习 1、创建数据库 CREATE DATABASE runoobdb #创建数据库，数据库名为runoobdb 2、选择数据库 \l查看已经存在的数据库 连接所选择的数据库 3、删除数据库 4、创建表格 SELECT id,name FROM company; #从company表中选择一些属性的记录 SELECT * FROM company; #从company
MySQL和pgsql的区别
qq_57442639的博客
04-19 7273
1.问题 如果打算为项目选择一款免费、开源的数据库，那么你可能会在MySQL与PostgreSQL之间犹豫不定。MySQL与PostgreSQL都是免费、开源、强大、且功能丰富的数据库。你主要的问题可能是：哪一个才是最好的开源数据库，MySQL还是PostgreSQL呢？该选择哪一个开源数据库呢？ 在选择数据库时，你所做的是个长期的决策，因为后面如果再改变决定将是非常困难且代价高昂的。你希望一开始就选择正确。两个流行的开源数据库MySQL与PostgreSQL常常成为最后要选择的产品。对这两个开源数据库的
PostgreSQl 语言篇
陈戏猿
05-23 7766
最后WHERE字句还支持LIKE那么问题是为什么将模式匹配放在WHERE子中，而不是放到HAVING字句中？要回答这个问题首先要弄清楚聚合函数、WHERE字句、GROUP字句以及HAVING字句的执行顺序。WHERE字句GROUP字句聚合函数HAVING字句WHERE字句最先执行，选出符合条件的记录。其次是GROUP字句，这也意味着GROUP的分组对象可以是SELECT选出来的部分记录。聚合函数在GROUP BY函数之后执行，这也是为什么在WHERE字句和GROUP BY。
PostgreSQL 基础（一）-- 新手教程
chinusyan的专栏
05-29 1万+
PostgreSQL 基础（一）-- 教程
PG常用SQL
qq961573863的博客
08-12 4813
以上命令我们使用了超级用户 postgres 登录到主机地址为 localhost，端口号为 5432 的 PostgreSQL 数据库中并创建 lhrpgdb 数据库。接下来我们打开一个命令窗口，进入到 PostgreSQL 的安装目录，并进入到 bin 目录，createdb 命令位于。用户必须有表空间所在目录访问权限，所以在创建表空间之前需要在对应分区下创建相应的目录，并为其分配权限。模式(schema):我们在pg数据库中创建的任何对象(表,索引,视图等)都会在一个模式下被创建。
postgresql if else if else 示例
11-04
PostgreSQL是一种开源的关系型数据库管理系统，它支持SQL语言和许多其他高级功能，例如事务、外键和存储过程。在PostgreSQL中，可以使用IF ELSE IF ELSE语句来实现条件控制流程。下面是一个示例： ``` CREATE OR REPLACE FUNCTION test_case(i INT) RETURNS VOID AS $$ DECLARE j INT; BEGIN IF i < 0 THEN RAISE EXCEPTION 'i must be non-negative'; ELSIF i < 10 THEN j := i * 2; ELSE j := i / 2; END IF; RAISE NOTICE 'j = %', j; END; $$ LANGUAGE plpgsql; ``` 在这个示例中，如果输入的参数i小于0，则会抛出一个异常；如果i在0到10之间，则将j设置为i的两倍；否则将j设置为i的一半。最后，使用RAISE NOTICE语句输出j的值。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值