BigQuery. CASE WHEN-CodePudding

I am working on BigQuery. I need to created a query that counts if the version is a fix or not. The first version is not a fix and the ones that follow are a fix. Unless the first version has been created by system, in that case, the first and second versions are not a fix. Now my query count only the first version as a non-fix, regardless of who created it.

My query is:

Select 


CASE WHEN ROW_NUMBER() 
OVER (PARTITION BY id_site ORDER BY timestamp ASC) = 1 THEN false ELSE true END is_fix,

FROM table1

Table1

ID  ID_site Timestamp   Version    version_created_by
1   1       1/1/2022    4624653    system
2   1       1/2/2022    4624651    1234
3   1       1/3/2022    4624655    4567
4   2       5/2/2022    4567830    1234
5   2       5/3/2022    4567835    5678
6   3       5/4/2022    7567836    8907
7   3       5/5/2022    7890000    9807

Expected result:

ID  ID_site Timestamp   Version    version_created_by   is_fix
1   1       1/1/2022    4624653    system               FALSE
2   1       1/2/2022    4624651    1234                 FALSE
3   1       1/3/2022    4624655    4567                 TRUE
4   2       5/2/2022    4567830    1234                 FALSE
5   2       5/3/2022    4567835    5678                 TRUE
6   3       5/4/2022    7567836    8907                 FALSE
7   3       5/5/2022    7890000    9807                 TRUE

CodePudding user response：

Try below approach

SELECT 
    *, 
    CASE WHEN ROW_NUMBER() OVER (
        PARTITION BY id_site 
        ORDER BY timestamp DESC
        ) <= t.cnt 
    THEN true ELSE false 
    END is_fix,
FROM (
    Select 
    *,
    CASE WHEN version_created_by = 'system' THEN 2 ELSE 1 END cnt
    FROM table1
) as t
ORDER BY id

my test data & result are ...

id  id_site timestamp               version version_created_by
1   1       2022-01-01T00:00:00Z    4624653 system
2   1       2022-01-02T00:00:00Z    4624651 1234
3   1       2022-01-03T00:00:00Z    4624655 4567
4   2       2022-05-02T00:00:00Z    4567830 1234
5   2       2022-05-03T00:00:00Z    4567835 5678
6   3       2022-05-04T00:00:00Z    7567836 8907
7   3       2022-05-05T00:00:00Z    7890000 9807

id  id_site timestamp               version version_created_by  cnt is_fix
1   1       2022-01-01T00:00:00Z    4624653 system              2   false
2   1       2022-01-02T00:00:00Z    4624651 1234                1   false
3   1       2022-01-03T00:00:00Z    4624655 4567                1   true
4   2       2022-05-02T00:00:00Z    4567830 1234                1   false
5   2       2022-05-03T00:00:00Z    4567835 5678                1   true
6   3       2022-05-04T00:00:00Z    7567836 8907                1   false
7   3       2022-05-05T00:00:00Z    7890000 9807                1   true

To match the format with the expected result,

SELECT * EXCEPT (cnt)
FROM (
    SELECT 
    *, 
    CASE WHEN ROW_NUMBER() OVER (
        PARTITION BY id_site 
        ORDER BY timestamp DESC
        ) <= t.cnt 
    THEN true ELSE false 
    END is_fix,
    FROM (
        Select 
        *,
        CASE WHEN version_created_by = 'system' THEN 2 ELSE 1 END cnt
        FROM table1
    ) as t
)
ORDER BY id

CodePudding user response：

You can check if the 1st version was created by 'system' with FIRST_VALUE() window function:

SELECT *,
       CASE ROW_NUMBER() OVER (PARTITION BY id_site ORDER BY timestamp)
         WHEN 1 THEN false
         WHEN 2 THEN FIRST_VALUE(version_created_by) OVER (PARTITION BY id_site ORDER BY timestamp) <> 'system' 
         ELSE true
       END is_fix  
FROM table1;

See the demo (for MySql).