如何用Oracle SQL关联日期表与交易表生成每日交易记录？

需求说明 #

将日期表Dates与交易表Deals关联，生成每个交易在其StartDate到EndDate范围内的每日记录，每条日期对应所属交易的编号与金额。

表结构与数据 #

Dates表 #

Date
01-Jan-22
02-Jan-22
03-Jan-22
04-Jan-22
04-Jan-22
05-Jan-22

Deals表 #

DealNo.       StartDate         EndDate           Amount
1ahk          02-Jan-22         04-Jan-22         10,000
1hyt          03-Jan-22         05-Jan-22          5,000
5tiu          01-Jan-22         03-Jan-22          8,000

期望输出 #

Date            DealNo.       Amount
02-Jan-22       1ahk          10,000
03-Jan-22       1ahk          10,000
04-Jan-22       1ahk          10,000
03-Jan-22       1hyt           5,000
04-Jan-22       1hyt           5,000
05-Jan-22       1hyt           5,000
01-Jan-22       5tiu           8,000
02-Jan-22       5tiu           8,000
03-Jan-22       5tiu           8,000

实现SQL #

基础版本（保留Dates表重复日期） #

SELECT
    d."Date",
    dl."DealNo.",
    dl."Amount"
FROM
    "Dates" d
CROSS JOIN
    "Deals" dl
WHERE
    d."Date" BETWEEN dl."StartDate" AND dl."EndDate"
ORDER BY
    dl."DealNo.",
    d."Date";

去重版本（去除Dates表重复日期） #

如果需要避免因Dates表重复日期导致的结果重复，先对日期表去重后再关联：

SELECT
    d."Date",
    dl."DealNo.",
    dl."Amount"
FROM
    (SELECT DISTINCT "Date" FROM "Dates") d
CROSS JOIN
    "Deals" dl
WHERE
    d."Date" BETWEEN dl."StartDate" AND dl."EndDate"
ORDER BY
    dl."DealNo.",
    d."Date";

逻辑说明 #

通过CROSS JOIN生成两张表的笛卡尔积，再利用BETWEEN条件筛选出落在交易有效期内的日期记录，最终按交易编号和日期排序得到目标结果。

内容的提问来源于stack exchange，提问作者Talch