sql - 关于 max creation_time 和 group by 的 Postgres 查询帮助

Question

我有一个复杂的场景，我无法理解。所以我们有一个规范化的 Postgres 数据库。

图片一查询

select id, rule_id, status, risk_level, creationtime, cloudaccount_id, organization_id, description,
       reg_id, pro_id, function_id, category_id, group_id, resource_id

from risk_child

图2查询

select ser_id, creationtime, cloud_account_id, owner_id, reg_id, organization_id, pro_id, record_creation_time
       resource_id
from resource

挑战在于我们有一个名为 Rules 的字段，每个 Rule 都有多个 resource 我们想触发一个查询，它会给我们我们的 risk_child 表中的最新条目基于具有多个资源的单个规则。

我遇到过一个查询，但由于某种原因它不起作用。

select rc.id,rc.resource_id,max(rc.creationtime)as "create_date_time",rl.rule_tag,rc.status,rc.risk_level,
       rc.user_id,rc.cloudaccount_id,rc.organization_id,rc.description,r.region,
       p.provider,f.function_name,c.category_name,g.group_name,rc.signature_status
from risk_child rc, resource rs,category c, function f , g_by g,
     provider p, region r, rule rl,service s
where rc.resource_id = rs.resource_id and c.id  = rc .category_id and 
      rc.function_id = f.id and rc.group_id = g.id and rc.pro_id = p.id and rc.rule_id = rl.id and
      rc.reg_id = r.id and s.id = rs.ser_id and rc.rule_id >=145 and 
      (rc.creationtime in(select max(creationtime) from risk_child group by resource_id) or 
      rc.creationtime in (select max(creationtime) from risk_child group by rule_id))

group by rc.creationtime,rl.rule_tag,rc.status,rc.risk_level,
       rc.user_id,rc.cloudaccount_id,rc.organization_id,rc.description,r.region,
       p.provider,f.function_name,c.category_name,g.group_name,rc.id,rc.resource_id,rc.signature_status
order by rc.id asc

输出没有根据 risk_child 中的 creationtime 给我们最新的记录

Result of Query # 1

Result of Query # 2

Answer 1

如果我对你的问题的理解是正确的，你想要一个对多关系获取，其中只为每个源对象显示最新的目标对象。通常，GROUP BY 请求不适合这种情况。在我看来，引用聚合列的 GROUP BY 子句也没有意义。

但是 DISTINCT ON 使这变得相对容易:

SELECT DISTINCT ON(rs.resource_id) rs.resource_id, rc.creation_time, ...
FROM resource rs JOIN risk_child rc ON rs.resource_id = rc.resource_id ...
WHERE ...
ORDER BY rs.resource_id, rc. rc.creation_time DESC, ...

DISTINCT ON 子句确保每个资源在结果中只出现一次。为了使它始终是最新的风险子项，您必须按 creation_time 排序，但在这种情况下，Postgres 还要求您按 DISTINCT ON 中的列排序。