SQL row_number() - how to ignore nulls?-CodePudding

I need to add counters to users activity, using this query:

select PERSON_ID, TIMESTAMP, 
  row_number() over (partition by  PERSON_ID order by  TIMESTAMP asc) as PERSON_COUNTER
from table1;

This works well, but it counts also the case where PERSON_ID is null and assigns counters as if null was a name of a user. What I want is the counter to be null in these cases - any ideas?

CodePudding user response：

Yes you can use a query like below

select 
PERSON_ID, TIMESTAMP, CASE WHEN PERSON_ID IS NULL THEN NULL ELSE PERSON_COUNTER END AS PERSON_COUNTER
from
(
select PERSON_ID, TIMESTAMP, 
  row_number() over (partition by  PERSON_ID order by  TIMESTAMP asc) as PERSON_COUNTER
from table1
) t;

CodePudding user response：

It will be more elegant and without subqueries:

select PERSON_ID, TIMESTAMP, 
  iff(PERSON_ID is null, null, row_number() over (partition by  PERSON_ID order by  TIMESTAMP asc)) as PERSON_COUNTER
from table1;

CodePudding user response：

Better to filter Nulls

      select PERSON_ID, TIMESTAMP, 
     row_number() over (partition by  PERSON_ID order by  
     TIMESTAMP asc) as PERSON_COUNTER
     from table1 where PERSON_ID IS NOT NULL;

CodePudding user response：

Yes, the counter keeps counting, but you don't really care about the values for rows where PERSON_ID is null. So just discard them

select PERSON_ID, 
       CASE PERSON_ID
         WHEN NULL THEN NULL
         ELSE PERSON_COUNTER
       END as PERSON_COUNTER
FROM (
  select PERSON_ID, TIMESTAMP, 
    row_number() over (partition by  PERSON_ID order by  TIMESTAMP asc) as PERSON_COUNTER
  from table1) T;

Or even add a WHERE clause on the same level to get rid of the rows if you don't care about them at all