日期清洗的sql函数

最新推荐文章于 2024-07-04 18:22:35 发布
最新推荐文章于 2024-07-04 18:22:35 发布
阅读量361 收藏 1
点赞数
分类专栏： 存储组件 文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhang5324496/article/details/131981293
版权

 

sql语言是postgressql。

函数只处理了

中国格式 ： xxxx年-xx月-xx日

美国格式： mm/dd/yyyy， September 04,1991， September 1991， sept 04， 1991

uunix格式： yyyy/mm/dd， yyyy/mm， 1991-01-1， 1991-1 ， 1991-01

其他格式可以自行添加

预期的日期格式是： 1991/1/1 。 不能有0，即1991/01/01 是失败的

函数返回的是： " ‘预期格式’，年，月"

一、主要使用的内置函数是：

1. instr( <start_position> ,'str1' , 'str2' , <times>) 。

查找str2在str1出现的位置。num是出现的次数，默认是1。例如num=2，就是str2在str1第二次出现的位置。start_position是起始的位置下标，默认是从头开始即 1。

若str2在str1找不到，则返回0

 instr('123456', '2')  -- 结果是: 2. 位置索引值是从1开始
     
 instr('123456', '2' , 2) -- 结果是0 ， 因为2 只出现一次

2. substr(str1, start_position, <length> )

截取字符串， 从str1 的 start_position开始截取， 截取长度是 length。length不填就是截取到字符串的最右边。

例如 start_position=2， length=3，  str1= ‘’12345678‘’

substr('123456', 2, 3) -- 结果是： 234. 因为postgres是下标索引是1开始
 
substr('123456', 1, 3) -- 结果是： 123. 因为postgres是下标索引是1开始
substr('123456', 0, 3) -- 结果是： 123. 0也当成1
 
substr('123456', 2) -- 结果是： 23456.  长度值不填，默认就是截取到最右边

3.  regexp_like(str1，str2)

查看str1 是否 符合 规则 str2.

str2是正则表达式： ^ 表示开头 ，$ 表示结尾。/d 表示数字 0-9，[0-9]也是表达数字，可读性更好。 | 是或的意思。

'^([1][8-9][0-9][0-9]|[2][0][0-9][0-9])$') 的规则就是， 19xx，18xx， 20xx 。xx代表数字的任意值

4. split_part(str1， str2， index)

华为gauss DB(DWS)中的字符串切分函数。 可以使用你自己sql相似的函数。

将 str1 按照 str2 切分， 并取切分后结果集合的第几个值。

-- 将'1，2，3'用 '，'切分， 并取切分后结果的第二个值。 
split_part('1,2,3' , ',' , 2) -- 结果是2

5. trim()

去除字符串收尾的空格，避免影响 split_part 以空格切分字符串。

6. month()

返回月值， 确保没有0 。 因为清洗的预期是不要0

二、日期清洗函数

-- 返回 'birthday，birthday_year，birthday_month'
CREATE OR REPLACE FUNCTION aml.f_get_date_str(birthday_str character varying(500))
 RETURNS character varying
 LANGUAGE plpgsql
 STABLE NOT FENCED SHIPPABLE -- 华为gausDB DWS的独有， postgres不用
AS $$
DECLARE
  p_birthday_str VARCHAR2(500);
  text_var1 text;
  text_var2 text;
  text_var3 text;
BEGIN
  SELECT 
      CASE -- 处理只有年份数字的 1988 、1978 
           WHEN regexp_like (trim(birthday_str),'^([1][8-9][0-9][0-9]|[2][0][0-9][0-9])$') > 0 
                then concat_ws(',', trim(birthday_str)
                                  , trim(birthday_str)
                                  , ' '
                               )
                               
           -- 处理1988/05/15 或 1988/5/15 ， 或1988/5/5
           WHEN regexp_like(trim(birthday_str),'^([1][8-9][0-9][0-9]|[2][0][0-9][0-9])(\/)([1-9]|[0][1-9]|[1][0-2])(\/)([1-9]|[0][1-9]|[1-2][0-9]|[3][0-1])$') > 0 
                then concat_ws(',', year(birthday_str) || '/' || month(birthday_str) || '/' || day(birthday_str)
                                  , year(birthday_str)
                                  , month(birthday_str)
                               )
           -- 处理1988/05,1988/5 ,  确保 没有0开头， 所以调用year，month函数再组装
           WHEN regexp_like(trim(birthday_str),'^([1][8-9][0-9][0-9]|[2][0][0-9][0-9])(\/)([1-9]|[0][1-9]|[1][0-2])$') > 0 
                then concat_ws(',', year(trim(birthday_str) || '/01') || '/' || month(trim(birthday_str) || '/01')
                                  , year(trim(birthday_str) || '/01')
                                  , month(trim(birthday_str) || '/01')
                               )
                               
            -- 处理mm/dd/yyyy
           WHEN regexp_like(trim(birthday_str),'^([1-9]|[0][1-9]|[1][0-2])(\/)([1-9]|[0][1-9]|[1-2][0-9]|[3][0-1])(\/)([1][8-9][0-9][0-9]|[2][0][0-9][0-9])$') > 0 
                then concat_ws(',', year(birthday_str) || '/' || month(birthday_str) || '/' || day(birthday_str)
                                  , year(birthday_str)
                                  , month(birthday_str)
                               )
           -- 处理mm/yyyy ,  确保 没有0开头， 所以调用year，month函数再组装
           WHEN regexp_like(trim(birthday_str),'^([1-9]|[0][1-9]|[1][0-2])(\/)([1][8-9][0-9][0-9]|[2][0][0-9][0-9])$') > 0 
                then concat_ws(',', year(replace(trim(birthday_str),'/','/01/')) || '/' || month(replace(trim(birthday_str),'/','/01/'))
                                  , year(replace(trim(birthday_str),'/','/01/'))
                                  , month(replace(trim(birthday_str),'/','/01/'))
                               )
                
           -- 处理中国格式，
           -- xxxx年xx月xx日，  -> yyyy/m/d
           WHEN regexp_like(trim(birthday_str),'^([1][8-9][0-9][0-9]|[2][0][0-9][0-9])(年)([1-9]|[0][1-9]|[1][0-2])(月)([1-9]|[0][1-9]|[1-2][0-9]|[3][0-1])(日)$') > 0
                then concat_ws(',', year(replace(replace(replace(birthday_str,'年','/'),'月','/'),'日','')) || '/' || month(replace(replace(replace(birthday_str,'年','/'),'月','/'),'日','')) || '/' || day(replace(replace(replace(birthday_str,'年','/'),'月','/'),'日',''))
                                  , year(replace(replace(replace(birthday_str,'年','/'),'月','/'),'日',''))  
                                  , month(replace(replace(replace(birthday_str,'年','/'),'月','/'),'日',''))
                               )
           -- xxxx年xx月  -> yyyy/m
           WHEN regexp_like(trim(birthday_str),'^([1][8-9][0-9][0-9]|[2][0][0-9][0-9])(年)([1-9]|[0][1-9]|[1][0-2])(月)$') > 0
                then concat_ws(',', year(trim(replace(replace(birthday_str,'年','/'),'月','/')) || '01') || '/' || month(trim(replace(replace(birthday_str,'年','/'),'月','/')) || '01') 
                                  , year(trim(replace(replace(birthday_str,'年','/'),'月','/')) || '01' ) 
                                  , month(trim(replace(replace(birthday_str,'年','/'),'月','/')) || '01') 
                              )
           -- xxxx年xx  -> yyyy/m
           WHEN regexp_like(trim(birthday_str),'^([1][8-9][0-9][0-9]|[2][0][0-9][0-9])(年)([1-9]|[0][1-9]|[1][0-2])$') > 0
                then concat_ws(',', year(trim(replace(birthday_str,'年','/')) || '/01') || '/' || month(trim(replace(birthday_str,'年','/')) || '/01') 
                                  , year(trim(replace(birthday_str,'年','/')) || '/01')
                                  , month(trim(replace(birthday_str,'年','/')) || '/01')
                               )
           -- xxxx年
           WHEN regexp_like(trim(birthday_str),'^([1][8-9][0-9][0-9]|[2][0][0-9][0-9])(年)$') > 0
                then concat_ws(',', replace(birthday_str,'年','')
                                  , replace(birthday_str,'年','')
                                  , ' ' )
           
           -- 处理1988-05-15 或 1988-5-15 ， 或1988-5-5
           WHEN regexp_like(trim(birthday_str),'^([1][8-9][0-9][0-9]|[2][0][0-9][0-9])(\-)([1-9]|[0][1-9]|[1][0-2])(\-)([1-9]|[0][1-9]|[1-2][0-9]|[3][0-1])$') > 0 
                then concat_ws(',', year(replace(birthday_str,'-','/')) || '/' || month(replace(birthday_str,'-','/')) || '/' || day(replace(birthday_str,'-','/'))
                                  , year(replace(birthday_str,'-','/'))  
                                  , month(replace(birthday_str,'-','/'))
                               )
           -- 处理1988-05
           WHEN regexp_like(trim(birthday_str),'^([1][8-9][0-9][0-9]|[2][0][0-9][0-9])(\-)([1-9]|[0][1-9]|[1][0-2])$') > 0 
                then concat_ws(',', year(trim(replace(birthday_str,'-','/')) || '/01') || '/' || month(trim(replace(birthday_str,'-','/')) || '/01')
                                  , year(trim(replace(birthday_str,'-','/')) || '/01')  
                                  , month(trim(replace(birthday_str,'-','/')) || '/01')
                               )
                               
           -- 处理19880515
           WHEN regexp_like(trim(birthday_str),'^([1][8-9][0-9][0-9]|[2][0][0-9][0-9])([0][1-9]|[1][0-2])([0][1-9]|[1-2][0-9]|[3][0-1])$') > 0 
                then concat_ws(',', year(birthday_str) || '/' || month(birthday_str)  || '/' || day(birthday_str) 
                                  , year(birthday_str)
                                  , month(birthday_str)
                               )
           -- 处理198805,20xx05 , 补齐日的值 例如：01
           WHEN regexp_like(trim(birthday_str),'^([1][8-9][0-9][0-9]|[2][0][0-9][0-9])([0][1-9]|[1][0-2])$') > 0 
                then concat_ws(',', year(trim(birthday_str) || '01') || '/' || month(trim(birthday_str) || '01')
                                  , year(trim(birthday_str) || '01')
                                  , month(trim(birthday_str) || '01')
                               )
           
           -- 处理美国时间格式(英文),'September 04, 1952' ,'Sept 04, 1952', 'September, 1952' , 'Sept, 1952'
           WHEN trim(split_part(split_part(birthday_str,',',1),' ',1)) in ('January' ,'February' ,'March' , 'April' , 'May' , 'June' , 'July' , 'August' ,'September' ,'October' ,'November' ,'December', 'Jan' , 'Feb' , 'Mar' ,'Apr' , 'Aug' , 'Sept' , 'Oct' ,'Nov' , 'Dec') 
            and regexp_like(trim(split_part(birthday_str,',',2)),'^([1][8-9][0-9][0-9]|[2][0][0-9][0-9])$') > 0 -- 判断年份值是否合理
            then 
                -- 'September 04, 1952' 
                case when regexp_like(trim(split_part(split_part(birthday_str,',',1),' ',2)),'^([1-9]|[0][1-9]|[1-2][0-9]|[3][0-1])$') > 0  -- 判断日份值是否合理
                          then concat_ws(',', year(birthday_str) || '/' || month(birthday_str) || '/' || day(birthday_str)
                                            , year(birthday_str)
                                            , month(birthday_str)
                                         )
                     --不完整,缺少日 'September, 1952'  。 补全日期后(日期数值随意，这里补01)，利用函数 year， month，day
                     when split_part(split_part(birthday_str,',',1),' ',2) is null 
                          then concat_ws(',', year(replace(birthday_str, ',' , '01,')) || '/' || month(replace(birthday_str, ',' , '01,')) 
                                            , year(replace(birthday_str, ',' , '01,'))
                                            , month(replace(birthday_str, ',' ,'01,')) 
                                         )
                     end
                     
           --  ’September 1952‘ 格式
           WHEN trim(split_part(birthday_str,' ',1)) in ('January' ,'February' ,'March' , 'April' , 'May' , 'June' , 'July' , 'August' ,'September' ,'October' ,'November' ,'December', 'Jan' , 'Feb' , 'Mar' ,'Apr' , 'Aug' , 'Sept' , 'Oct' ,'Nov' , 'Dec') 
            and regexp_like(trim(split_part(birthday_str,' ',2)),'^([1][8-9][0-9][0-9]|[2][0][0-9][0-9])$') > 0 -- 判断年份值是否合理
            then concat_ws(',', year(replace(birthday_str, ' ' , ' 01,')) || '/' || month(replace(birthday_str, ' ' , ' 01,'))
                              , year(replace(birthday_str, ' ' , ' 01,'))
                              , month(replace(birthday_str, ' ' , ' 01,'))
                           )
           
           ELSE concat_ws(',', ' ', ' ', ' ')
      END birthday_year_month INTO p_birthday_str ;
  RETURN (p_birthday_str) ;
  EXCEPTION
     WHEN others THEN
       GET STACKED DIAGNOSTICS text_var1 = RETURNED_SQLSTATE  ,
                             text_var2 = MESSAGE_TEXT  ,
                             text_var3 = PG_EXCEPTION_DETAIL;
       RAISE NOTICE E'--- [已处理的 异常代码_RETURNED_SQLSTATE ] : %', text_var1;    -- 记录异常信息
       RAISE NOTICE E'--- [已处理的 异常简要信息_MESSAGE_TEXT ] : %', text_var2;
       RAISE NOTICE E'--- [已处理的 异常详细信息_PG_EXCEPTION_DETAIL ] : %', text_var3;
       
       RETURN (concat_ws(',', ' ', ' ',' '))  ;  -- 处理异常的日期数据，直接返回。 例如日和月值不匹配，1955-02-30
END $$
;

龟速扣代码
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL日期处理技巧
恋上、小幸福的博客
08-09 1070
在数据库中，日期和时间数据是非常常见且重要的数据类型。MySQL提供了丰富的日期处理函数，可以帮助我们对日期数据进行各种操作、格式化和计算。本文将深入介绍MySQL中日期处理的常用技巧，让你能够更加优雅地操作日期数据。
关于我用mybatis-plus清空mysql表字段类型dateTime的数据不好用的这档事
天弃的博客
05-24 1145
有个需求，就是页面需要展示时间区间 但是这个时间在页面是非必填的，而且表单可修改，比如我添加数据加了时间，但是修改删掉时间，这个时候我们需要给数据的时间清空的。 那么问题来了，由于单表数据，肯定用plus省事多了啊，但是plus会自动过滤掉null数据，也就是你就算给实体set null，数据依旧没有变动，mysql在不动sqlmode的情况下，datetime是不允许设置空字符串这种不规则的数据，所以 解决，在实体字段上加注解过滤 @TableField(strategy = Field
数据库删除指定日期以前的数据
鳄鱼追日
08-04 5513
delete from 表名 where cast(字段名 as datetime)<'2009-07-21'; commit; delete from eip_user where cast (create_time as datetime)<'2019-08-04'; commit; delete from allTData where crt_time<'2017...
php中把日期转换成字符串,PHP将一个日期字符串转换成举例来说当前的天数 - trim...
weixin_33034073的博客
03-11 149
去掉前后的空格. 假设有一个字符串" ddd dd d "，经过Trim()之后成为"ddd dd d". 如上可以去掉两边多余的空格(包括制表符),但不能去掉中间的空格. PHP中的trim()函数跟ASP中的trim()函数用法一样的,如果你接触过ASP,那么就...PHP将一个日期字符串转换成举例当前的天数输入为一个日期字符串，例如：2011-3-23输出为举例当前的天数，例如：1?代...
sqlserver函数大全
12-07
SQL Server函数大全是一份...这些函数在日常的SQL查询、报表生成、数据清洗和分析等方面发挥着重要作用，掌握它们能极大地提高工作效率。通过深入理解和熟练运用这些函数，你可以更好地管理和操作SQL Server中的数据。
SQL 函数集.rar
03-12
SQL函数集是数据库查询语言SQL中的重要组成部分，用于处理和操作数据。在SQL SERVER中，函数的使用丰富了数据查询的...无论是数据清洗、报表生成还是复杂的数据分析任务，熟练运用SQL函数都将使你的工作效率大大提高。
Oracle 10g中的高级SQL函数
12-08
在Oracle 10g数据库系统中，高级SQL函数是开发者不可或缺的工具，它们极大地扩展了标准SQL的功能，使得数据查询和处理更为灵活高效。本文将深入探讨这些高级SQL函数，帮助你更好地理解和运用它们。 一、窗口函数 ...
SQL学习笔记七函数 数字，日期，类型转换，空值处理，case
09-11
了解和熟练掌握这些函数是SQL查询和报表创建的基础，它们可以帮助我们进行复杂的计算、数据清洗和分析。在实际工作中，结合这些函数，可以编写出满足各种需求的SQL查询，从而更高效地处理和解析数据。
SQL常用功能自定义函数
09-09
4. **完善SQL农历转换函数**: 农历转换函数是处理日期和时间的一种特殊应用，它能够将公历日期转换为农历日期，这对于处理与中华文化相关的日期信息特别重要。 5. **人民币小写金额转大写**: 这个函数可能是为了...
Excel 预习阶段Day4
weixin_42741880的博客
09-24 489
Excel 目录10.函数入门10.1 常用运算符：算术运算符、比较运算符、文本运算符10.2 公式或者函数需要使用=10.3 F1 调出函数帮助文档10.4 求和统计函数10.4 数据四舍五入函数10.5 最值操作函数10.6 字符串操作函数10.7 日期格式10.8 其他函数 10.函数入门 10.1 常用运算符：算术运算符、比较运算符、文本运算符     算术运算符：+ - * / ^ %     比较运算符(布尔类型)
mysql 日期比较 mybatis ＜trim＞
zhaozhuang002的博客
11-22 93
我的博客日记Java大神之路 Java大神之路 1. 解释： prefix:在trim标签内sql语句加上前缀。 suffix:在trim标签内sql语句加上后缀。 suffixOverrides:指定去除多余的后缀内容，如：suffixOverrides=","，去除trim标签内sql语句多余的后缀","。 prefixOverrides:指定去除多余的前缀内容 例如： insert int...
利用SQL对数据进行清洗处理
张俊红的个人博客
05-18 2838
获取数据后，对数据的清洗工作必不可少，常用的数据清洗方法主要有缺失值填充、数值替换、数据类型转换、数据分列、重复值处理等，清洗的数据结果直接影响最后数据分析的结果，一个成熟的数据分析师，在...
【数据分析】5大SQL数据清洗方法！
fengdu78的博客
10-26 6311
日常工作中，分析师会接到一些专项分析的需求，首先会搜索脑中的分析体悉，根据业务需求构建相应的分析模型（不只是机器学习模型），根据模型填充相应维度表，这些维度特征表能够被使用的前提是假设已...
SQL数据清洗
热门推荐
jacky_zhuyuanlu的博客
09-25 1万+
大家好，我是jacky,很高兴继续跟大家分享《MySQL数据分析实战》，从本节课程开始，我们的课程就会变得越来越实战，也会越来越有意思了； 我们课程的主体叫MySQL数据分析实战，那我们用MySQL来进行数据分析的时候，也一定会有其逻辑在的，大家想想我们无论从事任何的工作岗位，我们工作都是有我们的逻辑在的，可能有人会说，我的工作有什么工作逻辑？那jacky说，那是因为我们的工作都太熟悉了，我们也...
记一次——用sql进行数据清洗实例
大美mixer的博客
03-29 2821
数据库中有某段用户上报日志，记录着用户的一些操作行为。现要将其清洗后，并筛选出我们感兴趣的用户后，并整理为算法要求的格式。要求过程中只能用sql。 0 引言 源数据表介绍和分析 数据规模：每天2亿+条数据； 含义：每人每天登陆一次app则产生一条记录； 原始数据表字段如下所示： Original_table utdid user_id content ds zxcvb...
流式数据库 RisingWave「Demo」：直播指标实时分析
最新发布
https://risingwave.com/
07-04 800
直播是目前最为流行的娱乐形式之一，本教程将分享如何使用开源流式数据库 RisingWave 监控直播指标。我们为本教程设置了一个演示集群，以便大家可以轻松尝试。
QT学习积累——方法参数加const和不加const的区别
Arya的博客，专注后端领域
07-03 1054
QT学习积累——方法参数加const和不加const的区别
大数据学习：Hive函数.pdf
12-07
大数据学习：Hive函数.pdf

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值