I have a table with ID's, dates, and values. I'd like to retrieve each unique ID (date and value) the first time the value moves specifically from 0 to any positive number.

ID    DATE        Value   
1    2019-01-31     0
2    2019-02-27     0
3    2019-03-31     0

2    2019-01-31     5
1    2019-02-31     1
3    2019-04-31     5
2    2019-04-30     5
1    2019-05-31     10

3    2020-01-31     0
2    2020-02-28     3
1    2019-06-31     5
3    2020-04-30     5

Desired Output:

 ID    DATE        Value   
    1    2019-02-31     1
    2    2019-02-28     3
    3    2019-04-31     5

I'm trying to accomplish this in snowflake, not sure if that impacts anything.

CodePudding user response：

QUALIFY and ROW_NUMBER() can be used for this:

If you want the first non-zero value... but you did ask for that..

SELECT * 
FROM values
    (1, '2019-01-31'::date,0   ),
    (2, '2019-02-27'::date,0   ),
    (3, '2019-03-31'::date,0   ),
    (2, '2019-01-31'::date,5   ),
    (1, '2019-02-28'::date,1   ),
    (3, '2019-04-30'::date,5   ),
    (2, '2019-04-30'::date,5   ),
    (1, '2019-05-31'::date,10  ),
    (3, '2020-01-31'::date,0   ),
    (2, '2020-02-28'::date,3   ),
    (1, '2019-06-30'::date,5   ),
    (3, '2020-04-30'::date,5   )
    t(ID ,   DATE  ,      Value   )
QUALIFY value > 0 AND row_number() over(partition by id, value > 0 order by date ) = 1;
ORDER BY 1,2

The trick to note is that you want to exclude all values not above 0 and to partition the row_number by that also.

gives:

take two:

First transition from 0 to non-zero:

so lets just order the data so we are talking about the same things:

this can be done with two nested QUALIFY's:

SELECT * FROM (
    SELECT *
    FROM values
        (1, '2019-01-31'::date,0   ),
        (2, '2019-02-27'::date,0   ),
        (3, '2019-03-31'::date,0   ),
        (2, '2019-01-31'::date,5   ),
        (1, '2019-02-28'::date,1   ),
        (3, '2019-04-30'::date,5   ),
        (2, '2019-04-30'::date,5   ),
        (1, '2019-05-31'::date,10  ),
        (3, '2020-01-31'::date,0   ),
        (2, '2020-02-28'::date,3   ),
        (1, '2019-06-30'::date,5   ),
        (3, '2020-04-30'::date,5   )
        t(ID ,   DATE  ,      Value   )
    QUALIFY lag(value)over(partition by id order by date) = 0
) 
QUALIFY row_number() over(partition by id order by date ) = 1
ORDER BY 1,2

gives:

ANSI SQL:

If you need ANSI SQL you should use this form:

SELECT 
    b.ID,
    b.DATE,
    b.Value
FROM (
    SELECT 
        a.ID,
        a.DATE,
        a.Value,
        row_number() over(partition by a.id order by a.date ) as rn
    FROM (
        SELECT 
            ID,
            DATE,
            Value,
            lag(value)over(partition by id order by date) as lag_val
        FROM table_data
    ) AS a
    WHERE a.lag_val = 0
) AS b
WHERE b.rn = 1
ORDER BY 1,2

I tend to find that it's cleaner to express the desired output in the smallest code, so it's the most expressive to the task at hand.