问题描述

表 tb_healthy 记录病人的就诊历史，每个病人有多条记录，Visit_code 为 Surgery 表示手术，为 Office 表示咨询；有的病人只有手术，有的只有咨询。现在要判断两件事：这个病人有没有咨询过；这个病人有没有在手术之后 6 个月内咨询过。前者写入 Office_Visit，后者写入 SX_past_6mo，有记 1，没有记 0。

源数据

期望结果

Office_Visit 表示有咨询记录，SX_past_6mo 表示手术后 6 个月内有咨询记录：

A30 手术在 2004-05-15，咨询在 2005-02-05，相隔 8 个多月，超出了 6 个月，所以 Office_Visit 是 1 而 SX_past_6mo 是 0，可见 "有咨询" 和 "手术后 6 个月内有咨询" 是两件事。

C01 有两条手术记录和两条咨询记录，只有 2001-05-15 的手术与 2001-11-01 的咨询配成了 6 个月内的组合，所以 SX_past_6mo 记 1；C02 只有手术没有咨询、C03 只有咨询没有手术，两个标记都记 0。

SQLazy 分步实现

这道题要同时回答两个问题，适合把两件事分开算、最后再合起来。先把字符串日期转成真正的日期类型，再把手术记录和咨询记录各筛一份出来。"有没有咨询过" 用汇总功能按病人分组取最大值就能得到。"手术后 6 个月内有没有咨询过" 要靠拼接功能把咨询记录挂到手术记录上，挂的条件就是 "咨询日期不早于手术日期，且不晚于手术日期加 6 个月"，这是一个跨表的区间条件，正好用得上拼接功能的非等值条件参数。配成对的记录汇总一下就有结果，没配上的病人在最后合并时补 0。

【点击在线运行本例】

下面逐一解释这些步骤。

第 1 步：把字符串日期转成日期类型

compute date(Visit_Date; format "M/d/yyyy") as dt

Visit_Date 是 "5/15/2004" 这样的字符串，不能直接比大小。用 date 函数按格式 "M/d/yyyy" 解析成日期类型，写入新列 dt。这一步是后面所有日期比较的前提。

第 2 步：把手术记录和咨询记录各筛一份

filter Visit_code="Surgery"

filter Visit_code="Office"

两份记录后面要分别用于不同的计算：手术记录当拼接的基准表，咨询记录当被挂上去的关联表。比较用的是符号 "="，不是 SQL 里的 "=="。

第 3 步：先回答 "有没有咨询过"
summarize max if(Visit_code=“Office” then 1 else 0) as Office_Visit; group Id
汇总功能按 Id 分组，组内对条件式 if(Visit_code=“Office” then 1 else 0) 取最大值：组里只要有一条咨询记录就会算出 1，一条都没有则全是 0。聚合算法 max 写在被聚合项之前，结果命名为 Office_Visit。

第 4 步：把 6 个月内的咨询挂到手术上
join Id; with t_off; Id; take dt as off_dt; inner; condition ((dt>=dt@) and (dt<=(dt@ elapse 6 month)))
这是本题的核心一步。拼接功能以手术表 t_sx 为基准表，按 Id 关联咨询表 t_off，把咨询日期 dt 挂上来并改名为 off_dt。
条件是跨表的区间判断，写在条件参数里：带 @ 的 dt@ 是基准表（手术）的日期，不带 @ 的 dt 是关联表（咨询）的日期，整个条件读作 "咨询日期不早于手术日期，且不晚于手术日期往后 6 个月"。往后推 6 个月用偏移函数写成 (dt@ elapse 6 month)。
注意：条件式里引用关联表字段要用它在本表里的原名 dt，不能用改名后的 off_dt；内连接会把配不上窗口的手术记录丢掉，剩下的就是配成对的记录。

第 5 步：把配成对的记录汇总成标记
summarize max 1 as SX_past_6mo; group Id
按 Id 分组，组内对常数 1 取最大值。配成过对的病人至少有一行记录，算出 1；一个对都没配成的病人不会出现在这张表里，这正是后面要补 0 的原因。这里聚合的是常数 1 而不是某个字段，因为需要的信息只有 "有没有配上"，不需要具体数值。

第 6 步：把两个结果合并起来
join Id; with t_sx_yes; Id; take SX_past_6mo
以第 3 步的结果为主表，用左连接把第 5 步的标记挂上去。左连接保证没有配成对的病人也留在结果里，只是 SX_past_6mo 为空。这里不写连接方式，缺省就是左连接。

第 7 步：空值补 0
compute nvl(SX_past_6mo,0) as SX_past
用 nvl 把空值换成 0，命名成 SX_past，这样输出里的标记都是 0 或 1。最后一步用导出表挑出需要的三列：病人 Id、有没有咨询 Office_Visit、手术后 6 个月内有没有咨询 SX_past。

最后选取需要输出的字段。

编译生成 SQL

确认上述 7 步逻辑后，SQLazy 编译器自动生成原生 SQL（这里是 Oracle 语法）：

WITH t1 AS (
    SELECT Id, Visit_Date, Visit_code
      , TO_DATE(Visit_Date, 'M/d/yyyy') AS dt
    FROM tb_healthy
  ),
  t_results AS (
    SELECT Id
      , MAX(CASE
        WHEN Visit_code = 'Office' THEN 1
        ELSE 0
      END) AS Office_Visit
    FROM t1
    GROUP BY Id
  ),
  t_off AS (
    SELECT Id, Visit_Date, Visit_code, dt
    FROM t1
    WHERE Visit_code = 'Office'
  ),
  t_sx_yes AS (
    SELECT Id, MAX(1) AS SX_past_6mo
    FROM (
      SELECT t_sx.Id, t_sx.Visit_Date, t_sx.Visit_code, t_sx.dt, t_off.dt AS off_dt
      FROM t1
        INNER JOIN t_off
        ON t_sx.Id = t_off.Id
          AND ((t_off.dt >= t_sx.dt)
            AND (t_off.dt <= ADDMONTHS(t_sx.dt, 6)))
      WHERE t_sx.Visit_code = 'Surgery'
    ) t_2
    GROUP BY Id
  ),
  t_merged AS (
    SELECT t_results.Id, t_results.Office_Visit, t_sx_yes.SX_past_6mo
    FROM t_results
      LEFT JOIN t_sx_yes ON t_results.Id = t_sx_yes.Id
  ),
  t_final AS (
    SELECT Id, Office_Visit, SX_past_6mo
      , COALESCE(SX_past_6mo, 0) AS SX_past
    FROM t_merged
  )
SELECT Id, Office_Visit, SX_past
FROM t_final
ORDER BY Id

SQLazy 让你用业务语言描述逻辑，而不是用 SQL 语法写嵌套查询。这道题要同时回答 "有没有咨询过" 和 "手术后 6 个月内有没有咨询过"，SQLazy 把它们拆成两组各自独立的步骤，先分别算清楚，最后合并。拼接功能的非等值条件参数是关键："咨询日期落在手术日期之后的 6 个月内" 本来是跨表的区间判断，SQLazy 用 @ 后缀区分两张表的字段，区间条件直接写在拼接语句里，不必先做笛卡尔积再过滤。汇总功能让 "有没有" 这类问题变得很短，组内取最大值就能得到 0 或 1，不必先筛记录再判断是否存在。每一步都是一张可以单独查看的中间结果表，出错时能立刻看出是哪一步的问题。

官方链接

SQLazy 在线体验：sqlazy.com（免费，无需注册）

SQLazy 项目仓库：github.com/SPLWare/SQLazy