sql - SQL 中的自引用 CASE WHEN 子句

Question

我正在尝试将一些格式不正确的数据迁移到数据库中。数据来自 CSV，并首先加载到所有 varchar 列的暂存表中(因为我无法在这个阶段强制执行类型安全)。

数据可能看起来像

COL1     | COL2 | COL3
Name 1   |      |     
2/11/16  | $350 | $230
2/12/16  | $420 | $387
2/13/16  | $435 | $727
Name 2   |      |     
2/11/16  | $121 | $144
2/12/16  | $243 | $658
2/13/16  | $453 | $214

第一列是作为伪标题的公司名称的混合，以及与第 2 列和第 3 列数据相关的日期。我想通过创建“Brand”列开始转换数据 - 如果 Col2 为 NULL，则“StoreBrand”是 Col1 的值，否则是前一行的 StoreBrand。像这样:

COL1     | COL2 | COL3 | StoreBrand
Name 1   |      |      | Name 1
2/11/16  | $350 | $230 | Name 1
2/12/16  | $420 | $387 | Name 1
2/13/16  | $435 | $727 | Name 1
Name 2   |      |      | Name 2
2/11/16  | $121 | $144 | Name 2
2/12/16  | $243 | $658 | Name 2
2/13/16  | $453 | $214 | Name 2

我是这样写的:

SELECT 
    t.*,
    CASE
        WHEN t.COL2 IS NULL THEN COL1
        ELSE                     LAG(StoreBrand) OVER ()
    END AS StoreBrand
FROM
(
    SELECT
        ROW_NUMBER() OVER () AS i,
        *
    FROM
        Staging_Data
) t;

但数据库(在本例中为 postgres，但我们正在考虑替代方案，因此首选最多样化的答案)在 LAG(StoreBrand) 上阻塞，因为这是我正在创建的派生列。调用 LAG(Col1) 仅填充第一行的真实数据:

COL1     | COL2 | COL3 | StoreBrand
Name 1   |      |      | Name 1
2/11/16  | $350 | $230 | Name 1
2/12/16  | $420 | $387 | 2/11/16
2/13/16  | $435 | $727 | 2/12/16
Name 2   |      |      | Name 2
2/11/16  | $121 | $144 | Name 2
2/12/16  | $243 | $658 | 2/11/16
2/13/16  | $453 | $214 | 2/12/16

我的目标是 StoreBrand 列，它是下一个品牌名称之前所有日期值的 COL1 的第一个值:

COL1     | COL2 | COL3 | StoreBrand
Name 1   |      |      | Name 1
2/11/16  | $350 | $230 | Name 1
2/12/16  | $420 | $387 | Name 1
2/13/16  | $435 | $727 | Name 1
Name 2   |      |      | Name 2
2/11/16  | $121 | $144 | Name 2
2/12/16  | $243 | $658 | Name 2
2/13/16  | $453 | $214 | Name 2

当 Col2 和 Col3 为空时 StoreBrand 的值无关紧要 - 该行将作为转换过程的一部分被删除。重要的是将数据行(即带有日期的行)与其品牌相关联。

有没有办法为我丢失的列引用以前的值？

Answer 1

为通过搜索引擎找到此问题的人编辑:

诀窍是使用 WITH，它允许在多个地方使用临时结果 (link)。

---

我认为这可以满足您的需求并同时丢弃空行(如果您愿意的话)。我们基本上选择了我们当前正在查看的行之前的所有品牌，如果它与当前行之间不存在“品牌行”，那么我们就采用它。

WITH t AS
   (SELECT
      ROW_NUMBER() OVER () AS i,
      *
   FROM
      Staging_Data
   )
SELECT
   a.COL1,
   a.COL2,
   a.COL3,
   (SELECT b.COL1 FROM t b WHERE b.COL2 IS NULL AND b.i <= a.i AND NOT EXISTS(
      SELECT * FROM t c WHERE c.COL2 IS NULL AND c.i <= a.i AND c.i > b.i)
   ) StoreBrand
FROM
   t a
WHERE -- I don't think you need those rows? Otherwise remove it.
   a.COL2 IS NOT NULL

这可能有点令人困惑。 t 是我们用 您的查询定义的临时表。 a、b 和c 是t 的别名。我们也可以写成 FROM t AS a 来让它更明显。