sql - 从仅在值更改时记录的表中推断每日历史值（PostgreSQL

我有一个表，每当位置的分数发生变化时，该表就会记录一行。

score_history：

• id int PK（UUID自动递增int）
• 时间戳（发生变化时）
• location_id int FK（值用于的位置）
• 分数浮动（新分数）

这样做是出于效率的考虑，并且能够简单地检索给定位置的更改列表并很好地实现了该目的。

I'm trying to output the data in a very redundant format to help load it into a rigid external system. The external system expects a row for each location * every date. The goal is to represent the last score value for each location for each date. So if the score changed 3 times in a given date only the score closest to midnight would be considered that locations closing score for the day. I imagine this is similar to the challenge of creating a close of business inventory level fact table.

I have a handy star schema style date dimension table which has a row for every date fully covering this sample period and well into the future.

That table looks like

dw_dim_date:

• date date PK
• a bunch of other columns like week number, is_us_holiday etc.

So, if I had only 3 records in the score_history table...

1, 2019-01-01:10:13:01, 100, 5.0
2, 2019-01-05:20:00:01, 100, 5.8
3, 2019-01-05:23:01:22, 100, 6.2

The desired output would be:

2019-01-01, 100, 5.0 
2019-01-02, 100, 5.0 
2019-01-03, 100, 5.0
2019-01-04, 100, 5.0 
2019-01-05, 100, 6.2

3 Requirements:

1. 即使该天没有分数记录，每个位置每天也要排一行。
2. 如果当天有分数记录，则午夜之前的最后一个应该是该行的分数值。如果出现平局，则两者中的较大者应“获胜”。
3. 如果当天的分数记录为零，则分数应为最近的先前分数。

我一直在通过子查询和窗口函数来追逐自己的尾巴。

因为我不愿意发布没有任何内容的东西，所以我将分享这个火车残骸，它会产生输出，但毫无意义...

SELECT dw_dim_date.date,
       (SELECT score 
        FROM score_history 
        WHERE score_history.happened_at::DATE < dw_dim_date.date 
           OR score_history.happened_at::DATE = dw_dim_date.date 
        ORDER BY score_history.id desc limit 1) as last_score
FROM dw_dim_date
WHERE dw_dim_date.date > '2019-06-01'

感谢您提供指导或其他问题的阅读指南。

查看更多