代码之家 › 专栏 › 技术社区 › ddd

如何在不多次连接自身的情况下透视表

pivot join postgresql

ddd · 技术社区 · 7 年前

ctrlr_err_hist 其中包含以下列:

visit_id
err_cd
err_name
m1_err_cnt
m2_err_cnt

的可能值 err_cd 是0,1,3。

现在,我想编写一个查询,使其具有相同的错误 visit_id 在同一排。例如,如果该表有以下记录:

visit_id    err_cd    err_name    m1_err_cnt
    1         0       encoder        500
    1         3       breakout       212
    2         1       obclose         45
    2         3       breakout       143

tchn_visit_hist 桌上 访问id . 如果没有任何err_cd的数据,它将为空。最终结果如下所示:

visit_id    encoder_cnt     breakout_cnt   obclose_cnt
    1         500              212           null
    2         null             143            45

select t.visit_id, t.door_id, enc.encoder_cnt, brk.breakout_cnt, ob.ob_close_cnt
from tchn_visit_hist t
left join (
    select m1_err_cnt as encoder_cnt, visit_id
    from ctrlr_err_hist  
    where err_cd = '0' 
) as enc 
on t.visit_id = enc.visit_id
left join (
    select m1_err_cnt as breakout_cnt, visit_id
    from ctrlr_err_hist  
    where err_cd = '3'
) as brk 
on t.visit_id = brk.visit_id
left join (
    select m1_err_cnt as ob_close_cnt, visit_id
    from ctrlr_err_hist  
    where err_cd = '1'
) as ob 
on t.visit_id = ob.visit_id

我想知道是否有更好、更有效的方法来实现这一点。

2 回复 | 直到 7 年前

S-Man 7 年前

demo: db<>fiddle

可以通过以下方式实现简单的枢轴:

SELECT
    visit_id,
    MIN(m1_err_cnt) FILTER (WHERE err_cd = 0) as encoder_cnt,
    MIN(m1_err_cnt) FILTER (WHERE err_cd = 1) as ob_close_cnt,
    MIN(m1_err_cnt) FILTER (WHERE err_cd = 3) as breakout_cnt
FROM
    ctrlr_err_hist
GROUP BY visit_id

按分组 visit_id FILTER 子句筛选应聚合的元素。在这种情况下,聚合只针对一个特殊的 err_cd .

(visit_id = 1, err_cd = 0) . 在这种情况下,您必须决定对几个值执行什么操作( SUM , MIN , MAX , AVG ,随便了)。因为您的示例包含不同的行,所以这无关紧要。

在那之后,你只需加入反对党 tchn_visit_hist 表:

SELECT t.visit_id, t.door_id, c.encoder_cnt, c.breakout_cnt, c.ob_close_cnt
FROM (
   SELECT
       visit_id,
       MIN(m1_err_cnt) FILTER (WHERE err_cd = 0) as encoder_cnt,
       MIN(m1_err_cnt) FILTER (WHERE err_cd = 1) as ob_close_cnt,
       MIN(m1_err_cnt) FILTER (WHERE err_cd = 3) as breakout_cnt
   FROM
       ctrlr_err_hist
   GROUP BY visit_id
) c
JOIN tchn_visit_hist t
ON (t.visit_id = c.visit_id)

dwir182 7 年前

你可以创造条件 Join

select 
  a.visit_id, 
  max(b.encoder_cnt) as encoder_cnt, 
  max(b.breakout_cnt) as breakout_cnt, 
  max(b.ob_close_cnt) as ob_close_cnt
from 
  tchn_visit_hist a
  left join(select 
              case when err_cd = '0' then m1_err_cnt end as encoder_cnt, 
              case when err_cd = '3' then m1_err_cnt end as breakout_cnt,
              case when err_cd = '1' then m1_err_cnt end as ob_close_cnt,
              case when err_cd = '0' then visit_id end as encoder_visit, 
              case when err_cd = '3' then visit_id end as breakout_visit,
              case when err_cd = '1' then visit_id end as ob_close_visit
            from
              tchn_visit_hist) b on case 
                                      when a.err_cd = '0' then a.visit_id = encoder_visit
                                      when a.err_cd = '3' then a.visit_id = breakout_visit
                                      when a.err_cd = '1' then a.visit_id = ob_close_visit
                                     end
group by
   a.visit_id
order by
   a.visit_id asc

这是 Demo