SQL中的自引用CASE WHEN子句

Question

SQL中的自引用CASE WHEN子句

4

我将尝试将一些格式不规范的数据迁移到数据库中。这些数据来自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

第一列是伪标题的公司名称和相关的第二、三列数据的日期混合在一起。我想通过创建一个‘品牌’列来开始转换数据，其中‘StoreBrand’是如果Col2为NULL则为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的值是无关紧要的 - 该行将作为转换过程的一部分被删除。重要的是将数据行（即具有日期的行）与其品牌相关联。

是否有一种方法可以引用我丢失的列的先前值？

- J. Doe

1

结果应该长什么样？ - Vamsi Prabhala

你是否导入了带有某种行号列（例如 serial）以保留原始顺序的数据？ - Gordon Linoff

Gordon，数据库正在维护顺序，如果需要的话可以使用类似row_number()的函数来获取特定值。 - J. Doe

使用plpgsql而不是纯sql会更加容易和方便。当然，如果您没有反对的话。 - Abelisto

2个回答

0

我想我明白你的意思。从技术上讲，你想要在lag()函数中使用ignore nulls选项，代码应该是这样的：

select lag(case when col1 not like '%/%/%' then col1 end ignore nulls) over (order by linenumber) as brandname

唯一的问题？Postgres不支持ignore nulls。

但是，你可以用子查询做几乎相同的事情。思路是为每个组分配一个分组标识符。这是有效品牌名称的累积计数。然后使用简单的max()聚合即可：

select t.*,
       max(case when col1 not like '%/%/%' then col1 end) over (partition by grp) as brand
from (select t.*,
             sum(case when col1 not like '%/%/%' then 1 end) over
                 (order by linenumber) as grp
      from t
     );

- Gordon Linoff

1

不确定这应该如何工作 - col1 是 varchar 类型，你怎么可能对它求和？Postgres 对此查询的响应是“ERROR: function sum(character varying) does not exist LINE 5: SUM(CASE WHEN Col1 NOT LIKE '%' THEN Col1 END) OVER (ORDER B... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts。” - J. Doe

我认为你想使用 sum(... then 1 end)... 而不是 col1。 - shawnt00

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- maraca · Accepted Answer

针对那些通过搜索引擎找到这个问题的人进行编辑：

关键是要使用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 来使其更加明显。