PGSQL常用SQL整理

TO_DATE

select TO_DATE(‘2023-02-15’, ‘yyyy-mm-dd’) - 30

RIGHT、CONCAT

UPDATE bucket_change_task SET plan_change_time =to_timestamp( CONCAT (
‘2023-02-28’,
RIGHT (
to_char(plan_change_time, ‘yyyy-mm-dd HH24:MI:SS’),
9
)
),‘yyyy-mm-dd HH24:MI:SS’)
WHERE to_char(plan_change_time, ‘yyyy-mm-dd’) = ‘2023-02-27’;

UPDATE maintenance_task SET plan_date =to_timestamp( CONCAT (
‘2023-05’,
RIGHT (
to_char(plan_date, ‘yyyy-mm-dd HH24:MI:SS’),
12
)
),‘yyyy-mm-dd HH24:MI:SS’)
WHERE to_char(plan_date, ‘yyyy-mm’) = ‘2023-04’;

to_timestamp

select to_timestamp(‘2022-12-27 00:00:00’,‘yyyy-mm-dd hh24:mi:ss’)-interval ‘185 day’

interval、count、avg、stddev

select count(),avg(rt.value),stddev(rt.value),avg(rt.value)+3stddev(rt.value) as “3segma上限值”,avg(rt.value)-3*stddev(rt.value) as “3segma下限值” from rawtagdata_1min_1302218 rt
where rt.tag_id = 194853 and “time” >= to_timestamp(‘2022-12-27 00:00:00’,‘yyyy-mm-dd hh24:mi:ss’)-interval ‘185 day’
and “time” < to_timestamp(‘2022-12-27 00:00:00’,‘yyyy-mm-dd hh24:mi:ss’);

UNION all

select tag_id ,max(value) AS value,3 as “type” from grouptagdata_5min_1302218 where tag_id in (194833) and value_time >= ‘2023-01-04 16:00:00.000’
and value_time < ‘2023-01-05 16:00:00.000’ and “type” = 3
group by tag_id
UNION all
select tag_id ,min(value) AS value ,4 as “type” from grouptagdata_5min_1302218 where tag_id in (194833) and value_time >= ‘2023-01-04 16:00:00.000’
and value_time < ‘2023-01-05 16:00:00.000’ and “type” = 4
group by tag_id
UNION all
select tag_id ,avg(value) AS value ,2 as “type” from grouptagdata_5min_1302218 where tag_id in (194833) and value_time >= ‘2023-01-04 16:00:00.000’
and value_time < ‘2023-01-05 16:00:00.000’ and “type” = 2
group by tag_id ;

string_agg

select string_agg(concat_ws(‘’,ID),‘,’ order by ID asc) as idliststr from tag t where (“name” like ‘C769%’ or “name” = ‘Gongyeyuan Substation’ or “name” like ‘F90%’) and “name” not like ‘%Virtual%’ and status = 1
select string_agg(t.id::text,‘,’) FROM tag t WHERE status = 1;

any(array[])

SELECT * FROM tag WHERE name LIKE any(array[‘%ptag%’,‘%vtag%’]) AND status = 1;

特殊符号、单位、表情用nvarchar类型，SQL如下

INSERT INTO public.uom
(id, code, “comment”, status, updateuser, updatetime, “version”, calculationtype, isaccumulated)
VALUES(156, ‘m³’, ‘m³’, 1, ‘admin’, ‘2023-03-31 15:00:00.000’, 0, 0, false);

select * FROM Uom u where code in (
‘kg’,‘L’,‘m³’,‘t’,‘104Nm3’,‘J’,‘KJ’,‘MJ’,‘GJ’,‘Wh’,‘kWh’,‘MWh’,‘KJ’,‘kVAh’,‘h’,‘min’,‘s’,‘°C’,‘kgce’,‘kgCO₂’
)
测试¹²³⁴⁵⁶⁷⁸⁹⁰ªⁿ⁺⁻⁼⁽⁾
SQLServer插入SQL如下：
INSERT INTO REMInformation.dbo.Uom
(Code, Comment, Status, UpdateUser, UpdateTime, CalculationType, IsAccumulated)
VALUES(N’kgCO₂’, N’kgCO₂’, 1, ‘DEMO’, ‘2011-12-19 00:00:00.000’, 1, 1);

复制表及数据

SELECT * INTO system_dictionary_0607 FROM system_dictionary;

查询分区

select t1.chunk_name,t2.range_start, t2.range_end,
pg_size_pretty(t1.before_compression_total_bytes) as before_total ,
pg_size_pretty(t1.after_compression_total_bytes) as after_total
from chunk_compression_stats(‘grouptagdata_5min_1305532’) t1
inner join timescaledb_information.chunks t2
on t1.chunk_name = t2.chunk_name order by t2.range_start asc;

删除已压缩的分区

SELECT drop_chunks(‘grouptagdata_5min_1305532’,newer_than => to_date(‘2023-05-28 08:00:00’,‘yyyy-MM-dd HH24:mi:ss’));

查询time+8h的数据

select tag_id, time, value, quality, insert_time
from rawtagdata_1min_1305532
where tag_id = 110082 and time = TIMESTAMP ‘epoch’ + 1685638800 * interval ‘1 SECOND’
order by time ASC

层级数查询

WITH RECURSIVE tree AS (
– 初始查询，选择所有没有父级的分类（即根分类）
select hn.“name”,hn.parent_id,hn.id from hierarchy_node hn where tenant_id = 5418
and tree_type = ‘fmhc’ and status = 1 and parent_id = 0
UNION ALL
– 递归查询，选择当前层次的所有子分类
SELECT c.name, c.parent_id,c.id
FROM hierarchy_node c
INNER JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;