sql - 遍历表中的组

Question

我有以下数据:

cte1
===========================
m_ids    |p_id       |level
---------|-----------|-----
{123}    |98         |1
{123}    |111        |2
{432,222}|215        |1
{432,222}|215        |1
{432,222}|240        |2
{432,222}|240        |2
{432,222}|437        |3
{432,222}|275        |3

我必须执行以下操作:

1. 通过以下算法提取p_id

   1. 对于具有相同m_ids 的每一行
   2. 在每组中:
      2.我。按 p_id
      对记录进行分组2.二。按 level
      排序 desc 记录2.III.选择 p_id 精确计数为 m_ids 长度且具有最大的 level

到目前为止，我没能完全写出这个算法，但我在它的最后一部分写了这个(可能在我得到 array_length 的地方是错误的):

SELECT id 
FROM   grouped_cte1 
GROUP  BY id, 
          level 
HAVING Count(*) = array_length(grouped_cte1.m_ids, 1) 
ORDER  BY level DESC 
LIMIT  1 

m_ids={123} 的 grouped_cte1 是

m_ids    |p_id       |level
---------|-----------|-----
{123}    |98         |1
{123}    |111        |2

m_ids={432,222} 是

m_ids    |p_id       |level
---------|-----------|-----
{432,222}|215        |1
{432,222}|215        |1
{432,222}|240        |2
{432,222}|240        |2
{432,222}|437        |3
{432,222}|275        |3

等等

2) 将 p.1 中的查询与以下内容结合起来。以下提取每个 m_ids 的 p_id 和 level=1:

select m_ids, p_id from cte1 where level=1 --also selecting m_ids for joining later`

结果如下:

m_ids    |p_id
---------|----
{123}    |98  
{432,222}|215   

理想的结果:

m_ids    |result_1   |result_2
---------|-----------|--------
{123}    |111        |98
{432,222}|240        |215

那么谁能帮我解决算法的第一部分，然后(可选)将它与第二部分合并到一个查询中？

编辑:到目前为止我失败了:
1. 在遍历它的同时，通过m_ids将呈现的表分成子表。
2. 对查询中对应的行进行array_length(grouped_cte1.m_ids, 1)的计算。

Answer 1

对于查询的第一部分，您在正确的轨道上，但您需要更改分组逻辑，然后再次加入表以按每个 m_ids 的最高级别过滤掉它，您可以使用 DISTINCT ON 子句结合适当的排序:

select 
  distinct on (t.m_ids)
  t.m_ids, t.p_id, t.level
from cte1 t
join (
  select
    m_ids,
    p_id
  from cte1
  group by m_ids, p_id
  having count(*) = array_length(m_ids, 1)
  ) as g using (m_ids, p_id)
order by t.m_ids, t.level DESC;

这会给你:

   m_ids   | p_id | level
-----------+------+-------
 {123}     |  111 |     2
 {432,222} |  240 |     2

然后当与第二个查询结合时(使用 FULL JOIN 用于显示目的，当第一个查询缺少此类条件时)我通过添加 distinct 进行了修改，因为可以(事实上是) m_ids, p_id 的多个记录与第一级配对它看起来像:

select 
  coalesce(r1.m_ids, r2.m_ids) as m_ids,
  r1.p_id AS result_1,
  r2.p_id AS result_2
from (
  select 
    distinct on (t.m_ids)
    t.m_ids, t.p_id, t.level
  from cte1 t
  join (
    select
      m_ids,
      p_id
    from cte1
    group by m_ids, p_id
    having count(*) = array_length(m_ids, 1)
    ) as g using (m_ids, p_id)
  order by t.m_ids, t.level DESC
  ) r1
full join (
  select distinct m_ids, p_id
  from cte1
  where level = 1
  ) r2 on r1.m_ids = r2.m_ids

给你结果:

   m_ids   | result_1 | result_2
-----------+----------+----------
 {123}     |      111 |       98
 {432,222} |      240 |      215

这看起来与您的预期不同，但根据我对逻辑的理解，它是正确的。如果我误解了什么，请告诉我。

---

只是为了逻辑说明，一点:

为什么带有 {123} 的 m_ids 为 result_1 返回 111？

• 对于 m_ids = {123} 组，我们有两个不同的 p_id 值
• 98 和111 都考虑了与m_ids 长度相等计数的条件
• p_id = 111 具有更高的级别，因此被选为 result_1