Self-referential CASE WHEN clause in SQL

Question

I'm trying to migrate some poorly formed data into a database. The data comes from a CSV, and is first loaded into a staging table of all varchar columns (as I cannot enforce type safety at this stage).

The data might look like

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

The first colum is a mixture of company names as pseudo-headers, and dates for which colum 2 and 3 data is relevant. I'd like to start transforming the data by creating a 'Brand' column - where 'StoreBrand' is the value of Col1 if Col2 is NULL, or the previous row's StoreBrand otherwise. Comething like:

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

I wrote this:

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;

But the database (postgres in this case, but we're considering alternatives so the most diverse answer is preferred) chokes on LAG(StoreBrand) because that's the derived column I'm creating. Invoking LAG(Col1) only populates the first row's real data:

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

My goal would be a StoreBrand column which is the first value of COL1 for all date values before the next brand name:

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

The value of StoreBrand when Col2 and Col3 are null is inconsequential - that row will be dropped as part of the conversion process. The important thing is associating the data rows (i.e. those with dates) with their brand.

Is there a way to reference the previous value for the column that I'm missing?

And did you import the data with some sort of line number column (say, `serial`) that preserves the original ordering. — Gordon Linoff, Apr 04 '16 at 19:07
Gordon, the database is maintaining the order and something like row_number() can be used to get a specific value if that helps — J. Doe, Apr 04 '16 at 19:14
It will be much easier and more convenient to use a plpgsql instead of pure sql. Of course if you are not have something against it. — Abelisto, Apr 04 '16 at 19:47

maraca · Accepted Answer · 2016-04-04T20:12:12.743

Edit for people who find this question through a search engine:

The trick was to use WITH which allows to use a temporary result at several places (link).

I think this does what you want and discards the null rows at the same time (if you want to). We basically select all the brands before the row we are currently looking at and if no "brand row" exists between it and the current row then we take it.

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

It can be a bit confusing. t is a temporary table we defined with your query. And a, b and c are aliases for t. We could also write FROM t AS a to make it more obvious.

OK first pass, this looks good! Can you explain to me where the alias 'a' is defined? Near as I can tell it just exists in this query (might just be me struggling with all the single character names) — J. Doe, Apr 04 '16 at 19:43
@J.Doe added explanation for it. You can also choose a longer name than `t`, e.g. Stage_Two or something to make it more readable. — maraca, Apr 04 '16 at 19:48

Gordon Linoff · Answer 2 · 2016-04-05T01:28:48.637

0

I think I understand what you want. Technically, you want the ignore nulls option on lag(), so it would look like this:

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

The only problem? Postgres doesn't support ignore nulls.

But, you can do pretty much the same thing with a subquery. The idea is to assign a grouping identifier to each group. And this is a cumulative count of the valid brand names. Then a simple max() aggregation works:

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
     );

edited Apr 05 '16 at 01:28

answered Apr 04 '16 at 19:14

Gordon Linoff

1,242,037
58
646
786

1

Not sure how this is supposed to be working - col1 is varchar, how are you going to sum it? Postgres responded to this query with '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 Apr 04 '16 at 19:24
I think you want to use `sum(... then 1 end)...` rather than `col1`. – shawnt00 Apr 04 '16 at 19:46

Self-referential CASE WHEN clause in SQL

2 Answers2