PostgreSQL(Hologress-Sql)常见问题总结

hologres时间函数哪些事

---获取某个月最后一天最后时刻的时间戳
date_trunc('month', (current_date-interval '4 month')) :: TIMESTAMP + INTERVAL '1 month - 1 second' AS month_last_timestamp

SELECT 
    to_timestamp('2021-07-17 17:40:33','yyyy-MM-dd hh24:mi:ss') as "将 string 转成时间戳格式",
    to_date(TO_CHAR(to_timestamp(max(event_time)/1000),'yyyy-MM-dd'),'YY MM DD') as "将 long 类型转换成时间戳",
    current_date-1 as 昨天,
    current_date-to_date(TO_CHAR(to_timestamp(max(event_time)/1000),'yyyy-MM-dd'),'YY MM DD') as 相差几天,
    substring(cast(now() as varchar),0,11) as curr_day,
    to_char(current_date,'YYYY-MM-DD') as time2, --将date类型转换成字符串
    cast(TO_CHAR(CURRENT_TIMESTAMP-regist_time,'dd') as integer)*1.0/365 as age,    利用timestamp直接加减计算天数/365=年龄
    corp_id,
    now(),
    --字符串转long类型
    event_time >= EXTRACT(epoch from (TIMESTAMP '2022-01-01 00:00:00'))*1000, 
    --- 获取整点的时间戳(1651420800000=2022-5-2 0:0:0) 
    EXTRACT(epoch from cast(current_date -interval '1 month' AS TIMESTAMPTZ))*1000, 
    date_part('week',TIMESTAMP '2021-03-11'),        --获取周数
    date_trunc('week', '2021-03-11'::timestamp),     --获取周时间
    to_date(TO_CHAR(event_time,'yyyy-MM-dd'),'YY MM DD'),       -- 将timestamp转化成date类型
    EXTRACT(epoch FROM to_timestamp('2021-06-16', 'YYYY-MM-DD')) as day_time_ms     -- 将字符串转成时间戳
FROM "flow"."ods_flow_web_active_log"
group by corp_id

### regist_time是TIMESTAMP类型，关于 TIMESTAMP 的条件搜索
select 
    regist_time 
    from boss.ods_company 
    where regist_time >=TIMESTAMP '2010-06-01 00:00:00' and regist_time<=TIMESTAMP '2017-11-02 23:59:59'
 order by regist_time asc
 
 ### 根据日期 获取 周
 select 
     corp_id,
     count(1) as weekly_bo_change_count
 from app.ods_bizdata_op_log 
 where extract(DOW FROM current_timestamp)=1  --- pgsql判断，当前日期是否是[周一]
 and to_timestamp(event_time/1000)<to_timestamp(cast(CURRENT_TIMESTAMP::DATE as varchar),'yyyy-MM-dd')  --获取整点的timestamp
 and to_timestamp(event_time/1000)>to_timestamp(cast(CURRENT_TIMESTAMP::DATE-7 as varchar),'yyyy-MM-dd')
 group by corp_id

hologres 行转列拼接字符串的问题 string_agg()=group_concat()

SELECT
      concat('{',
        '"engineCode":"',engine_code,'",',
        '"batchNo":"',batch_no,'",',
        '"operationType":',operation_type,',',
        '"operator":{',
            '"id":"',operator_id,'",',
            '"type":',operator_type,',',
            '"name":"',operator_name,'"',
        '},',
        '"bizObjectId":"',biz_object_id,'",',
        '"bizObjectTitle":"',biz_object_title,'",',
        '"operationTime":',operation_time,',',
        '"properties":[',
       string_agg(concat('{',
            '"dataType":',property_data_type,',',
            '"code":"',property_code,'",',
            '"name":"',property_name,'",',
            '"after":"',property_after,'",',
            '"before":"',property_before,'"',
        '}'),','),
        ']}') 
    FROM
      tst.ods_engine_bizdatachanged_log 
    WHERE
      engine_code = 'c0000000000up9-0' 
      AND schema_code = 'smzbx1k6n9x324az3a' 
      AND biz_object_id='60daf0ec88f9545ac8000001'
      AND pt>'20210628'
      GROUP BY engine_code,schema_code,biz_object_id,batch_no,operation_type,operator_id,operator_type,operator_name,biz_object_title,operation_time
      ORDER BY operation_time DESC

hologress 插入跟新sql

INSERT into boss.ods_xxx_table (name,address,geohashcode,location)
    VALUES(?, ?, ?, ?) 
    on conflict(name) 
    do 
        update set
        name=excluded.name,
        address=excluded.address,
        geohashcode=excluded.geohashcode,
        location=excluded.location

A表跟新B表

--- PostgreSQL官网
    https://www.postgresql.org/docs/9.1/sql-update.html?spm=holoweb.holoweb-cn-hangzhou.help.13.78155ab0jSP1cb
    
    update boss.ods_company_location_distribution f1 set 
        geohashcode=f2.geohashcode,
        location=f2.location,
        address=f2.address,
        update_time=CURRENT_TIMESTAMP 
    from map.ods_aiqicha_source_data f2 
    where f2.cmp_name=f1.name
    and f2.is_match='成功'
    and length(f1.geohashcode)<=5   -- 排除已经算过的情况
    and length(f2.geohashcode)>5;   -- 排除更新集合本身就是错误的情况

字符串的替换(将','替换成'#'的操作)

update map.map_business_area_data set location=translate(location,',','#')
    where wtime='2021-10-28'

PostgreSQL自定义函数

CREATE OR REPLACE FUNCTION is_json(input_text varchar) RETURNS boolean AS 
$$
  DECLARE
    maybe_json json;
  BEGIN
    BEGIN
      maybe_json := input_text;
    EXCEPTION WHEN others THEN
      RETURN FALSE;
    END;

    RETURN TRUE;
  END;
$$ LANGUAGE plpgsql IMMUTABLE;