![个人头像照片](https://ucc.alicdn.com/avatar/img_4a2d12b89a2dd1d56b8543a511f15bf6.jpg)
阿里巴巴-淘宝-新制造-高级技术专家,PostgreSQL、Clojure、FreeBSD等技术爱好者
暂时未有相关通用技术能力~
阿里云技能认证
详细说明提供两种思路:
参考:https://stackoverflow.com/questions/20039856/import-excel-data-into-postgresql-9-3
根据题目的描述,假设表dm_org,有四个字段:
decode(t.org_up_id , t.org_id , 0 , t.org_up_id)
,推测根节点的org_up_id等于org_id。假设表中包含以下六行记录:
org_id | org_up_id | org_level | org_name |
---|---|---|---|
1 | 1 | 1 | 'A' |
2 | 1 | 2 | 'B' |
3 | 1 | 2 | 'C' |
4 | 2 | 3 | 'D' |
5 | 3 | 3 | 'E' |
6 | 5 | 4 | 'F' |
利用题主的SQL查询结果如下:
org_id | org_name | org_level | org_up_id | org_up_name | org_level | leaf |
---|---|---|---|---|---|---|
1 | A | 1 | 1 | A | 1 | 0 |
2 | B | 2 | 1 | A | 1 | 0 |
4 | D | 3 | 1 | A | 1 | 1 |
3 | C | 2 | 1 | A | 1 | 0 |
5 | E | 3 | 1 | A | 1 | 0 |
6 | F | 4 | 1 | A | 1 | 1 |
2 | B | 2 | 2 | B | 2 | 0 |
4 | D | 3 | 2 | B | 2 | 1 |
3 | C | 2 | 3 | C | 2 | 0 |
5 | E | 3 | 3 | C | 2 | 0 |
6 | F | 4 | 3 | C | 2 | 1 |
4 | D | 3 | 4 | D | 3 | 1 |
5 | E | 3 | 5 | E | 3 | 0 |
6 | F | 4 | 5 | E | 3 | 1 |
6 | F | 4 | 6 | F | 4 | 1 |
因为没有指定start with
语句,从功能上来说,是递归查询dm_org表所有记录的子节点。与之对应的PostgreSQL的查询语句是:
with recursive leafs as (
select
distinct
parents.org_id as leaf_id
from
dm_org as parents
left join
dm_org as children
on
parents.org_id = children.org_up_id
where
children.org_id is null
), trees as (
select
org_id,
org_name,
org_level,
org_id as org_up_id,
org_name as org_up_name,
org_level as org_up_level
from
dm_org
union all
select
dm_org.org_id,
dm_org.org_name,
dm_org.org_level,
trees.org_up_id,
trees.org_up_name,
trees.org_up_level
from
dm_org
inner join
trees
on
dm_org.org_up_id = trees.org_id
and dm_org.org_id != trees.org_id
)
select
trees.*,
leafs.leaf_id is not null as leaf
from
trees
left join
leafs
on
trees.org_id = leafs.leaf_id
order by
org_up_id,
org_id;
定义了两个公共表表达式,其中:
connect_by_isleaf
这样的关键字。看问题描述,你是要直接开通PG数据库的直连用户?
create user xxx
创建用户即可,相应的权限用grant
来赋予;postgresql.conf
的日志选项log_statement = 'all'
,这样就会把所有执行的语句都输出到日志文件中,配置如下:log_destination = 'csvlog'
log_directory = 'pg_logs'
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = 1d
log_statement = 'all'
按照上面的配置,日志会输出到$PGDATA/pg_logs/postgresql-YYYY-MM-DD.log
文件中,一天一个文件。
例如,我用redraiment
用户在postgres
数据库中执行了三条SQL,日志中得到结果如下:
2018-07-09 22:13:57.746 CST,"redraiment","postgres",11009,"[local]",5b436da3.2b01,1,"",2018-07-09 22:13:55 CST,3/9,0,日志,00000,"语句: create table users (id bigserial primary key, name text);",,,,,,,,,"psql"
2018-07-09 22:13:58.969 CST,"redraiment","postgres",11009,"[local]",5b436da3.2b01,2,"",2018-07-09 22:13:55 CST,3/10,0,日志,00000,"语句: insert into users (name) values ('Joe'), ('Alice'), ('Wiki');",,,,,,,,,"psql"
2018-07-09 22:14:00.546 CST,"redraiment","postgres",11009,"[local]",5b436da3.2b01,3,"",2018-07-09 22:13:55 CST,3/11,0,日志,00000,"语句: select * from users;",,,,,,,,,"psql"
因为问题描述中没有提到数据模型,我假设alarm
表与alarm_date
表的结构如下:
create table alarm(
id serial primary key,
description text,
hits int
);
comment on column alarm.hits is '告警的次数';
create table alarm_date (
id serial primary key,
aid int references alarm,
create_time timestamp
);
假设alarm与alarm_date是“one to many”的关系,则
查询2018-11-11之后发生的告警,并按告警次数倒序的查询SQL如下:
select
id,
description,
hits
from
alarm
inner join (
select
distinct
aid
from
alarm_date
where
create_time > '2018-11-11'
) as records
on
alarm.id = records.aid
order by
hits desc
猜测md5_wp_cur
这个字段的类型是varchar(32)
,所以代码中array[md5_wp_cur]::varchar[]
转换成非32位varchar的数组时,会有类型不匹配的问题。可以改成:
...
array[md5_wp_cur]::varchar(32)[],
...
(path || g.md5_wp_cur)::varchar(32)[],