首页 文章

SQL中的自引用CASE WHEN子句

提问于
浏览 次
3

我正在尝试将一些格式不佳的数据迁移到数据库中 . 数据来自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

第一个列是公司名称作为伪 Headers 的混合，以及列2和3数据相关的日期 . 我想通过创建“品牌”列来开始转换数据 - 其中，如果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，但是我们正在考虑替代方案，所以最多样化的答案是首选的）chrom on LAG(StoreBrand) 因为's the derived column I' m创建 . 调用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的值无关紧要 - 该行将作为转换过程的一部分被删除 . 重要的是将数据行（即具有日期的行）与其品牌相关联 .

有没有办法引用我缺少的列的先前值？

sql postgresql self-reference

2 回答

  • 0

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

    诀窍是使用 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 是我们定义 with 您的查询的临时表 . 并且 a ， b 和 c 是 t 的别名 . 我们也可以写 FROM t AS a 以使其更加明显 .

    回复于
  • 1

    我想我明白你想要什么 . 从技术上讲，您需要 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
     );
    回复于

相关问题