黄东旭解析 TiDB 的核心优势
897
2023-11-07
这篇“PGSQL怎么查询最近N天的数据及SQL语句实现替换字段内容的方法”文章的知识点大部分人都不太理解,所以小编给大家总结了以下内容,内容详细,步骤清晰,具有一定的借鉴价值,希望大家阅读完这篇文章能有所收获,下面我们一起来看看这篇“PGSQL怎么查询最近N天的数据及SQL语句实现替换字段内容的方法”文章吧。
思路:先获取当前的时间,然后更具当前时间去减去对应的天数,就可以得到需要的天数了。然后用这些天数作为条件,然后即可查询出需要多少天的数据了。
-- 获取当前时间 SELECT CURRENT_TIMESTAMP; -- 或 SELECT now(); select CURRENT_TIMESTAMP :: DATE AS "当前日期"; SELECT CURRENT_DATE; select CURRENT_TIMESTAMP :: TIME AS "当前时间"; SELECT CURRENT_TIME;最近N天
-- 最近7天 SELECT DATE( T ) AS DAY FROM generate_series ( ( CURRENT_TIMESTAMP - INTERVAL 6 day ) :: DATE,CURRENT_TIMESTAMP :: DATE, 1 days ) AS T ;当然6 day、1 day这些也是可修改的;
--统计最近30、7、昨天、今天的数据 SELECT count(*) AS AllData, count(*) filter ( WHERE create_time BETWEEN ( SELECT CURRENT_DATE - INTERVAL 30 day ) AND CURRENT_DATE ) AS Last30DayData, count(*) filter ( WHERE create_time BETWEEN ( SELECT CURRENT_DATE - INTERVAL 7 day ) AND CURRENT_DATE ) AS Last7DayData, count(*) filter ( WHERE create_time BETWEEN ( SELECT CURRENT_DATE - INTERVAL 1 day ) AND CURRENT_DATE ) AS YesterdayData, count(*) filter ( WHERE create_time >= CURRENT_DATE ) AS todayAddData FROM table_name;实例如下:查询lic_plate字段不为空和为空的最近7天的数据
-- 查询lic_plate字段不为空的最近7天的数据 select count(tn.lic_plate)AS number,tn.create_time AS percentage from table_name tn where tn.create_time in (SELECT DATE(T) AS DAY FROM generate_series((CURRENT_TIMESTAMP - INTERVAL 6 day) :: DATE, CURRENT_TIMESTAMP :: DATE,1 days) AS T) AND lic_plate is not null group by tn.create_time order by tn.create_time; -- 查询lic_plate字段为空的最近7天的数据 select count(id) AS number,CSQV.create_time AS percentage from (select * from table_name tn where lic_plate is null) AS CSQV where CSQV.create_time in (SELECT DATE(T) AS DAY FROM generate_series((CURRENT_TIMESTAMP - INTERVAL 6 day) :: DATE,CURRENT_TIMESTAMP :: DATE,1 days) AS T) group by CSQV.create_time;思路:使用替换函数替换即可
-- 替换语句 update 表名 set 替换字段名 = replace(替换字段名,被替换内容,替换内容);实例如下:将 my_test 表的 test_content 字段内容进行替换
update my_test settest_content =replace(test_content,1,18);替换函数:如果没有可以创建,创建函数如下:
-- replace all occurrences in string of old_substr with new_substr -- auto-generated definition create function replace(text, text, text) returns textimmutablestrict cost 1 language internal as $$begin -- missing source code end;$$; comment on function replace(text,text, text) is replace all occurrences in string of old_substr with new_substr;以上就是关于“PGSQL怎么查询最近N天的数据及SQL语句实现替换字段内容的方法”这篇文章的内容,相信大家都有了一定的了解,希望小编分享的内容对大家有帮助,若想了解更多相关的知识内容,请关注行业资讯频道。
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。