如何关联不同日期维度的tblDaily与tblWeekly表？

嘿，这个场景我太熟了！之前帮公司处理过类似的跨表日期关联需求，刚好可以给你详细唠唠～

先把你给的示例表数据整理得更清楚些：

示例表数据 #

tblDaily（每日数据）

tblWeekly（仅周五数据）

这里给你两种实用方案，根据你的数据量和维护需求选就行：

方案1：纯日期计算关联（零额外表，快速上手） #

这种方案不用建新表，直接靠数据库的日期函数把每日数据的日期转换为最近的上一个周五，再和tblWeekly关联就行，适合数据量不算特别大、不想折腾额外表的场景。

给你几个主流数据库的实现代码：

MySQL/MariaDB #

SELECT 
    d.date AS daily_date,
    d.val AS daily_val,
    w.date AS weekly_date,
    w.val AS weekly_val
FROM tblDaily d
LEFT JOIN tblWeekly w 
    ON w.date = DATE_SUB(d.date, INTERVAL (WEEKDAY(d.date) + 2) % 7 DAY);

解释：WEEKDAY()返回0=周一、4=周五，(WEEKDAY(d.date)+2)%7算出来的就是当前日期到上周五的天数差，比如周三（WEEKDAY=2），减4天刚好到上周五。

PostgreSQL #

SELECT 
    d.date AS daily_date,
    d.val AS daily_val,
    w.date AS weekly_date,
    w.val AS weekly_val
FROM tblDaily d
LEFT JOIN tblWeekly w 
    ON w.date = d.date - ((EXTRACT(DOW FROM d.date) + 2) % 7) * INTERVAL '1 day';

解释：EXTRACT(DOW FROM date)返回0=周日、5=周五，公式逻辑和MySQL类似，算出差值后减去对应天数就能得到上周五。

SQL Server #

SELECT 
    d.date AS daily_date,
    d.val AS daily_val,
    w.date AS weekly_date,
    w.val AS weekly_val
FROM tblDaily d
LEFT JOIN tblWeekly w 
    ON w.date = DATEADD(day, -((DATEPART(weekday, d.date) + 1) % 7), d.date);

解释：SQL Server默认DATEPART(weekday)返回1=周日、6=周五，通过公式算出需要减去的天数，就能匹配到最近的上周五。

方案2：窗口函数关联（应对特殊场景） #

如果tblWeekly偶尔会有周五数据缺失的情况，需要关联最近的存在的周五数据，那窗口函数的方案更稳妥：

SELECT 
    d.date AS daily_date,
    d.val AS daily_val,
    w.date AS weekly_date,
    w.val AS weekly_val
FROM (
    SELECT 
        *,
        -- 找到每个每日日期之前最近的已存在的周五日期
        MAX(w.date) OVER (PARTITION BY d.date ORDER BY w.date DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS nearest_fri
    FROM tblDaily d
    CROSS JOIN tblWeekly w
    WHERE w.date <= d.date
) sub
LEFT JOIN tblWeekly w ON sub.nearest_fri = w.date;

不过这个方案因为会先做笛卡尔积再计算，数据量大的时候性能会差一些，更适合tblWeekly数据很少的情况。

当然适用！而且如果你的数据量很大，或者未来可能有更多类似的日期关联需求（比如关联上月末、季度末），日历表绝对是一劳永逸的选择。

落地步骤： #

1. 创建日历表 #

先建一个包含核心日期维度的表，至少要有date（日期）和last_friday（对应最近上一个周五），还可以加些扩展字段方便后续使用：

-- MySQL示例，其他数据库语法类似
CREATE TABLE calendar (
    date DATE PRIMARY KEY,
    last_friday DATE NOT NULL,
    is_friday TINYINT(1) NOT NULL COMMENT '1=周五，0=其他'
);

2. 填充日历表 #

用递归SQL或者存储过程批量填充日期范围，比如MySQL 8.0+支持的递归CTE：

WITH RECURSIVE date_range AS (
    SELECT '2018-01-01' AS date -- 起始日期
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM date_range
    WHERE date < '2030-12-31' -- 结束日期，按需调整
)
INSERT INTO calendar (date, last_friday, is_friday)
SELECT 
    date,
    DATE_SUB(date, INTERVAL (WEEKDAY(date) + 2) % 7 DAY) AS last_friday,
    CASE WHEN WEEKDAY(date) = 4 THEN 1 ELSE 0 END AS is_friday
FROM date_range;

这样就把2018到2030年的所有日期都填充好了，每个日期对应的上周五也预计算完成。

3. 关联查询 #

有了日历表之后，关联就超级简单，而且性能拉满（给last_friday建个索引更快）：

SELECT 
    d.date AS daily_date,
    d.val AS daily_val,
    w.date AS weekly_date,
    w.val AS weekly_val
FROM tblDaily d
JOIN calendar c ON d.date = c.date
LEFT JOIN tblWeekly w ON c.last_friday = w.date;

日历表的优势： #

• 性能高：预计算好的字段，关联时直接用索引匹配，百万级数据量下比实时计算日期快很多。
• 扩展性强：后续要加其他日期维度（比如最近的周一、上月最后一天），只需要在日历表加字段就行，不用改查询逻辑。
• 可读性好：查询语句一目了然，新人接手也能快速理解。

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