⑪：判断某张表中某个字段是否存在 （2023-03-13更新）
前言
最近也是在做项目，没有什么时间更新。今天抽空来更新一篇在项目中使用的postgresql一些相关的知识。
了解postgresql
需求：千百万数据存储在Greenplum中，需要使用Python来对gp进行读写，其中包含各种字段处理及拼接，都是使用gp函数解决的。
Python连接gp
这里使用了Python的一个包Psycopg2 控制台直接下载： pip install psycopg2
import psycopg2#连接数据库读取数据
def gp_concat(sql):
conn = psycopg2.connect(dbname="初始化库名",
user="用户",
password="密码",
host="主机地址",
port="端口号")
cur = conn.cursor()
try:
cur.execute(sql) #执行传过来的sql
res = cur.fetchall()
col = [item[0] for item in cur.description]
return res,col #返回data和columns
except Exception as ex:
print(ex)
finally:
conn.close()调用连接函数并执行sql,返回数据后进行dataframe操作，以便后期对数据处理
sql = '''
select *
from xxx
'''
res,col = gp_concat(sql)
all_ip = pd.DataFrame(res,columns=col)
print(all_ip)==========================================我是分割线===========================================
记录postgresql一些函数和方法（持续更新）
①：int类型时间转time类型时间
有这样一个场景，数据中有一列时间字段，但是值是整形像：01012、161555、51233等等。不用怀疑这确实是时间！所以我们需要把整形的这些值转换成time类型。
涉及函数或方法：lpad() rpad()
示例如下：
-- 函数类型
-- LPAD(str,len,padstr)
-- 解释
-- lapd(t1,t2,t3) 当t1不满t2位数时，左补0，让其达到t2位长度为止
-- rapd(t1,t2,t3) 当t1不满t2位数时，右补0，让其达到t2位长度为止
select lpad('01012', 6, '0')::time --00:10:12
select rpad('01012', 6, '0')::time --01:01:20②：合并两列值
有这样一个场景，数据中有两列值，分别是日期列（2021-05-21）和时间列（17：05：44）。需求是把这两列值一 一拼接。组成一个新列（2021-05-21 17：05：44）。
涉及函数或方法：||
示例如下：
-- 函数类型
-- (type text || type text)
-- 解释
-- ( left || right ) 将left和right值拼接
-- 注意日期和时间中有空格
select (2021-05-21 || ' ' || rpad('170544', 6, '0')::time) --2021-05-21 17:05:44③：取最近N个月数据
有这样一个场景，经过上两步的处理，我们现在已经得到了完整日期时间。需求是找到近1个月、2个月、3个月、半年、一年的数据。
涉及函数或方法：now() - interval 'N个月 month'
示例如下：
-- 解释
-- now() - interval 'N month' now()获取当前日期时间 - N个月，得到N个月之前的日期时间
select now() - interval '3 month' -- now() = 2021-05-21 17:05:44 、 interval '3 month' = 0000-03-00 00:00:00
-- 可以理解为 2021-05-21 17:05:44 - 0000-03-00 00:00:00
-- 之后我们可以拿已有的日期时间来判断是否大于等于N个月之前的日期时间，得到想要的数据。
select *
from date_time >= (now() - interval '3 month')④：按指定字符串或者符号切割
有这样一个场景，数据中有一个字段存储 120.110.119.000##1a-2b-5c-1d 这些值，这是包含了ip，mac信息，不过是用两个#连接起来的。需求是单独把ip（120.110.119.000）取出来。
涉及函数或方法：split_part()
示例如下：
-- 函数接收类型
-- split_part(string text, delimiter text2, field int)
-- 解释
-- split_part(t1, t2, t3) 使用分隔符t2，来切割t1，最终取t3的值 (会以t2为分隔符一分为二)
select split_part('120.110.119.000##1a-2b-5c-1d', '##', 1) -- 120.110.119.000
select split_part('120.110.119.000##1a-2b-5c-1d', '##', 2) -- 1a-2b-5c-1d⑤：字符串切割
有这样一个场景，需要将某列值切割出来几位数。如：将2021-05-21 17:05:44中的日期或时间切出来，也当然可以使用split_part()以空格切割取第一个。不过里介绍另一个函数，所以请忽略掉上个函数！
涉及函数或方法：substring()
示例如下：
-- 函数接收类型
-- substring(text , int , int)
-- 解释
-- SUBSTRING(t1 , t2 , t3) 将t1切割，从t2的位置开始切t3单位长度 (空格也算一个单位长度)
select SUBSTRING('2021-05-21 17:21:44',1,10) -- 2021-05-21
select SUBSTRING('2021-05-21 17:21:44',12,8) -- 17:21:44⑥：寻找近N个月的数据
有这样一个场景，现有一张大表，里面有某年的全量数据。现需求是找到最近N个月的数据，拿出来进行分析计算。
涉及函数或方法：now() - interval N month'
示例如下：
-- 函数接收类型 N : int,float
-- select now() - interval 'N month'
-- 解释
-- select now() - interval 'N month' 从当前时间now() - 近N个月的区间范围
select now() - interval '1 month' --now : 2021-07-26 result ：2021-06-26
select now() - interval '1.2 month' --now : 2021-07-26 result ：2021-06-20⑦：找出表中含有小数的记录
有这样一个场景，现有一个trade_money字段，代表为交易金额。现需求是筛选出交易金额带小数的记录。虽然sql没有直接查找小数的方法，但是我们可以利用一些函数的返回值来逆向筛选判断，从而达到目的。
涉及函数或方法：split_part()
示例如下：
-- 函数接收类型
-- split_part(string text, delimiter text2, field int)
-- 解释
-- split_part(t1, t2, t3) 使用分隔符t2，来切割t1，最终取t3的值 (会以t2为分隔符一分为二)
-- 示例：
select split_part("100.52"::text ,'.',2)::int --result : 52
select split_part("100"::text ,'.',2)::int --result : 0
-- 我们不难发现，如果交易金额为小数的话，我们利用split_part函数使用小数点进行分割，最后取出第二位（也就是小数部分），接着我们可以判断 > 0。这样我们就可以筛选出小数的记录了。
-- 完整sql如下：
select trade_moeny
from test_table
where split_part("trade_money"::text ,'.',2)::int > 0⑧：判断某字段是否在一张表中存在
有这样一个场景，需要对一张表中的一个字段进行聚合计算。众所周知如果这个字段不存在，计算是会报错，为了防止这一错误发生我们首先需要进行判断这个字段是否存在。
涉及函数或方法：if()
示例如下：
-- 使用方法
-- SELECT COUNT(*) cnt
-- FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '表名'
-- AND COLUMN_NAME = '字段名' --判断字段数量是不足为0
-- 解释
-- 以上sql填入表名、字段名，返回结果cnt 。如果此字段存在此表，则返回1，否则返回0。
-- 示例1：
SELECT COUNT(*) cnt
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'test_table' AND COLUMN_NAME = 'trade_moeny' --ct1 : 1
-- 示例2：
SELECT COUNT(*) cnt
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'test_table' AND COLUMN_NAME = 'sadffffffff' --ct1 : 0
-- 得到cnt后，我们可以利用if来判断，返回0时我们不进行聚合，返回1我们进行聚合。
-- 完整sql如下
if(
SELECT COUNT(*) AS cnt FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '表名'
AND COLUMN_NAME = '字段名'
) > 0
-- 第二种方式（用于报错：（psycopg2.internalerror） : sequence scan on catalog is disabled）
select count(1) as cnt
from(
select attname
from pg_catalog.pg_attribute
where attstattarget = -1 and attrelid in (select oid from pg_catalog.pg_class where relname = 'tableName')
) t
where attname = 'tableColumn'
⑨：判断表是否存在
有这样一个场景，现有一批数据，需要插入到一张表中。需求是插入的时候判断表是否存在，不存在则不予插入。
涉及函数或方法：if()
示例如下：
-- 使用方法
-- SELECT COUNT(1) cnt
-- FROM pg_class c LEFT JOIN pg_namespace n ON
-- (n.oid = c.relnamespace) WHERE n.nspname = 'open' AND c.relname = '表名'
-- 解释
-- 以上sql填入表名，返回结果cnt 。如果存在此表，则返回1，否则返回0。
-- 示例1：
SELECT COUNT(1) cnt
FROM pg_class c LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE n.nspname = 'open' AND c.relname = 'test_table' --ct1 : 1
-- 示例2：
SELECT COUNT(1) cnt
FROM pg_class c LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE n.nspname = 'open' AND c.relname = 'table' --ct1 : 0
-- 得到cnt后，我们可以利用if来判断，返回0时我们不进行插入，返回1我们进行插入。
-- 完整sql如下
if(
SELECT COUNT(1) cnt
FROM pg_class c LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE n.nspname = 'open' AND c.relname = 'table'
) > 0⑩：模糊匹配多个项
有这样一个场景，现有一批数据，需要模糊匹配多个项，以达到多种目的的筛选。
涉及函数或方法：like any( array['x1','x2'] )
示例如下：
-- like any( array['%x1%','%x2%'] ) 模糊匹配前尾模糊匹配x1、x2
select *
from test_table
where 字段 like any( array['%123456789%','%987654321%'] )
⑪：判断某张表中某个字段是否存在 （2023-03-13更新）
用于在未知表字段的前提下，或程序需要某个字段但表里没有的场景。
涉及函数或方法：数据库内置表：information_schema
示例如下：
# 下面例子用于判断在student表中，name和id两个字段是否存在。注意两个字段直接的关系为or，都存在返回2。
# 具体可以自己测试下
select count(1) as exists
from information_schema.columns
where table_name = 'student' and column_name = 'name' or column_name = 'id'
289
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
