时间戳

首页 > 解决方案 > 两个查询在它们应该相等时返回不同的结果？

sql - 两个查询在它们应该相等时返回不同的结果？

问题描述

我们的数据集从根本上将一组日期（从当前一周到过去的几周）加入一组部分，具体取决于这些部分是在该周开始还是在该周之前开始，在该周结束还是在该周之后结束。虽然最初这个查询给了我们预期的结果，但本周它开始给我们提供不正确的结果。经过一番修改，我们发现如果我们将查询更改为 aLEFT JOIN然后使用WHERE子句过滤查询，它会再次给我们正确的结果。

有什么不同？为什么一个有效而另一个无效？（奖励点：为什么原始查询工作了数周才突然遇到此错误？）在 Redshift 上执行相同的内部联接可提供正确的结果，因此这似乎是我们不理解的雪花细微差别。

原始查询：

WITH week_list AS
(
    SELECT DATEADD(week, -4, DATE_TRUNC(week, CURRENT_DATE())) AS week_value

    UNION ALL

    SELECT DATEADD(week, 1, week_value)
    FROM week_list
    WHERE DATEADD(week, 1, week_value) < CURRENT_DATE()
),
active_sections_per_week AS
(
    SELECT 
        wl.week_value, s.id section_id
    FROM week_list wl
    JOIN schema.sections s ON wl.week_value >= DATE_TRUNC(week, s.starts_at)
                           AND wl.week_value <= DATE_TRUNC(week, s.ends_at)
)
SELECT 
    aspw.week_value,
    COUNT(DISTINCT aspw.section_id) count_sections
FROM 
    active_sections_per_week aspw
GROUP BY 1
ORDER BY 1 DESC

结果：一行，日期为 2019-12-30（4 周前）。过去三周没有数据。

注意：如果您DATEADD在第一个 CTE 中调整，无论返回的第一个日期是什么，似乎总是会成功加入。此行为仅在上周开始 - 以前，此查询提供了预期的行数（换句话说，在 first 中指定的周数DATEADD）。

“固定”查询：

WITH week_list AS
(
    SELECT DATEADD(week, -4, DATE_TRUNC(week, CURRENT_DATE())) AS week_value

    UNION ALL

    SELECT DATEADD(week, 1, week_value)
    FROM week_list
    WHERE DATEADD(week, 1, week_value) < CURRENT_DATE()
),
active_sections_per_week AS
(
    SELECT wl.week_value, s.id section_id
    FROM week_list wl
    LEFT JOIN schema.sections s ON wl.week_value >= DATE_TRUNC(week, s.starts_at)
                                AND wl.week_value <= DATE_TRUNC(week, s.ends_at)
    WHERE s.id IS NOT NULL
)
SELECT aspw.week_value, COUNT(DISTINCT aspw.section_id) count_sections
FROM active_sections_per_week aspw
GROUP BY 1
ORDER BY 1 DESC

结果：返回四行，日期为 2019 年 12 月 30 日至 2020 年 1 月 20 日的周数，并具有适当的节数。

标签： sqlinner-joinsnowflake-cloud-data-platform

解决方案

这是“week_list”上的递归 CTE。Redshift 不支持递归 CTE。

Snowflake 确实支持递归 CTE，这可以解释行为上的差异。

如果没有基础数据，很难对此进行测试。如果您在 Redshift 中得到正确的结果，那么您可能不需要或不想要递归 CTE。您可以对其进行修改，以使“week_list”不引用自身。

至于为什么它之前起作用，很可能是表状态和递归 CTE 仅在特殊情况下起作用。当 CURRENT_DATE() 推进时，它就摆脱了这种特殊情况。此外，如果不在递归 CTE 中，则s.id IS NOT NULL 的内连接和左外连接将是等效的。

您可以在此处阅读有关递归 CTE 的更多信息：

https://docs.snowflake.net/manuals/user-guide/queries-cte.html#recursive-ctes-and-hierarchical-data

推荐阅读