如何从当前和历史记录中创建时间表？

发布于 2025-02-08 13:32:16 字数 1068 浏览 2 评论 0原文

假设我有一个看起来像这样的当前表：

location   status     price
A          sold       3

并且我有一个看起来这样的历史表：

location   field     oldval_str   newval_str   oldvar_num   newval_num   created_at
A          status    closed       sold         null         null         2022-06-01
A          status    listed       closed       null         null         2022-05-01
A          status    null         listed       null         null         2022-04-01
A          price     null         null         null         1            2022-04-01
A          price     null         null         1            2            2022-05-01
A          price     null         null         2            3            2022-06-01

如何在纯SQL中构建一个诸如以下内容的时间表？

location   status     price   created_at
A          listed     1       2022-04-01
A          closed     2       2022-05-01
A          sold       3       2022-06-01

我正在使用postgresdb是方言帮助！

我知道我可以在Python中这样做，我坚持使用历史记录的字段动态命名列。想找到一个所有SQL解决方案。有任何资源要指向吗？

原文

Let's say I have a current table that looks like this:

location   status     price
A          sold       3

and I have a history table that looks as such:

location   field     oldval_str   newval_str   oldvar_num   newval_num   created_at
A          status    closed       sold         null         null         2022-06-01
A          status    listed       closed       null         null         2022-05-01
A          status    null         listed       null         null         2022-04-01
A          price     null         null         null         1            2022-04-01
A          price     null         null         1            2            2022-05-01
A          price     null         null         2            3            2022-06-01

How can I build a temporal table such as the following in pure SQL?

location   status     price   created_at
A          listed     1       2022-04-01
A          closed     2       2022-05-01
A          sold       3       2022-06-01

I am using a PostgresDB is dialect helps!

I know I can do this in Python, I'm stuck on calling dynamically named columns using the field from history. Would like to find an all SQL solution. Any resources to point to?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

墨洒年华 2025-02-15 13:32:16

您可以使用这样的查询

SELECT
    l.location,
    MAX(h.newval_str) FILTER (WHERE h.field = 'status') AS status,
    MAX(h.newval_num) FILTER (WHERE h.field = 'price') AS price,
    h.created_at
FROM locations l
LEFT JOIN history h ON h.location = l.location
GROUP BY l.location, h.created_at
ORDER BY h.created_at

，请检查

0cefca4bf2c42e620efca0aCC0777154D位置表与历史表连接在一起，以获取所有可用的位置行，即使是从未更改的值和历史记录表不包含周围的任何行。

如果您不需要它，只需单独使用历史表即可。

 SELECT
    location,
    MAX(newval_str) FILTER (WHERE field = 'status') AS status,
    MAX(newval_num) FILTER (WHERE field = 'price') AS price,
    created_at
FROM history
GROUP BY location, created_at
ORDER BY created_at

You can use a query like this

SELECT
    l.location,
    MAX(h.newval_str) FILTER (WHERE h.field = 'status') AS status,
    MAX(h.newval_num) FILTER (WHERE h.field = 'price') AS price,
    h.created_at
FROM locations l
LEFT JOIN history h ON h.location = l.location
GROUP BY l.location, h.created_at
ORDER BY h.created_at

Please, check a demo

Here the locations table is joined with the history table to get all of the available location rows, even those which values was not ever changed, and the history table does not contain any row about them.

If you do not need it, just use the history table alone.

 SELECT
    location,
    MAX(newval_str) FILTER (WHERE field = 'status') AS status,
    MAX(newval_num) FILTER (WHERE field = 'price') AS price,
    created_at
FROM history
GROUP BY location, created_at
ORDER BY created_at

回复收藏 0 原文

~没有更多了~