PGSQL查詢最近N天的資料及SQL語句實現替換欄位內容

-- 獲取當前時間
SELECT CURRENT_TIMESTAMP;
-- 或
SELECT now();
select CURRENT_TIMESTAMP :: DATE AS "當前日期";
SELECT CURRENT_DATE;
select CURRENT_TIMESTAMP :: TIME AS "當前時間";
SELECT CURRENT_TIME;

-- 最近7天
SELECT DATE( T ) AS DAY
FROM
generate_series ( ( CURRENT_TIMESTAMP - INTERVAL '6 day' ) :: DATE,CURRENT_TIMESTAMP :: DATE, '1 days' ) AS T ;

--統計最近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天的資料
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(替換欄位名,'被替換內容','替換內容');

update my_test set test_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 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';

--查詢表資料，給test_content欄位並賦值
select test_id,test_code,'測試資料' test_content from my_test;
--查詢表資料給自定義 ABCD欄位 並賦值
select test_id,test_code,'測試資料' ABCD from my_test;

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;