PGSQL查询最近N天的数据及SQL语句实现替换字段内容_PostgreSQL

PGSQL查询最近N天的数据及SQL语句实现替换字段内容

2023-03-06 16:38两点王爷 PostgreSQL

PostgreSQL提供了WITH语句,允许你构造用于查询的辅助语句,下面这篇文章主要给大家介绍了关于PGSQL查询最近N天的数据及SQL语句实现替换字段内容的相关资料,文中通过实例代码介绍的非常详细,需要的朋友可以参考下

1、用SQL查询最近N天的数据

思路：先获取当前的时间，然后更具当前时间去减去对应的天数，就可以得到需要的天数了。然后用这些天数作为条件，然后即可查询出需要多少天的数据了。

				?

									-- 获取当前时间

									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;

2、用SQL语句实现替换字段里的内容

思路：使用替换函数替换即可

				?

									-- 替换语句

									update 表名 set 替换字段名 = replace(替换字段名,'被替换内容','替换内容');

实例如下：将 my_test 表的 test_content 字段内容进行替换

PGSQL查询最近N天的数据及SQL语句实现替换字段内容

				?

									update my_test set test_content = replace(test_content,'1','18');

PGSQL查询最近N天的数据及SQL语句实现替换字段内容

替换函数：如果没有可以创建，创建函数如下：

				?

									-- replace all occurrences in string of old_substr with new_substr 

									-- auto-generated definition

									create function replace(text, text, text) returns text

									    immutable

									    strict

									    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';

3、SQL将查询结果赋值

				?

									--查询表数据，给test_content字段并赋值

									select test_id,test_code,'测试数据' test_content from my_test;

									--查询表数据给自定义 ABCD字段 并赋值

									select test_id,test_code,'测试数据' ABCD from my_test;

4、SQL查询字段内容为 NULL值时候赋值

				?

									select test_id,test_code,COALESCE(test_content,'测试数据') ABCD from my_test;

									-- ||

									select test_id,test_code,

									       CASE

									           WHEN test_content IS NOT NULL THEN test_content

									           ELSE '测试数据'

									           END ABCD from my_test;