如何将OUTER APPLY改写为JOIN等其他语法以适配Blazing SQL测试

以下方案完全移除了APPLY算子，逻辑和原查询完全一致，同时优化了Table2的扫描次数，更适配GPU并行计算特性：

SELECT IIF (t2_agg.TargetMinTime <= t2_agg.CLMinTime, T.[target], C.[CL])  
FROM [defaultDB].[dbo].[Table1] AS M
-- 保留原阈值列表笛卡尔积
JOIN(VALUES(0.003), (0.0035), (0.004), (0.0045), (0.005), (0.0055), (0.006), (0.0065), (0.007), (0.0075), (0.008)) AS T([target])
    ON 1 = 1
JOIN(VALUES(-0.003), (-0.0035), (-0.004), (-0.0045), (-0.005), (-0.0055), (-0.006), (-0.0065), (-0.007), (-0.0075), (-0.008), (-0.0085), (-0.009), (-0.0095), (-0.01), (-0.0105), (-0.011), (-0.0115)) AS C([CL])
    ON 1 = 1
-- 预聚合Table2所有阈值组合的最小时间，替代两次OUTER APPLY
LEFT JOIN (
    SELECT
        [date],
        [Start],
        [Time],
        target_val,
        cl_val,
        MIN(CASE WHEN [H] > target_val THEN [time] END) AS TargetMinTime,
        MIN(CASE WHEN [L] < cl_val THEN [time] END) AS CLMinTime
    FROM [defaultDB].[dbo].[Table2]
    -- 提前关联阈值列表，一次扫描完成所有计算
    CROSS JOIN (
        SELECT [target] AS target_val FROM (VALUES(0.003), (0.0035), (0.004), (0.0045), (0.005), (0.0055), (0.006), (0.0065), (0.007), (0.0075), (0.008)) AS t([target])
    ) targets
    CROSS JOIN (
        SELECT [CL] AS cl_val FROM (VALUES(-0.003), (-0.0035), (-0.004), (-0.0045), (-0.005), (-0.0055), (-0.006), (-0.0065), (-0.007), (-0.0075), (-0.008), (-0.0085), (-0.009), (-0.0095), (-0.01), (-0.0105), (-0.011), (-0.0115)) AS c([CL])
    ) cls
    GROUP BY [date], [Start], [Time], target_val, cl_val
) t2_agg
ON t2_agg.[date] = M.[DATE]
AND t2_agg.[Start] >= M.Stime
AND t2_agg.[Time] <= M.etime
AND t2_agg.target_val = T.target
AND t2_agg.cl_val = C.CL
WHERE [M].[date] IN(20120307, 20120601, 20121109, 20130826, 20131002, 20140117, 20140122, 20140311, 20140529, 20140718, 20150619, 20151014, 20151022, 20160411, 20160516, 20160721, 20160818, 20160909, 20170127, 20170213, 20170921, 20171025, 20171229, 20180116, 20180315, 20180926, 20181022, 20181128, 20181211, 20190104, 20190329, 20190502, 20190521, 20190528, 20190611, 20190627, 20190823, 20190930, 20191104, 20191211, 20200214, 20200318, 20200529, 20200706, 20200828, 20201230, 20210112, 20210305, 20210318, 20210408, 20210525, 20210617, 20210625)
  AND [Stime] >= 133000;

• 原查询两次OUTER APPLY相当于对Table2做两次逐行关联扫描，改为预聚合方案后仅需扫描一次Table2，更适配Blazing SQL的GPU并行计算特性，性能会优于直接替换APPLY为LEFT JOIN的写法
• 所有逻辑和原查询完全对齐，返回结果无差异

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