sql – 数据库：选择最后一个非空条目

part | timestamp | x-pos | y-pos | status
------+-----------+-------+-------+--------
 a5   |       151 |     5 |    15 |      g
 a5   |       153 |  NULL |    17 |   NULL

(部分时间戳)是主键.第二条记录中的NULL表示自第一条记录以来不变的值.

我想要做的是选择由零件分组的每个字段的最新值.例如,给定上述条目,结果将为153,5,17,g部分a5.

到目前为止,我已经把这个黑客一起查询了.

((SELECT x-pos FROM part_changes WHERE x-pos IS NOT NULL
    ORDER BY timestamp DESC
    LIMIT 1)

    UNION

    (SELECT y-pos FROM part_changesWHERE y-pos IS NOT NULL
    ORDER BY timestamp DESC
    LIMIT 1)

    UNION

    (SELECT status FROM part_changes WHERE status IS NOT NULL
    ORDER BY timestamp DESC
    LIMIT 1))

但是这返回一个列,这意味着我可以使用group-by来组织.

必须做一个更优雅的做事方式,比如以创造性的方式使用COALESCE或IS NULL.但我被困住了,无法想像出来.有人有想法吗？

不,我不能改变数据库结构.

编辑：ruakh有正确的想法.现在唯一的问题是逐个分组.我似乎无法绕过LIMIT 1分组多个部分.有任何想法吗？

mdahlman,我不太熟悉postgresql中的分析函数.所以,如果这个解决方案比一个复杂的查询更容易,那么一切都会发布你的想法.

编辑2：感谢大家的帮助.我认为我已经很好地掌握了我需要做的事情.

解决方法

例如：

SELECT part,( SELECT x_pos
           FROM part_changes
          WHERE part = pc.part
            AND x_pos IS NOT NULL
          ORDER
             BY timestamp DESC
          LIMIT 1
       ) AS x_pos,( SELECT y_pos
           FROM part_changes
          WHERE part = pc.part
            AND y_pos IS NOT NULL
          ORDER
             BY timestamp DESC
          LIMIT 1
       ) AS y_pos,( SELECT status
           FROM part_changes
          WHERE part = pc.part
            AND status IS NOT NULL
          ORDER
             BY timestamp DESC
          LIMIT 1
       ) AS status
  FROM ( SELECT DISTINCT
                part
           FROM part_changes
       ) AS pc
;

但是在这一点上,我真的会考虑编写一个存储过程.

或者：

SELECT DISTINCT
       part,FIRST_VALUE(x_pos) OVER
         ( PARTITION BY part
               ORDER BY CASE WHEN x_pos IS NULL
                             THEN NULL
                             ELSE TIMESTAMP
                         END DESC NULLS LAST
         ) AS x_pos,FIRST_VALUE(y_pos) OVER
         ( PARTITION BY part
               ORDER BY CASE WHEN y_pos IS NULL
                             THEN NULL
                             ELSE TIMESTAMP
                         END DESC NULLS LAST
         ) AS y_pos,FIRST_VALUE(status) OVER
         ( PARTITION BY part
               ORDER BY CASE WHEN status IS NULL
                             THEN NULL
                             ELSE TIMESTAMP
                         END DESC NULLS LAST
         ) AS status
  FROM part_changes
;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!