我有两张桌子:
日期:
| date |
|---|
| 2024-01-01 |
| 2024-01-02 |
| 2024-01-03 |
| 2024-01-04 |
| 2024-01-05 |
当前:
| business_date | id |
|---|---|
| 2024-01-01 | 1 |
| 2024-01-02 | 2 |
| 2024-01-04 | 4 |
| 2024-01-05 | 5 |
我希望在条件DATE=BERVICE_DATE的情况下连接这些表，但是在日期没有匹配的情况下，它将获得前一个日期的记录.因此，生成的表格将如下所示:
| business_date | id | date |
|---|---|---|
| 2024-01-01 | 1 | 2024-01-01 |
| 2024-01-02 | 2 | 2024-01-02 |
| 2024-01-02 | 2 | 2024-01-03 |
| 2024-01-04 | 4 | 2024-01-04 |
| 2024-01-05 | 5 | 2024-01-05 |
我该如何做到这一点呢？
SELECT
*
FROM
dates
CROSS JOIN LATERAL
(
SELECT
*
FROM
actual
WHERE
actual.business_date <= dates.date
ORDER BY
actual.business_date DESC
LIMIT
1
)
AS actual
或者...
WITH
actual_ranged AS
(
SELECT
*,
LEAD(business_date, 1, 'infinity')
OVER (
ORDER BY business_date
)
AS next_business_date
FROM
actual
)
SELECT
*
FROM
actual_ranged AS a
LEFT JOIN
dates AS d
ON d.date >= a.business_date
AND d.date < a.next_business_date