我有一个表，每当位置的分数发生变化时，该表就会记录一行。
score_history：
id int PK（UUID自动递增int）
时间戳（发生变化时）
location_id int FK（值用于的位置）
分数浮动（新分数）
这样做是出于效率的考虑，并且能够简单地检索给定位置的更改列表并很好地实现了该目的。
我正在尝试以非常冗余的格式输出数据，以帮助将其加载到严格的外部系统中。外部系统希望每个位置*每个日期都有一行。目标是代表每个日期每个位置的最后得分值。因此，如果分数在给定日期中更改了3次，则只有最接近午夜的分数才被视为该位置当天的分数。我想这类似于创建关闭业务库存级别事实表的挑战。
我有一个方便的星形模式样式日期维表，其中每个日期都有一行，完全覆盖了此示例期间以及未来的日期。
那张桌子看起来像
dw_dim_date：
日期日期PK
一堆其他列，例如星期数，is_us_holiday等。
因此，如果我在score_history表中只有3条记录...
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
所需的输出将是：
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要求：
即使该天没有分数记录，每个位置每天也要排一行。
如果当天有分数记录，则午夜之前的最后一个应该是该行的分数值。如果出现平局，则两者中的较大者应“获胜”。
如果当天的分数记录为零，则分数应为最近的先前分数。
我一直在通过子查询和窗口函数来追踪自己的尾巴。
因为我不愿意发布没有任何内容的东西，所以我将分享这个火车残骸，它会产生输出，但没有任何意义...
SELECT dw_dim_date.date,
(SELECT score
FROM score_history
WHERE score_history.happened_at::DATE '2019-06-01'
感谢您提供指导或其他问题的阅读指南。
您可以通过使用相关子查询和实现此目的LATERAL：
SELECT sub.date, sub.location_id, score
FROM (SELECT * FROM dw_dim_date
CROSS JOIN (SELECT DISTINCT location_id FROM score_history) s
WHERE date >= '2019-01-01'::date) sub
,LATERAL(SELECT score FROM score_history sc
WHERE sc.happened_at::date <= sub.date
AND sc.location_id = sub.location_id
ORDER BY happened_at DESC LIMIT 1) l
,LATERAL(SELECT MIN(happened_at::date) m1, MAX(happened_at::date) m2
FROM score_history sc
WHERE sc.location_id = sub.location_id) lm
WHERE sub.date BETWEEN lm.m1 AND lm.m2
ORDER BY location_id, date;
db <> fiddle演示
怎么运行的：
1）s（这是每个location_id的所有日期的交叉联接）
2）l（按位置选择分数）
3）lm（选择每个位置的最小/最大日期进行过滤）
4）WHERE在可用范围内过滤日期，如有需要可以放宽日期
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有