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

案例说明

将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在使用中常见操作及示例说明，希望对您有所帮助。 

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/315485.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

vivado Revision Control

vivado Revision Control

2020.2 只需要git 管理 prj.xpr 和 prj.srcs/ https://china.xilinx.com/video/hardware/ip-revision-control.html Using Vivado Design Suite with Revision Control https://www.xilinx.com/video/hardware/vivado-design-suite-revision-control.html http://www.xi…
阅读更多...
UCB Data100：数据科学的原理和技巧：第十一章到第十二章

UCB Data100：数据科学的原理和技巧：第十一章到第十二章

十一、恒定模型、损失和转换 原文&#xff1a;Constant Model, Loss, and Transformations 译者&#xff1a;飞龙 协议&#xff1a;CC BY-NC-SA 4.0 学习成果 推导出在 MSE 和 MAE 成本函数下恒定模型的最佳模型参数。 评估 MSE 和 MAE 风险之间的差异。 理解变量线性化的必要…
阅读更多...
大创项目推荐 深度学习猫狗分类 - python opencv cnn

大创项目推荐 深度学习猫狗分类 - python opencv cnn

文章目录 0 前言1 课题背景2 使用CNN进行猫狗分类3 数据集处理4 神经网络的编写5 Tensorflow计算图的构建6 模型的训练和测试7 预测效果8 最后 0 前言 &#x1f525; 优质竞赛项目系列&#xff0c;今天要分享的是 &#x1f6a9; **基于深度学习猫狗分类 ** 该项目较为新颖&a…
阅读更多...
如何配置Kafka账号密码

如何配置Kafka账号密码

背景 我们需要与第三方系统进行数据同步&#xff0c;需要搭建公网Kafka&#xff0c;Kafka默认是没有用户密码校验的&#xff0c;所以我们需要配置用户名密码校验。 配置 新增JAAS配置文件 在conf目录下新增kafka_server_jaas.conf文件&#xff0c;文件内容如下&#xff1a;…
阅读更多...
前端规范扩展

前端规范扩展

前端编程规范是基于原有vue2基础上那套《编码风格及标准》上&#xff0c;应用于vue3、typescript、vite2基础上延伸出来的扩展补充&#xff0c;持续完善 一、编码规范 ESLint 代码检测工具 Pretter 代码格式化工具配合双校验代码 Git 规范 - 编码工具 vscode 同步参考文档中…
阅读更多...
MySQL夯实之路-查询性能优化深入浅出

MySQL夯实之路-查询性能优化深入浅出

MySQL调优分析 explain&#xff1b;show status查看服务器状态信息 优化 减少子任务&#xff0c;减少子任务执行次数&#xff0c;减少子任务执行时间&#xff08;优&#xff0c;少&#xff0c;快&#xff09; 查询优化分析方法 1&#xff0e;访问了太多的行和列&#xff1…
阅读更多...
c语言三目运算符（条件运算符）

c语言三目运算符（条件运算符）

c语言三目运算符 c语言三木运算符 c语言三目运算符一、c语言三目运算符&#xff08;条件运算符&#xff09;格式二、c语言三目运算符&#xff08;条件运算符&#xff09;嵌套三目运算符 一、c语言三目运算符&#xff08;条件运算符&#xff09;格式 三目运算符格式&#xff1a;…
阅读更多...
NAND新一代接口Separate Command Address (SCA) 简介

NAND新一代接口Separate Command Address (SCA) 简介

通过NAND Flash总线传输的信号分为三种类型&#xff1a;命令&#xff08;Commands&#xff09;、地址&#xff08;Addresses&#xff09;和数据&#xff08;Data&#xff09;。这些信号利用DQ[7:0]时间分时复用技术&#xff0c;在不同的时间段分别进行传输。其中&#xff0c;数…
阅读更多...
使用Pygame库创建了一个窗口，并在窗口中加载了一个名为“ball.png“的图片，通过不断改变物体的位置，实现了一个简单的动画效果

使用Pygame库创建了一个窗口，并在窗口中加载了一个名为“ball.png“的图片，通过不断改变物体的位置，实现了一个简单的动画效果

import pygame import sys# 初始化Pygame pygame.init()# 创建窗口 screen pygame.display.set_mode((640, 480))# 加载图片 image pygame.image.load("ball.png")# 将物体初始位置设为屏幕左上角 x 0 y 0# 游戏循环 while True:# 处理事件for event in pygame.e…
阅读更多...
四川古力未来科技有限公司：抖音小店的崛起之路

四川古力未来科技有限公司：抖音小店的崛起之路

随着互联网的飞速发展&#xff0c;电子商务已经成为人们日常生活中不可或缺的一部分。作为一家以科技为核心的公司&#xff0c;四川古力未来科技有限公司在电子商务领域中崭露头角&#xff0c;特别是其抖音小店的发展引人注目。 四川古力未来科技有限公司的抖音小店自开业以来&…
阅读更多...
redis（14）：缓存雪崩、击穿、穿透及其处理方式

redis（14）：缓存雪崩、击穿、穿透及其处理方式

1 Redis 缓存过程 通常后端会采用Mysql等磁盘数据库，可以持久化但是访问慢，高并发时性能差，需要设置Nosql内存型数据库缓存：Redis等； Redis 数据库运行在内存中，因此他的查询速度比 MySql 快的多。所以我们会把一些用户经常查询的数据放在 Redis 中，当 Redis 有的时候…
阅读更多...
Move_Certificates-v1.9安装-Magisk movecert模块安装时出现‘unzip error‘的解决办法

Move_Certificates-v1.9安装-Magisk movecert模块安装时出现‘unzip error‘的解决办法

下载链接 安装成功
阅读更多...
word写标书的疑难杂症总结

word写标书的疑难杂症总结

最近在解决方案工作&#xff0c;与office工具经常打交道&#xff0c;各种问题&#xff0c;在此最下记录&#xff1a; 1.word中文档距离文档顶端有距离调整不了 1.疑难杂症问题1&#xff0c;多个空格都是不能解决 #解决办法&#xff1a;word中--布局-下拉框---“版式”--“垂直…
阅读更多...
ArrayList源码阅读

ArrayList源码阅读

文章目录 简介例子继承结构概览代码分析成员变量方法迭代器子列表 总结参考链接 本人的源码阅读主要聚焦于类的使用场景&#xff0c;一般只在java层面进行分析&#xff0c;没有深入到一些native方法的实现。并且由于知识储备不完整&#xff0c;很可能出现疏漏甚至是谬误&#x…
阅读更多...
[机缘参悟-125] ：实修 - “心性、自性”与“知识、技能”的区别，学习、修、悟的区别？

[机缘参悟-125] ：实修 - “心性、自性”与“知识、技能”的区别，学习、修、悟的区别？

目录 一、“知识、技能” 1.1 什么是知识技能 1.2 知识、技能的位置 1.3 知识、技能的学习方法 二、"明心见性" 2.1 什么是"明心见性" 2.2 "明心见性"解读 2.2.1 何其自性&#xff0c;本自清净&#xff1b; 2.2.2 何其自性&#xff0c;…
阅读更多...
将.NET应用转换成Window服务

将.NET应用转换成Window服务

写在前面 本文介绍了将.NET8.0应用程序转换成Windows服务。 需要在NuGet中获取并安装&#xff1a;Microsoft.Extensions.Hosting.WindowsServices 包 代码实现 using System.Runtime.InteropServices; using WorkerService1;public class Program {public static void Main…
阅读更多...
【机器学习300问】6、什么是机器学习中的特征量？

【机器学习300问】6、什么是机器学习中的特征量？

一、首先我们看三个例子 例一&#xff1a;在辨别水果的任务中&#xff0c;人类一般会通过外观、味道、颜色等方面信息来进行区分。而机器学习则通过水果的颜色、重量、气味成分的量等被称之为“特征量”的数值来区分。 例二&#xff1a;在手写数字识别任务中&#xff0c;人类…
阅读更多...
【Golang】二进制字符串转换为数字

【Golang】二进制字符串转换为数字

在本文中&#xff0c;我们将探讨如何使用 Go 语言将十六进制字符串转换为二进制字符串&#xff0c;将不定长整型补码字符串转换为数字&#xff0c;以及如何将 IEEE754 标准的单精度&#xff08;32位&#xff09;和双精度&#xff08;64位&#xff09;浮点数字符串转换为数字。最…
阅读更多...
LaTeX中的框以及框中的子图

LaTeX中的框以及框中的子图

目录 文章目录 目录框&#xff08;盒子&#xff09;\fboxframed包framed环境leftbar环境 mdframed包fcolorbox命令tcolorbox包adjustbox包调整盒子的宽度和高度旋转盒子 框中的子图问题一&#xff1a;框中插入图片问题二&#xff1a;给框中图片加上图名、编号caption包 问题三&…
阅读更多...
动态规划学习笔记

动态规划学习笔记

背景 一般形式是求最值&#xff0c;核心是穷举。 首先&#xff0c;虽然动态规划的核心思想就是穷举求最值&#xff0c;但是问题可以千变万化&#xff0c;穷举所有可行解其实并不是一件容易的事&#xff0c;需要你熟练掌握递归思维&#xff0c;只有列出正确的「状态转移方程」…
阅读更多...
最新文章

Copyright @ 2022~2023