记一次postgresql数据导出

2022-07-03 04:04:11

1. 问题一：实现同一字段的多条记录合并到一条记录

　方法一：array_agg(expression) 把表达式变成一个数组一般配合 array_to_string() 函数使用

　方法二：string_agg(expression, delimiter) 直接把一个表达式变成字符串,参数二为拼接连接符

方法三：自定义函数
-- 创建合并方法
  CREATE AGGREGATE group_concat(anyelement)
(
    sfunc = array_append, -- 每行的操作函数，将本行append到数组里 
    stype = anyarray,  -- 聚集后返回数组类型 
    initcond = ‘{}‘    -- 初始化空数组
);

　　实现SQL

SELECT
    pd.project_id,
    array_agg ( pd.file_name ) AS filename 
FROM
    a pp
    LEFT JOIN b pd ON pp.ID = pd.project_id 
WHERE
    pp.ENABLEd = ‘T‘ 
    AND pd.ENABLEd = ‘T‘ 
GROUP BY
    pd.project_id;

SELECT
    pd.project_id,
    string_agg ( pd.file_name,‘,‘ ) AS filename 
FROM
    a pp
    LEFT JOIN b pd ON pp.ID = pd.project_id 
WHERE
    pp.ENABLEd = ‘T‘ 
    AND pd.ENABLEd = ‘T‘ 
GROUP BY
    pd.project_id;

SELECT
    pd.project_id,    group_concat ( pd.file_name ) AS filename

FROM
    a pp
    LEFT JOIN b pd ON pp.ID = pd.project_id 
WHERE
    pp.ENABLEd = ‘T‘ 
    AND pd.ENABLEd = ‘T‘ 
GROUP BY
    pd.project_id;

补充：array_agg和string_agg函数还有一些其他操作，如排序，去重，按下表取元素等。之后碰上在补充了

问题二：pg中的json字段操作

SELECT

pp.duty :: json -> 0 ->> ‘label‘ AS 负责人

from a pp

当然这个和json的存储格式有关系，因为库中存的是json数组，所以以上方法使用了 0 这个数字去第一个json，如果直接存储的是json则可以省去

问题三：查询父项目及子项目的数据，并排序。这里涉及了pg中的递归函数

with RECURSIVE cte as
(
select a.id,cast(a.name as varchar(100)) from tb a 
union all 
select k.id,cast(c.name||‘>‘||k.name as varchar(100)) as name  from tb k inner join cte c on c.id = k.pid
)select id,name from cte ;

解释下该递归的意思，上半部分为父级，下半部分为递归部分，之后将结果作为临时表，还可以和其他表做关联查询。

问题四：pg中分组，并查询每个分组中最大或者最小的值 row_number () over PARTITION BY 相关函数的使用

SELECT
    *,
    ROW_NUMBER ( ) OVER ( PARTITION BY a.proc_inst_id ORDER BY last_modified_date DESC NULLS LAST ) t1 
FROM
    a 
    ) AS TEMP　　 
WHERE
    t1 < 2

row_number有点类似虚列，该SQL的意思是按照实力id（proc_inst_id）分组，并按修改时间降序排序，并取出每一组的第一条记录。即最新修改的数据

附上最终SQL，SQL中的表将用a，b，c代替，以及中文注释随意删除了，请不要太在意

-- 创建合并方法
    CREATE AGGREGATE group_concat(anyelement)
(
    sfunc = array_append, -- 每行的操作函数，将本行append到数组里 
    stype = anyarray,  -- 聚集后返回数组类型 
    initcond = ‘{}‘    -- 初始化空数组
);

WITH RECURSIVE T AS (
  SELECT ID
    ,
    pid,
    root_id,
        name,
    ID :: TEXT AS PATH,
    1 AS LEVEL 
  FROM
    a
  WHERE
    pid IS NULL 
    UNION ALL
  SELECT
    D.ID,
    D.pid,
    D.root_id,
        d.name,
    ( T.PATH || ‘/‘ || D.ID ) :: TEXT AS PATH,
    ( T.LEVEL + 1 ) AS LEVEL 
  FROM
    a D
    JOIN T ON D.pid = T.ID 
  ) SELECT 
    (case when T.LEVEL=‘1‘ then ‘一级计划‘
                when T.LEVEL=‘2‘ then ‘二级计划‘
                when T.LEVEL=‘3‘  then ‘三级计划‘
                when T.LEVEL=‘4‘ then ‘四级计划‘
                end
                
        ) as 计划, 
        parent.name as 父级项目,
  ( pp.extent_1 :: json ->> 0 ) :: json ->> ‘label‘ AS 功能,-- 二级功能
   pp.assist_depts :: json -> 0  ->> ‘label‘ AS 部门,
    ((case when ppr.operate =‘ADD‘ then ‘新增流程‘
                when ppr.operate =‘UPDATE‘ then ‘更新流程‘
                when ppr.operate = ‘FINISH‘ then ‘结束流程‘
                END
        ) || ‘/‘ ||
        (
            case when ppr.status =‘active‘ then ‘执行中‘
                        when ppr.status =‘end‘ then ‘已结束‘
                        when ppr.status =‘cancel‘ then ‘已取消‘
                        END
        )
        )
    as 展示,
  pr.CONTENT as 日报,
  sub.filename as 交,
    pp.ID,
  pp.pid
FROM
  a pp
  INNER JOIN T T ON T.ID = pp.ID
    LEFT JOIN a parent on pp.pid = parent.ID
  LEFT JOIN ( SELECT plan_id,operate,status
      FROM
       ( SELECT *, row_number ( ) over ( PARTITION BY b.proc_inst_id ORDER BY last_modified_date DESC nulls last ) t1 FROM   b) AS temp　　 WHERE 
       t1 < 2) ppr ON pp.ID = ppr.plan_id -- 关联表
  LEFT JOIN ( SELECT project_id,content
      FROM
       ( SELECT *, row_number ( ) over ( PARTITION BY c.project_id ORDER BY last_modified_date DESC nulls last ) t1 FROM   c) AS temp　　 WHERE 
       t1 < 2) pr on pr.project_id = pp.id -- 关联表
  LEFT JOIN (
  SELECT
    pd.project_id,
    group_concat ( pd.file_name ) AS filename 
  FROM
    a pp
    LEFT JOIN d pd ON pp.ID = pd.project_id  -- 关联表
  WHERE
    pp.ENABLEd = ‘T‘ 
    AND pd.ENABLEd = ‘T‘ 
  GROUP BY
    pd.project_id 
  ) sub ON sub.project_id = pp.ID    
    
ORDER BY
  T.root_id,
  T."path";

记一次postgresql数据导出

码农公寓

相关文章